Developer FAQ - Subroutines
Note: This document was written for Excel 97 - 2000.
What's the difference between a VBA subroutine and a macro?
Nothing, really. The term macro is a carry-over from the old days of spreadsheets. These terms are now used interchangeably.
What's a procedure?
A procedure can be either a subroutine or a function.
What is a Variant data type?
Variables that aren't specifically declared are assigned as a variant type, and VBA automatically converts the data to the proper type when it's used. This is particularly useful when getting values from a worksheet cell when you don't know in advance what the cell contains. Generally, it's a good idea to specifically declare your variables (using the Dim statement), because using variants is quite a bit slower.
What's the difference between a Variant array and an array of Variants?
A Variant is a special data type that can contain any kind of data -- a single value, or an array of values (that is, a Variant array). The code below creates a Variant that contains an array.
Dim X as Variant X = Array(30, 40, 50)
A normal array can contain items of a specified data type, including non-typed Variants. The statement below creates an array that consists of 12 Variants.
Dim X (0 to 2) as Variant
Although a Variant containing an array is conceptually different from an array whose elements are of type Variant, the array elements are accessed in the same way.
What's a type definition character?
VBA lets you append a character to a variable's name to indicate the data type. For example, you can declare the MyVar variable as an integer by tacking % onto the name, as follows:
Here's a list of the type-declaration characters supported by VBA:
Integer % Long & Single ! Double # Currency @ String $
Can a custom worksheet function written in VBA perform the same types of actions as a subroutine?
No. Functions called from a worksheet formula have some limitations. In general, they must be strictly "passive" -- they can't change the active cell, apply formatting, open workbooks, change the active sheet, and so on.
Functions can only perform calculations and return a value. An exception to this rule is the VBA MsgBox function. A custom function can display a MsgBox whenever it is recalculated. This is very handy for debugging a custom function.
I would like to create a function or subroutine that automatically changes the formatting of a cell based on the data I enter. For example, if I enter a value greater than 0, the cell's background color should be red. Is this possible?
It's certainly possible, and you don't need any programming. Use Excel's Conditional Formatting feature, accessed with the Format - Conditional Formatting command.
The Conditional Formatting features is useful, but I'd like to perform other types of operations when data is entered into a cell.
In that case, you can take advantage of the Change event for a worksheet object. Whenever a cell is changed, the Change event is triggered. If the code module for the Sheet object contains a procedure named Worksheet_Change, this procedure will be executed automatically.
What other types of events can be monitored?
Lots! Search the online help for events to get a complete listing.
I tried entering an event procedure (Sub Workbook_Open), but the procedure isn't executed when the workbook is open. What's wrong?
You probably put the procedure in the wrong places. Workbook event procedures must be in the code module for the ThisWorkbook object. Sheet event procedures must be in the code module for the appropriate Sheet object.
I can write an event procedure for a particular workbook. Is it possible to write an event procedure that will work for any workbook that's open?
Yes, but you need to use a class module. Details are in Chapter 18 of Excel 2000 Power Programming With VBA.
I'm very familiar with creating formulas in Excel. Does VBA use the same mathematical and logical operators?
Yes. And it includes some additional operators that aren't valid in worksheet formulas. These additional VBA operators are:
\ Division with an integer result
Eqv Returns True if both expressions are true or both are false
Imp Logical implication on two expressions
Is Compares two object variables
Like Compares two strings using wildcard characters
Xor Returns True if only one expression is true
How can I execute a subroutine that's in a different workbook?
Use the Run method of the Application object. The statement below executes a subroutine named Macro1 located in the Personal.xls workbook.
I've created several custom functions using VBA. I like to use these functions in my worksheet formulas, but I find it inconvenient to precede the function name with the workbook name. Is there any way around this?
Yes. Convert the workbook that holds the function definitions to an XLA add-in. When the add-in is open, you can use the functions in any other worksheet without referencing the function's filename.
Also, if you set up a reference to the workbook that contains the custom functions, you can use the function without preceding it with the workbook name. To create a reference, use the Tools - References command in the VBE.
I would like a particular workbook to be loaded every time I start Excel. I would also like a macro in this workbook to execute automatically. Am I asking too much?
Not at all. To open the workbook automatically, just store it in your \XLStart directory. To have the macro execute automatically, create a Workbook_Open macro in the code module for the ThisWorkbook object.
I have a workbook that uses a Workbook_Open subroutine. Is there a way to prevent this from executing when I open the workbook?
Yes. Hold down Shift when you issue the File - Open command. This technique also works with the Workbook_BeforeClose procedure. However, it does not work if the workbook you're opening is an add-in.
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.
However, there is a way to do this by using an XLM macro.
How can I prevent the "save file" prompt from being displayed when I close a workbook from VBA?
Insert the following statement to eliminate this and other prompts:
Application.DisplayAlerts = False
How can I set things up so my macro runs once every hour?
You need to use the OnTime method of the Application object. This lets you specify a subroutine to execute at a particular time of day. When the subroutine ends, use the OnTime method again to schedule another event in one hour.
How do I prevent a macro from showing in the macro list?
Declare the subroutine using the Private keyword:
Private Sub MyMacro()
Or, you can add a dummy optional argument:
Sub MyMacro (Optional FakeArg)
I wrote a macro that creates lots of charts. After some of the charts are created, I get a "not enough memory" error. My system has lots of memory, so what's the problem?
Most likely, your system is running low on system resources. In some versions of Excel, creating charts uses system resources that are not returned to the system. The only way to regain the system resources is to restart Windows. Try upgrading to SR-2.
Is it possible to save a chart as a GIF file?
Yes, the code below saves the first embedded chart on Sheet1 as a GIF file named Mychart.gif.
Set CurrentChart = Sheets("Sheet1").ChartObjects(1).Chart Fname = ThisWorkbook.Path & "\Mychart.gif" CurrentChart.Export Filename:=Fname, FilterName:="GIF"
Are variables in a VBA procedure available to other VBA procedures? What if the procedure is in a different module? Or in a different workbook?
You're talking about a variable's scope. There are three levels of scope: local, module-level, and public. Local variables have the narrowest scope and are declared within a procedure. A local variable is visible only to the procedure in which it was declared. Module-level variables are declared at the top of a module, prior to the first procedure. Module-level variables are visible to all procedures in the module. Public variables have the broadest scope, and they are declared by using the Public keyword.
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