Microsoft KB Archive/149689

= Visual Basic Macro Examples for Working with Arrays =

Article ID: 149689

Article Last Modified on 10/10/2006

-

APPLIES TO


 * Microsoft Excel 97 Standard Edition
 * Microsoft Excel 95 Standard Edition
 * Microsoft Excel 5.0 Standard Edition
 * Microsoft Excel 98 for Macintosh

-



This article was previously published under Q149689



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. NOTE: In Visual Basic for Applications procedures, the words after the apostrophe (') are comments.

To Fill an Array, and Then Copy It to a Worksheet
 Open a new workbook and insert a Visual Basic module sheet.  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  Select Sheet1. On the Tools menu, Click Macro. In the Macro dialog box, click Sheet_Fill_Array, and then click Run.

To Take Values from a Worksheet and Fill the array
 Type values on Sheet1 in cells a1:a10.</li>  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 </li> Select Sheet1.</li> On the Tools menu, click Macro.</li> In the Macro dialog box, click from_sheet_make_array, and then click Run.</li></ol>

To Pass and Receive an Array
  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 </li> Select Sheet1, and highlight the range A1:A10.</li> On the Tools menu, click Macro.</li> In the Macro dialog box, click pass_array, and then click Run.</li></ol>

To Compare Two Arrays
 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.</li>  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 </li> Select Sheet2.</li> On the Tools menu, click Macro.</li> In the Macro dialog box, click compare_two_array, and then click Run.

You will see one message box for every comparison.</li></ol>

To Fill an Array
<ol> <li> Type the following code on a module sheet: Function array_fill array_fill = Application.Transpose(Array(1, 2, 3)) End Function </li> <li>Highlight the range A1:A3 on a Sheet2 and type =array_fill, and then press CTRL+SHIFT+ENTER. In Microsoft Excel for the Macintosh, press COMMAND+RETURN.</li></ol>

To Fill an Array and Add up the Array
<ol> <li> 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 </li> <li>Select Sheet1 and highlight range A1:A10.</li> <li>In cell A12 type =test(cell_range), and then press CTRL+SHIFT+ENTER. In Microsoft Excel for the Macintosh, press COMMAND+RETURN.</li></ol>

To Fill a Dynamic Array
<ol> <li> 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 </li> <li>On the Tools menu, click Macro.</li> <li>In the Macro dialog box, click fill_array, and then click Run.</li></ol>

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

Additional query words: 5.00a 5.00c 8.00 xl97 copy paste visual basic editor XL

Keywords: kbdtacode kbhowto kbprogramming KB149689

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.