Fill an Array

Now that you can declare an array, you need to fill it. One method was shown earlier— individually entering a value for each element of the array. There's a quicker way, as shown in the following sample code and Figure 17.1: Option Base 1

Sub ColumnHeaders() Dim myArray As Variant Dim myCount As Integer

1 Fill the array myArray = Array("Name", "Address", "Phone", "Email")

' Empty the array

With Worksheets("Sheet2")

For myCount = 1 To UBound(myArray)

.Cells(1, myCount).Value = myArray(myCount) Next myCount End With End Sub

Figure 17.1

Use an array to quickly create column headers.

Figure 17.1

Use an array to quickly create column headers.

Remember that Variant variables can hold any type of information. To quickstack the array, create a variant-type variable that can be treated like an array. When the data is shoved into the variant, it is forced to take on the properties of an array.

But what if the information needed in the array is on the sheet already? Use the following to quickly fill an array (see Figure 17.2):

0 0

Post a comment