VBA for Excel – Syntax Examples and Samples

When switching between programming languages, it can be a pain to remember the syntax.

To help, here is a “cheat sheet” of syntax for VBA for Excel.

In addition, there are two handy samples of VBA code:

• Identifying the worksheet’s lowest non-empty ROW (into a variable called “last_row”) and right-most non-empty COLUMN (into a variable called “last_col”)
• Writing all the values of a worksheet’s column A (each value on a new row) as a text file to the active directory

First, some examples of VBA syntax:

Sub syntax_examples()
    
    ' INITIALIZING VBA VARIABLES
    
    ' Variable in data types Integer, Long (i.e., long integer), Single (i.e., single-length real numbers), Double (i.e., double-length real numbers), and String
    ' Dim is Private, available to only that sub
    ' Public variables available to other sub routines
    
    Dim x, y, z As Integer
    Public string_variable As String
    
    ' Initialize arrays with ()
    Dim data_values() As String
    Dim count_if()
    
    
    ' LOOPS
    
    For x = 1 To raw_last_col
        Worksheets("Input Page").Cells(2 + x, 1).Value = Worksheets("Raw from Qualtrics").Cells(1, x).Value
        Worksheets("Input Page").Cells(2 + x, 2).Value = Worksheets("Raw from Qualtrics").Cells(2, x).Value
        Worksheets("Input Page").Cells(2 + x, 4).Value = Worksheets("Raw from Qualtrics").Cells(1, x).Value
    Next x

                
    ' CONDITIONAL
                
    If data_values(i) = "" Then
        first_empty = i
    Else
        x = 2
    End If


    If dup_list <> "" Then MsgBox ("WARNING! Dup List is empty")


    ' STRING FUNCTIONS
    
    If Left(Worksheets("Sheets1").Cells(1, 1).Value, 4) = "test" Then MsgBox ("It's a test")


    ' MANIPULATING WORKSHEETS
    
    ' Directly setting the value of cell in row 2, col 1
    Worksheets("Sheet1").Cells(2, 1).Value = "Setting value directly"
    
    'Clearing contents and formats for a certain range
    Worksheets("Sheet1").Range("A:D").ClearContents
    Worksheets("Sheet1").Range("A1:D5").ClearFormats
    
    ' Clearing the contents (but not the formats) for an entire worksheet
    Sheets("Sheet1").Cells.ClearContents
    

    ' Changing the shading of a range of cells (defined with variables, not hard-coded as with "A1:D5")
    With Worksheets("Sheet1")
        .Range(.Cells(3, 1), .Cells(2 + raw_last_col, 2)).Interior.Color = RGB(241, 240, 231)
    End With


End Sub

Next, a sample of VBA code for identifying the worksheet’s lowest non-empty ROW (into a variable called “last_row”) and right-most non-empty COLUMN (into a variable called “last_col”)

Sub identify_last_row_and_column()
        
    ' Identifies the worksheet's lowest non-empty ROW (var = last_row) and right-most non-empty COLUMN (var = last_col)
    
    ' NOTE: returns zero for last_row and last_col if the whole worksheet is empty

    With Sheets("Sheet1")
        ' Identifies the lowest non-empty ROW the worksheet:
        If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
            last_row = .Cells.Find(What:="*", _
                          After:=.Range("A1"), _
                          Lookat:=xlPart, _
                          LookIn:=xlFormulas, _
                          SearchOrder:=xlByRows, _
                          SearchDirection:=xlPrevious, _
                          MatchCase:=False).Row
        Else
            last_row = 0
        End If
        
        ' Identifies the right-most non-empty COLUMN the worksheet:
        If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
            last_col = .Cells.Find(What:="*", _
                          After:=.Range("A1"), _
                          Lookat:=xlPart, _
                          LookIn:=xlFormulas, _
                          SearchOrder:=xlByColumns, _
                          SearchDirection:=xlPrevious, _
                          MatchCase:=False).Column
        Else
            last_col = 0
        End If
    
    End With

    MsgBox ("Lowest non-empty ROW is " & last_row & " and right-most non-empty COLUMN is " & last_col)

End Sub

Lastly, a sample of VBA code for writing all the values of a worksheet’s column A (each value on a new row) as a text file to the active directory

Sub write_text_file()
    ' Writes all the values of a worksheet's column A (each value on a new row) as a text file (filename = output.txt) to the active directory
    
    ' NOTE: For this code to work, the workbook must be saved as a "macro-enabled workbook" (i.e., .xlsm).
    '       Otherwise, the error "Run-time error '75' /n/n Path/File access error" is thrown.
   
    my_file = ActiveWorkbook.Path & "\output.txt"
    Open my_file For Output As #1
    
    last_entry = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
    For x = 1 To last_entry
        Print #1, Worksheets("Sheet1").Cells(x, 1).Value
    Next x
        
    Close #1
    
    MsgBox ("File created as ... " & my_file & vbCrLf & vbCrLf & "With " & last_entry & " rows")

End Sub

– Eric DeRosia