This chapter introduces the Visual Basic for Applications (VBA) macro language—a key component for users who want to customize and automate Excel. This chapter teaches you how to record macros and create simple macro procedures. Subsequent chapters expand upon the topics in this chapter.
A macro is a sequence of instructions that automates some aspect of Excel so that you can work more efficiently and with fewer errors. You may create a macro, for example, to format and print your month-end sales report. After you write the macro, you can then execute it every month. Not only does this save you from having to repeat the formatting steps every month, it also ensures that the exact same formatting is applied.
You don't have to be a computer programmer to create and use simple VBA macros. Once they understand a few basics, even casual users can simply turn on Excel's macro recorder to record their actions and convert them into a VBA macro. When you execute a recorded macro, Excel performs the actions again. Once you get the hang of recorded macros, you can write macros from scratch, including code that performs tasks that can't be recorded. For example, you can write procedures that display custom dialog boxes or process data in a series of workbooks and even create special-purpose add-ins.
If you plan to work with VBA macros, make sure you can see the Developer tab on the Excel Ribbon. The Developer tab, which does not appear by default, contains useful commands for VBA users (see Figure 39.1). To display this tab, follow these steps:
Macros have the potential to cause serious damage to your computer, such as erasing files or installing malware. Consequently, Microsoft has added macro-security features to help prevent macro-related problems.
Figure 39.2 shows the Macro Settings section of the Trust Center dialog box. To display this dialog box, choose Developer ➪ Code ➪ Macro Security.
By default, Excel uses the Disable VBA Macros with Notification option. With this setting in effect, if you open a workbook that contains macros (and the file is not already trusted), the macros will be disabled and Excel will display a security warning above the Formula bar (see Figure 39.3). If you're certain that the workbook comes from a trusted source, click the Enable Content button in the security warning area, and the macros will be enabled. Excel remembers your decision; if you enable the macros, you won't see the security warning the next time you open that file.
Rather than deal with individual workbooks, you may prefer to designate one or more folders as “trusted locations.” Opening workbooks in a trusted location enables macros automatically. You designate trusted folders in the Trusted Locations section of the Trust Center dialog box.
If you store one or more VBA macros in a workbook, you must save the file with an .xlsm filename extension.
When you save a workbook that contains macros (or even an empty VBA module), the file format defaults to .xlsm. If you try to save a workbook with macros using the .xlsx file format, Excel will display the warning shown in Figure 39.4. You need to click No and then choose Excel Macro-Enabled Workbook (*.xlsm) from the drop-down list.
A VBA macro (also known as a procedure) is usually one of two types: a Sub or a Function. The next two sections discuss the difference.
You can think of a Sub procedure as a new command that either the user or another macro can execute. You can have any number of Sub procedures in an Excel workbook. Figure 39.5 shows a simple VBA Sub procedure. When this code is executed, VBA inserts the current date into the active cell, applies a number format, makes the cell bold, sets the text color to white, sets the background color to black, and adjusts the column width.
Sub procedures always start with the keyword Sub, the macro's name (every macro must have a unique name), and then a list of arguments inside of parentheses. The parentheses are required even if the procedure doesn't use arguments, like the example in Figure 39.5. The End Sub
statement signals the end of the procedure. The lines in between Sub and End Sub make up the procedure's code.
The CurrentDate macro also includes a comment. Comments are simply notes to yourself, and VBA ignores them. A comment line begins with an apostrophe. You can also put a comment in the same line as a statement. In other words, when VBA encounters an apostrophe, it ignores the rest of the text in the line.
You execute a VBA Sub procedure in any of the following ways:
The second type of VBA procedure is a function. A function returns a value (just as a worksheet function always returns a value). A VBA function can be executed by other VBA procedures or used in worksheet formulas, just as you would use Excel's built-in worksheet functions.
Figure 39.6 shows a custom worksheet function. This function is named CubeRoot, and it requires a single argument. CubeRoot calculates the cube root of its argument and returns the result. A Function procedure looks much like a Sub procedure. Notice, however, that function procedures begin with the keyword Function and end with an End Function
statement.
Excel provides two ways to create macros:
The following sections describe these methods.
In the following sections, we describe the basic steps that you take to record a VBA macro. In most cases, you can record your actions as a macro and then simply replay the macro; you don't need to look at the code that's automatically generated. If simply recording and playing back macros is as far as you go with VBA, you don't need to be concerned with the language itself (although a basic understanding of how things work can be helpful).
The Excel macro recorder translates your actions into VBA code. To start the macro recorder, choose Developer ➪ Code ➪ Record Macro (or click the Record Macro icon on the left side of the status bar). The Record Macro dialog box, shown in Figure 39.7, appears.
The Record Macro dialog box presents several options:
To begin recording your actions, click OK; your actions within Excel are converted to VBA code. When you finish recording the macro, choose Developer ➪ Code ➪ Stop Recording, or you can click the Stop Recording button on the status bar. This button replaces the Record Macro button while your macro is being recorded.
This example demonstrates how to record a simple macro that inserts your name into the active cell.
To create the macro, start with a new workbook and follow these steps:
The macro was recorded in a new module named Module1. To view the code in this module, you must activate the VBE. You can activate the VBE in either of two ways:
In the VBE, the Project window displays a list of all open workbooks and add-ins. This list is displayed as a tree diagram, and you can expand or collapse elements of the tree. The code you recorded previously is stored in Module1 in the Modules folder of the current workbook. When you double-click Module1, the code in the module appears in the Code window.
Figure 39.8 shows the recorded macro, as displayed in the Code window.
The macro should look something like this (with your name substituted in, of course):
Sub MyName()
'
' MyName Macro
'
' Keyboard Shortcut: Ctrl+Shift+N
'
ActiveCell.FormulaR1C1 = "Dick Kusleika"
Range("A2").Select
End Sub
The macro recorded is a Sub procedure that is named MyName. The statements tell Excel what to do when the macro is executed.
Notice that Excel inserted some comments at the top of the procedure. These comments are based on information that appeared in the Record Macro dialog box. These comment lines (which begin with an apostrophe) aren't really necessary and deleting them has no effect on how the macro runs. If you ignore the comments, you'll see that this procedure has only two VBA statements:
ActiveCell.FormulaR1C1 = "Dick Kusleika"
Range("A2").Select
The first statement causes the name that you typed while recording the macro to be inserted into the active cell. The FormulaR1C1 part is a property of the Range object, which we'll discuss later. When you press Enter in a cell, Excel moves down one cell (unless you've changed the default behavior). You can guess from this code that the active cell was A1 when the macro was recorded.
Before you recorded this macro, you set an option that assigned the macro to the Ctrl+Shift+N shortcut key combination. To test the macro, return to Excel by using either of the following methods:
When Excel is active, activate a worksheet. It can be in the workbook that contains the VBA module or in any other workbook. Select a cell and press Ctrl+Shift+N. The macro immediately enters your name into the cell and selects cell A2.
After you record a macro, you can make changes to it. For example, assume that you don't want to select A2, but rather you want to select the cell below the active cell. Press Alt+F11 to activate the VBE window. Then activate Module1 and change the second statement to the following:
ActiveCell.Offset(1, 0).Select
The edited macro appears as follows:
Sub MyName()
'
' MyName Macro
'
' Keyboard Shortcut: Ctrl+Shift+N
'
ActiveCell.FormulaR1C1 = "Dick Kusleika"
ActiveCell.Offset(1, 0).Select
End Sub
Test this new macro, and you will see that it performs as it should.
If you're going to work with recorded macros, you need to understand the concept of relative versus absolute recording modes. In the previous example, we showed how even a simple macro could act unexpectedly because of an incorrect recording mode.
Normally, when you record a macro, Excel stores exact references to the cells that you select. That is, it uses absolute references. If you press Enter while in cell A1 and the active cell moves down one, the recorded macro will show that you selected cell A2. Similarly, if you were to select cells B1:B10 while you're recording a macro, for example, Excel records this selection as follows:
Range("B1:B10").Select
This VBA statement means exactly what it says: “Select the cells in the range B1:B10.” When you invoke the macro that contains this statement, the same cells are always selected, regardless of which cell is active.
In the Developer ➪ Code group of the Ribbon there is a Use Relative References control. When you click this control, Excel changes its recording mode from absolute (the default) to relative. When recording in relative mode, selecting a range of cells is translated differently, depending on which cell is active. For example, if you're recording in relative mode and cell A1 is active, selecting the range B1:B10 generates the following statement:
ActiveCell.Offset(0, 1).Range("A1:A10").Select
This statement can be translated as “From the active cell, move 0 rows down and 1 column right. Then treat this new cell as if it were cell A1. Now select what would be A1:A10.” In other words, a macro that is recorded in relative mode uses the active cell as its starting point and then stores relative references to this cell. As a result, you get different results depending on the location of the active cell. When you replay this macro, the cells that are selected depend on the active cell. This macro selects a range that is 10 rows by 1 column, offset from the active cell by 0 rows and 1 column.
When Excel is recording in relative mode, the Use Relative References control appears with a background color. To return to absolute recording, click the Use Relative References control again and it displays its normal state, with no background color.
In the first example, the macro behaved oddly by selecting cell A2 after it entered the name. This odd behavior didn't cause any harm or cause the macro not to execute properly. This example demonstrates how choosing the wrong recording mode can cause the macro to work incorrectly. You will record a macro that inserts the current date and time into the active cell. To create the macro, follow these steps:
=NOW()
Activate an empty cell and press Ctrl+Shift+T to execute the macro. There's a pretty good chance that the macro won't work!
The VBA code that is recorded in this macro depends on a setting on the Advanced tab of the Excel Options dialog box, namely, After Pressing Enter, Move Selection. If this setting is enabled (which is the default), the recorded macro won't work as intended because the active cell was changed when you pressed Enter. Even if you reactivated the date cell while recording (in step 7), the macro still fails.
Activate the VBE and look at the recorded code. Figure 39.9 shows the recorded macro, as displayed in the Code window.
The procedure has five statements. The first inserts the NOW()
formula into the active cell. The second statement selects cell D8—if the active cell moved to the next cell in step 7, then you had to reselect the active cell in step 8. The exact cell address depends on the location of the active cell when the macro was recorded.
The third statement copies the cell. The fourth statement, which is displayed on two lines (the underscore character means that the statement continues on the next line), pastes the Clipboard contents (as a value) to the current selection. The fifth statement cancels the dashed border around the copied cell.
The problem is that the macro is hard-coded to select cell D8. If you execute the macro when a different cell is active, the code always selects cell D8 before it copies the cell. This is not what you intended, and it causes the macro to fail.
You can fix the macro in several ways. If you understand VBA, you can edit the code so that it works properly, or you can re-record the macro using relative references.
Activate the VBA Editor, delete the existing TimeStamp procedure, and re-record it. Before you start recording, click the Use Relative References command in the Code group of the Developer tab.
Figure 39.10 shows the new macro, recorded with relative references in effect.
Note that the second line now says ActiveCell
.
Select
instead of using a specific cell address. This solves the problem of copying and pasting the wrong cell, but why would it select a cell that's already selected? It's just one of the curiosities of recording macros. As you select cells, such as when you press Enter after keying a formula, the recorder dutifully registers every selection. In this example, when you pressed Enter in step 7, the recorder recorded this line:
ActiveCell.Offset(1, 0).Range("A1").Select
Since you didn't do anything while in that cell, however, it didn't save it. When you reselected the cell with the date in it, it replaced the line above with the one you see in the macro.
When Excel is active, activate a worksheet. The worksheet can be in the workbook that contains the VBA module or in any other workbook. Select a cell and press Ctrl+Shift+T. The macro immediately enters the current date and time into the cell. You may need to widen the column to see the date and time.
When the result of a macro requires additional manual intervention, it's a sign that the macro could be improved. To widen the column automatically, just add this statement to the end of the macro (before the End Sub statement):
ActiveCell.EntireColumn.AutoFit
If you followed along with the preceding examples, you should have a better feel for how to record macros and for problems that might occur with even simple macros. If you find the VBA code confusing, don't worry. You don't really have to be concerned with it as long as the macro that you record works correctly. If the macro doesn't work properly, rerecording the macro rather than editing the code is often easier.
A good way to learn about what is recorded is to set up your screen so that you can see the code that is being generated in the VBE window. To do so, make sure that the Excel window isn't maximized; then arrange the Excel window and the VBE window so that both are visible. While you're recording your actions, make sure that the VBE window is displaying the module in which the code is being recorded. (You may have to double-click the module name in the Project Explorer window.)
Most user-created macros are designed for use in a specific workbook, but you may want to use some macros in all your workbooks. You can store these general-purpose macros in the Personal Macro Workbook so that they're always available to you. The Personal Macro Workbook is loaded whenever you start Excel. This file, named personal.xlsb
, doesn't exist until you record a macro, using Personal Macro Workbook as the destination.
To record the macro in your Personal Macro Workbook, select the Personal Macro Workbook option in the Record Macro dialog box before you start recording. This option is in the Store Macro In drop-down box.
If you store macros in the Personal Macro Workbook, you don't have to remember to open the Personal Macro Workbook when you load a workbook that uses macros. When you want to exit, Excel asks whether you want to save changes to the Personal Macro Workbook.
When you begin recording a macro, the Record Macro dialog box gives you an opportunity to provide a shortcut key for the macro. Here's what to do if you'd like to change the shortcut key or provide a shortcut key for a macro that doesn't have one:
After you record a macro and test it, you may want to assign the macro to a button placed in a worksheet. You can follow these steps to do so:
After you perform these steps, clicking the button executes the assigned macro.
You can also assign a macro to a button on your Quick Access Toolbar:
After performing these steps, your Quick Access Toolbar will display a button that executes your macro.
As we demonstrated in the preceding sections, the easiest way to create a simple macro is to record your actions. To develop more complex macros, however, you have to enter the VBA code manually—in other words, write a program. To save time (and assist in the learning process), you can often start with a recorded macro and edit it.
Before you begin writing VBA code, it helps to have a good understanding of such topics as objects, properties, and methods. As you progress, you will become familiar with common programming constructs, such as looping and If-Then statements.
The following sections are an introduction to VBA programming, which is essential if you want to write (rather than record) VBA macros. It isn't intended to be a complete instructional guide. Our book Excel 2019 Power Programming with VBA (Wiley, 2019) covers all aspects of VBA and advanced spreadsheet application development.
Before you can enter code, you must insert a VBA module into the workbook. If the workbook already has a VBA module, you can use the existing module sheet for your new code.
Follow these steps to insert a new VBA module:
A VBA module, which is displayed in a separate window, works like a text editor. You can move through the window, select text, insert text, copy, cut, paste, and so on.
VBA is a language designed to manipulate objects. Some objects are contained in the language itself, but most of the objects that you will use when programming VBA for Excel come from the Excel object model.
At the top of the object model is the Application object. This object represents Excel itself, and all other objects are below it in the hierarchy. One way to think about writing code is to ask which object you'd like to change and which property or method controls the aspect of the object you'd like to change. For example, if you want to force users to enter data using the Formula bar as opposed to entering data directly in cells, you can change the EditDirectlyInCell property of the Application object.
If you don't know which object or property to change—and when you're just starting out, you won't—you can use the macro recorder. Start recording a macro and make the changes. Then see what the recorder came up with. If you record a macro and choose File ➪ Options ➪ Advanced and uncheck Allow editing directly in cells, you will see this recorded macro.
Sub Macro1()
'
' Macro1 Macro
'
'
Application.EditDirectlyInCell = False
End Sub
Now you know that the EditDirectlyInCell property of the Application object is where that setting is stored, and you can use that in your own code.
In addition to the Application object, there are hundreds of other objects available to use in your code, such as Range, Chart, and Shape objects. These objects are arranged in a hierarchy with the Application object at the top.
Objects of the same type are contained in collections. (Collections are also an object.) Collection objects are named using the plural form of the objects that they contain. Each open workbook is a Workbook object, and all the open workbooks are in the Workbooks collection object. Similarly, the Shapes collection object contains all the objects of type Shape.
There are a few places where the plural collection naming convention breaks down. The Range object is an important exception that we'll discuss later in this chapter.
You reference a specific object by traversing the hierarchy. To reference cell A1, you might use code such as the following:
Application.Workbooks.Item("MyBook.xlsx").Worksheets.Item(1).Range("A1")
Fortunately, VBA provides some shortcuts. Since Application is at the top, you can omit it, and VBA will know what you want. VBA also provides default properties for some objects. All collection objects have a default property called Item that's used to access one of the collections. You can shorten your code as follows:
Workbooks("MyBook.xlsx").Worksheets(1).Range("A1")
When accessing an Item of a collection, you can ask for it by name or by number. For the Workbooks collection, we passed in the name of the workbook that we wanted, and it returned the Workbook object with that name. For the Worksheets collection, however, we asked for the first Worksheet object in the collection regardless of its name.
The objects you work with have properties, which you can think of as attributes of the objects. For example, a Range object has properties such as Column, Row, Width, and Value. A Chart object has properties such as Legend, ChartTitle, and so on. ChartTitle is also an object, with properties such as Font, Orientation, and Text. Excel has many objects, and each has its own set of properties. You can write VBA code to do the following:
You refer to a property in your VBA code by placing a period (a dot) and the property name after the object's name. For example, the following VBA statement sets the Value property of a range named frequency to 15. (That is, the statement causes the number 15 to appear in the range's cells.)
Range("frequency").Value = 15
You might have noticed that we used the dot operator to traverse the hierarchy in the previous section, and we're using it again to access properties. That's not a coincidence. Properties can contain a lot of different values, and they can also contain other objects. When we used Application.Workbooks("MyBook.xlsx")
, we were actually accessing the Workbooks property of the Application object. That property returns a Workbooks collection object.
Some properties are read-only, which means you can examine the property but you can't change the property. For a single-cell Range object, the Row and Column properties are read-only properties: you can determine where a cell is located (in which row and column), but you can't change the cell's location by changing these properties.
A Range object also has a Formula property, which is not read-only; that is, you can insert a formula into a cell by changing its Formula property. The following statement inserts a formula into cell A12 by changing the cell's Formula property:
Range("A12").Formula = "=SUM(A1:A10)"
The Application object has several useful properties that refer to where the user is in the program:
Application.ActiveWorkbook
: Returns the active workbook (a Workbook object) in Excel.Application.ActiveSheet
: Returns the active sheet (a Sheet object) of the active workbook.Application.ActiveCell
: Returns the active cell (a Range object) of the active window.Application.Selection
: Returns the object that is currently selected in the active window of the Application object. This can be a Range, a Chart, a Shape, or some other selectable object.In many cases, you can refer to the same object in a number of different ways. Assume you have a workbook named Sales.xlsx
and that it's the only workbook open. Furthermore, assume that this workbook has one worksheet, named Summary. Your VBA code can refer to the Summary sheet in any of the following ways:
Workbooks("Sales.xlsx").Worksheets("Summary")
Workbooks(1).Worksheets(1)
Workbooks(1).Sheets(1)
Application.ActiveWorkbook.ActiveSheet
ActiveWorkbook.ActiveSheet
ActiveSheet
The method that you use is determined by how much you know about the workspace. For example, if more than one workbook is open, the second or third method isn't reliable. If you want to work with the active sheet (whatever it may be), any of the last three methods would work. To be absolutely sure you're referring to a specific sheet on a specific workbook, the first method is your best choice.
Objects also have methods. You can think of a method as an action taken with an object. Generally, methods are used to interact with the computer outside the Excel application or to modify multiple properties at once. For example, Range objects have a Clear method. The following VBA statement clears a Range, an action that is equivalent to selecting the Range and then choosing Home ➪ Editing ➪ Clear ➪ Clear All:
Range("A1:C12").Clear
The Clear method involves changing several properties of the Range object at once. It includes setting the Value property to Empty (clearing its contents), the Bold property of the Font object to False (clearing all formats), and the Comments property to Nothing (deleting the cell's comment). It's doing a few other things as well.
If your code interacts with files on a disk, printers, or other aspects of your computer outside of Excel, you'll probably use a method. Each Workbook object has a Name property that's read-only. You can't change the Name property by setting it directly as follows:
Workbooks(1).Name = "xyz.xlsx"
That will fail. However, you can change a Workbook's name by using the SaveAs method:
Workbooks(1).SaveAs "xyz.xlsx"
In addition to changing the name property, SaveAs changes a few other properties, and it also writes the file to the hard drive.
In VBA code, methods look like properties because they're connected to the object with a “dot.” However, methods and properties are different concepts.
The Range object is special. As you might imagine, it's central to the Excel object model. Workbooks and worksheets exist only to hold cells. But while the Worksheets collection holds a bunch of Worksheet objects and the Shapes collection holds a bunch of Shape objects, the Range object works differently.
A single cell is a Range object. A group of cells is also a Range object, but not a Ranges collection object. It's one of the few objects that breaks the plural naming convention of collections.
Most collection objects have a default property of Item. That allows you to write the following:
Workbooks(1)
instead of the following:
Workbooks.Item(1)
Generally, if an object has an Item property, that's the default. For objects that aren't collections, if they have a Value property, that's the default. For example, these two lines of VBA are identical because Value is the default property of Checkbox objects:
If Sheet1.CheckBox1.Value = True Then
If Sheet1.CheckBox1 = True Then
The Range object has both an Item property and a Value property. In some contexts, the Item property is the default, while in others, it's the Value property. The good news is that VBA does a pretty good job of picking the right one.
Like all programming languages, VBA enables you to work with variables. In VBA (unlike in some languages), you don't need to declare variables explicitly before you use them in your code (although doing so is definitely a good practice).
In the following example, the value in cell A1 on Sheet1 is assigned to a variable named Rate:
Rate = Worksheets("Sheet1").Range("A1").Value
After the statement is executed, you can work with the variable Rate in other parts of your VBA code.
VBA uses many constructs that are found in other programming languages. These constructs are used to control the flow of execution. We'll now introduce a few of the more common programming constructs.
One of the most important control structures in VBA is the If-Then construct, which gives your applications decision-making capability. The basic syntax of the If-Then structure is as follows:
If condition Then statements [Else elsestatements]
In plain English, if a condition is true, then a group of statements will be executed. If you include the Else clause, then another group of statements will be executed if the condition is not true.
The following is an example (which doesn't use the optional Else clause). This procedure checks the active cell. If it contains a negative value, the cell's font color is changed to red. Otherwise, nothing happens:
Sub CheckCell()
If ActiveCell.Value < 0 Then ActiveCell.Font.Color = vbRed
End Sub
Here's another multiline version of that procedure that uses an Else clause. Because it uses multiple lines, you must include an End If
statement. This procedure colors the active cell text red if it's a negative value and green otherwise:
Sub CheckCell()
If ActiveCell.Value < 0 Then
ActiveCell.Font.Color = vbRed
Else
ActiveCell.Font.Color = vbGreen
End If
End Sub
You can use a For-Next loop to execute one or more statements a number of times. Here's an example of a For-Next loop:
Sub SumSquared()
Total = 0
For Num = 1 To 10
Total = Total + (Num ^ 2)
Next Num
MsgBox Total
End Sub
This example has one statement between the For statement and the Next statement. This single statement is executed 10 times. The variable Num takes on successive values of 1, 2, 3, and so on, up to 10. The variable Total stores the sum of Num squared added to the previous value of Total. The result is a value that represents the sum of the first 10 integers squared. This result is displayed in a message box.
For-Next loops execute a set of statements a particular number of times. Do loops execute a set of statements until a particular condition exists or stops existing.
Sub SumSquaredTo500()
Total = 0
num = 0
Do
num = num + 1
Total = Total + (num ^ 2)
Loop Until Total>= 500
MsgBox num & Space(1) & Total
End Sub
This procedure keeps summing squares until the total reaches 500 or higher. With Do loops, you can check the condition on the Do line or the Loop line, but not both. The four options are as follows:
A construct that you sometimes encounter if you record macros is the With-End With construct. This is a shortcut way of dealing with several properties or methods of the same object. The following is an example:
Sub AlignCells()
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = xlHorizontal
End With
End Sub
The following macro performs the same operations but doesn't use the With-End With construct:
Sub AlignCells()
Selection.HorizontalAlignment = xlCenter
Selection.VerticalAlignment = xlCenter
Selection.WrapText = False
Selection.Orientation = xlHorizontal
End Sub
The Select Case construct is useful for choosing among two or more options. The following example demonstrates the use of a Select Case construct. In this example, the active cell is checked. If its value is less than 0, it's colored red. If it's equal to 0, it's colored blue. If the value is greater than 0, it's colored black:
Sub CheckCell()
Select Case ActiveCell.Value
Case Is < 0
ActiveCell.Font.Color = vbRed
Case 0
ActiveCell.Font.Color = vbBlue
Case Is> 0
ActiveCell.Font.Color = vbBlack
End Select
End Sub
Any number of statements can go below each Case statement, and they all are executed if the case is true.
The following is a VBA macro that can't be recorded because it uses programming concepts that must be entered manually. This macro creates a list of all formulas on the active sheet. The list is stored on a new worksheet:
Sub ListFormulas()
' Create a range variable
Set InputRange = ActiveSheet.UsedRange
' Add a new sheet and save in a variable
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
Although this macro may look complicated, it's fairly simple when you break it down. Here's how it works:
Figure 39.13 shows the part of the result of running this macro—a handy list of all formulas in the worksheet.
As macros go, this example is okay, but it's certainly not perfect. It's not very flexible, and it doesn't include error handling. For example, if the workbook structure is protected, trying to add a new sheet will cause an error.
This chapter is a basic introduction to VBA. If this is your first exposure to VBA, you might be a bit overwhelmed by objects, properties, and methods. It's frustrating when you know what you want to do but don't know which objects, properties, or methods you need to do it. Fortunately, several good ways are available to learn about objects, properties, and methods.