XL: Visual Basic Macro Examples for Working with Arrays

• Microsoft Excel for Windows, version 5.x
• Microsoft Excel for Windows 95, version 7.0
• Microsoft Excel 97 for Windows
• Microsoft Excel for the Macintosh, version 5.0, 5.0a
• Microsoft Excel 98 Macintosh Edition

SUMMARY

This article contains sample Microsoft Visual Basic for Applications procedures that you can use to work with several types of arrays.

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

   http://www.microsoft.com/support/supportnet/refguide/default.asp

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, Click Macro.

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. Input 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, click Macro.

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, click Macro.

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"(without the quotation marks) and the other "range2"(without the quotation marks).

   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 "nope"
               End If
               counter = counter + 1
            Wend
         End Sub
   
   

3. Select Sheet2.

4. On the Tools menu, 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 an Array

1. Type the following code on a module sheet.

         Function array_fill()
            array_fill = Application.Transpose(Array(1, 2, 3))
         End Function
   
   

2. Highlight the range A1:A3 on a Sheet2 and type "=array_fill()" (without the quotation marks), and then press CTRL+SHIFT+ENTER. In Microsoft Excel for the Macintosh, press COMMAND+RETURN.

To Fill an Array and Add up the Array

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

         Function test(x As Object) As Integer
            For Each mycell In x
               test = test + mycell.Value
            Next
         End Function
   
   

2. Select Sheet1 and highlight range A1:A10.

3. In cell A12 type "=test(cell_range)" (without the quotation marks), and then press CTRL+SHIFT+ENTER. In Microsoft Excel for the Macintosh, press COMMAND+RETURN.

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, click Macro.

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