Developer FAQ - Objects, Properties, And Methods
Note: This document was written for Excel 97 - 2000.
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.
I’m overwhelmed with all 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 available in the VBE. Press F2 to access the Object Browser, then choose Excel from the Libraries/Workbooks drop-down list. The list on the left shows all the Excel objects. When you select an object, its corresponding properties and methods appear in the list on the right.
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.
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 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 by using the For Each...Next construct.
When I refer to a worksheet in my VBA code, I get a "subscript out of range" error. I'm not using any subscripts. What gives?
This error will occur if you attempt to access an element in a collection that doesn't exist. For example, the statement below will generate the error if the active workbook does not contain a sheet named "MySheet".
Set X = ActiveWorkbook.Sheets("MySheet")
How can I prevent the user from scrolling around the worksheet?
You can either hide the unused rows and columns, or use a VBA statement to set the scroll area for the worksheet. The statement below, for example, sets the scroll area on Sheet1 so the user cannot activate any cells outside of B2:D50.
Sheets("Sheet1").ScrollArea = "B2:D50"
To set scrolling back to normal, use a statement like this:
Sheets("Sheet1").ScrollArea = ""
Be aware that the ScrollArea setting is not saved with the workbook. Therefore, you'll need to execute the VBA statement whenever the workbook is opened. You can use a Workbook_Open subroutine to do this.
What’s the difference between using Select and Application.Goto?
Range.Select will select a range on the active worksheet only. Use Application.Goto to select a range on any worksheet in a workbook. Application.Goto may or may not make another sheet the active sheet. The Goto method also lets you scroll the sheet so the range is in the upper left corner.
What's the difference between activating a range and selecting a range?
In some case, the Activate method and the Select method have exactly the same effect. But in other cases, they produce quite different results. Assume that range A1:C3 is selected. The statement below activates cell C3. The original range remains selected, but C3 becomes the active cell.
Again, assuming that range A1:C3 is selected, the statement below selects a single cell (which also becomes the active cell).
I know how to write a VBA statement to select a range using a cell address, but how can I write a statement to select a range if I know only the row and column number?
Use the Cells method. The statement below, for example, selects the cell in the fifth row and the 12th columns.
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 the following statement 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:
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 the following:
Notice that this statement does not use the Select method.
I have not been able to find a method to sort a VBA array. Does this mean that I have to copy the values to a worksheet and then use the Range.Sort method?
There is no built-in way to sort an array in VBA. Copying the array to a worksheet is one method, but you'll probably be better off if you write your own sorting routine. There are many sorting algorithms available, and some are quite easy to code in VBA. Excel 2000 Power Programming With VBA contains VBA code for several sorting techniques.
My macro works with the selected cells, but it fails if something else (like a chart) is selected. How can I make sure that a range is selected?
You can use VBA's TypeName function to check the Selection. Here's an example:
If TypeName(Selection) <> "Range" Then MsgBox "Select a range!" Exit Sub End If
Another alternative is to use the RangeSelection property, which returns a Range object that represents the selected cells on the worksheet in the specified window (even if a graphic object is active or selected). This property applies to a Window object, not a Workbook object. The statement below, for example, displays the address of the selected range.
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 so it won't be hidden by another application's window?
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
Is there a VBA command to select the last entry in a column or row? Normally, I can use Ctrl+Shift+down arrow or Ctrl+Shift+right arrow to do this, but how can I do it with a macro?
The VBA equivalent for Ctrl+Shift+down arrow is the following:
The constants used for the other directions are XLToLeft, XLToRight, and XLUp.
How can I determine the last non-empty cell in a particular column?
The statement below displays the address of the last non-empty cell in column A.
The statement in the previous question doesn't work if cell A65536 is not empty.
To handle that unlikely occurrence, use this code:
With ActiveSheet.Range("A65536") If .Value <> "" Then MsgBox .Address Else MsgBox .End(xlUp).Address End If End With
VBA references can become very lengthy, especially when you need to fully qualify an object by referencing its sheet and workbook. Is there a way to reduce the length of these references?
Yes, create an object variable by using the Set command. Here’s an example:
Dim MyRange as Range Set _MyRange = _ ThisWorkbook.Worksheets("Sheet1").Range("A1")
After the Set statement is executed, you can refer to the Range object simply as MyRange. For example, you can assign a value to the range with the following:
MyRange.Value = 10
Besides making it easier to refer to objects, using object variables can also make your code execute more quickly.
Is there a way to dimension an array if you don’t know how many elements it will have?
Yes. You can dimension the array with the Dim command by using empty parentheses, and then redimension with the ReDim command when you know how many elements it has. Use ReDim Preserve if you don’t want to lose the current array contents when redimensioning it.
How can I write a macro to select some, but not all of the sheets in a workbook?
Use an argument (False) for the Select method. For example, the subroutine below selects all Chart sheets in the active workbook.
Sub SelectSheets() For Each sht In Sheets If TypeName(sht) = "Chart" Then sht.Select False Next sht End Sub
Can I let the user undo my macro?
Yes, but it's not something that can be done automatically. In order to allow the user to undo the effects of a macro, your macro must keep track of what was changed by the macro, and then restore the original state if the user selects Edit - Undo.
To enable the Edit - Undo command, use the OnUndo method as the last action in your macro. This method lets you specify text that will appear on the Undo menu item, and also specify a procedure to run if the user selected Edit - Undo. Here's an example:
Application.OnUndo "The Last Macro", "MyUndoMacro"
I have a 1-2-3 macro that pauses so the user can enter data into a certain cell. How can I get the same effect in a VBA macro?
Excel can't duplicate that type of behavior, but you can use Excel's InputBox statement to get a value from a user and place it in a particular cell. The statement below, for example, displays an input box. When the user enters a value, that value is placed in cell A1.
UserVal = Application.InputBox("Value?", , , , , , , 1) If UserVal <> False Then Range("A1") = UserVal
VBA has an InputBox function, and there's also an InputBox method for the Application object. Are these the same?
No. Excel's InputBox method is more versatile since it allows validation of the user's entry. The preceding example uses 1 (which represents a numeric value) for the last argument of the InputBox method. This ensures that the user enters a value into the input box.
I can use Excel's File - Properties command to add custom properties to a workbook. How can my VBA macro read these custom properties?
Unfortunately, there is no way to do that. Chalk it up to a deficiency of the object model.
When I use the RGB function to assign a color, the color sometimes isn't correct. What am I doing wrong?
Probably nothing. An Excel workbook can only use 56 different colors (the color palette). If a specified RGB color isn't in the palette, Excel uses the closest match it can find.
I'm trying to write a VBA statement that creates a formula. In order to do so, I need to insert a quote character within quoted text. How can I do that?
Sure. Assume you want to enter the following formula into cell B1 using VBA
The statement below generates a syntax error:
Range("B1").Formula = "=IF(A1="Yes",TRUE,FALSE)"
The solution is to use double quotes. The statement below produces the desired result.
Range("B1").Formula = "=IF(A1=""Yes"",TRUE,FALSE)"
Another approach is to use VBA's Chr function with an argument of 34 (which returns a quotation mark). The example below demonstrates.
Range("B1").Formula = _ "=IF(A1=" & Chr(34) & "Yes" & Chr(34) & ",TRUE,FALSE)"
I created an array, but the first element is really the second element. What's wrong?
Unless you tell it otherwise, VBA uses 0 as the first index number for an array. If you want your arrays to start with 1, insert the following statement at the top of your VBA module:
Option Base 1
Or, you can specify the upper and lower bounds of an array when you dimension it. Here's an example:
Dim Months(1 to 12) as String
I would like my VBA code to run as quickly as possible. Any suggestions?
Yes. Here are a few general tips. Make sure that you declare all of your variables (use Option Explicit at the top of your modules to force yourself to do this). If you reference an object more than once, create an object variable. Use the With...End With construct whenever possible. Finally, if your macro writes information to a worksheet, turn off screen updating by using:
Application.ScreenUpdating = False
Search for Tips
Browse Tips by Category
Needs tips? Here are two books, with nothing but tips:
Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks