Understanding a variant – a changing scalar
At the very basic level a variant is a type of
variable that can contain any kind of data item. Effectively, the same variable can behave as a
string or a number or a Boolean. This is
very useful when working with a procedure that returns different types of
values. One example of such a procedure
is Excel’s InputBox method. If the user clicks the Cancel button, the
function returns a False value. Otherwise, it returns whatever the user
entered in the textbox as the datatype requested by
the developer.
In Code
Sample 1, one variable, Rslt,
behaves as a String, a Double, a Boolean, or a Range object, depending on what
the developer wanted and what the user does.
Option Explicit
Sub testInputBox()
Dim Rslt As Variant
Rslt = Application.InputBox("Please enter a number", Type:=1)
Debug.Print TypeName(Rslt)
Rslt = Application.InputBox("Please enter a string", Type:=2)
Debug.Print TypeName(Rslt)
Rslt = Application.InputBox("Please click the Cancel button", Type:=2)
Debug.Print TypeName(Rslt)
Set Rslt = Application.InputBox("Please select a range, then click OK", _
Type:=8)
Debug.Print TypeName(Rslt)
End Sub
Code Sample
1
The result of running the above code is shown below:
Double
String
Boolean
Range
This is the simplest way where a variant is not only useful
but mandatory. No other datatype would suffice for the variable Rslt.
Please don’t
misunderstand me. I am not advocating
the willy-nilly reuse of the same variable in different roles. A variant is not meant to be a cheap trick to
save a few bytes of memory. However,
there are instances where it is necessary to use a variable that takes on
different types depending on circumstance.
With an understanding of the simplest kind of variant under
our belt, we move on to the use of variants as arrays.
Understanding a variant – a scalar or an array
In the previous section, we saw that a variant can contain different
kinds of scalar variables. What might
not have been evident is that the scalar does not have to be one of the
“built-in” types. It can also refer to a
custom object. The use of a variant in
this instance is analogous to its use in the previous section. Given a class module named myClass with a property namedHeightInches,
the following is valid.
Dim Rslt As Variant
Set Rslt = New MyClass
Debug.Print TypeName(Rslt)
Rslt.HeightInches = 65
Debug.Print Rslt.HeightInches
The result of running the above code would be:
MyClass
65
A big disadvantage of
using a variant for an object is that one does not have access to the VBE’s Intellisense capability.
Next, we look at how a variant can also contain a
complex data type such an array. This is
an important stepping stone en route to unleashing the true power behind a
variant data type.
Consider the code
Sub VariantAsArray()
Dim SomeVar As Variant
ReDim SomeVar(9)
SomeVar(9)
= 81
MsgBox SomeVar(9)
End Sub
SomeVar is a variable of type
Variant. Since a variant can behave like
any data type, we can make it look like an array, which is exactly what the ReDim statement does.
After it is executed, SomeVar becomes an
array! Of course, we could create a 2
dimensional array with
ReDim SomeVar(9, 9)
One should also understand the conceptual difference
between a variant that behaves like an array and an array that contains
elements that are variants. Consider the
following code:
Option Explicit
Sub VariantAndArrays()
Dim aVariantArray(2)
As Variant
Dim aVar As Variant
ReDim aVar(2) As Long
Debug.Print TypeName(aVar), TypeName(aVar(2))
ReDim aVar(2) As String
Debug.Print TypeName(aVar), TypeName(aVar(2))
aVar = 10
Debug.Print TypeName(aVar)
End Sub
The results of the Debug.Print
statements are shown below. Note that
the variant aVar first contains an array of Long’s and
then it morphs into an array of String’s.
Yet, after the assignment of 10 to aVar, it
now behaves like an Integer.
Effectively, a variant can be not only a scalar but also an array.
Long() Long
String() String
Integer
By contrast, the variable aVariantArray
is fixed as an array. Each element in it
can be a different type, but the variable as a whole will always be an
array. Effectively, we cannot add a line
of code such as
aVariantArray = 10
As soon as we try to execute the code, VBA pops up with the
error “Can’t assign to an array.” So,
there is a difference between a variant that temporarily behaves like an array
and a variable declared as an array. The
latter cannot change its type.
Comparing a dynamic array with a variant
Now that we have seen how a variant can behave like an array
that we can resize as desired, one might compare it with a dynamic array. Is there a difference between a dynamic array
and a variant that behaves like an array?
Yes, there is. For the former, VB
knows that it is an array and consequently expects the developer to treat it as
such. By contrast, a variant is a
variant that temporarily becomes an array.
Consider the code below and the subsequent snapshots of the Locals
window.
Sub DynamicArrAndVariant()
Dim anArr()
As Variant, aVar As Variant
'anArr = 2
aVar = 2
ReDim anArr(2)
ReDim aVar(2)
End Sub
Just after the variables are allocated and before
any assignment statements are executed, the Locals window shows:
VBA knows that the anArr
variable is an array and consequently shows the parenthesis in the Type
column. Of course, since it doesn’t know
the dimensions of the array, the parenthesis are empty. By contrast, VBA knows nothing about aVar other than it is a variant that is currently empty.
After the execution of the aVar=2
statement, VBA indicates that aVar is a variant that
currently contains an integer (see below).
After both ReDims are executed, VBA knows that anArr is an array with 3 elements (indices 0, 1, and
2). It also knows that aVar is a variant that currently behaves like an array of 3
elements (again, indices 0, 1, and 2).
Functions that return an array or a scalar
There are some Excel functions that return an array of
values or a scalar such as a Boolean. And,
of course, one can write a custom function that does the same. One built-in function that returns either an
array or a scalar is the GetOpenFilename
function. The only way to get the result
of such a function is through a variant.
Consider the code:
Sub demoGetFileOpenFilename()
Dim FileList As Variant
FileList = Application.GetOpenFilename(MultiSelect:=True)
End Sub
Step through the code twice.
The first time select two filenames and check the Locals Window after
the GetOpenFilename method is completed. FileList will
appear as an array with each element containing a string value as shown in Figure 1
Figure
1
The second time around cancel the dialog box and once again
check the Locals window after the method completes. This time FileList
will appear to be a Boolean as shown in Figure
2.
Figure
2
Returning an array as a function value and backward compatibility with Excel
97
As we have already noted, there is nothing more
useful for developing good software as using the correct data type. But, at times, a compromise is necessary to
support some other capability. One such
instance is returning an array result from a function while maintaining compatibility
with Excel 97.
In VB6 (Excel 2000 and later), it is possible to
declare the result of a function as an array type. For example, in the code below, the function SomeRandomNumbers returns an array of type single.
Function SomeRandomNumbers(HowMany As Long) As Single()
Dim I As Long, Rslt() As
Single
ReDim Rslt(HowMany)
For I = LBound(Rslt) To UBound(Rslt)
Rslt(I) =
Rnd()
Next I
SomeRandomNumbers = Rslt
End Function
However, VB5 (Excel 97) doesn’t support an array as a
function return type. The above code
would result in a syntax error. The work
around would be to declare the return type as a variant and then create an
array of type Single in that variant, as in the code below.
Function SomeRandomNumbersXL97(HowMany As Long) As Variant
Dim I As Long, Rslt() As
Single
ReDim Rslt(HowMany)
For I = LBound(Rslt) To UBound(Rslt)
Rslt(I) =
Rnd()
Next I
SomeRandomNumbersXL97 = Rslt
End Function
Efficiently exchanging information with Excel
Passing an array as a ‘by value’ argument
Understanding a variant – implementing an array of arrays
We have already seen how to create an array in a
variant. Now, we explore the kind of
data one can store in each element. Depending
on the syntax of the ReDim statement, it can,
obviously, be a variant. That leads us to a very powerful capability: the
concept of “an array of arrays.”
Consider, for example,
Dim SomeVar As Variant
ReDim SomeVar(9)
Since each element is a variant, the same rules that apply
to the variable itself now apply to each individual element. Hence, we should be able to assign an array
to each element itself. Unfortunately,
we need to do this indirectly since we cannot use the ReDim
statement directly. Using ReDim with element 9 of SomeVar
to create an array as below generates a syntax error.
redim somevar(9)(9)
The VBA compiler doesn’t understand that the first set of parenthesis
– SomeVar (9) – simply identifies one element of SomeVar and the 2nd set of brackets specifies
the dimension of the array we want to create in this variant. So, we use a slight detour that illustrates,
as we will explore in more detail later, another powerful capability of
variants.
Sub addAnArrayToAnArray()
Dim SomeVar As Variant, Temp As
Variant
ReDim SomeVar(9)
ReDim Temp(8)
SomeVar(9)
= Temp
SomeVar(9)(8)
= 72
MsgBox SomeVar(9)(8)
End Sub
Code Sample
2
In Code
Sample 2 the first 3 lines of the subroutine simply change
each of the variants SomeVar and Temp to an
array. The next statement will be new to
some readers. It assigns the entire
array Temp to just one variable, i.e., SomeVar (9). If we look at the local variables just after
this line of code is executed (use View | Local Windows) we would see
Figure
3
There are several important things to note in Figure 3.
First, SomeVar looks
like a regular array and each element can be accessed with SomeVar
(<index>) such as SomeVar (9).
Second, since each element is a variant in itself,
they don’t have to look or behave identically to each other. For example, in Figure 3 all the elements are empty except the last element.
That single element contains an entire array in it!
Third, note how VBA identifies each element of the
array embedded inside of SomeVar (9). It is important to understand this. SomeVar(9) identifies one element of the array in SomeVar. This
element is itself of a Variant type.
Then we inserted a complete array into this single element. So, SomeVar(9) identifies the element of SomeVar
and SomeVar(9)(8) identifies the element of the array
inserted into this element. This is very
different from a 2-dimensional array where one would use Array2D (9,8) to identify a particular element. What we have here is not a 2D array but an
array of arrays! Figure 4 illustrates the concept of an array of arrays.
Figure
4
The astute reader may have already picked up on an important
capability. It is eminently possible for
each element of SomeVar to contain an array of a
different size! We will see a practical
application of this capability in the next section.
Half a matrix – track travel distances between cities
Figure
5
Figure
5 shows a very common way of showing distances between
various cities. While the overall figure
may appear to be a two dimensional matrix, it is only the bottom half that is
really used. If one wanted to use a 2D array
it would be declared as
Dim CityDistances(1 To 8, 1 To
8) As Long.
However, that is not an ideal data structure
because it doesn’t reflect the true intent of the application, which requires a
triangular matrix, which as the correct data structure for this application
makes the code that much more self-documented and easily understood. We can create this data structure, where the
number of elements in the 2nd dimension of the matrix equals the
index of the first dimension with the code in Code
Sample 3. A secondary
benefit is the associated performance savings.
The triangular matrix has only ˝ the number of elements of the more
traditional matrix and consequently uses about ˝ the memory.
In the following code sample, the function TriangularMatrix creates
a variant array called FirstDimension that is the
size requested by the calling procedure.
It then proceeds to embed an array in each of those elements. Each of these embedded arrays has a size
equal to the index of the first dimension.
Option Explicit
Function TriangularMatrix(ByVal nbrRows
As Long)
Dim FirstDimension()
As Variant, i As Long, TempArr()
As Variant
ReDim FirstDimension(1 To nbrRows)
For i = LBound(FirstDimension) To UBound(FirstDimension)
ReDim TempArr(1 To i)
FirstDimension(i) = TempArr
Next i
TriangularMatrix = FirstDimension
End Function
Sub CityDistancesInfo()
Dim CityDistances As Variant
CityDistances = TriangularMatrix(8)
CityDistances(3)(1)
= 8
MsgBox CityDistances(3)(1)
End Sub
Code Sample
3
Figure
6 shows the data structure created by the TriangularMatrix
function. The first element of CityDistances
contains an embedded array of size 1.
The 2nd element contains an embedded array of size 2 all the
way to the eighth element, which contains an array of size 8. We should re-emphasize the difference between
how one addresses the elements of this triangular matrix and a traditional 2D
matrix. In the case of a regular matrix,
one would use CityDistances (3,1) for the 1st element of the 3rd
row. However, in the case of the triangular
matrix, each dimension is created independently. Consequently, the VBA compiler only knows
that we created a one dimensional array of type variant and then embedded an
array in each element. The addressing of
the matrix has to reflect the nature of how it was created. One would use CityDistances(3)(1)
– just as shown in the Locals dialog box in Figure
6 – to get at the same element as the traditional CityDistances (3,1). The first number, (3), indexes the 3rd
element of the variant array. The next
(1) indexes the array embedded in
that element.
Figure
6
Preserve existing content while resizing any dimension of a matrix
With a traditional multi-dimensional matrix it is not
possible to preserve the existing data and change the size of any dimension but
the last. For example, in Code Sample 4, the Redim Preserve
line generates a ‘Subscript Out of Range’
error.
Sub failRedimPreserve()
Dim SomeArr()
ReDim SomeArr(1 To 10, 1 To 10)
ReDim Preserve SomeArr(1 To 11, 1 To 10)
End Sub
Code Sample
4
By contrast, if one implements the 2D array as an array of
arrays, it is eminently possible to change the “first” dimension while
preserving the existing data. In the
code below, the function create2DMatrix creates an array of 10 elements in the
variable SomeArr.
Then, it inserts an array of size 9 in each of those (variant)
elements. The addTestData
subroutine initializes the “array of arrays” with some data. The next statement changes the dimension of
the SomeArr array to 11 and the next statement copies
the content of SomeArr(10) into the new element SomeArr(11). Of course, since SomeArr(10) contains an
array, we have effectively inserted an array into SomeArr(11)
as shown in Figure 7.
Sub VariantRedimPreserve()
Dim SomeArr()
SomeArr = create2DMatrix(10, 9)
addTestData SomeArr
ReDim Preserve SomeArr(1 To 11)
SomeArr(11)
= SomeArr(10)
End Sub
Code Sample 5
Figure
7
Work with an entire row of an array
The previous example also demonstrated another
powerful capability of the array of
arrays concept. Since each dimension
in an array of arrays is independent of the others, it is possible to treat an
entire row as a single element for certain types of operations. This allows us to apply a function to an
entire row. Or, even swap to rows with
simple assignment statements. In the
previous section, we used the assignment statement SomeArr(11) = SomeArr(10). By
simple extension, one can use a temporary variant and implement a swap that
looks very much like that for two scalar variables. What would a scalar swap look like?
Of course, assignment statements are only one
possibility. To sum each row of a 2D
matrix implemented as an array of arrays, one would use the subroutine
below. In contrast to the traditional 2D
matrix that would need two loops one embedded in the other, the code below uses
a single loop and the Excel SUM function.
Option Explicit
Sub SumARow()
Dim a2DArr, I As Integer
a2DArr = create2DMatrix(8, 9)
addTestData a2DArr
For I = LBound(a2DArr)
To UBound(a2DArr)
Debug.Print Application.WorksheetFunction.Sum(a2DArr(I))
Next I
End Sub
We can swap rows of an array
Summary
