Excel Macro Examples

The following simple Excel macro examples illustrate some of the learning points that have been covered during the Excel-VBA-Tutorial.


Excel Macro Example 1

The following subroutine was initially used to illustrate the use of comments. However, it also contains examples of the use of variable declaration, Excel cell referencing, a 'For' loop, 'If' statements, and a message box.

' Subroutine 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"
    Else
        MsgBox "String " & sFindText & " found in cell A" & iRowNumber
    End If

End Sub

Excel Macro Example 2

The following subroutine is an example of use of a Do While Loop. However, it also contains examples of the use of variable declaration, Excel cell referencing and the 'If' statement.

' Subroutine 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
        Else
            ' 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
    Loop

End Sub

Excel Macro Example 3

The following subroutine reads values from the cells in 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 example you might expect something to be done with the array after collecting the data into it!

' Subroutine 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
    Loop

End Sub

Excel Macro Example 4

The following subroutine reads values from Column A of the "Sheet2" Worksheet and performs arithmetic operations on the values, before inserting the result into Column A of the current active Worksheet. This example shows the use of Excel objects. In particular, the subroutine accesses the Columns object and shows the way in which this is accessed from the Worksheet object. It also illustrates how, when accessing a cell or cell range on the current active Worksheet, we do not need to specify which Worksheet we are referencing.

' Subroutine 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

    Loop

End Sub

Excel Macro Example 5

The following macro is linked to the Excel Event, 'SelectionChange', which is 'fired' every time a different cell or range of cells is selected within a worksheet. The Subroutine is very simple, in that it just displays a message box if cell B1 is selected, but it does provide a useful example of vba code linked to an Excel Event.

' 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 subroutine illustrates the use of the On Error and the Resume statements, for handling runtime errors. The code also includes examples of opening, and reading data from a file

' Subroutine 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)

        DataWorkbook.Close

    Exit Sub

ErrorHandling:

    ' 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"
    Resume

End Sub
Return to the Excel VBA Tutorial Page

Valid XHTML 1.0 Transitional Valid CSS!
Disclaimer   Privacy Policy
Copyright © 2008-2014 ExcelFunctions.net