Contents
The following simple Excel macro examples illustrate some of the features and techniques described in the Excel VBA Tutorial.
Excel Macro Example 1
Initially, this procedure Sub was given as an example of using comments in VBA code. However, here you can also see how variables are declared, how Excel cell references work, the use of a loop The, conditional operator If and displaying a message box.
'The Sub procedure searches for a cell containing the given string 'in the cell range A1:A100 of the active sheet Sub Find_String(sFindText As String) Dim i As Integer storing the result iRowNumber = 0 'Looks through cells A1:A100 one by one until the string is found sFindText For i = 1 To 100 If Cells(i, 1).Value = sFindText Then 'If a match with the given string is found ' save the current row number and exit the loop For iRowNumber = i Exit For End If Next i 'Tell the user in a pop-up window whether the searched row is found 'If the given row is found, indicate in which cell the match was found If iRowNumber = 0 Then MsgBox "Row " & sFindText & " not found" Else MsgBox "String " & sFindText & " found in cell A" & iRowNumber End If End Sub
Excel Macro Example 2
Next procedure Sub – an example of using a cycle Do while. Here you can also see how variables are declared, working with Excel cell references, and applying a conditional operator. If.
'Sub procedure outputs Fibonacci numbers up to 1000 Sub Fibonacci() Dim i As Integer 'Counter to indicate element position in sequence Dim iFib As Integer 'Stores current value of sequence Dim iFib_Next As Integer 'Stores next value of sequence Dim iStep As Integer 'Stores size of the next increment 'Initialize the variables i and iFib_Next i = 1 iFib_Next = 0 'Do While loop will be executed until the value of 'the current Fibonacci number exceeds 1000 Do While iFib_Next < 1000 If i = 1 Then 'Special case for the first element iStep = 1 iFib = 0 Else 'Save the size of the next increment before overwriting 'the current value of the sequence iStep = iFib iFib = iFib_Next End If 'Display the current Fibonacci number in column A of the active worksheet 'in the row with index i Cells(i , 1).Value = iFib 'Compute the next Fibonacci number and increment the element position index by 1 iFib_Next = iFib + iStep i = i + 1 Loop End Sub
Excel Macro Example 3
This procedure Sub scans the cells of a column A active sheet until it encounters an empty cell. The values are written to an array. This simple Excel macro shows how to work with dynamic arrays and how to use a loop. Do Until. In this example, we will not perform any actions with the array, although in real programming practice, after the data is written to the array, such actions are usually performed on them.
'The Sub procedure saves the cell values of column A of the active sheet in the array Sub GetCellValues() Dim iRow As Integer 'Store the number of the current row Dim dCellValues() As Double 'An array to store cell values iRow = 1 ReDim dCellValues(1 To 10) 'Do loop Until iterates through the cells of column A of the active sheet 'and extracts their values into an array until an empty cell is encountered Do Until IsEmpty(Cells(iRow, 1)) 'Check that the dCellValues array has sufficient size array by 10 with ReDim If UBound(dCellValues) < iRow Then ReDim Preserve dCellValues(1 To iRow + 9) End If 'Save the value of the current cell in the array dCellValues dCellValues(iRow) = Cells(iRow, 1).Value iRow = iRow + 1 Loop End Sub
Excel Macro Example 4
In this example, the procedure Sub reads values from a column A worksheet Sheet2 and performs arithmetic operations on them. The results are entered in the cells of the column A on the active worksheet. This macro demonstrates the use of Excel objects. In particular, the call is made by the procedure Sub to the object Columns, and shows how this object is accessed through the object spreadsheet. It is also shown that when referring to a cell or range of cells on the active sheet, the name of this sheet when writing the link is not required.
'The Sub procedure loops through the values in column A of worksheet Sheet2, 'performs arithmetic operations on each value, and writes the result to 'column A of the active worksheet (Sheet1) Sub Transfer_ColA() Dim i As Integer Dim Col As Range Dim dVal As Double 'Assign to variable Col column A of worksheet Sheet 2 Set Col = Sheets("Sheet2").Columns("A") i = 1 'Using a loop, read the cell values of column Col until an empty cell 'is encountered Do Until IsEmpty(Col.Cells(i)) 'Perform arithmetic operations on the value of the current cell dVal = Col.Cells(i).Value * 3 - 1 'The following command writes the result to column A of the active worksheet no need as it is the active sheet. Cells(i, 1) = dVal i = i + 1 Loop End SubExcel Macro Example 5
This macro shows an example of VBA code that listens for an Excel event. The event to which the macro is bound occurs each time a cell or range of cells is selected on the worksheet. In our case, when selecting a cell B1, a message box will be displayed.
'This code shows a message box if cell B1 is selected in the current worksheet Private Sub Worksheet_SelectionChange(ByVal Target As Range) 'Check if cell B1 is selected If Target.Count = 1 And Target.Row = 1 And Target.Column = 2 Then 'If cell B1 is selected, perform the required action MsgBox "You have selected cell B1" End If End Sub
Excel Macro Example 6
An example of this procedure shows the use of the operators On Error и DESCRIPTION for error handling. This code also shows an example of opening and reading data from a file.
'The Sub procedure sets the values of cells A1 and B2 to the arguments Val1 and Val1 'from the workbook Data.xlsx located in the folder C:Documents and Settings Sub Set_Values(Val1 As Double, Val2 As Double) Dim DataWorkbook As Workbook On Error GoTo ErrorHandling 'Open workbook with data Set DataWorkbook = Workbooks.Open("C:Documents and SettingsData") 'Assign to variables Val1 and Val2 the values from the given workbook Val1 = Sheets("Sheet1").Cells(1, 1) Val2 = Sheets(" Sheet1").Cells(1, 2) DataWorkbook.Close Exit Sub ErrorHandling: 'If the file is not found, the user will be prompted to place the searched file 'in the correct folder and then continue with the macro MsgBox "Data.xlsx file not found! " & _ "Please add the workbook to the C:Documents and Settings folder and click OK" Resume End Sub