Convert A List Into An Array

By converting a list of common values to an array, you can access the individual values quickly using one variable. You can convert a list of values to an array using a variety of different methods. You assign values to arrays by referencing the index values of each element. Arrays use index values to identify each of their elements. For example, if an array has 10 elements with a lower bounds of 1, the third element in the array has an index value of 3. In order to assign a value to an array you need to specify the index values that correspond to the appropriate array element. For example, this code assigns a value of 45 to the third array element: SampleArray(3) =45.

With large arrays, assigning values to each element of the array using the above statement can become rather cumbersome. After all, the purpose of using an array is to simplify your code by storing all related values in one variable, instead of a series of different variables. For Next loops work well for adding a series of values to an array. You simply declare a For Next loop to cycle through the entire array. See Chapter 6 for more information about working with For Next loops.

For Next loops work best for adding values either from a series of cells or when you can increment values equally. When you have a specific list of values to add to an array, you can also use the Array function, which enables you to add a list of values to an array. The function adds values to the array starting at the lower bounds of the array, the first element, and then adds consecutively. For example, the following code adds the values "One", "Two" "Three" to the SampleArray: SampleArray = Array("One", "Two", "Three").

You can produce the same results when you specify each element individually, for example, when you assign a value to the first element of the array: SampleArray(i) = "One".

The biggest disadvantage of the Array function is that you can only use it with a Variant data type variable. In other words, you cannot declare the variable to which you assign the list of values as an array.

CONVERT A LIST TO AN ARRAY

CONVERT A LIST TO AN ARRAY

'-H Create a new subroutine.

Q Type Dim ArrayVar As Variant, replacing ArrayVar with the name of the variable to receive the list of values.

'-H Create a new subroutine.

Q Type Dim ArrayVar As Variant, replacing ArrayVar with the name of the variable to receive the list of values.

Type ArrayVar = Array(Value1, Value2, Value3), replacing Valuel, Value2, and Value3 with the values to assign to the array.

0 0

Post a comment