Sample Visual Basic macros for working with arrays in Excel

Article translations Article translations
Article ID: 213798 - View products that this article applies to.
This article was previously published under Q213798
Expand all | Collapse all

On This Page

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. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to 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 requirements.

To fill an array and copy it to a worksheet

  1. Open a new workbook.
  2. Press Alt+F11 to run the Visual Basic Editor.
  3. On the Insert menu, click Module.
  4. 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.WorksheetFunction.Transpose(myarray)
       End Sub
    					
  5. On the File menu, click Close and return to Microsoft Excel.
  6. Select Sheet1.
  7. On the Tools menu, point to Macro, and then click Macros.
  8. In the Macro dialog box, click Sheet_Fill_Array, and then click Run.

To take values from a worksheet and fill the array

  1. Open a new workbook and type values in cells A1:A10 on Sheet1.
  2. Press Alt+F11 to run the Visual Basic Editor.
  3. On the Insert menu, click Module.
  4. Type the following code on the module sheet:
       Sub From_sheet_make_array()
          Dim myarray As Variant
          myarray = Range("a1:a10").Value
    
          'Looping structure to look at array.
          For i = 1 To UBound(myarray)
          	MsgBox myarray(i, 1)
          Next
       End Sub
    				
    
  5. On the File menu, click Close and return to Microsoft Excel.
  6. Select Sheet1.
  7. On the Tools menu, point to Macro, and then click Macros.
  8. In the Macro dialog box, click From_sheet_make_array, and then click Run.

To pass and receive an array

  1. Open a new workbook.
  2. Press Alt+F11 to run the Visual Basic Editor.
  3. On the Insert menu, click Module.
  4. Type the following code on the module sheet:
       Sub Pass_array()
          Dim myarray As Variant
          myarray = Range("a1:a10").Value
          receive_array myarray
       End Sub
    
       Sub receive_array(thisarray)
          For i = 1 To UBound(myarray)
          	MsgBox myarray(i, 1)
          Next
       End Sub
  5. On the File menu, click Close and return to Microsoft Excel.
  6. Select Sheet1 and highlight the range A1:A10.
  7. On the Tools menu, point to Macro, and then click Macros.
  8. In the Macro dialog box, click Pass_array, and then click Run.

Properties

Article ID: 213798 - Last Review: January 24, 2007 - Revision: 5.3
APPLIES TO
  • Microsoft Office Excel 2003
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Excel 2000 Standard Edition
  • Microsoft Excel 97 Standard Edition
  • Microsoft Excel 2004 for Mac
  • Microsoft Excel X for Mac
  • Microsoft Excel 2001 for Mac
  • Microsoft Excel 98 for Macintosh
Keywords: 
kbdtacode kbhowto kbprogramming KB213798

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com