In Microsoft Excel, you can run Microsoft Visual Basic for
Applications
Sub procedures and Microsoft Excel version 4.0 macros from a Visual
Basic procedure by using the
Application.Run and
Application.ExecuteExcel4Macro methods. You can also run Visual Basic
Sub procedures with the
Call method or by entering the name of a procedure on a line by
itself.
This article illustrates several methods that you can use to
run
Sub procedures and Microsoft Excel version 4.0 macros from Visual
Basic in Microsoft Excel.
Back to the top
Microsoft
provides programming examples for illustration only, without warranty either
expressed or implied, including, but not limited to, the implied warranties of
merchantability and/or fitness for a particular purpose. This article assumes
that you are familiar with the programming language being demonstrated and the
tools used to create and debug procedures. Microsoft support professionals can
help explain the functionality of a particular procedure, but they will not
modify these examples to provide added functionality or construct procedures to
meet your specific needs. If you have limited programming experience, you may
want to contact a Microsoft Certified Partner or the Microsoft fee-based
consulting line at (800) 936-5200. For more information about Microsoft
Certified Partners, please visit the following Microsoft Web site:
For more information about the support options that are available
and about how to contact Microsoft, visit the following Microsoft Web site:
Back to the top
Method 1: Application.Run
You can use the
Application.Run method to run Visual Basic
Sub procedures or Microsoft Excel version 4.0 macros from other
Visual Basic procedures. The
Application.Run method requires one named argument: the name of the macro or
Sub procedure to be run. (However, other optional arguments may also
be included.) This name can be a text string (for example, "TestXLM") or it can
be a variable that is equal to the name of the macro.
Case 1: Text String
To run a Microsoft Excel version 4.0 macro called TestXLM, you
could use this text string:
Application.Run "TestXLM"
Case 2: Variable
If you have the variable "MacroToRun" set to "TestXLM," you could
use this variable:
Application.Run MacroToRun
Back to the top
Method 2: Application.ExecuteExcel4Macro
You can also use the
Application.ExecuteExcel4Macro method to run Microsoft Excel version 4.0 macros or other Visual
Basic
Sub procedures, but the syntax is somewhat different. To use
Application.ExecuteExcel4Macro to run a macro or
Sub procedure, you must also include the Microsoft Excel version 4.0
RUN() function, as in the following examples:
Application.ExecuteExcel4Macro "RUN(""TestXLM"")"
-or-
Application.ExecuteExcel4Macro "RUN(""" & MacroToRun & """)"
When you use
Application.ExecuteExcel4Macro, you must use quotation marks. For example, to use the
RUN() function, you must enclose the name of the argument in
quotation marks:
RUN("TestXLM")
The entire string must then be enclosed in quotation marks. When you
add quotation marks to the outside of the string, you must add an additional
quotation mark adjacent to each quotation mark within the string:
"RUN(""TestXLM"")"
The
Application.ExecuteExcel4Macro command that uses a variable inside the
RUN() function is more complex than the equivalent
Application.Run method. For the command to be properly evaluated, the macro
string must be entered as:
"RUN(""" & MacroToRun & """)"
This command is evaluated as
RUN("" & MacroToRun & "")
which is a valid Microsoft Excel version 4.0 macro command.
Back to the top
Method 3: The Call Method
The
Call method may be used to run Visual Basic
Sub procedures, but not Microsoft Excel version 4.0 macros. For
example, to run the
Sub procedure
TestVBSub, you would use this method:
Call TestVBSub
You cannot pass a variable name to the
Call method. For example, if you have the variable "SubToRun" set to
"TestVBSub," you cannot run the
TestVBSub Sub procedure with the following:
Call SubToRun
Back to the top
Method 4: Run a Sub Procedure Using Only Its Name
You can run a Visual Basic
Sub procedure by entering its name on a line by itself. For example,
if you want your
Sub procedure to run the
TestVBSub subroutine, you would enter
TestVBSub
on a line by itself. When that line in the subroutine is executed, it
will run the
TestVBSub subroutine.
Back to the top
Method 5: Sample Visual Basic Procedures
To create six
Sub procedures that illustrate the most common methods you can use to
run a Visual Basic
Sub procedure or Microsoft Excel version 4.0 macro from another
Visual Basic procedure, follow these steps:
| 1. | In a new workbook, insert a Microsoft Excel 4.0 macro sheet
called Macro1 and a Visual Basic module called Module1.
NOTE: To insert a Visual Basic module in Microsoft Excel, press
ALT+F11 to activate the Visual Basic Editor. Then, click Module on the Insert menu. |
| 2. | On the macro sheet, enter the following macro:
A1: TestXLM
A2: =ALERT("TestXLM works!")
A3: =RETURN()
|
| 3. | On the macro sheet, select cell A1. |
| 4. | On the Insert menu, point to Name, and then click Define. |
| 5. | Verify that the following information appears in the Define Name dialog box:
| | | • | The Names In Workbook box contains the name TestXLM. | | • | The Refers To box contains the reference =Macro1!$A$1. | | • | The Command option is selected under Macro. |
|
When the settings are as specified above, click OK to define the name of the macro. |
| 6. | In Module1, enter the following Sub procedures:
Option Explicit
'The TestVBSub subroutine displays a message box: it is the Visual
'Basic equivalent of the TestXLM macro shown above.
Sub TestVBSub()
MsgBox "TestVBSub works!" 'Displays a message box.
End Sub
'The Test1 Sub procedure makes use of the Application.Run method with
'hard-coded macro/subroutine names.
Sub Test1()
Application.Run "TestVBSub"
Application.Run "TestXLM"
End Sub
'The Test2 Sub procedure makes use of the Application.Run method with
'variable macro/Sub procedure names.
Sub Test2()
Dim SubToRun As String, MacroToRun As String
SubToRun = "TestVBSub"
MacroToRun = "TestXLM"
Application.Run SubToRun
Application.Run MacroToRun
End Sub
'The Test3 Sub procedure makes use of the
'Application.ExecuteExcel4Macro
'method with hard-coded macro/Sub procedure names.
Sub Test3()
'Note the extra quotation marks which are contained within the
'RUN statements. These are required in order for the command to
'evaluate properly.
Application.ExecuteExcel4Macro "RUN(""TestVBSub"")"
Application.ExecuteExcel4Macro "RUN(""TestXLM"")"
End Sub
'The Test4 Sub procedure makes use of the
'Application.ExecuteExcel4Macro
'method with variable macro/Sub procedure names.
Sub Test4()
Dim SubToRun As String, MacroToRun As String
SubToRun = "TestVBSub"
MacroToRun = "TestXLM"
'Note the extra quotation marks which are contained within the
'RUN statements. These are required in order for the command to
'evaluate properly.
Application.ExecuteExcel4Macro "RUN(""" & SubToRun & """)"
Application.ExecuteExcel4Macro "RUN(""" & MacroToRun & """)"
End Sub
'The Test5 Sub procedure uses the Call method with hard-coded
'Sub procedure names.
Sub Test5()
Call TestVBSub
End Sub
'The Test6 Sub procedure runs the TestVBSub subroutine because its
'name is entered on a line by itself.
Sub Test6()
TestVBSub
End Sub
|
When you run Test1, Test2, Test3, or Test4, two alert boxes
will appear with the messages "TestVBSub works!" and "TestXLM works!" When you
run Test5 or Test6, one alert box will appear with the message "TestVBSub
works!"
Back to the top
