Subroutines and Functions
What's the difference between a VBA subroutine and a macro?
Nothing really. With Excel 95, these terms can be used pretty much interchangeably.
How can I run a VBA subroutine macro?
You can choose from many methods:
- From another subroutine, just enter the subroutine name as a statement, or use the Call keyword.
- Assign a shortcut key to the macro using the Tools Macro command.
- Assign the macro to a command on the Tools menu using the Tools Macro command.
- Assign the macro to an object such as a button or rectangle. Select the object and use the Tools Assign Macro command. This technique changes the OnAction property of the object.
- Assign the macro to a toolbar button, by using the View Toolbars command, and then click the Customize button.
- Give the subroutine any of four special names to cause it to execute automatically. The special names are Auto_Open, Auto_Close, Auto_Activate, and Auto_Deactivate.
- Change certain properties of certain objects to cause the macro to execute when a particular event occurs. Example of such properties are OnTime, OnDoubleClick, OnEntry, OnKey. There are several others.
Can a custom worksheet function written in VBA perform the same types of actions as a subroutine?
No. Functions called from worksheets 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 perform only calculations. 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'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 formulas. These new 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?
First, set up a reference to the workbook that contains the subroutine that you want to call (references are set up with the Tools References command). Then, precede the subroutine name with the workbook name (in square brackets), a period, and the module name (in square brackets). The following example calls the SetUp procedure in Module1 of BUDGET.XLS:
[BUDGET.XLS].[Module1].SetUp
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 (refer to the previous question), you can use the function without preceding it with the workbook name.
Can I use the same Subroutine or Function name in multiple open workbooks?
Sure. If you call a subroutine from a VBA procedure, it first searches for the subroutine in the current workbook. If not found, it then searches in all workbooks to which the current workbook has references (references are set up with the Tools References command). It runs the first subroutine it finds with the name you specified.
I have a workbook with an Auto_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 Auto_Close, Auto_Activate, and Auto_Deactivate. However, it does not work if the workbook you're opening is an add-in.
I've written a VBA macro that opens a workbook. When I execute the macro the workbook is opened, but its Auto_Open procedure doesn't get executed. What's wrong?
This is normal behavior. If you want to execute the Auto_Open procedure when a workbook is opened by a macro, you must use the RunAutoMacros method. The online help describes this method, and includes examples.
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 EXCEL/XLSTART directory. To have the macro execute automatically, just name it Auto_Open.
I have a macro that will eventually be converted to an add-in. However, for testing purpose I would like it to perform some different actions. Is there a way to determine if the code is running from an XLS or from an XLA file?
Yes. Access the FileFormat property of the workbook. The following expression returns True if the workbook is an add-in:
ActiveWorkbook.FileFormat = xlAddin
I created a custom worksheet function. When I access this function using the Function Wizard, it says "no help available." How can I get Function Wizard to display a description of this function?
In your VBA module, press F2 to access the Object Browser. Select the workbook that contains the function from the Libraries/Worksheets drop-down list. Then select the module in the Objects/Modules list box. Then select your function from the Methods/Properties list box. Click the Options button and enter the descriptive text in the Description box.
Can I also display help for the arguments for my custom function in the Function Wizard?
No. This is not possible.
Is it possible to write recursive functions in VBA?
A recursive function is a function that calls itself a number of times. Theoretically, it's possible. But in practice, you often run into stack limitations that cause the function to fail. In general, it's best to avoid using recursive functions in VBA.
I have a custom function that will be used in a worksheet formula. If the user enters arguments that are not appropriate, how can I make the function return a true error value (#VALUE)?
If your function is named MyFunction, you can use the following to return an error value to the cell:
MyFunction = CVErr(xlErrValue)
In this example, xlErrValue is a pre-defined constant. Constants for the other error values are listed in the online help.
I wrote a custom worksheet function that uses the Application.Volatile property. I've found that if I move or delete a sheet in the workbook, formulas that use my custom function return a #VALUE error. What gives?
Microsoft has confirmed this to be a bug in the original release of Excel 95 (it may be fixed if you have a later version). The only workaround I know of is to code the function using the XLM macro language.
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 using the Public keyword.
Is it possible to pass arguments to a VBA subroutine executed by a custom menu?
Yes. Assume you have a subroutine called MySub, and it's designed to accept one argument. Further assume that you would like to call the subroutine from a custom menu, and pass the value 10 as an argument. In the menu editor, specify the macro and its argument value separated by a spaced and enclosed in single quotes, like this:
'MySub 10'
To pass additional arguments, separate each argument with a comma (still within the single quotes).
I'm trying to write a VBA procedure that executes an SQL query on an external database. I'm having lots of problems with long queries. Am I missing something?
Before you can work with SQL databases, you need to load the XLODBC.XLA add-in. This gives you access to several new functions. The SQLExecQuery and SQLRequest functions are used to perform a query, but they have some problems. The most notable problem is that the query cannot exceed 255 characters. A common workaround is to convert a long query string into an array, and then pass the array. However, if you do this, each array element cannot exceed 127 characters. Another approach is to store your query in a worksheet range, and then use a statement such as:
Res = SQLExecQuery(Connection,Worksheets("Sheet1").Range("D1:D4"))
In this case, the query is stored in four cells on Sheet1. If you use this method, each cell can contain no more than 127 characters.
Can you use Excel's built-in worksheet functions in your VBA code?
Yes. Excel's worksheet functions are accessed as if they were methods of the Application object. Therefore, you must precede the function name with a reference to Application. For example, you could access the POWER worksheet functions with a statement such as:
Ans = Application.Power(5, 3)
This example raises 5 to the third power. Generally, if VBA includes an equivalent function you cannot use Excel's worksheet version. For example, because VBA has a function to compute square roots (Sqr) you cannot use the SQRT worksheet function in your VBA code.
Is it possible to create your own DLLs (dynamic link libraries) that can be called from Excel VBA code?
Yes. The procedure is described in the Microsoft Excel Developer's Kit, which is available directly from Microsoft or at many retail stores. You do, however, need to know how to program in C or Pascal to create DLLs. Visual Basic Professional Edition 4.0 also lets you create DLL files.