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 usually determined by how much you know about the workspace. For example, if more than one workbook is open, the second and third methods aren'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 that you're referring to a specific sheet on a specific workbook, the first method is your best choice.
About the code examples
Throughout this book, we present many small snippets of VBA code to make a point or to provide an example. In some cases, this code consists of a single statement, or only an expression, which isn't a valid instruction by itself.
For example, the following is an expression:
Range("A1").Value
To test an expression, you must evaluate it. The MsgBox function is a handy tool for this:
MsgBox Range("A1").Value
To try these examples, put the statement in a procedure in a VBA module, like this:
Sub Test() ' statement goes here End Sub
Then put the cursor anywhere in the procedure and press F5 to execute it. Also, make sure that the code is being executed in the proper context. For example, if a statement refers to Sheet1, make sure that the active workbook has a sheet named Sheet1.
If the code is just a single statement, you can use the VBE Immediate window. The Immediate window is useful for executing a statement immediately, without having to create a procedure. If the Immediate window isn't displayed, press Ctrl+G in the VBE.
Just type the VBA statement in the Immediate window and press Enter. To evaluate an expression in the Immediate window, precede the expression with a question mark (?), which is a shortcut for Print. For example, you can type the following in the Immediate window:
? Range("A1").Value
The result of this expression is displayed in the next line of the Immediate window.
Don't Panic—You Are Not Alone
If this is your first exposure to VBA, you're probably a bit overwhelmed by objects, properties, and methods. That's normal. No one is going to be a VBA expert in one day. VBA is a journey of time and practice. The good news is that you won't be alone on this journey. There are plenty of resources out there that can help you on your path. This section highlights a few resources you can leverage when you need a push in the right direction.
Read the rest of the book
Don't forget, the name of this chapter is “Introducing Visual Basic for Applications.” The remainder of this book covers many additional details and provides many useful and informative examples.
Let Excel help write your macro
One of the best places to get macro help is the macro recorder in Excel. When you record a macro with the macro recorder, Excel writes the underlying VBA for you. After recording, you can review the code, see what the recorder is doing, and then try to turn the code it creates into something more suited to your needs. For example, let's say you need a macro that refreshes all the pivot tables in your workbook and clears all of the filters in each pivot table. Writing this macro from a blank canvas would be a daunting task. Instead, you can start the macro recorder and record yourself refreshing all the pivot tables and clearing all the filters. Once you've stopped recording, you can review the macro and make any changes that you deem necessary.
Use the Help system
To a new Excel user, the Help system may seem like a clunky mechanism that returns a perplexing list of topics that has nothing to do with the topic you're searching. The truth is, however, once you learn how to use the Excel Help system effectively, it's often the fastest and easiest way to get extra help on a topic.
You just need to remember two basic tenets of the Excel Help system: location matters when asking for help, and you need to be connected to the Internet to use Excel's Help system.
Location matters when asking for help
In Excel, there are actually two Help systems: one providing help on Excel features and another providing help on VBA programming topics. Instead of doing a global search with your criteria, Excel throws your search criteria only against the Help system that is relevant to your current location. This essentially means that the help you get is determined by the area of Excel in which you're working. So, if you need help on a topic that involves macros and VBA programming, you'll need to be in the Visual Basic Editor while performing your search. This will ensure that your keyword search is performed on the correct Help system.
You need to be connected to the Internet
When you search for help on a topic, Excel checks to see if you're connected to the Internet. If you are, Excel takes you to the MSDN website where you can search for the topic on which you need help. If you aren't connected to the Internet, Excel gives you a message telling you that you need to be online to use Help.
Use the Object Browser
The Object Browser is a handy tool that lists every property and method available for every object. When you are in the VBE, you can bring up Object Browser in any of the following three ways:
Press F2.
Choose View ➪ Object Browser.
Click the Object Browser button on the Standard toolbar.
The Object Browser is shown in Figure 2.17.
FIGURE 2.17 The Object Browser is a great reference source.
The drop-down list in the upper-left corner of Object Browser includes a list of all object libraries to which you have access:
Excel itself
MSForms (if user forms are utilized in your workbook)
Office (objects common to all Microsoft Office applications)
Stdole (OLE automation objects)
VBA
The current project (the project that's selected in Project Explorer) and any workbooks referenced by that project
Your selection in this
