Microsoft Office Tutorials and References
In Depth Information
Choosing the Scope and Lifetime of Your Constants
Note that once you declare a constant in the macro, you cannot assign a different value to it later in
the macro. If you need the value to change during the macro, what you really need is a variable instead
of a constant.
choosing the scope and lifetime of your constants
The scope and lifetime of constants are much the same as for variables:
For the constant to be available only to a particular macro, declare the constant within that
For the constant to be available only to the macros that are housed in the same module,
declare the constant at the top of that module, above and outside all macros.
For the constant to be available to all macros in all modules, prefix the constant declaration
with the Public statement, and set it at the top of a standard module, above and outside all
macros. For example:
Public Const SalesTax as Double = .0825
In this lesson you practice creating a macro that includes a declared variable. Create a macro,
without using the Macro Recorder, in which you declare a variable for the String data type, and you
manipulate the string text with a few lines of practice code.
Create a macro that includes the following actions:
Declare a String type variable.
Assign text to the String variable.
Populate a range of cells with the String variable’s text.
Open Excel and add a new workbook.
In your active worksheet, enter the text Hello in cell A1.
Press Alt+F11 to get into the Visual Basic Editor.
From the VBE menu, click Insert ➪ Module.
In the new module, type in the name of your macro as