Excel VBA Programming For Dummies. Dick Kusleika

       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.

       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.