How To Pass a Visual Basic Array to an Excel Worksheet

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

On This Page

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.

MORE INFORMATION

Step-by-Step Example

Method 1:

  1. Start a new project. Form1 is created by default.
  2. Place a Command button on the form.
  3. 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
    					
  4. 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
				

Properties

Article ID: 153090 - Last Review: July 2, 2004 - Revision: 3.1
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
Keywords: 
kbhowto kbinterop KB153090

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