Examining your macro

The macro was recorded in a new module named Module1. To view the code in this module, you must activate the Visual Basic Editor. You can activate the VB Editor in either of two ways:

• Press Alt+F11.
• Choose Developer ➜ Code ➜ Visual Basic.

In the VB Editor, the Project window displays a list of all open workbooks and add-ins. This list is displayed as a tree diagram on the left of the screen, which you can expand or collapse. The code that you recorded previously is stored in Module1 in the current workbook. When you double-click Module1, the code in the module appears in the Code window.

The macro should look something like this:

Sub MyName()
'
' MyName Macro
'
' Keyboard Shortcut: Ctrl+Shift+N
'
'   ActiveCell.FormulaR1C1 ="Michael Alexander"
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 some of the 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 one VBA statement:

ActiveCell.FormulaR1C1 ="Michael Alexander"

This single statement causes the name you typed while recording to be inserted into the active cell.

Testing your macro

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:

• Press Alt+F11.
• Click the View Microsoft Excel button on the VB Editor toolbar.

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.

Editing your macro

After you record a macro, you can make changes to it. For example, assume that you want your name to be bold. You could re-record the macro, but this modification is simple, so editing the code is more efficient. Press Alt+F11 to activate the VB Editor window. Then activate Module1 and insert: ActiveCell.Font.Bold = True as demonstrated in the sample code below.

ActiveCell.Font.Bold = True
The edited macro appears as follows:
Sub MyName()
'
' MyName Macro
'
' Keyboard Shortcut: Ctrl+Shift+N
'
    ActiveCell.Font.Bold = True
    ActiveCell.FormulaR1C1 ="Michael Alexander"
End Sub

Test this new macro, and you see that it performs as it should.

Recording macros with absolute references

Excel’s default recording mode is in absolute reference. As you may know, the term absolute reference is often used in the context of cell references found in formulas. When a cell reference in a formula is an absolute reference, it does not automatically adjust when the formula is pasted to a new location.

The best way to understand how this concept applies to macros is to try it out. Open the Chapter 2 Sample.xlsm file and record a macro that counts the rows in the Branchlist worksheet. (See Figure 2.2.)

Follow these steps to record the macro:

1. Before recording, make sure cell A1 is selected.
2. Select Record Macro from the Developer tab.
3. Name the macro AddTotal.
4. Choose This Workbook for the save location.

5. Click OK to start recording.

   At this point, Excel is recording your actions. While Excel is recording, perform the following steps:

6. Select cell A16 and type Total in the cell.
7. Select the first empty cell in Column D (D16) and type = COUNTA(D2:D15). This gives a count of branch numbers at the bottom of column D. The COUNTA function is used to catch any branch numbers stored as text.
8. Click Stop Recording on the Developer tab to stop recording the macro.

The formatted worksheet should look like something like the one in Figure 2.3.

To see your macro in action, delete the total row you just added and play back your macro by following these steps:

1. Select Macros from the Developer tab.
2. Find and select the AddTotal macro you just recorded.
3. Click the Run button.

If all goes well, the macro plays back your actions perfectly and gives your table a total. Here’s the thing: No matter how hard you try, you can’t make the AddTotal macro work on the second table. Why? Because you recorded it as an absolute macro.

To understand what this means, examine the underlying code. To examine the code, select Macros from the Developer tab to get the Macro dialog box illustrated in Figure 2.4. The Macro dialog box will, by default, list the macros available in all open Excel workbooks (including any Add-ins you may have installed). You can limit the list to only those macros contained in the active workbook by changing the Macros In setting to This Workbook.

Select the AddTotal macro and click the Edit button. This opens the Visual Basic Editor to show you the code that was written when you recorded your macro:

Sub AddTotal()
  Range("A16").Select
  ActiveCell.FormulaR1C1 ="Total"
  Range("D16").Select
  ActiveCell.FormulaR1C1 ="=COUNTA(R[-14]C:R[-1]C)"
End Sub

Pay particular attention to lines two and four of the macro. When you asked Excel to select cell range A16 and then D16, those cells are exactly what it selected. Because the macro was recorded in absolute reference mode, Excel interpreted your range selection as absolute. In other words, if you select cell A16, that cell is what Excel gives you. In the next section, you take a look at what the same macro looks like when recorded in relative reference mode.

Recording macros with relative references

In the context of Excel macros, relative means relative to the currently active cell. So you should use caution with your active cell choice — both when you record the relative reference macro and when you run it.

First, make sure the Chapter 2 Sample.xlsm file is open. (This file is available on this book’s companion website.) Then, use the following steps to record a relative-reference macro:

1. Select the Use Relative References option from the Developer tab, as shown in Figure 2.5.
2. Before recording, make sure cell A1 is selected.
3. Select Record Macro from the Developer tab.
4. Name the macro AddTotalRelative.
5. Choose This Workbook for the save location.
6. Click OK to start recording.
7. Select cell A16 and type Total in the cell.
8. Select the first empty cell in Column D (D16) and type = COUNTA(D2:D15).
9. Click Stop Recording on the Developer tab to stop recording the macro.

At this point, you have recorded two macros. Take a moment to examine the code for your newly-created macro.

Select Macros from the Developer tab to open the Macro dialog box. Here, choose the AddTotalRelative macro and click Edit.

Again, this opens the Visual Basic Editor to show you the code that was written when you recorded your macro. This time, your code looks something like the following:

Sub AddTotalRelative()
  ActiveCell.Offset(15, 0).Range("A1").Select
  ActiveCell.FormulaR1C1 ="Total"
  ActiveCell.Offset(0, 3).Range("A1").Select
  ActiveCell.FormulaR1C1 ="=COUNTA(R[-14]C:R[-1]C)"
End Sub

Notice that there are no references to any specific cell ranges at all (other than the starting point “A1”). Let’s take a moment to take a quick look at what the relevant parts of this VBA code really mean.

Notice that in line 2, Excel uses the Offset property of the active cell. This property tells the cursor to move a certain number of cells up or down and a certain number of cells left or right.

The Offset property code tells Excel to move 15 rows down and 0 columns across from the active cell (in this case, A1). There’s no need for Excel to explicitly select a cell as it did when recording an absolute reference macro.

To see this macro in action, delete the total row and do the following:

1. Select cell A1.
2. Select Macros from the Developer tab.
3. Find and select the AddTotalRelative macro.
4. Click the Run button.
5. Now select cell F1.
6. Select Macros from the Developer tab.
7. Find and select the AddTotalRelative macro.
8. Click the Run button.

Notice that this macro, unlike your previous macro, works on both sets of data. Because the macro applies the totals relative to the currently active cell, the totals are applied correctly.

For this macro to work, you simply need to ensure that:

• You’ve selected the correct starting cell before running the macro.
• The block of data has the same number of rows and columns as the data on which you recorded the macro.

Hopefully, this simple example has given you a firm grasp of macro recording with both absolute and relative references.

Macro-enabled file extensions

Beginning with Excel 2007, Excel workbooks were given the standard file extension .xlsx. Files with the .xlsx extension cannot contain macros. If your workbook contains macros and you then save that workbook as an .xlsx file, all VBA code is removed automatically. Luckily, Excel will warn you that your macro content will be removed when saving a workbook with macros as an .xlsx file.

If you want to retain the macros, you must save your file as an Excel Macro-Enabled Workbook. This gives your file an .xlsm extension. The idea is that all workbooks with an .xlsx file extension are automatically known to be safe, whereas you can recognize .xlsm files as a potential threat.

Macro security in Excel

With the release of Office 2010, Microsoft introduced significant changes to its Office security model. One of the most significant changes is the concept of trusted documents. Without getting into the technical minutia, a trusted document is essentially a workbook you have deemed safe by enabling macros.

If you open a workbook that contains macros, you will see a yellow bar message under the Ribbon stating that macros (active content) have, in effect, been disabled.

If you click Enable, it automatically becomes a trusted document. This means you no longer are prompted to enable the content as long as you open that file on your computer. The basic idea is that if you told Excel that you “trust” a particular workbook by enabling macros, it is highly likely that you will enable macros each time you open it. Thus, Excel remembers that you’ve enabled macros before and inhibits any further messages about macros for that workbook.

This is great news for you and your clients. After enabling your macros just one time, they won’t be annoyed at the constant messages about macros, and you won’t have to worry that your macro-enabled dashboard will fall flat because macros have been disabled.

Trusted locations

If the thought of any macro message coming up (even one time) unnerves you, you can set up a trusted location for your files. A trusted location is a directory that is deemed a safe zone where only trusted workbooks are placed. A trusted location allows you and your clients to run a macro-enabled workbook with no security restrictions as long as the workbook is in that location.

To set up a trusted location, follow these steps:

1. Select the Macro Security button on the Developer tab. This activates the Trust Center dialog box.
2. Click the Trusted Locations button. This opens the Trusted Locations menu (see Figure 2.6), which shows you all the directories that are considered trusted.
3. Click the Add New Location button.
4. Click Browse to find and specify the directory that will be considered a trusted location.

After you specify a trusted location, any Excel file that is opened from this location will have macros automatically enabled.

Storing macros in your Personal Macro Workbook

Most user-created macros are designed for use in a specific workbook, but you may want to use some macros in all your work. 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 list (refer back to Figure 2.1 under “Creating Your First Macro”).

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.

Assigning a macro to a button and other form controls

When you create macros, you may want to have a clear and easy way to run each macro. A basic button can provide a simple but effective user interface.

As luck would have it, Excel offers a set of form controls designed specifically for creating user interfaces directly on spreadsheets. There are several different types of form controls, from buttons (the most-commonly-used control) to scrollbars.

The idea behind using a form control is simple. You place a form control on a spreadsheet and then assign a macro to it — that is, a macro you’ve already recorded. When a macro is assigned to the control, that macro is executed, or played, when the control is clicked.

Take a moment to create a button for the AddTotalRelative macro you created earlier. Here’s how:

1. Click the Insert button under the Developer tab. (See Figure 2.7.)
2. Select the Button Form Control from the drop-down list that appears.

3. Click the location where you want to place your button.

   When you drop the button control onto your spreadsheet, the Assign Macro dialog box, as shown in Figure 2.8, activates and asks you to assign a macro to this button.

4. Select the macro you want to assign to the button and then click OK.

At this point, you have a button that runs your macro when you click it! Keep in mind that all the controls in the Form Controls group (shown in Figure 2.7) work in the same way as the command button, in that you assign a macro to run when the control is clicked.

Placing a macro on the Quick Access toolbar

You can also assign a macro to a button in Excel’s Quick Access toolbar. The Quick Access Toolbar sits either above or below the Ribbon. You can add a custom button that will run your macro by following these steps:

1. Right-click your Quick Access toolbar and select Customize Quick Access Toolbar. This will open the dialog box illustrated in Figure 2.9.
2. Click the Quick Access Toolbar button on the left of the Excel Options dialog box.
3. Select Macros from the Choose Commands From drop-down list on the left.
4. Select the macro you want to add and click the Add button.
5. Change the icon by clicking the Modify button.
6. Click the OK button.

Menu bar

The VBE menu bar works just like every other menu bar you’ve encountered. It contains commands that you use to do things with the various components in the VBE. You will also find that many of the menu commands have shortcut keys associated with them.

The VBE also features shortcut menus. You can right-click virtually anything in the VBE and get a shortcut menu of common commands.

Toolbar

The Standard toolbar, which is directly under the menu bar by default, is one of four VBE toolbars available. You can customize the toolbars, move them around, display other toolbars, and so on. If you’re so inclined, use the View ➜ Toolbars command to work with VBE toolbars. Most people just leave them as they are.

Project window

The Project window displays a tree diagram that shows every workbook currently open in Excel (including add-ins and hidden workbooks). Double-click items to expand or contract them. You’ll explore this window in more detail in the “Working with the Project Window” section later in this chapter.

If the Project window is not visible, press Ctrl+R or use the View ➜ Project Explorer command. To hide the Project window, click the Close button in its title bar. Alternatively, right-click anywhere in the Project window and select Hide from the shortcut menu.

Code window

A Code window contains VBA code. Every object in a project has an associated Code window. To view an object’s Code window, double-click the object in the Project window. For example, to view the Code window for the Sheet1 object, double-click Sheet1 in the Project window. Unless you’ve added some VBA code, the Code window will be empty.

You find out more about Code windows later in this chapter’s “Working with a Code Window” section.

Immediate window

The Immediate window may or may not be visible. If it isn’t visible, press Ctrl+G or use the View ➜ Immediate Window command. To close the Immediate window, click the Close button in its title bar (or right-click anywhere in the Immediate window and select Hide from the shortcut menu).

The Immediate window is most useful for executing VBA statements directly and for debugging your code. If you’re just starting out with VBA, this window won’t be all that useful, so feel free to hide it and free up some screen space for other things.

Adding a new VBA module

When you record a macro, Excel automatically inserts a VBA module to hold the recorded code. The workbook that holds the module for the recorded macro depends on where you chose to store the recorded macro, just before you started recording.

In general, a VBA module can hold three types of code:

• Declarations: One or more information statements that you provide to VBA. For example, you can declare the data type for variables you plan to use, or set some other module-wide options.
• Sub procedures: A set of programming instructions that performs some action. All recorded macros will be Sub procedures.
• Function procedures: A set of programming instructions that returns a single value (similar in concept to a worksheet function, such as Sum).

A single VBA module can store any number of Sub procedures, Function procedures, and declarations. How you organize a VBA module is completely up to you. Some people prefer to keep all their VBA code for an application in a single VBA module; others like to split up the code into several different modules. It’s a personal choice, just like arranging furniture.

Follow these steps to manually add a new VBA module to a project:

1. Select the project’s name in the Project window.
2. Choose Insert ➜ Module.

Or you can:

1. Right-click the project’s name.
2. Choose Insert ➜ Module from the shortcut menu.

The new module is added to a Modules folder in the Project window (see Figure 2.12). Any module you create in a given workbook is placed in this Modules folder.

Removing a VBA module

You may want to remove a code module that is no longer needed. To do so, follow these steps:

1. Select the module’s name in the Project window.
2. Choose File ➜ Remove xxx, where xxx is the module name.

Or

1. Right-click the module’s name in the Project window.
2. Choose Remove xxx from the shortcut menu.

Minimizing and maximizing windows

Code windows are much like workbook windows in Excel. You can minimize them, maximize them, resize them, hide them, rearrange them, and so on. Most people find it much easier to maximize the Code window that they’re working on. Doing so lets you see more code and keeps you from getting distracted.

To maximize a Code window, click the maximize button in its title bar (right next to the X). Or, just double-click its title bar to maximize it. To restore a Code window to its original size, click the Restore button. When a window is maximized, its title bar isn’t really visible, so you’ll find the Restore button to the right of the Type a Question for Help box.

Sometimes, you may want to have two or more Code windows visible. For example, you may want to compare the code in two modules or copy code from one module to another. You can arrange the windows manually, or use the Window ➜ Tile Horizontally or Window ➜ Tile Vertically commands to arrange them automatically.

You can quickly switch among code windows by pressing Ctrl+Tab. If you repeat that key combination, you keep cycling through all the open code windows. Pressing Ctrl+Shift+Tab cycles through the windows in reverse order.

Minimizing a Code window gets it out of the way. You can also click the window’s Close button in a Code window’s title bar to close the window completely. (Closing a window just hides it; you won’t lose anything.) To open it again, just double-click the appropriate object in the Project window. Working with these Code windows sounds more difficult than it really is.

Getting VBA code into a module

Before you can do anything meaningful, you must have some VBA code in the VBA module. You can get VBA code into a VBA module in three ways:

• Use the Excel macro recorder to record your actions and convert them to VBA code.
• Enter the code directly.
• Copy the code from one module and paste it into another.

You have discovered the excellent method for creating code by using the Excel Macro recorder. However, not all tasks can be translated to VBA by recording a macro. You often have to enter your code directly into the module. Entering code directly basically means either typing the code yourself or copying and pasting code you have found somewhere else.

Entering and editing text in a VBA module works as you might expect. You can select, copy, cut, paste, and do other things to the text.

A single line of VBA code can be as long as you like. However, you may want to use the line-continuation character to break up lengthy lines of code. To continue a single line of code (also known as a statement) from one line to the next, end the first line with a space followed by an underscore (_). Then continue the statement on the next line. Here’s an example of a single statement split into three lines:

Selection.Sort Key1:=Range("A1"), _
    Order1:=xlAscending, Header:=xlGuess, _
    Orientation:=xlTopToBottom

This statement would perform exactly the same way if it were entered in a single line (with no line-continuation characters). Notice that the second and third lines of this statement are indented. Indenting is optional, but it helps clarify the fact that these lines are not separate statements.

The VBE has multiple levels of undo and redo. If you deleted a statement that you shouldn’t have, use the Undo button on the toolbar (or press Ctrl+Z) until the statement appears again. After undoing, you can use the Redo button to perform the changes you’ve undone.

Are you ready to enter some real, live code? Try the following steps:

1. Create a new workbook in Excel.
2. Press Alt+F11 to activate the VBE.
3. Click the new workbook’s name in the Project window.
4. Choose Insert ➜ Module to insert a VBA module into the project.

5. Type the following code into the module:

   Sub GuessName()
       Dim Msg as String
       Dim Ans As Long
       Msg ="Is your name" & Application.UserName &"?"
       Ans = MsgBox(Msg, vbYesNo)
       If Ans = vbNo Then MsgBox"Oh, never mind."
       If Ans = vbYes Then MsgBox"I must be clairvoyant!"
   End Sub

6. Make sure the cursor is located anywhere within the text you typed and press F5 to execute the procedure.

When you enter the code listed in Step 5, you might notice that the VBE makes some adjustments to the text you enter. For example, after you type the Sub statement, the VBE automatically inserts the End Sub statement. And if you omit the space before or after an equal sign, the VBE inserts the space for you. Also, the VBE changes the color and capitalization of some text. This is all perfectly normal. It’s just the VBE’s way of keeping things neat and readable.

If you followed the previous steps, you just created a VBA Sub procedure, also known as a macro. When you press F5, Excel executes the code and follows the instructions. In other words, Excel evaluates each statement and does what you told it to do. You can execute this macro any number of times — although it tends to lose its appeal after a few dozen executions.

This simple macro uses the following concepts:

• Defining a Sub procedure (the first line)
• Declaring variables (the Dim statements)
• Assigning values to variables (Msg and Ans)
• Concatenating (joining) two strings (using the & operator)
• Using a built-in VBA function (MsgBox)
• Using built-in VBA constants (vbYesNo, vbNo, and vbYes)
• Using an If-Then construct (twice)
• Ending a Sub procedure (the last line)

As mentioned previously, you can copy and paste code into a VBA module. For example, a Sub or Function procedure that you write for one project might also be useful in another project. Instead of wasting time reentering the code, you can activate the module and use the normal copy-and-paste procedures (Ctrl+C to copy and Ctrl+V to paste). After pasting it into a VBA module, you can modify the code as necessary.

The Editor tab

Figure 2.13 shows the options accessed by clicking the Editor tab of the Options dialog box. Use the options in the Editor tab to control how certain things work in the VBE.

• The Auto Syntax Check option: The Auto Syntax Check setting determines whether the VBE pops up a dialog box if it discovers a syntax error while you’re entering your VBA code. The dialog box tells roughly what the problem is. If you don’t choose this setting, VBE flags syntax errors by displaying them in a different color from the rest of the code, and you don’t have to deal with any dialog boxes popping up on your screen.
• The Require Variable Declaration option: If the Require Variable Declaration option is set, VBE inserts the following statement at the beginning of each new VBA module you insert: Option Explicit. Changing this setting affects only new modules, not existing modules. If this statement appears in your module, you must explicitly define each variable you use. Using a Dim statement is one way to declare variables.
• The Auto List Members option: If the Auto List Members option is set, VBE provides some help when you’re entering your VBA code. It displays a list that would logically complete the statement you’re typing. This is one of the best features of the VBE.
• The Auto Quick Info option: If the Auto Quick Info option is selected, VBE displays information about functions and their arguments as you type. This is similar to the way Excel lists the arguments for a function as you start typing a new formula.
• The Auto Data Tips option: If the Auto Data Tips option is set, VBE displays the value of the variable over which your cursor is placed when you’re debugging code. This is turned on by default and often quite useful. There is no reason to turn this option off.
• The Auto Indent setting: The Auto Indent setting determines whether VBE automatically indents each new line of code the same as the previous line. Most Excel developers are keen on using indentations in their code, so this option is typically kept on.
• The Drag-and-Drop Text Editing option: The Drag-and-Drop Text Editing option, when enabled, lets you copy and move text by dragging and dropping with your mouse.
• The Default to Full Module View option: The Default to Full Module View option sets the default state for new modules. (It doesn’t affect existing modules.) If set, procedures in the Code window appear as a single scrollable list. If this option is turned off, you can see only one procedure at a time.
• The Procedure Separator option: When the Procedure Separator option is turned on, separator bars appear at the end of each procedure in a Code window. Separator bars provide a nice visual line between procedures, making it easy to see where one piece of code ends and where another starts.

Specifying properties for the active object

When you’re working with Excel, only one workbook at a time can be active. In that workbook, only one sheet can be active. And if the sheet is a worksheet, one cell is the active cell (even if a multicell range is selected). VBA knows about active workbooks, worksheets, and cells and lets you refer to these active objects in a simplified manner.

This method of referring to objects is often useful because you won’t always know the exact workbook, worksheet, or range on which you want to operate. VBA makes object referencing easy by providing properties of the Application object. For example, the Application object has an ActiveCell property that returns a reference to the active cell. The following instruction assigns the value 1 to the active cell:

ActiveCell.Value = 1

Note that in the preceding example, we omitted the reference to the Application object and to the active worksheet because both are assumed. This instruction will fail if the active sheet isn’t a worksheet. For example, if VBA executes this statement when a chart sheet is active, the procedure halts, and you get an error message.

If a range is selected in a worksheet, the active cell is a cell within the selected range. In other words, the active cell is always a single cell (never a multicell range).

The Application object also has a Selection property that returns a reference to whatever is selected, which may be a single cell (the active cell), a range of cells, or an object such as ChartObject, TextBox, or Shape.

Table 2.1 lists the other Application properties that are useful when working with cells and ranges.

The advantage of using these properties to return an object is that you don’t need to know which cell, worksheet, or workbook is active, and you don’t need to provide a specific reference to it. This allows you to write VBA code that isn’t specific to a particular workbook, sheet, or range. For example, the following instruction clears the contents of the active cell, even though the address of the active cell isn’t known:

ActiveCell.ClearContents

The example that follows displays a message that tells you the name of the active sheet:

MsgBox ActiveSheet.Name

If you want to know the name and directory path of the active workbook, use a statement like this:

MsgBox ActiveWorkbook.FullName

If a range on a worksheet is selected, you can fill the entire range with a value by executing a single statement. In the following example, the Selection property of the Application object returns a Range object that corresponds to the selected cells. The instruction simply modifies the Value property of this Range object, and the result is a range filled with a single value:

Selection.Value = 12

Note that if something other than a range is selected (such as a ChartObject or a Shape), the preceding statement generates an error because ChartObject and Shape objects don’t have a Value property.

The following statement, however, enters a value of 12 into the Range object that was selected before a non-Range object was selected. If you look up the RangeSelection property in the Help system, you find that this property applies only to a Window object.

ActiveWindow.RangeSelection.Value = 12

To find out how many cells are selected in the active window, access the Count property. Here’s an example:

MsgBox ActiveWindow.RangeSelection.Count

Understanding methods

Methods are the actions that can be performed against an object. It helps to think of methods as verbs. You can paint your house, so in VBA, that translates to something like house.paint.

A simple example of an Excel method is the Select method of the Range object:

Range("A1").Select

Another is the Copy method of the Range object:

Range("A1").Copy

Some methods have arguments that can dictate how they are applied. For instance, the Paste method can be used more effectively by explicitly defining the Destination argument.

ActiveSheet.Paste Destination:=Range("B1")

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 VBA 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 you need help with. If you aren’t connected to the Internet, Excel gives you a message telling you that you need to be online to use Help.