Excel VBA Programming For Dummies. Dick Kusleika
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.
Ensuring Excel Compatibility
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).
Excel 2010 and later also have some new objects, methods, and properties. If you use these in your code, users with an older version of Excel will receive an error when they run your macro — and you’ll get the blame.
Chapter 2
Building Simple Macros
IN THIS CHAPTER
The best way to get into a cold body of water is to jump right in — no sense prolonging the agony. By wading through this chapter, you can get your feet wet immediately but avoid getting in over your head.
By the time you reach the end of this chapter, you’ll start feeling better about this Excel programming business, and you’ll be glad you took the plunge. This chapter provides a step-by-step demonstration of how to develop a simple but useful VBA macro.
Displaying the Developer Tab
Before you can call yourself an Excel programmer, you need to learn the secret handshake. That means you need to make a small change so that Excel will display a new tab at the top of the screen: Developer. Getting Excel to display the Developer tab is easy (and you only have to do it once). Just follow these steps:
1 Right-click any part of the Ribbon and choose Customize the Ribbon from the shortcut menu.
2 In the Customize Ribbon tab of the Excel Options dialog box, locate Developer in the box on the right.
3 Put a check mark next to Developer.
4 Click OK.You’re back to Excel with a brand-new tab: Developer.
When you click the Developer tab, the Ribbon displays information that is of interest to programmers (that’s you!). Figure 2-1 shows how the Ribbon looks when the Developer tab is selected.
FIGURE 2-1: The Developer tab is normally hidden, but it’s easy to unhide.
Creating a Macro
In this chapter, you create your first macro. The macro that you create does the following:
Types your name in a cell
Enters the current date and time in the cell below
Formats