Using Variables And Arrays


Because you may not always know the size of the array, VBA provides functions for determining an array's upper and lower bounds. When working with dynamic arrays ,you frequently need to know the upper and lower bounds of the array to correctly code your procedure. To find the upper and lower bounds of an array, VBA provides the UBound and LBound functions. The sample code finds the lower and upper bounds and assigns them to variables.

UpperBound = UBound(EmployeeArray) LowerBound = LBound(EmployeeArray)

Each of these functions returns a Long data type indicating the upper or lower bounds of the specified array. If the array is multidimensional, you need to specify the dimension for which you want the bounds.

UpperBounds = UBound(MultiArray, 2)

-0 Type ReDim Preserve ArrayName(M), replacing M with the new upper bounds for the array.

□ Use the MsgBox function to view the same element of the array.

0 Switch to Excel and run the macro.

-■ The message boxes display the values from the array.


You can create user-defined data types to deal with specific types of data. User-defined data types resemble multidimensional arrays in that you can store related values using one variable name. However, because you construct it from other data types, you can create a user-defined data type containing multiple data types, while all elements in the array must contain the same data type.

You declare user-defined data types at the top of your module in the same location as your public and private module variables. You specify a user-defined data type with the Type and End Type statements. The Type statement indicates the start of the user-defined data type definition, and the End Type statement specifies the end. After the Type statement, you indicate the name of the new data type;for example, Type ItemInfo creates a data type called ItemInfo. To create a user-defined data type to store an item price and description you can specify a user-defined data type with two components.

After you create the data type, you can declare variables that use the specified data type. You typically use user-defined data types as the data type for an array. For example, to create an array of the ItemInfo data type, you type: Dim NewItems(10) As ItemInfo

To assign values to a user-defined array, you not only specify the array element, but you also indicate the component you want to change. For example, this code changes the value of the first component in the array:

NewItems(1).ItemDescription = "15" Monitor"

Similiarly, you can copy the entire contents of one element to another by simply referring to the array element. The following code copies ItemDescription and ItemPrice of the first element of the array to the third array element:

NewItems(3) = NewItems(1)



< Declare the data type components.

0 Create a new subroutine.

Type Type DataType replacing DataType with the name of the user-defined data type.

< Declare the data type components.

_Q Type End Type.

0 Create a new subroutine.

^0 Type Dim NewArray(N) As DataType, replacing NewArray with the name of the array, N with the number of elements, and DataType with the user-defined data type name you used in step 2.

■ Typically you create an array using the new data type.

0 0

Post a comment