Objects, Properties, and Methods
I don't understand the concept of objects. Is there a listing of all of the Excel objects I can use?
Yes. The online help includes the information in a graphical format. In addition, the companion CD-ROM includes a workbook file in outline format This file shows you how the objects relate to each other, and which objects are contained within other objects.
I'm overwhelmed with all of the properties and methods available. How can I find out which methods and properties are available for a particular object?
There are several ways. You can also use the Object Browser from a VBA module. Press F2 to access the Object Browser. Choose Excel from the Libraries/Workbooks drop-down list. The list on the left shows all of the Excel objects. When you select an object, its corresponding properties and methods appear in the list on the right (unfortunately, there's no indication as to whether the entry in the right list is a method or a property).
The on-line help system for VBA is very extensive, and lists the properties and methods available for every object. The easiest way to access this is to enter the object in your VBA module and move the cursor anywhere within the object name. Press F1 and you'll get the help topic appropriate for the object.
I don't understand the different between ActiveWorkbook and ThisWorkbook. Can they be used interchangeably?
Sometimes yes, but generally no. Both of these references return a Workbook object. ThisWorkbook returns the workbook that contains the VBA code that is running. ActiveWorkbook returns the workbook that currently has the focus. In some cases these may be the same workbook. When you're executing code contained in an add-in, the add-in will never be the ActiveWorkbook.
What's the story with collections? Is a collection an object? What are collections?
A collection is an object that contains a group of related objects. A collection is designated by a plural noun. For example, the Worksheets collection is an object that contains all of the Worksheet objects in a workbook. You can think of this as an array. Worksheets(1), for example, refers to the first Worksheet object in the workbook. Rather than use index numbers, you can also use the actual worksheet name, such as Worksheets("Sheet1"). The concept of a collection makes it easy to work with all related objects at once, and to loop through all objects in a collection using the For Each...Next construct.
Can a VBA procedure access a cell's value in a workbook that is not open?
No. A formula in a worksheet can do this, but VBA cannot.
I want to use the OnDoubleClick property to execute a macro when I double-click a cell. But I also like the idea of editing cell contents directly in the cell (which occurs when I double-click). Does this mean that I have to turn off the Edit Directly in Cell setting in the Edit tab of the Options dialog box?
No. This setting is automatically disabled when the OnDoubleClick property of a worksheet contains a macro.
What's the difference between Application.Goto and Range.Select?
Use Application.Goto to select a named range in a workbook (range names are global in a workbook). Application.Goto may or may not make another sheet the active sheet. Range.Select will select a range on the active worksheet only.
Is there a VBA command to quit Excel? When I try to record the File Exit command, Excel closes down before I can see what code it generates!
Use Application.Quit to end Excel.
How can I turn off the screen updating while a macro is running?
The following statement turns off screen updating and speeds up macros that modify the display:
Application.ScreenUpdating=False
Is it possible to display messages in the status bar while a macro is running? I have a lengthy macro, and it would be nice to display its progress in the status bar.
Yes. Assign the text to the StatusBar property of the Application object. Here's an example:
Application.StatusBar = "Now processing File " & FileNum
When your routine finishes, return the status bar back to normal with the following statement:
Application.StatusBar = False
I recorded a VBA macro that copies a range and pastes it to another area. The macro uses the Select method. Is there a more efficient way to copy and paste?
Yes. Although the macro recorder generally selects cells before doing anything with them, selecting is not necessary and may actually slow down your macro. Recording a very simple copy and paste operation generates four lines of VBA code (two of which use the Select method). Here's an example:
Range("A1").Select
Selection.Copy
Range("B1").Select
ActiveSheet.Paste
These four lines can be replaced with a single statement, such as:
Range("A1").Copy(Range("B1"))
Notice that this statement does not use the Select method.
My VBA macro needs to count the number of rows selected by the user. Using Selection.Rows.Count doesn't work when nonadjacent rows are selected. Is this a bug?
Actually, this is the way it's supposed to work. The Count method returns the number of elements in only the first Area of the selection (a noncontiguous selection has multiple areas). To get an accurate row count your VBA code must first determine the number of areas in the selection and then count the number of rows in each area. Use Selection.Areas.Count to count the number of areas. Here's an example that stores the total number of selected rows in the NumRows variable:
NumRows = 0 For Each area In Selection.Areas NumRows = NumRows + area.Rows.Count Next area
By the way, this is also relevant to counting columns and cells.
Is there a workbook property that will force an Excel workbook to always remain visible - similar to the "Always on Top" option available in WinHelp?
No.
Is there a way to stop Excel from displaying messages while my macro is running? For example, I'd like to eliminate the message that appears when my macro deletes a worksheet.
The following statement turns off most of Excel's warning messages:
Application.DisplayAlerts = False
I can't find any VBA commands to automate Solver. Does this mean I can't create macros that use Solver?
Solver is an add-in, not part of Excel. Therefore, it's not part of Excel's object model. However, you can control Solver using two different techniques. You can write XLM macro code and execute it from a VBA module. To see how this works, try recording your actions to a VBA macro while setting up a problem for Solver. Another option is to add a reference to Solver.xla using the Tools References command in a VBA module. Doing so gives you access to 13 Solver functions that you can use in your VBA code. These functions, which are not documented, actually call XLM functions. The advantage of the second method is that your code is a bit cleaner.