Learning Objectives
Assigning an Array to a Scalar Variable. Array References.
Using Array () Function. Array Error Handling.
Assigning an Array to a Scalar Variable
A variable containing a single value is a scalar variable. A scalar variable can be used to hold a reference to an array. Assigning an array to a scalar variable creates a copy of the array and a reference to the copy. An array reference works like an array.
Dim aScalar, anArray(1) aScalar = anArray
Assigning an array to a scalar variable is valid. It will be used to store a reference of an array. A new dynamic-size array will be created as a copy of the array represented by array variable, anArray. Dim aScalar Dim anArray(1) aScalar = "Rabbit" anArray(0) = "1" anArray(1) = "2"
Msgbox "anArray has type before assigning: " & TypeName(anArray) Msgbox "aScalar has type before assigning:" & T ypeName(aScalar) aScalar = anArray
aScalar(0) = "10" 'Array reference works like an array
ReDim Preserve aScalar(2) 'The referenced array is re-sized aScalar(2) = "30"
Msgbox "anArray has type after assigning:" & TypeName(anArray) Msgbox "aScalar has type after assigning:" & T ypeName(aScalar)
Msgbox "aScalar(0): " & aScalar(0) Msgbox "aScalar(1): " &aScalar(1) Msgbox "aScalar(2): " & aScalar(2)
Msgbox "anArray(0): " & anArray(0) Msgbox "anArray(1): " & anArray(1)
Array References Work Like Arrays
An array reference is a scalar value. This is why it can be assigned to a scalar variable. A scalar variable holding an array reference can be used like an array variable.
In Below Example:
array_reference(i)" represents the element of index i of the referenced array. "ReDim Preserve array_reference(n)" resets the size of the referenced array.
When an array reference is used as a function argument, the referenced array is passed into the function, not the reference. For example, "TypeName(array_reference)" returns Variant().
Example
Dim aReference Dim anArray(1) anArray(0) = "Dog" anArray(1) = "Cat"aReference = anArray 'An array reference is assigned
aReference(0) = "Pig" 'Accessing an element through the reference ReDim Preserve aReference(2) 'Re-sizing the referenced array aReference(2) = "Fox"
upperLimit = UBound(aReference) 'The referenced array is passed
Msgbox "TypeName(aReference): " & TypeName(aReference) Msgbox "UBound(aReference): " & upperLimit
For Each e In aReference Msgbox " " & e
Next
TypeName(aReference): Variant() UBound(aReference): 2
Pig Cat Fox
Using Array () Function
The Array function returns a variant array containing the elements whose values are passed to the function as arguments.
Syntax:
Array(element1, element2, elementN,....)
Arguments:
Arguments Description
element1 The data to be assigned to the first array element. elementN Any number of data items you wish to add to the array.
Although the array you create with the Array function is a variant array data type, the individual elements of the array can be a mixture of different data types.
The initial size of the array you create is the number of arguments you place in the argument list and pass to the Array function.
The lower bound of the array created by the Array function is 0.
The array returned by the Array function is a dynamic rather than a static array. Once created, you can re-dimension the array using Redim, Redim Preserve, or another call to the Array function.
If you don't pass any arguments to the Array function, an empty array is created. Although this may appear to be the same as declaring an array in the conventional manner with the statement“Dim myArray()”
Example:
Dim varArray
varArray = Array(10,20,30,40,50) Dim arrTitles
arrTitle = Array( Mr , Mrs , Miss , Ms )
Array Error Handling
One common error that we face while working with Array is Type Miss Match:
You attempted to compare values of incompatible data types for example, comparing a string and a numeric value.
Make sure the data types match when performing comparisons.
Cast one of the values to the data type of the other, and try the comparison again.
Can you assign the value of a scalar variable to an array variable? The answer is simple: "No". For example:
array_variable = scalar_variable
If you try it, you will get a runtime error: Type mismatch.
Can you assign the value of an array variable to a scalar variable? The answer is not so simple. Technically, a scalar variable is not capable to store an array. So the answer is no - you cannot assign an array directly to a scalar variable.
But a scalar variable can be used to store a reference, or a pointer, of an array. So the answer is yes - you can assign an array indirectly to a scalar variable. For example:
scalar_variable = array_variable
The assignment operation will first create a new dynamic-size array as a copy of the specified array.
The assignment operation will then create a reference of the new dynamic-size array. Finally, the reference of the new dynamic-size array is stored into the specified scalar variable.
Can you assign the value of an array variable to another array variable? The answer is simple: "No". For example:
another_array_variable = array_variable
If you try it, you will get a runtime error: Type mismatch.
Try to play with the following example below to understand how assignment operation works with scalar variables and array variables:
Example
Dim aScalar
Dim anotherScalar Dim anArray(9) Dim anotherArray(9)
anotherScalar = aScalar 'Creates a copy of aScalar's value anArray = aScalar 'Runtime error: Type mismatch aScalar = anArray 'Creates a copy of anArray's array anotherArray = anArray 'Runtime error: Type mismatch
Try It Out
Code:
Dim aWeek(6)
Msgbox "Is aWeek an array? " & IsArray(aWeek) aWeek(0) = "Sun"
aWeek(4) = "Thu" aWeek(6) = "Sat" aWeek(1) = "Mon"
Msgbox"Days in a week:"
For i=LBound(aWeek) To UBound(aWeek) Msgbox " " & i & " = " & aWeek(i)
Out Put:
Is aWeek an array? True Days in a week: 0 = Sun 1 = Mon 2 = 3 = 4 = Thu 5 = 6 = Sat How It Works:
Error 1 - Array index (subscription) must be in the range of lower bound and upper bound. Error 2 - Fixed-size array cannot be resized with "ReDim" statements.
Summary
A scalar variable can be used to hold a reference to an array.
Assigning an array to a scalar variable creates a copy of the array and a reference to the copy.
An array reference works like an array.
"Array()" function returns a reference of a new dynamic-size array.
Runtime error 'Type mismatch' happens when you try to assign scalar values or arrays to array variables.
Write a program to concatenate all of the strings in an array, each separated by a specified string separator.