5. Scope for Procedures
You may have guessed
that procedures have levels of scope, too. In fact, Visual Basic uses
the same keywords (plus one) to define the scope of a procedure. The
following procedure declarations show the scope and lifetime keywords in
use (in bold):
Private Sub Proc1( )
' Private procedures are local to the module or class.
End Sub
Public Sub Proc2( )
' Public procedures are global to all open projects.
End Sub
Friend Sub Proc3( )
' Friend procedures are public to the project that contains them.
' This is available only in classes.
End Sub
Private Static Sub Proc3( )
' In Static procedures
, all local variables are Static.
' Static procedures may be Private, Public, or Friend.
End Sub
The default scope for procedures is public, so omitting the scope keyword is the same as declaring the procedure as Public. Most of the procedures you create will probably be public. Use Private or Friend when you want to restrict how others use a procedure. For example, declaring a function in a module as Private prevents users from using it as a user-defined function in an Excel formula.
6. Constants and Enumerations
Constants are names that have fixed values. Visual Basic and Excel each defines many constants
that are used to identify commonly used settings and values. For example, the constant vbCrLf is used to start new paragraphs within strings:
Debug.Print "This" & vbCrLf & "and that"
To declare your own constants, use the Const statement:
' Module level
Const AUTHOR = "Jeff Webb"
Const VERSION = 1.1
Const CHANGED = #6/5/2004#
It's
a common practice to capitalize constant names in code. It's also
common to avoid using local (procedure-level) constants since they may
shadow global or module-level constants. |
|
Constant have global (Public), module-level (Private),
or local scope and they can be shadowed. However, you can't assign to a
constant after it is declared. That is what distinguishes constants
from variables! The following code demonstrates using the preceding
module-level constants in a procedure:
Sub Constants( )
' Constants can be shadowed.
Const AUTHOR = "Joey"
Debug.Print AUTHOR, VERSION, "Days since changed: " & Round(Now - CHANGED)
End Sub
If you run the code, you'll see the following output in the Immediate window:
Joey 1.1 Days since changed: 4
You don't specify a type when declaring constants, but Visual Basic assigns one based on the value you set. The #
signs in the preceding declarations identify the value as a date, so
Visual Basic can use those values to evaluate how much time has passed
since the last change. You can use the other type-declaration characters
if you want to use a special data type for the constant, such as Currency (@):
Public Const PRICE = 24.95@
Enumerations are a special type of constant that organizes a group of values by name. There are all sorts of enumerations
used in Visual Basic itself; a good example is the VbMsgBoxResult enumeration:
Sub GetResponse( )
' Declare variable as an enumerated value
Dim res As VbMsgBoxResult
' Get the response.
res = MsgBox("What's your response?", vbYesNoCancel)
' Test the response against possible values.
Debug.Print "Response is:", Switch(res = vbYes, "Yes", _
res = vbNo, "No", res = vbCancel, "Cancel")
End Sub
In the preceding code, the variable res can contain any of the possible message box results. The Switch function compares the variable to each of the possible responses to display an appropriate string in the Immediate window.
These enumerations are
handy in part because they enable Auto Complete for the variable in the
Visual Basic code window. If you type res = or VbMsgBoxResult,
you'll automatically see the possible settings for the variable—that
really helps you remember the Visual Basic constant names, which are
sometimes very long.
You can create your own enumerations by using the Enum keyword. Enum is kind of like a block Const statement:
Enum Numbers
One = 1
Two = 2
Three = 3
End Enum
You can use the Numbers enumeration in code, just as you would any Visual Basic enumeration:
Sub DemoEnum( )
' Declare a variable as a user-defined enumerated value.
Dim res As Numbers
' Set the value of the variable.
res = One
' Display its value.
Debug.Print res
End Sub
7. Arrays
Arrays are variables that identify a set of values. That set has one or more dimensions, and each item within the set is identified by an index within that dimension, as illustrated in Figures 3 and 4.
Arrays can have more than two dimensions, but that starts to get hard to illustrate on paper. One- and two-dimensional arrays
are by far the most common. You use arrays whenever you have a set of
data that you want to work with as a unit—lists and tables are typical
examples of when to use an array.
The items in an array must all have the same data type
, but that's not very restrictive considering that you can have arrays of Variants,
which can assume any other type. For example, the following array
contains the names of products and their prices. To prove that the
stored prices are numbers, I calculate the tax on each price before
displaying the table in the Immediate window:
Sub ShowVariantArray( )
Const RATE = 0.06
Dim PriceChart(4, 1) As Variant
PriceChart(0, 0) = "Grudgeon"
PriceChart(0, 1) = 24.95@
PriceChart(1, 0) = "Pintle"
PriceChart(1, 1) = 11.15@
PriceChart(2, 0) = "Tiller"
PriceChart(2, 1) = 93.75@
PriceChart(3, 0) = "Rudder"
PriceChart(3, 1) = 42.49@
Dim i As Integer
Debug.Print "Item", "Price", "Tax"
For i = 0 To UBound(PriceChart) - 1
Debug.Print PriceChart(i, 0), PriceChart(i, 1), _
Round(PriceChart(i, 1) * RATE, 2)
Next
End Sub
The preceding arrays have
a fixed number of items, set when the array was declared. That's
realistic if you know that the number of items won't change frequently,
but it's much more handy to be able to change the number of items
dynamically at runtime. To create a dynamic
array, declare the array with an empty number of dimensions, then use ReDim to declare the bounds
. For example, the following code declares an array named Values,
resizes the array to fit the number of cells selected in Excel, then
copies the values from the selected range of cells into that array (key
items in bold):
Sub DynamicArray( )
Dim Values( ) As Variant
' Get rows and columns of selected range
Dim rows As Integer, cols As Integer
rows = Selection.rows.count
cols = Selection.Columns.count
ReDim Values(1 To rows, 1 To cols)
' If multiple cells are selected, Selection returns an array.
If IsArray(Selection) Then
Values = Selection
Dim i As Integer, j As Integer, str As String
For i = 1 To rows
str = ""
For j = 1 To cols
str = str & vbTab & Values(i, j)
Next
Debug.Print str
Next
End If
End Sub
By default, arrays' bounds start at 0, but you can change that as shown by ReDim Values(1 To rows, 1 To cols). I do that so the ValuesSelection method. Arrays returned by Excel always start at 1. array matches the bounds used by the array returned by Excel's |
|
Dynamic arrays are usually cleared any time you call ReDim. The exception to that rule occurs if you are using a one-dimensional array (a list) and you qualify ReDimPreserve to save the existing data in the array. Here's our Flavors array again, but this time you can add items: with
Sub DynamicArrayPreserve( )
Dim Flavors( ) As String
' Set the inital size of the array.
ReDim Flavors(4)
' Set some values.
Flavors(0) = "Vanilla"
Flavors(1) = "Chocolate"
Flavors(2) = "Strawberry"
Flavors(3) = "Peach"
' Add items to the list, enlarging it as needed.
Dim str As String, count As Integer
Do
' Get the count of items in list.
count = UBound(Flavors)
str = InputBox("Enter a flavor.")
' Exit if nothing entered.
If str = "" Then Exit Do
' Make the array bigger.
ReDim Preserve Flavors(count + 1)
' Set the value of the new item
Flavors(count) = str
Loop
' Display the items
Dim i As Integer
For i = 0 To UBound(Flavors) - 1
Debug.Print Flavors(i)
Next
End Sub
Visual Basic can make arrays
larger at runtime because all arrays are reference types. The value
represented by the array name in Visual Basic is the address of where
the array starts. Table 7 lists the keywords that Visual Basic provides for working with arrays.
Table 7. Visual Basic array keywords
| Keyword | Use to |
|---|
| Array | Create an array from a list of values. |
| Erase | Clear the values in an array. |
| IsArray | Determine whether or not a variable is an array. |
| LBound | Get the lower bound of an array dimension. |
| Option Base | Change
the default lower bound of arrays. (This is not generally a good
practice and is mainly provided for compatibility with earlier Basics.) |
| UBound | Get the upper bound of an array dimension. |
