XL: Limitations of Passing Arrays to Excel Using Automation

• Microsoft Excel 97 for Windows
• Microsoft Excel for Windows 95, versions 7.0, 7.0a
• Microsoft Excel for Windows, version 5.0c

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 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 needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

   http://www.microsoft.com/support/supportnet/refguide/default.asp

   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

Footnotes

A:

The maximum number of elements in the array is approximately 6550. If you exceed this limit, you receive one or both of the following error messages:

   Out of Memory
   1005: Unable to set the Value property of the Range class

   Run-time error '1004':
   Transpose method of Application class failed

The maximum number of elements in the array is 4095. If you exceed this limit, you may receive the following error message:

   Run-time error '1004':
   Run method of Application class failed

C:

When defining the array parameter in the Excel macro, it must be defined as a Variant variable or you will receive the following error message when you attempt to run the macro using Automation:

   Run-time error '1004':
   Cannot find macro <macro name>

   Public Sub AcceptArray(ByVal myarray As Variant)
      ' You can pass the parameter either ByVal or ByRef.
      ' To determine the number of elements in the array, use
      ' the UBound function.
   End Sub

The maximum number of elements in the array is 5461. If you exceed this limit, you receive one of the following error messages:

   Run-time error '1004':
   Transpose method of Application class failed

   - or -

   Run-time error '1005':
   Unable to set the Value property of the Range class

The maximum number of elements in the array is 5461. If you exceed this limit, you receive the following error message:

   Run-time error '1004':
   Run method of Application class failed

The maximum number of elements in the array is limited by available memory or the Excel worksheet maximum size (65536 rows X 256 columns). However, the maximum number of elements in the array that you can pass to Excel using the Excel Transpose function is 5461. If you exceed this limit, you receive the following error message:

   Run-time error '13':
   Type Mismatch

The maximum number of elements in the array is limited only by available memory. Also, you do not have to define the parameter as a Variant variable in the Excel macro. However, if you want to pass the array ByVal, you must define the parameter as a Variant variable as in the example in footnote C earlier in this article. You will get a run-time error 13, "Type Mismatch" error, if you do not define the variable as a Variant.

H:

When passing an argument ByRef to an out-of-process Automation server, such as Excel, marshalling of the data is done between the Automation controller (or client) and server since they run in separate processes. This means that when an array is passed to Excel using ByRef, a copy of the array is sent to the address space of Excel. After the Excel procedure runs, a copy of the array is passed back to the client. Although this does allow for passing arguments using ByRef to an out-of-process server, it is not very efficient. On the other hand, when using an in-process automation server (a dynamic-link library (DLL)) and you pass an argument ByRef, this is very efficient since no marshalling is done. The server is using the same array in memory as the client. This is possible since the server runs in the same address space as the client.

Because of marshalling with an out-of-process server such as Excel, it is more efficient to pass the array ByVal instead of ByRef. This way, only one copy is passed to Excel and Excel does not have to pass the copy back to the client. To pass an array ByVal to an Excel macro, you must define the parameter in the Excel macro as a Variant variable. See footnote C earlier in this article for an example.

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

   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

   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

   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

   Option Explicit

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

REFERENCES

For more information about arrays and Excel, please see the following articles in the Microsoft Knowledge Base:

   ARTICLE-ID: Q75376
   TITLE     : XL: Maximum Array Size in Microsoft Excel

   ARTICLE-ID: Q166342
   TITLE     : XL97: Maximum Array Size in Microsoft Excel 97

   ARTICLE-ID: Q153307
   TITLE     : HOWTO: Call Microsoft Excel Macros that Take Parameters

   ARTICLE-ID: Q153090
   TITLE     : How To Pass a Visual Basic Array to an Excel Worksheet

   ARTICLE-ID: Q163435
   TITLE     : VBA: Programming Resources for Visual Basic for
               Applications