A procedure is simply a block of code that performs specific actions. Typically, when using VBA with Excel macros, you perform the actions with or on Excel objects.
VBA provides essentially two types of VBA procedures: Functions and Subroutines. The only real difference between the two types of procedures is that a function returns a value.
If, up to this point, you have created all of your VBA code with the Macro Recorder, you have probably seen only subrountines. Because you cannot call functions from Excel as macros, the only way a macro uses a function is when it is called by a subroutine.
To use functions with your macros, a subroutine must call the functions. See "Create a Subroutine" for more information on working with subroutines.
You can make procedure names almost anything, as long as they do not resemble a worksheet cell references. For example, VBA cannot accept a subroutine named A3 because it resembles a cell reference. You can create procedure names using the same rules as those discussed in the Variables section.
An array is a group of variables with the same name and data type. For example, if you have a list of the 50 U.S. states, you can place the state names in an array called States. You refer to each value in an array as an element. You access elements of the array using an index number that corresponds to their position in the array.
Using an array reduces the number of variables required in your code because you only have to declare one variable to manage all of your data values. Otherwise, storing our 50 states could require declaring and managing 50 different variables.
Dim States(50) States(43) = "Texas"
You refer to an array with one list of data as a one-dimensional array. VBA provides the ability to declare multidimensional arrays. With a multidimensional array, each array element has a corresponding array. For example, with the aforemention States array, you can have a corresponding list of cities in each state. If a user selects Texas, a list of the cities in Texas becomes available.
VBA allows for up to 60 dimensions in an array, but most developers rarely use more than 2 or 3 dimensions.
You can declare arrays either as fixed-length, where you specify the number of elements, or as dynamic with an unknown number of elements.
Chapter 5 provides several examples for working with arrays in your VBA code.
Constants, as the name implies, represent specific values that do not change within your code. You declare constants using the Const statement.
Const MyName As String = "Jinjer"
As with variables, if you do not specify the data type for the constant, Excel treats the constant as a variant.
Using constants enables you to have only one place in the code to modify if the value of the constant changes. For example, suppose that you have the following constant declaration:
Const SalesTax As String = ".075"
If your state raises the sales tax, you simply have to modify the constant value — .075 in the example — and not each calculation. Using constants helps to eliminate potentials errors that can arise from mistyping a value.
Was this article helpful?