Programming Excel with VBA and .NET : Variables (part 3) - Constants, Enumerations & Arrays

- How To Install Windows Server 2012 On VirtualBox
- How To Bypass Torrent Connection Blocking By Your ISP
- How To Install Actual Facebook App On Kindle Fire
11/15/2011 9:19:52 AM

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.

Figure 3. One-dimensional arrays are simply lists

Figure 4. Arrays with two dimensions are tables

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)
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)
Debug.Print str
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
' 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
' Display the items
Dim i As Integer
For i = 0 To UBound(Flavors) - 1
Debug.Print Flavors(i)
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
KeywordUse to
ArrayCreate an array from a list of values.
EraseClear the values in an array.
IsArrayDetermine whether or not a variable is an array.
LBoundGet the lower bound of an array dimension.
Option BaseChange the default lower bound of arrays. (This is not generally a good practice and is mainly provided for compatibility with earlier Basics.)
UBoundGet the upper bound of an array dimension.
  •  Windows Vista : Performing Local PC Administration (part 2) - Performing common workstation administration tasks
  •  Windows Vista : Performing Local PC Administration (part 1) - Working with workstation administration tools
  •  Filtering Out Evil with Firewalls (part 3) - Manually Configuring a Firewall's Ports
  •  Filtering Out Evil with Firewalls (part 2)
  •  Filtering Out Evil with Firewalls (part 1)
  •  Windows 7 : Windows Driver Foundation Architecture (part 4) - Tools for Development and Testing
  •  Windows 7 : Windows Driver Foundation Architecture (part 3) - Driver Frameworks
  •  Windows 7 : Windows Driver Foundation Architecture (part 2) - Integrated I/O Queuing and Cancellation
  •  Windows 7 : Windows Driver Foundation Architecture (part 1)
  •  Windows 7 : Using Advanced Security Options (part 2) - Configuring Windows Defender
    Top 10
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 2) - Wireframes,Legends
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 1) - Swimlanes
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Formatting and sizing lists
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Adding shapes to lists
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Sizing containers
    - Microsoft Access 2010 : Control Properties and Why to Use Them (part 3) - The Other Properties of a Control
    - Microsoft Access 2010 : Control Properties and Why to Use Them (part 2) - The Data Properties of a Control
    - Microsoft Access 2010 : Control Properties and Why to Use Them (part 1) - The Format Properties of a Control
    - Microsoft Access 2010 : Form Properties and Why Should You Use Them - Working with the Properties Window
    - Microsoft Visio 2013 : Using the Organization Chart Wizard with new data
    Video Sports
    programming4us programming4us