Dick Kusleika - Excel VBA Programming For Dummies

Here are some benefits of automating a task by using VBA:

Excel always executes the task in exactly the same way. (In most cases, consistency is a good thing.)

Excel performs the task much faster than you can do it manually. (Unless, of course, you’re Clark Kent.)

If you’re a good macro programmer, Excel always performs the task without errors (which probably can’t be said about you, no matter how careful you are).

If you set up things properly, someone who doesn’t know anything about Excel can perform the task by running the macro.

You can do things in Excel that are otherwise impossible — which can make you a very popular person around the office.

For long, time-consuming tasks, you don’t have to sit in front of your computer and get bored. Excel does the work while you hang out at the water cooler.

Using VBA can present some disadvantages (or potential disadvantages). Understanding these limitations upfront helps you avoid running down rabbit trails to nowhere. Most people use VBA to save time, not waste it!

Keep the following points in mind when deciding whether to use VBA:

Other people who need to use your VBA programs must have their own copies of Excel. It would be nice if you could press a button that transforms your Excel/VBA application into a stand-alone program, but that isn’t possible. (And probably never will be.)

Sometimes, things go wrong. In other words, you can’t blindly assume that your VBA program will always work correctly under all circumstances. Welcome to the world of debugging (fixing errors) and, if others are using your macros, providing technical support.

VBA is a moving target. As you know, Microsoft is continually upgrading Excel. Even though Microsoft puts great effort into compatibility between versions, you might discover that the VBA code you’ve written doesn’t work properly with older versions or with a future version of Excel. For more information on the importance of Excel compatibility, see the section “ Ensuring Excel Compatibility,” later in this chapter.

Just to let you know what you’re in for, here’s a quick-and-dirty summary of what VBA is all about.

You perform actions in VBA by writing (or recording) code in a VBA module. You view and edit VBA modules by using the Visual Basic Editor (VBE).

A VBA module consists of Sub procedures. A Sub procedure has nothing to do with underwater vessels or tasty sandwiches. Rather, it’s a chunk of computer code that performs some action on or with objects (discussed in a moment). The following example shows a simple Sub procedure called AddEmUp. This amazing program, when executed, displays the result of 1 plus 1: Sub AddEmUp() Sum = 1 + 1 MsgBox "The answer is " & SumEnd SubA Sub procedure that doesn’t perform properly is said to be substandard.

A VBA module can also have Function procedures. A Function procedure returns a single value. You can call it from another VBA procedure or even use it as a function in a worksheet formula. An example of a Function procedure (named AddTwo) follows. This Function accepts two numbers (called arguments) and returns the sum of those values: Function AddTwo(arg1, arg2) AddTwo = arg1 + arg2End FunctionA Function procedure that doesn’t work correctly is said to be dysfunctional.

VBA manipulates objects. Excel provides dozens and dozens of objects that you can manipulate. Examples of objects include a workbook, a worksheet, a cell range, a chart, and a shape. You have many more objects at your disposal, and you can manipulate them by using VBA code.

Objects are arranged in a hierarchy. Objects can act as containers for other objects. At the top of the object hierarchy is Excel. Excel itself is an object called Application . The Application object contains other objects, such as Workbook objects and Add-In objects. The Workbook object can contain other objects, such as Worksheet objects and Chart objects. A Worksheet object can contain objects such as Range objects and PivotTable objects. The term object model refers to the arrangement of these objects. (Object-model mavens can find out more in Chapter 4.)

Collection Objects hold other objects of the same type. For example, the Worksheets collection consists of all the worksheets in a particular workbook. The Charts collection consists of all Chart objects in a workbook. Collections are themselves objects.

You refer to an object by specifying its position in the object hierarchy, using a dot (entered as a period) as a separator. For example, you can refer to the workbook Book1.xlsx as Application.Workbooks("Book1.xlsx")This refers to the workbook Book1.xlsx in the Workbooks collection. The Workbooks collection is contained in the Application object (that is, Excel). Extending this to another level, you can refer to Sheet1 in Book1.xlsx asApplication.Workbooks("Book1.xlsx").Worksheets("Sheet1")You can take this to still another level and refer to a specific cell (in this case, cell A1):Application.Workbooks("Book1.xlsx").Worksheets("Sheet1").Range("A1")

If you omit specific references, Excel uses the active objects. If Book1.xlsx is the active workbook, you can simplify the preceding reference as follows: Worksheets("Sheet1").Range("A1")If you know that Sheet1 is the active sheet, you can simplify the reference even more:Range("A1")

Objects have properties. You can think of a property as a characteristic of an object. For example, a Range object has such properties as Value and Address. A Chart object has such properties as HasTitle and Type. You can use VBA to read object properties and also to change properties.

You refer to a property of an object by combining the object name with the property name, separated by a dot. For example, you can refer to the Value property in cell A1 on Sheet1 as follows: Worksheets("Sheet1").Range("A1").Value

You can assign values to variables. A variable is a named element that stores information. You can use variables in your VBA code to store such things as numbers, text, and properties. To assign the value in cell A1 on Sheet1 to a variable called Interest, use the following VBA statement: Interest = Worksheets("Sheet1").Range("A1").Value

Objects have methods. A method is an action Excel performs with an object. For example, one of the methods for a Range object is ClearContents. This aptly named method clears the contents of the range.

You specify a method by combining the object with the method, separated by a dot. For example, the following statement deletes the value in cell A1 using the ClearContents method: Worksheets("Sheet1").Range("A1").ClearContents

VBA includes all the constructs of modern programming languages, including variables, arrays, and looping. In other words, if you’re willing to spend a little time learning the ropes, you can write code that does some incredible things.

Believe it or not, the preceding list pretty much describes VBA. Now you just have to discover the details by reading the rest of the book.

This book is written for the desktop versions of Excel 2019 and Excel 2021. If you don’t have one of those versions, there’s a slight risk you’ll get confused in a few places, but mostly it will just work.

If you plan to distribute your Excel/VBA files to other users, it’s vitally important that you understand which versions of Excel they use. People using older versions won’t be able to take advantage of features introduced in later versions. For example, if you write VBA code that references cell XFD1048576 (the last cell in a workbook), those who use a version prior to Excel 2007 will receive an error because those pre-Excel 2007 worksheets had only 65,536 rows and 255 columns (the last cell is IV65536).