Microsoft KB Archive/177991

= XL: Limitations of Passing Arrays to Excel Using Automation =

Article ID: 177991

Article Last Modified on 3/29/2007

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition
 * Microsoft Excel 97 Standard Edition
 * Microsoft Excel 95 Standard Edition
 * Microsoft Excel 95a
 * Microsoft Excel 5.0c
 * Microsoft Office 2000 Developer Edition

-



This article was previously published under Q177991



SUMMARY
This article discusses limitations in passing arrays to Microsoft Excel worksheets and macros using Automation. If these limitations are exceeded, you may receive run-time errors in your Automation code.



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 professionals 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 a Microsoft Certified Partner or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Partners, please visit the following Microsoft Web site:

https://partner.microsoft.com/global/30000104

For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site:

http://support.microsoft.com/default.aspx?scid=fh;EN-US;CNTACTMS

When passing arrays to Microsoft Excel using Automation, the following limitations apply, depending on the version of Excel and whether you are passing the array to a worksheet range or as an argument to a macro. See the appropriate footnotes following the table for details.   Version of          Passing Array to     Passing Array to   Microsoft Excel     Worksheet Range      Macro (Procedure) --

5.0                       A                B,C

7.0 (Excel 95)            D                E,C,H

8.0 (Excel 97)            F                G,H

9.0 (Excel 2000)          F                G,H

Sample Visual Basic Procedures
The following sample Microsoft Visual Basic for Applications Sub procedures show how to pass arrays to Excel. The first two procedures show how to populate a range of cells on a worksheet by passing an array to a worksheet range. The third procedure shows how to pass an array to an Excel macro.

The following declarations are used with each example:

Option Explicit

Private xlApp As Object

Private xlBook As Object

Private xlSheet As Object

Passing a 1-Dimensional Array to a Worksheet Range: Public Sub OneDimension Const size = 5461 Dim myarray(1 To size) As Integer Set xlApp = CreateObject("Excel.Application") xlApp.Visible = True Set xlBook = xlApp.Workbooks.Add Set xlSheet = xlBook.Worksheets("Sheet1") xlSheet.Cells(1, 1).Resize(size, 1).Value = _ xlApp.Application.Transpose(myarray) End Sub Passing a 2-Dimensional Array to a Worksheet Range: Public Sub TwoDimension Const size = 2730 Dim myarray(1 To size, 1 To 2) As Integer ' Number of elements = 2730 * 2 = 5460. Set xlApp = CreateObject("Excel.Application") xlApp.Visible = True Set xlBook = xlApp.Workbooks.Add Set xlSheet = xlBook.Worksheets("Sheet1") xlSheet.Cells(1, 1).Resize(size, 2).Value = myarray End Sub Passing an Array as an Argument to an Excel Macro: Public Sub RunExcelMacro Const size = 5461 Dim myarray(1 To size) As Integer Set xlApp = CreateObject("Excel.Application") xlApp.Visible = True Set xlBook = xlApp.Workbooks.Open("C:\MyBook.xls") xlApp.Run "AcceptArray", myarray End Sub The AcceptArray procedure within a module inside C:\MyBook.xls resembles the following: Option Explicit

Public Sub AcceptArray(ByVal myarray As Variant) MsgBox "Size of first dimension: " & UBound(myarray, 1) End Sub

