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:
For more information about the support options that are available
and about how to contact Microsoft, visit the following Microsoft Web site:
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
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
The maximum
number of elements in the array that you can pass to Excel using the Excel
Transpose function is 4095. If you exceed this limit, you receive the following
error message:
Run-time error '1004':
Transpose
method of Application class failed
B:
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
NOTE: When passing a
multidimensional array, you may not receive an error message, but the Excel
macro will not run when the total number of elements exceeds 4095.
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>
For
example, suppose the name of your macro in Excel is AcceptArray. Here is an
example of how the array must be defined in the Excel macro:
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
D:
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
Run-time error '1005':
Unable to set
the Value property of the Range class
E:
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
F:
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
G:
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
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