The following simple Excel macro examples were used in the Excel-VBA-Tutorial to illustrate various features of VBA, including code presentation and documentation.
Excel Macro Example 1
Getting started with Excel VBA, Arrays, Conditional statements, Ranges and Cells, Named Ranges, Merged Cells. Conditional formatting using VBA, Workbooks, Working with Excel Tables in VBA and File System Object. Excel® VBA Example Page 1 Excel® VBA Example Page 2. Excel VBA – Information, Tutorials, Examples & Resources. Excel VBA allows you to automate various activities you do in Excel. We use Excel to analyze data, create reports, prepare charts & presentations, do calculations and understand information. When we are doing all these, we spend quite some time, repeating same steps.
The following Sub procedure was initially used to illustrate the use of comments in VBA code. However, this code also contains examples of:
- Variable declaration;
- Referencing cells in a Worksheet;
- A 'For' loop;
- 'If' statements;
- The built-in VBA MsgBox function (which displays a message box to the user).
' Sub procedure to search cells A1-A100 of the current active ' sheet, and find the cell containing the supplied string Dim i As Integer' Integer used in 'For' loop Dim iRowNumber As Integer' Integer to store result ' Loop through cells A1-A100 until 'sFindText' is found For i = 1 To 100 If Cells(i, 1).Value = sFindText Then Next i' A match has been found to the supplied string End If' Store the current row number and exit the 'For' Loop iRowNumber = i Exit For ' 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 Else MsgBox 'String ' & sFindText & ' found in cell A' & iRowNumber End If |
Excel Macro Example 2
The following Sub procedure lists all values in the Fibonacci series, up to 1,000. This provides examples of:
- Variable declarations;
- A Do While Loop;
- Referencing cells in the current Excel Worksheet;
- An '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 ' Do While loop to be executed as long as the value of thei = 1 iFib_Next = 0 ' current Fibonacci number exceeds 1000 Do While iFib_Next < 1000 If i = 1 Then ' Special case for the first entry of the series ElseiStep = 1 iFib = 0 ' Store the next step size, before overwriting the End If' current entry of the series iStep = iFib iFib = iFib_Next ' Print the current Fibonacci value to column A of the ' Calculate the next value in the series and increment' current Worksheet Cells(i, 1).Value = iFib ' the position marker by 1 iFib_Next = iFib + iStep i = i + 1 |
Excel Macro Example 3
The following Sub procedure reads values from the cells of column A of the active Worksheet, until it encounters a blank cell. The values are stored in an array. This simple Excel macro example illustrates the use of:
![100 Excel Vba Examples Pdf Portfolio 100 Excel Vba Examples Pdf Portfolio](/uploads/1/2/5/8/125884256/761103724.jpg)
- Variable declarations;
- Dynamic Arrays;
- A Do Until Loop;
- Referencing cells in the current Excel Worksheet;
- The built-in VBA Ubound function (which returns the highest subscript of an array).
' 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 ' 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 ThenEnd If ' Store the current cell in the CellValues array iRow = iRow + 1dCellValues(iRow) = Cells(iRow, 1).Value |
Excel Macro Example 4
The following Sub procedure reads values from Column A of the Worksheet named 'Sheet2' and performs arithmetic operations on the values. The resulting values are printed out into Column A of the current active Worksheet.
This macro illustrates:
- Variable declarations;
- Excel Objects (specifically, use of the keyword Set and the way the 'Columns' object is accessed from the 'Sheets' object);
- A Do Until Loop;
- Accessing worksheets and cell ranges in the current Excel Workbook.
' 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') Dim i As Integer Dim Col As Range Dim dVal As Double ' Set the variable 'Col' to be Column A of Sheet 2 ' Loop through each cell of the column 'Col' until ' a blank cell is encountered ' Apply arithmetic operations to the value of the current cell ' 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. Loop |
![Vba Vba](http://bardwellparkphysiotherapy.com/a/2018/12/dashboard-samples-in-excel-creating-dashboard-in-excel-using-vba-hr-dashboard-examples-in-excel-creating-dashboard-in-excel-pdf-free-download-dashboard-reports.jpg)
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 uses the built-in MsgBox function to display 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 End SubIf Target.Count = 1 And Target.Row = 1 And Target.Column = 2 Then ' The selection IS cell B1, so carry out required actions End IfMsgBox 'You have selected cell B1' |
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 On Error GoTo ErrorHandling Set DataWorkbook = Workbooks.Open('C:Documents and SettingsData') ' 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' Resume |