Free Microsoft Excel 2013 Quick Reference

Passing Object Array to a Subroutine

Second question of the day.

Suppose I want to have an array of objects as an input parameter to a subroutine. What is the syntax for this?

Suppose, "A" is an array of "Foo" object, where "Foo" is a user-defined class. How do I pass "A" to another subroutine or function? Here are some failed syntax examples:



I've tried passing it as a variant, but I get the error "ByRef argument type mismatch".

Any help appreciated.

Post your answer or comment

comments powered by Disqus

I'm having trouble getting the syntax right for passing a 3-dimensional
array to a subroutine.
The array in question in originally defined as
dim variable() as single


ReDim variable(x,y,z) (through a series of For statements)

and later still....

Subroutine is called (subroutine needs a specific component of array)
A function would work for me as well.

Hope someone can help...


Hi Ozgrid

I am trying to pass three arrays from one subroutine to another, but VB doesnt appear to like it. It works fine if i only try to pass one array.

The calling statement looks like this:

Car_Fleet1 cars_1(), cars_2(), cars_3()

the definition for the Car_fleet1 sub looks like this

Sub Car_Fleet1(cars_1(), cars_2(), cars_3() As String)

Where am i going wrong??!!

Hello all

I want to pass a workbook object or a filepath string from a module in
MS Access 2000 to a subroutine in MS Word 2000. Before passing the
object or filepath, I want to check the workbook is already opened or
not. How can I do that?

IF the workbook is already opened
MS Word subroutine will extract excel data from the opened workbook
Open the excel workbook
MS Word subroutine extract excel data
End IF

If the workbook has been opened, the workbook is opened in the MS Word
subroutine would be better? Or the workbook is opened in MS Access
would be better?

My whole workflow
Firstly, MS Access calls a module with Workbook obj/filepath as
parameter in MS Word
Secondly, MS Word will extract data from MS Excel and then paste it
under a bookmark in MS Word.

Could anyone give me some advices? Thank you


If you have cells in a column that are defined as a named range, such as

MyRangeName defined as B10:B20

you may now have a formula in Column C from C:10 to C:20 that simply refers to the named range such as

=MyRange * 5 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
If you now want the formula to be a call to a subroutine, you have make the call by referencing the particular cell in the range and can not use the range name, as in


If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
You can not do


If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
If the Range was defined as a single cell then the above call would work. The problem as I conceptualize it is that when calling the VBA function, The function is designed to work on a single cell whereas the Name range being passed is not a single cell. Excel does not do you the favor of figuring out which element of the range you must be refering to as it normally does for just plain old formulas.

Having to use cell references defeats the benefit of having the column as a named range. My scenario is that we are modifying the design of the sheet often and it becomes very cumbersom and error prone having to use absolute column references in place of taking advantage of the Named Ranges.

Is there a way around this problem?


Thank you all for all the help you have given me thus far. I have another question, is there something special about how you pass an array that I missed?

I have a class

Private Sub Class_Initialize() 
    Redim MemoryArray(0) As Variant 
End Sub 
Public Sub ReplaceMemory(GivenArray() As Variant) 
    Redim MemoryArray(UBound(GivenArray)) As Variant 
    For Index = LBound(GivenArray) To UBound(GivenArray) 
        MemoryArray(Index) = GivenArray(Index) 
    Next Index 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
that I am specificall passing an array to the replacememory sub. So in the program I have a global array doved criteria:

Sub Product2() 
     'fill criteria with various entries
     'do whatever in macro sub
    Set MemoryCriteria = New Memory 
    MemoryCriteria.ReplaceMemory (Criteria) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
and on the last line I get an error message:

Compile Error:
Type mismatch: array or user-defined type expected

Does anyone have any idea why it is doing this?


Hi, there
Has anyone got an example to pass a two dimentional array to a function and return a two dimentional array back. When calling this function, how to use the value in the returned two dimentional array?


Best regards,

How can one pass parameters to a subroutine that is in a different excel workbook and execute the subroutine?

Trying to update some VBA I've developed to change a two dimensional array
to a custom data type as I've found it to be easier to work with the entire

However, I ran into a problem when I tried to pass the array to a Listbox in
a userform.

With the array (Item) defined as a 2-d array the code

UserForm1.ListBox1.List = Item

works just fine and the array appears in the Userform.

However if I dim the "Item" array as a custom type I get the following error

"Only user-defined types defined in public object modules can be coerced to
or from a variant or passed to late-bound functions"

Is there something I can do to make this work?



I have a variant array "class" which I wish to pass to a list box on
another form.

Please advise.

Training videos :

I have a procedure that I want to pass two arrays to. When I pass a single array it works fine but as soon as I try to pass two arrays I get a syntax error. Both arrays are declared exactly the same way and are used in the same way. Am I limited to passing only one array per procedure?

Hi Folks!

Anyone know how to pass an array of values as the ref argument of the Rank
function without hardcoding or using a range reference?

Assume I have this array of values generated by another formula:

How do I pass that array to Rank?

I can get the array passed but then the formula #VALUE! errors:


I've tried using a name for the array, Indexing, Indirect ???

I've never seen this done but I'm not certain that it can't be done.


Heres what's going on: my C# program is using an array of 2 Dimensional object arrays to represent a workbook and its subsequent worksheets. Essentially, each 2D object array represents a worksheet. My program manipulates these 2D object arrays (in stead of manipulting the worksheets directly through excel interop), then before the program closes, it tries to save the 'n' object arrays to the 'n' worksheets of the workbook. This doesnt seem to work at all though... only the data from the 1st worksheet is saved...

// sheets is a Sheets object and contains all of the sheets of the
// sheetArray (analogous to workbook) is a instance of a class that contains an array of 2 dimensional object arrays
(analogous to worksheets)

Worksheet tempSheet;
object[,] tempArray_sheet;

for (int i=0; i < sheetArray.Length; i++)
tempArray_sheet = sheetArray.getSheet(i);
tempSheet = (worksheet)sheets[i + 1]; // sheets[]' starting index is at 1
tempSheet.UsedRange.set_Value(XlRangeDataType.XlRangeValueDefault, tempArray_sheet);


I really need to get this working, can anybody help me out? It would be greatly appreciated!


XL2000: Returning Data with Data Access Objects (DAO) to a Custom ...

With Microsoft Excel, you can create custom functions using Visual Basic for Applications. You can also use Data Access Objects (DAO) to programmatically return information from an ...

XL2000: Returning Data with Data Access Objects (DAO) to a Custom ...

... you can create custom functions using Visual Basic for Applications. You can also use Data Access Objects (DAO) to programmatically return information from an external database.

Sub Test()
Dim d As Variant

Set d = Range("A1:A4") ' line 1
Range("B1:B4") = d ' line 2
End Sub

...line 1 does what I want, I get d(1) through d(4) set to the value in cells A1 though A4. Line 2, on the other hand, writes null values rather than the values in d(1) through d(4). I'm sure I used to have a single line assignment of an array to a range working a few years ago - what am I missing here? (I know the result of the above code is a simple copy but the question of about arrays and ranges)

(Excel 2007),
(re 'Posting Rules', 'The BB "Code" tag works very well' - it would be helpful if the rules could link to an example of how to do this...)

I have an array with 5000 rows and 3 columns. I want to assign the values in this array to a range. Is there any way to do this w/o using a for next loop that copies each cell value in the array into a cell in the range.

for example is it possible to do something like the following?


I know the above does not work but what else do i need to specify to avoid using a "for next" loop?

I am open to suggestions on how to go about trouble-shooting this one!

The program was working well, and I was giving a demo through when the program gave an Error 13 : "Type mismatch" when it went to transfer an array to a worksheet.

The array is a Variant type, single dimension, and each element consists of a string, some long, some short.

The arrays contain a list of differences found when comparing two worksheets, in this case the latest v. previous Excel reports.

The strange part about it is that this line of code in this Procedure works OK when used for comparing other earlier spreadsheets. It is just this latest comparison that causes the error. Previous comparisons have 6 elements, this latest has 23 elements in the array.

I have looked at each of the elements of the arrays in the different cases, and they look correct.

Got me beat, so after a few hours of head-banging I have decided to call on a collective 'Greater Knowledge and Wisdom'

Hoping you can help

    ' create worksheet for logging changes
    'delete previous sheets
    'skip any error messages if sheet not found
    Set wb3 = ActiveWorkbook 'Working in active workbook
    On Error Resume Next
    Application.DisplayAlerts = False
    For Each wsh In wb3.Worksheets
        If InStr(wsh.Name, "Change Log") > 0 Then
        End If
    Next wsh
    Sheets().Add After:=wb3.Sheets(Sheets.Count)  ' add after the last existing sheet
    ActiveSheet.Name = "Change Log"
    Set ws3 = wb3.Sheets("Change Log")
    On Error GoTo 0  'clears any errors found above
    'examine the array contents
    For i = 1 To UBound(aryChange)
        Debug.Print i & " " & aryChange(i)

Hi everyone,

I have username and password data that need to be sent to a web query, but for security reasons, I don't want either one to appear on the spreadsheet. To get around this, I made a UserForm that runs when the workbook opens to get the data into VBA variables, but I don't know how to then pass this data to a query. The query goes to a PHP SOAP client that requires authentication, so I can't send requests without a username and password, and yet I don't want them to appear on the screen. What I want to know is if there is a way to either:

1) Refer to VBA variables in a query so that the data inside the variables gets sent.

2) Write a macro that builds and executes a dynamic query at runtime.


How do I pass an array in a user defined function? E.g.,

=FunctionName(Array1, Constant, ...)

Thanks for any help

Is it possible to write a single row from a 2D array to a range?

Let's say I have a 10x10 Array called TrueTestTimeA
and a 10x10 range called TrueTestRange

To write the entire Array to the Range I would use:

But what if I only want to write one row of the array to a singe row range. I know I can do this by looping, but is there a
way to do it with a single line like the .value (or another) technique?



Is there any quick way of passing an array to a group of cells ?

It is easy enough to place an array of numbers in a spreadsheet as follows:-

RowNum = 0
ColNum = 0
For I = 1 to 50
  RowNum = RowNum + 1
  For J = 1 to 10
     ColNum = ColNum + 1
     Cells(RowNum,ColNum)  = X(I,J)
BUT this takes a long time compared with copying and pasting a group of cells
in Excel or compared with calculating values in the array - operations which can be virtually instantaneous.

Many of my VB macros work splendidly fast while they are calculating but the user gets held up staring at the screen while the results get posted to a worksheet with for/next loops.

What is the short-hand method to copy a varaint array to a worksheet.

Given a variant array called vaData which is 10 rows x 5 columns

Range("F20:I26").Value = vaData 'Would copy the data to F20:I26

How can this be written dynamically using
Ubound(vaData) and UBound(vaData,2) in place of the cell range reference?


Hi there,

If one has data in a database, and gets the data via ADO, is there an easy way to transfer the data to a Range object. Is there some kind VBA function that can do this.

Or does one have to pass an ADO recordset to a VBA arrray, and then pass the array to the range object.

If this is the case, is there a VBA function to easily copy data from an array to Range object, without having to do lots of looping?



I have a subroutine defined as
Sub subCreateSpreadsheetWORKSHEET(ByRef NEWBOOK As Object, ByRef DASHEET As Object)

But keep getting compiling errors when I call the function by


What is the syntax I am suppose to use to pass two variables into a function? Thanks!

No luck finding an answer? You could always try Google.