The Array Function

The Array function allows you to create an array during code execution without having to dimension it first. This function always returns an array of Variants. Using the Array function, you can quickly place a series of values in a list. The Carlnfo procedure shown below creates a fixed-size, one-dimensional, three-element array called auto.

1. Insert a new module into the current project and rename it Array_Function.

2. Enter the following Carlnfo procedure: Option Base 1

Sub CarInfo()

Dim auto As Variant auto = Array("Ford", "Black", "1999") MsgBox auto(2) & " " & auto(1) & ", " & auto(3) auto(2) = "4-door"

MsgBox auto(2) & " " & auto(1) & ", " & auto(3) End Sub

Another example demonstrates how to use the Array function to enter column headings in a worksheet:

Sub ColumnHeads()

Dim heading As Variant Dim cell As Range Dim i As Integer i = 1

heading = Array("First Name", "Last Name", "Position", _ "Salary")


For Each cell in Range("A1:D1") cell.Formula = heading(i) i = i+1


Columns("A:D").Select Selection.Columns.AutoFit Range("A1").Select End Sub

0 0

Post a comment