Microsoft KB Archive/153090

= How To Pass a Visual Basic Array to an Excel Worksheet =

Article ID: 153090

Article Last Modified on 7/2/2004

-

APPLIES TO


 * Microsoft Visual Basic 6.0 Learning Edition
 * Microsoft Visual Basic 6.0 Professional Edition
 * Microsoft Visual Basic 6.0 Enterprise Edition
 * Microsoft Visual Basic 5.0 Control Creation Edition
 * Microsoft Visual Basic 5.0 Learning Edition
 * Microsoft Visual Basic 5.0 Professional Edition
 * Microsoft Visual Basic 5.0 Enterprise Edition
 * Microsoft Visual Basic 4.0 Standard Edition
 * Microsoft Visual Basic 4.0 Professional Edition
 * Microsoft Visual Basic 4.0 Professional Edition
 * Microsoft Visual Basic 4.0 16-bit Enterprise Edition
 * Microsoft Visual Basic 4.0 32-Bit Enterprise Edition

-



This article was previously published under Q153090



SUMMARY
One of the enhancements of Visual Basic is the ability to pass an array of values to a Microsoft Excel spreadsheet with a single command. This is done by a simple assignment of an array to a Microsoft Excel range.



Step-by-Step Example
Method 1:

 Start a new project. Form1 is created by default. Place a Command button on the form.  Add the following code to the Form1 code window: Option Explicit

Private Sub Command1_Click Dim o As Object Dim i As Integer Dim iNumbers(1 To 10) As Integer For i = LBound(iNumbers) To UBound(iNumbers) iNumbers(i) = Int(Rnd * 100) + 1 Next i       Set o = CreateObject("excel.application") o.Visible = True o.Workbooks.Add o.sheets("sheet1").Range("A1:J1").Value = iNumbers End Sub  Press the F5 key or select Start from the Run menu to start the program. You should see a new instance of Microsoft Excel created with the first row of the sheet populated with random numbers.

Method 2:

The code sample above uses a hard-coded range. You can change the UBound of the Array making your code more flexible so you can specify the Starting column and Row for output on the spreadsheet. The array will then be entered irrespective of its size. Instead of simply using the range object, you incorporate the cells object to build up the relevant strings. To implement this behavior, use the following code: Private Sub Command1_Click Dim o As Object Dim i As Integer Dim iNumbers(1 To 10) As Integer Dim iStartRow As Integer Dim iStartCol As Integer iStartRow = 1 iStartCol = 1 For i = LBound(iNumbers) To UBound(iNumbers) iNumbers(i) = Int(Rnd * 100) + 1 Next i    Set o = CreateObject("excel.application") o.Visible = True o.Workbooks.Add o.sheets("sheet1").range(o.cells(iStartRow, iStartCol).address, _      o.cells(iStartRow, ((iStartCol - 1) + _       UBound(iNumbers))).address).Value = iNumbers End Sub

Keywords: kbhowto kbinterop KB153090

-

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

© Microsoft Corporation. All rights reserved.