Excel Macro Examples

The following simple Excel macro examples were used in the Excel-VBA-Tutorial to illustrate specific features of VBA.

Excel Macro Example 1

The following Sub procedure was initially used to illustrate the use of comments in your VBA code. However, this code also contains examples of the use of variable declaration, Excel cell referencing, a 'For' loop, 'If' statements, and a message box.

' Sub procedure to search cells A1-A100 of the current active
' sheet, and find the cell containing the supplied string
Sub Find_String(sFindText As String)
Dim i As Integer           ' Integer used in 'For' loop
Dim iRowNumber As Integer   ' Integer to store result in
iRowNumber = 0
' Loop through cells A1-A100 until 'sFindText' is found
For i = 1 To 100
If Cells(i, 1).Value = sFindText Then
' A match has been found to the supplied string
' Store the current row number and exit the 'For' Loop
iRowNumber = i
Exit For
End If
Next i
' Pop up a message box to let the user know if the text
' string has been found, and if so, which row it appears on
If iRowNumber = 0 Then
MsgBox "String " & sFindText & " not found"
MsgBox "String " & sFindText & " found in cell A" & iRowNumber
End If
End Sub

Excel Macro Example 2

The following Sub procedure is an example of use of a Do While Loop. It also contains examples of variable declarations, Excel cell referencing and the 'If' statement.

' Sub procedure to list the Fibonacci series for all values below 1,000
Sub Fibonacci()
Dim i As Integer   ' counter for the position in the series
Dim iFib As Integer   ' stores the current value in the series
Dim iFib_Next As Integer   ' stores the next value in the series
Dim iStep As Integer   ' stores the next step size
' Initialise the variables i and iFib_Next
i = 1
iFib_Next = 0
' Do While loop to be executed as long as the value of the
' current Fibonacci number exceeds 1000
Do While iFib_Next < 1000
If i = 1 Then
' Special case for the first entry of the series
iStep = 1
iFib = 0
' Store the next step size, before overwriting the
' current entry of the series
iStep = iFib
iFib = iFib_Next
End If
' Print the current Fibonacci value to column A of the
' current Worksheet
Cells(i, 1).Value = iFib
' Calculate the next value in the series and increment
' the position marker by 1
iFib_Next = iFib + iStep
i = i + 1
End Sub

Excel Macro Example 3

The following Sub procedure reads values from the cells of column A of the active spreadsheet, until it encounters a blank cell. The values are stored in an array. This simple Excel macro example illustrates the use of dynamic arrays and also the use of the Do Until loop - although in a real-life program you might expect something to be done with the array after collecting the data into it!

' Sub procedure store values in Column A of the active Worksheet
' into an array

Sub GetCellValues()
Dim iRow As Integer            ' stores the current row number
Dim dCellValues() As Double  ' array to store the cell values
iRow = 1
ReDim dCellValues(1 To 10)
' Do Until loop to extract the value of each cell in column A
' of the active Worksheet, as long as the cell is not blank
Do Until IsEmpty(Cells(iRow, 1))
' Check that the dCellValues array is big enough
' If not, use ReDim to increase the size of the array by 10
If UBound(dCellValues) < iRow Then
ReDim Preserve dCellValues(1 To iRow + 9)
End If
' Store the current cell in the CellValues array
dCellValues(iRow) = Cells(iRow, 1).Value
iRow = iRow + 1
End Sub

Excel Macro Example 4

The following Sub procedure reads values from Column A of the "Sheet2" Worksheet and performs arithmetic operations on the values. The resulting values are printed out into Column A of the current active Worksheet.

This macro example shows the use of Excel objects. In particular, the Sub accesses the 'Columns' object and illustrates how this is accessed from the 'Worksheet' object. The example also illustrates how, when accessing a cell or cell range on the current active Worksheet, you do not need to include a reference to the Worksheet.

' Sub procedure to loop through the values in Column A of the Worksheet
' "Sheet2", perform arithmetic operations on each value, and write the
' result into Column A of the current Active Worksheet ("Sheet1")
Sub Transfer_ColA()
Dim i As Integer
Dim Col As Range
Dim dVal As Double
' Set the variable 'Col' to be Column A of Sheet 2
Set Col = Sheets("Sheet2").Columns("A")
i = 1
' Loop through each cell of the column 'Col' until
' a blank cell is encountered
Do Until IsEmpty(Col.Cells(i))
' Apply arithmetic operations to the value of the current cell
dVal = Col.Cells(i).Value * 3 - 1
' The command below copies the result into Column A
' of the current Active Worksheet - no need to specify
' the Worksheet name as it is the active Worksheet.
Cells(i, 1) = dVal
i = i + 1
End Sub

Excel Macro Example 5

The following macro provides an example of vba code linked to an Excel Event. The macro is linked to the Excel Event, 'SelectionChange', which is activated every time a different cell or range of cells is selected within a worksheet. The code displays a message box if cell B1 is selected.

' Code to display a Message Box if Cell B1 of the current Worksheet
' is selected.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' Check if the selection is cell B1
If Target.Count = 1 And Target.Row = 1 And Target.Column = 2 Then
' The selection IS cell B1, so carry out required actions
MsgBox "You have selected cell B1"
End If
End Sub

Excel Macro Example 6

The following Sub procedure illustrates the use of the On Error and Resume statements, for handling runtime errors. The code also includes examples of opening, and reading data from a file

' Sub procedure to set the supplied values, Val1 and Val2 to the values
' in cells A1 and B1 of the Workbook "Data.xls" in the C:\ directory
Sub Set_Values(Val1 As Double, Val2 As Double)
Dim DataWorkbook As Workbook
On Error GoTo ErrorHandling
' Open the Data Workbook
Set DataWorkbook = Workbooks.Open("C:\Documents and Settings\Data")
' Set the variables Val1 and Val2 from the data in DataWorkbook
Val1 = Sheets("Sheet1").Cells(1, 1)
Val2 = Sheets("Sheet1").Cells(1, 2)
Exit Sub
' If the file is not found, ask the user to place it into
' the correct directory and then resume
MsgBox "Data Workbook not found;" & _
       "Please add the workbook to C:\Documents and Settings and click OK"
End Sub