Microsoft Office Tutorials and References
In Depth Information
• To make a constant available within a single procedure only, declare it after the Sub or Function statement to
make it a local constant.
• To make a constant available to all procedures in a module, declare it before the first procedure in the mod-
• To make a constant available to all modules in the workbook, use the Public keyword and declare the con-
stant before the first procedure in a module. The following statement creates a constant that is valid in all
VBA modules in the workbook:
Public Const AppName As String = “Budget Tools”
If you attempt to change the value of a constant in a VBA procedure, you get an error —
as you would expect. A constant is a constant, not a variable.
Using constants throughout your code in place of hard-coded values or strings is an excellent programming
practice. For example, if your procedure needs to refer to a specific value (such as an interest rate) several
times, it's better to declare the value as a constant and use the constant's name rather than its value in your ex-
pressions. This technique makes your code more readable and makes it easier to change should the need arise
— you have to change only one instruction rather than several.
VBA and Excel define many constants that you can use in your code without declaring them. For example, the
following statement uses a constant named vbInformation:
MsgBox “Hello”, vbInformation
The vbInformation constant has a value of 64, but it's not important that you know that. If you use the Excel
macro recorder to record your actions, you'll find many other constants in the recorded code.
Like Excel, VBA can manipulate both numbers and text (strings). VBA supports two types of strings:
• Fixed-length strings are declared with a specified number of characters. The maximum length is 65,535
• Variable-length strings theoretically can hold up to 2 billion characters.
Each character in a string takes 1 byte of storage. When you declare a string variable with a Dim statement, you
can specify the maximum length if you know it (that is, a fixed-length string), or you can let VBA handle it dy-
namically (a variable-length string). In some cases, working with fixed-length strings may be slightly more effi-
cient in terms of memory usage.
In the following example, the MyString variable is declared to be a string with a fixed length of 50 characters.
YourString is also declared as a string but with an unspecified length.
Dim MyString As String * 50
Dim YourString As String