Solved: Passing an array to a sub

Hi guys,

I'm trying to pass a 13x2 array to a subroutine, but I keep getting a "ByRef Argument Type Mismatch". I'm not sure what to do about it...

Here's the code that is receiving the array:

[vba]Sub Invoice_InsertDetail(lInvoiceID As Long, aryInvDetail() As Variant)
'Date Created : February 16,2006 11:31
'Author : Ken Puls
'Macro Purpose: Place an array of invoice detail data into the database

Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim colHead As String
Dim lDeptID As Long
Dim indx As Long
Dim sSQL As String

'Concatenate a string with the names of the column headings
colHead = "(ExpTypeID,Amount,DeptID,InvID)"
lDeptID = Application.WorksheetFunction.Index(_
ActiveWorkbook.Worksheets("Tables").Range("tblDepts"), _
ActiveSheet.Range("VBA_Dept"))

'Open connection to the database
cnt.Open sConnect

'Build the SQL statement
For indx = 0 To 13
sSQL = "INSERT INTO tblInvDetail " & colHead & " VALUES (" & _
GetExpTypeID(aryInvDetail(indx, 0)) & "," & _
aryInvDetail(indx, 1) & "," & lDeptID & "," & lInvoiceID & ");"
rst.Open sSQL, cnt
Next indx

'Close the ADO objects
cnt.Close
Set rst = Nothing
Set cnt = Nothing
End Sub[/vba]

Highlighted code is this part: aryInvDetail(indx, 0)

I've tried:

[vba]Sub Invoice_InsertDetail(lInvoiceID As Long, ByRef aryInvDetail() As Variant)
Sub Invoice_InsertDetail(lInvoiceID As Long, ByRef aryInvDetail As Variant)
Sub Invoice_InsertDetail(lInvoiceID As Long, aryInvDetail As Variant)[/vba]

Can anyone help out on this? It's driving me crazy!

Thanks!

I pass multidimensional arrays around all the time and do not remember any problems. Most of my procs avoid declaring array type as they are normally numeric arrays and could be integer, long, single or double. It is not a very good solution from the standpoint of "good code", but try eliminating everything except the actual array names.

Serves you right for using zero based arrays. Who ever heard of the zeroth item in a list or matrix. Bah! Humbug!

Ken,

It seems to me if you're getting the error at aryInvDetail(indx, 0) then it may have something to do with the function you're sending it to
GetExpTypeID(aryInvDetail(indx, 0))

Do you by chance have the argument for that function to be a ByVal? Array items have to be ByRef, if the function is expecting ByVal, you'll error out.

Matt

Hey guys,

Mark, why not use a 0 based array? They come standard out of the box that way, and I figured that I'd just work with what MS gave me. Save the Option Base 1. Any advantage to going that route?

Matt, I think you nailed it. I can get it to work by assigning the array element to a temporary string, although it seems kind of dumb to have to do it:

[vba] 'Build the SQL statement
For indx = 0 To 13
sTemp = aryInvDetail(indx, 0)
sSQL = "INSERT INTO tblInvDetail " & colHead & " VALUES (" & _
GetExpTypeID(sTemp) & "," & _
aryInvDetail(indx, 1) & "," & lDeptID & "," & lInvoiceID & ");"
rst.Open sSQL, cnt
Next indx[/vba]

Changing the function to ByRef didn't help at all. :dunno

Another thought could be that you're sending a variant array item to a string variable in the function.. maybe you could use CStr(aryInvDetail(indx, 0)) ?

Pass it as a simple variant?

[vba]
Sub Invoice_InsertDetail(lInvoiceID As Long, aryInvDetail)
[/vba]

BINGO!

That's it, Matt. :thumb

I would have thought that the element would pass as a string on it's own, but I guess not.

Thanks!