This article is part of my series: Excel VBA & Macros – A Step by Step Complete Guide.
The following is a VBA macro that you can’t create by recording. It uses programming concepts that must be entered manually. This macro shows a list of all formulas on the active sheet. The list will be created on a new worksheet.
Sub ListFormulas() 'Create a range object Set InputRange = ActiveSheet.UsedRange 'Add a new sheet Set OutputSheet = Worksheets.Add 'Variable for the output row OutputRow = 1 'Loop through the range For Each cell In InputRange If cell.HasFormula Then OutputSheet.Cells(OutputRow, 1) = "'" & cell.Address OutputSheet.Cells(OutputRow, 2) = "'" & cell.Formula OutputRow = OutputRow + 1 End If Next cell End Sub
This macro may look complicated to you at the first time, but this is really simple when you will break it down. Here’s how this code snippet works:
- Sub ListFormulas(): This statement starts the macro subprocedure.
- ‘Create a range object: A comment that describes the next statement Set InputRange = ActiveSheet.UsedRange.
- Set InputRange = ActiveSheet.UsedRange: This statement creates an object variable that corresponds to the used range on the active sheet.
- ‘Add a new sheet: This comment describes the next statement: Set OutputSheet = Worksheets.Add.
- Set OutputSheet = Worksheets.Add: This statement adds a new worksheet and creates an object variable name OutputSheet.
- ‘Variable for the output row: This is a comment for this statement: OutputRow = 1
- OutputRow = 1: This statement sets 1 for the variable OutputRow.
- ‘Loop through the range: A comment about the next For Each loop
- For Each cell In InputRange: This is starting of For Each loop. The first cell is selected from the InputRange object variable.
- If cell.HasFormula Then: This If-Then statement is checking whether the selected cell (from the InputRange object variable) has any Formula. If any formula then executes the next three statements:
OutputSheet.Cells(OutputRow, 1) = “‘” & cell.Address
OutputSheet.Cells(OutputRow, 2) = “‘” & cell.Formula
OutputRow = OutputRow + 1
- OutputSheet.Cells(OutputRow, 1) = “‘” & cell.Address: This statement put the absolute cell address with an apostrophe ‘ in the first cell of row 1 in the new worksheet opened by this statement: Set OutputSheet = Worksheets.Add.
- OutputSheet.Cells(OutputRow, 2) = “‘” & cell.Formula: This statement put the cell’s formula with an apostrophe ‘ sign in the second cell of row 2 in the new worksheet opened by this statement: Set OutputSheet = Worksheets.Add.
- OutputRow = OutputRow + 1: This statement increases the value of OutputRow by 1.
- End If: End the If-Then construct.
- Next cell: This statement will select the next cell of object variable InputRange and will execute the No. 10 operation again. This will continue until all the cells in InputRange are manipulated.
- End Sub: This statement ends the macro subprocedure.
The following figure shows the result of running this macro — a handy list of all formulas in the worksheet.
This macro is okay, but it is not the perfect one. It doesn’t show any message when it faces any kind of error. For example, if the workbook is protected by its creator, running this macro will show error as this macro adds a new worksheet.
Download Working File
Happy Excelling 🙂