Excel VBA Coding, Macro Recording Examples — Free Download

Excel VBA Coding, Macro Recording Examples: When working with Visual Basic for Applications (VBA), you can write code directly into modules without relying on the Macro Recorder—as long as you understand how to script the task yourself. This approach works perfectly for customizing prebuilt code blocks or even trimming unnecessary steps created by the recorder. Once you create a new module in the VBA editor, you can freely add commands to achieve your desired functionality. For example, imagine designing a procedure named FillData. Here, the green text inside this procedure serves as comments, which don’t affect the code’s execution. Meanwhile, blue-highlighted words represent VBA’s reserved keywords, forming the backbone of the language.

To test your code, use the toolbar buttons: specifically, the first one runs the script, the second pauses execution, and the third stops it entirely. For complex tasks, VBA’s IntelliSense feature simplifies coding. For instance, when you type a period after an object, a dropdown list of its methods and properties appears to guide you. However, mastering Excel’s object hierarchy remains critical here. Take the expression Cells(5, 3). Value, which accesses the value in the 5th row and 3rd column. Here, each dot in the syntax drills deeper into the object model, and over time, these chains may become lengthy. To decode complex structures, select any object, method, or property and press F1 to instantly pull up detailed help documentation.

While the Macro Recorder remains a powerful tool for automating repetitive tasks, you’ll need to click Record Macro under the View tab to start. When you do, a dialog box prompts you to name your macro, choose its storage location, and optionally assign a keyboard shortcut. Once recording begins, every Excel action—from selecting cells to entering formulas—translates into VBA code in real time. After stopping the recording, review and tweak the generated code. For example, you could turn a multi-step report formatting task into a one-click automation, thereby eliminating hours of manual work. Finally, saved macros live in the module window and can be reused across projects, promoting efficient code recycling.

1. Assigning Values and Formatting Cells Code

Sub EditCell()
    Range("A1").Value = "Hello World"     ' Write text to cell A1
    Range("A1").Font.Bold = True          ' Bold the text
    Range("A1").Interior.Color = RGB(255, 255, 0) ' Set background to yellow
    Range("A1").HorizontalAlignment = xlCenter    ' Center-align text
End Sub

What Does It Do?
Writes “Hello World” to cell A1, applies bold formatting, yellow background, and centers the text.

2. Macro Recorder-Generated Example Code

Sub RecordMacro()
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "=RAND()"    ' Generate a random number
    Range("B2").Copy
    Range("C2").PasteSpecial Paste:=xlPasteValues ' Paste as value
End Sub

What Does It Do?
A basic macro-generated code: Adds a random number to B2 and pastes the value to C2.

3. InputBox for User Input Code

Sub GetUserInput()
    Dim userInput As String
    userInput = InputBox("Please enter text:", "Data Entry") ' Open InputBox
    
    If userInput <> "" Then
        Range("A3").Value = userInput    ' Write to A3 if input is not empty
    Else
        MsgBox "No data entered!", vbExclamation ' Warning message
    End If
End Sub

What Does It Do?
Prompts the user for text and writes it to A3. Displays a warning if no input is provided.

4. For Loop to Auto-Fill Rows Code

Sub FillRows()
    Dim i As Integer
    For i = 1 To 10
        Cells(i, 1).Value = i    ' Fill column 1 with numbers 1 to 10
    Next i
End Sub

What Does It Do?
Fills cells A1 to A10 with numbers 1 through 10.

5. Error Handling Code (On Error Resume Next)

Sub ErrorHandling()
    On Error Resume Next    ' Ignore errors
    Dim result As Double
    result = 10 / 0         ' Division by zero error
    
    If Err.Number <> 0 Then
        MsgBox "Error: " & Err.Description, vbCritical ' Show error message
        Err.Clear           ' Clear error
    End If
    On Error GoTo 0         ' Disable error handling
End Sub

What Does It Do?
Catches division-by-zero errors and alerts the user.

6. Worksheet_Change Event for Dynamic Control Code

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A:A")) Is Nothing Then  ' If column A changes
        Target.Offset(0, 1).Value = Now()    ' Add timestamp to column B
    End If
End Sub

What Does It Do?
Adds a timestamp to column B when any cell in column A is modified.

7. Custom Function (UDF) for Tax Calculation Code

Function CalculateTax(Amount As Double) As Double
    CalculateTax = Amount * 0.18    ' Calculate 18% tax
End Function

How to Use?
In Excel, type =CalculateTax(100) to get a result of 18.

8. Conditional Logic (If-Else) with MsgBox Code

Sub CheckValue()
    Dim value As Double
    value = Range("C5").Value
    
    If value > 100 Then
        MsgBox "Value exceeds 100! Warning!", vbExclamation ' Alert
    Else
        MsgBox "Value is within normal range.", vbInformation    ' Info message
    End If
End Sub

What Does It Do?
Checks cell C5: Alerts if the value exceeds 100; otherwise, shows a confirmation.

Notes for Using the Code:

1. Open the VBA editor in Excel with Alt + F11.
2. Insert a new module and paste the code.
3. Run macros via Alt + F8.