Passing 2-dimensional array to subroutine

[Guest]

Help!
(I am using Access 2003.)

I am having problems passing a 2-dimensional array to a subroutine. The
array is "redimed", as the number of elements is not known. The array fills
successfully, as my debug.print statement shows:
x=4: 84111 13
x=5: 87315 1
x=6: 87326 3

However, when the array is passed to another routine, the program crashes
with an error when it tries to read the passed array: "Compile Error:
Expected Array"

I've tried different syntaxes, but nothing seems to work.
The subroutine opens an OLE Automation session with Excel.
This goes just fine. I can even push in single variable data just fine.
But Access chokes on my array. Here is a code snippet of both routines:

Sub CallingRoutine
Dim arWU() As Long
....
ReDim Preserve arWU(1, RecRows)
For x = 0 To RecRows - 1
arWU(0, x) = rsWrk.Fields("CrsSection") ' a long
arWU(1, x) = Nz(rsWrk.Fields("CurrWorkHrs"), 0) ' a long
' I already showed you sample outputs of the debug statement
Debug.Print "x=" & x & ": " & arWU(0, x) & " " & arWU(1, x)
.MoveNext
Next x

UpdateExcelSumAttd, SumMbr, arWU(1, RecRows)
....
End Sub

Sub UpdateExcel(att As Single, mbr As Single, ByRef WUnits As Long)
Dim y As Integer
....
Set oExcel = CreateObject("Excel.Application")

oExcel.Workbooks.Open "H:\SDL Tracking Sheets\Student.xls"
oExcel.Range("B15").Select
For y = 0 To arMax - 1
oxcel.activecell.Offset(y, 5) = WUnits(1, y) ' <-- BOMBS on WUNits word
Next y
....

If I replace "WUnits(1,y)" with a literal value, the routine works just
fine. it also works fine with a single variable. Any help is gratefully
appreciated!!

 

[Guest]

There are a number of issues here. Arrays are funny beasts. I think you can
find out all you need to know if you go to VBA help and seach for
"Understanding Parameter Arrays"

 

[Guest]

Thanks for the response. I already researched paramarrays and parameter
arrays, even tried it out. That didn't work for me. What other issues are you
referring to?

 

[Guest]

To pass an array, it has to be a Variant type, it has to be the last argument
passed, etc. the Understanding Parameter Arrays info in Help should tell you
all you need to know.

Here is an example that might help:

Public Sub setArray()
Dim varAry(3, 3) As Variant
Dim intX As Integer
Dim intY As Integer

Randomize
For intX = 0 To 3
For intY = 0 To 3
varAry(intX, intY) = Int((99 * Rnd) + 1)
Next intY
Next intX
Debug.Print TestArray(varAry)
End Sub

Public Function TestArray(varVals As Variant) As Long
Dim intX As Integer
Dim intY As Integer
Dim intTop As Integer

For intX = 1 To UBound(varVals) - 1
For intY = 1 To UBound(varVals, intX) - 1
If varVals(intX - 1, intY - 1) > intTop Then
intTop = varVals(intX - 1, intY - 1)
End If
Next intY
Next intX
TestArray = intTop

End Function

 

[Guest]

KLATUU,BARADA,NIKTO!

I had, in fact, done something like this before, even with Variant data type
in the subroutine. However, your example pointed out two mistakes on my part:
I forgot to dim the original array as variant rather than long; and I had
also tried to include parentheses with the array name.

I just ran the corrected version and it works. So, thanks!

 

[Guest]

Glad I could help.

KLATUU,BARADA,NIKTO!

I had, in fact, done something like this before, even with Variant data type
in the subroutine. However, your example pointed out two mistakes on my part:
I forgot to dim the original array as variant rather than long; and I had
also tried to include parentheses with the array name.

I just ran the corrected version and it works. So, thanks!

 

[RoyVidar]

Help!
(I am using Access 2003.)

I am having problems passing a 2-dimensional array to a subroutine.
The array is "redimed", as the number of elements is not known. The
array fills successfully, as my debug.print statement shows:
x=4: 84111 13
x=5: 87315 1
x=6: 87326 3

However, when the array is passed to another routine, the program
crashes with an error when it tries to read the passed array:
"Compile Error: Expected Array"

I've tried different syntaxes, but nothing seems to work.
The subroutine opens an OLE Automation session with Excel.
This goes just fine. I can even push in single variable data just
fine. But Access chokes on my array. Here is a code snippet of both
routines:

Sub CallingRoutine
Dim arWU() As Long
...
ReDim Preserve arWU(1, RecRows)
For x = 0 To RecRows - 1
arWU(0, x) = rsWrk.Fields("CrsSection") ' a long
arWU(1, x) = Nz(rsWrk.Fields("CurrWorkHrs"), 0) ' a long
' I already showed you sample outputs of the debug statement
Debug.Print "x=" & x & ": " & arWU(0, x) & " " & arWU(1, x)
.MoveNext
Next x

UpdateExcelSumAttd, SumMbr, arWU(1, RecRows)
...
End Sub

Sub UpdateExcel(att As Single, mbr As Single, ByRef WUnits As Long)
Dim y As Integer
...
Set oExcel = CreateObject("Excel.Application")

oExcel.Workbooks.Open "H:\SDL Tracking Sheets\Student.xls"
oExcel.Range("B15").Select
For y = 0 To arMax - 1
oxcel.activecell.Offset(y, 5) = WUnits(1, y) ' <-- BOMBS on
WUNits word Next y
...

If I replace "WUnits(1,y)" with a literal value, the routine works
just fine. it also works fine with a single variable. Any help is
gratefully appreciated!!

You are passing one array element (arWU(1, RecRows)), not the array!

You ar declaring one single element (ByRef WUnits As Long) not an
array!

Try declaring as array, and pass the array

Sub UpdateExcel(att As Single, mbr As Single, ByRef WUnits() As Long)

....


UpdateExcelSumAttd, SumMbr, arWU

 

[RoyVidar]

To pass an array, it has to be a Variant type, it has to be the last
argument passed, etc. the Understanding Parameter Arrays info in
Help should tell you all you need to know.

Here is an example that might help:

Public Sub setArray()
Dim varAry(3, 3) As Variant
Dim intX As Integer
Dim intY As Integer

Randomize
For intX = 0 To 3
For intY = 0 To 3
varAry(intX, intY) = Int((99 * Rnd) + 1)
Next intY
Next intX
Debug.Print TestArray(varAry)
End Sub

Public Function TestArray(varVals As Variant) As Long
Dim intX As Integer
Dim intY As Integer
Dim intTop As Integer

For intX = 1 To UBound(varVals) - 1
For intY = 1 To UBound(varVals, intX) - 1
If varVals(intX - 1, intY - 1) > intTop Then
intTop = varVals(intX - 1, intY - 1)
End If
Next intY
Next intX
TestArray = intTop

End Function

I think you are talking about ParamArray not passing Arrays, aren't
you?

Try the following alterations of your routine;

Public Sub setArray()
Dim varAry(3, 3) As Long
Dim intX As Integer
Dim intY As Integer

Randomize
For intX = 0 To 3
For intY = 0 To 3
varAry(intX, intY) = Int((99 * Rnd) + 1)
Next intY
Next intX
Debug.Print TestArray(varAry)
End Sub

Public Function TestArray(varVals() As Long) As Long
Dim intX As Integer
Dim intY As Integer
Dim intTop As Integer

For intX = 1 To UBound(varVals) - 1
For intY = 1 To UBound(varVals, intX) - 1
If varVals(intX - 1, intY - 1) > intTop Then
intTop = varVals(intX - 1, intY - 1)
End If
Next intY
Next intX
TestArray = intTop

End Function

 

[Stefan Hoffmann]

hi Roy,

Public Function TestArray(varVals() As Long) As Long
Dim intX As Integer
Dim intY As Integer
Dim intTop As Integer

For intX = 1 To UBound(varVals) - 1
For intY = 1 To UBound(varVals, intX) - 1
If varVals(intX - 1, intY - 1) > intTop Then
intTop = varVals(intX - 1, intY - 1)
End If
Next intY
Next intX
TestArray = intTop

End Function

Better use the LBound() function too:

Public Function TestArray(varVals() As Long) As Long

Dim intX As Integer
Dim intY As Integer
Dim intTop As Integer

For intX = LBound(varVals, 1) To UBound(varVals, 1)
For intY = LBound(varVals, 2) To UBound(varVals, 2)
If varVals(intX, intY) > intTop Then
intTop = varVals(intX, intY)
End If
Next intY
Next intX
TestArray = intTop

End Function


mfG
--> stefan <--

 

[RoyVidar]

hi Roy,

Better use the LBound() function too:

Public Function TestArray(varVals() As Long) As Long

Dim intX As Integer
Dim intY As Integer
Dim intTop As Integer

For intX = LBound(varVals, 1) To UBound(varVals, 1)
For intY = LBound(varVals, 2) To UBound(varVals, 2)
If varVals(intX, intY) > intTop Then
intTop = varVals(intX, intY)
End If
Next intY
Next intX
TestArray = intTop

End Function


mfG
--> stefan <--

I concentrated only on the issue of passing an array to a function or
sub, and did not pay any attention to the rest of Klatuu's code.

I would probably not use LBound at all, since all arrays I use are
indexed starting with 0, and I would probably not use UBound within the
loops either, but perhaps something like this

Public Function rvsTestArray(varVals() As Long) As Long

Dim lngOuter As Long
Dim lngInner As Long
Dim lngOuterMax As Long
Dim lngInnerMax As Long
Dim lngMax As Long

lngOuterMax = UBound(varVals, 1)
lngInnerMax = UBound(varVals, 2)

For lngOuter = 0 To lngOuterMax
For lngInner = 1 To lngInnerMax
If varVals(lngOuter, lngInner) > lngMax Then
lngMax = varVals(lngOuter, lngInner)
End If
Next lngInner
Next lngOuter

rvsTestArray = lngMax

End Function