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