Visual Basic macro examples for working with arrays

To Fill an Array and Then Copy It to a Worksheet

1. Open a new workbook and insert a Visual Basic module sheet.

2. Type the following code on the module sheet.

   Sub Sheet_Fill_Array()
      Dim myarray As Variant
      myarray = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
      Range("a1:a10").Value = Application.Transpose(myarray)
   End Sub
   

3. Select Sheet1.

4. On the Tools menu, point to Macro and then click Macros.

5. In the Macro dialog box, click Sheet_Fill_Array, and then click Run.

To Take Values from a Worksheet and Fill the Array

1. Type values on Sheet1 in cells A1:A10.

2. On a Visual Basic module sheet, type the following code:

   Sub from_sheet_make_array()
      Dim thisarray As Variant
      thisarray = Range("a1:a10").Value
   
      counter = 1                'looping structure to look at array
      While counter <= UBound(thisarray)
         MsgBox thisarray(counter, 1)
         counter = counter + 1
      Wend
   End Sub
   

3. Select Sheet1.

4. On the Tools menu, point to Macro and then click Macros.

5. In the Macro dialog box, click from_sheet_make_array, and then click Run.

To Pass and Receive an Array

1. On a module sheet, type the following code:

   Sub pass_array()
      Dim thisarray As Variant
      thisarray = Selection.Value
      receive_array (thisarray)
   End Sub
   
   Sub receive_array(thisarray)
      counter = 1
      While counter <= UBound(thisarray)
         MsgBox thisarray(counter, 1)
         counter = counter + 1
      Wend
   End Sub
   

2. Select Sheet1, and highlight the range A1:A10.

3. On the Tools menu, point to Macro and then click Macros.

4. In the Macro dialog box, click pass_array, and then click Run.

To Compare Two Arrays

1. Create two named ranges on Sheet1. Name one range1 and the other range2.
   
   For example, highlight the cell range A1:A10 and name it range1; highlight the cell range B1:B10 and name it range2.

2. Type the following code on the module sheet.

   Sub compare_two_array()
      Dim thisarray As Variant
      Dim thatarray As Variant
   
      thisarray = Range("range1").Value
      thatarray = Range("range2").Value
      counter = 1
      While counter <= UBound(thisarray)
         x = thisarray(counter, 1)
         y = thatarray(counter, 1)
         If x = y Then
            MsgBox "yes"
         Else MsgBox "no"
         End If
         counter = counter + 1
      Wend
   End Sub
   

3. Select Sheet2.

4. On the Tools menu, point to Macro and then click Macro.

5. In the Macro dialog box, click compare_two_array, and then click Run.
   
   You will see one message box for every comparison.

To Fill a Dynamic Array

1. On a module sheet, type the following code:

   Sub fill_array()
   
      Dim thisarray As Variant
      number_of_elements = 3     'number of elements in the array
   
      'must redim below to set size
      ReDim thisarray(1 To number_of_elements) As Integer
      'resizes this size of the array
      counter = 1
      fillmeup = 7
      For counter = 1 To number_of_elements
         thisarray(counter) = fillmeup
      Next counter
   
      counter = 1         'this loop shows what was filled in
      While counter <= UBound(thisarray)
         MsgBox thisarray(counter)
         counter = counter + 1
      Wend
   
   End Sub
   

2. On the Tools menu, point to Macro and then click Macros.

3. In the Macro dialog box, click fill_array, and then click Run.

NOTE: Changing the variable "number_of_elements" will determine the size of the array.