Hi all,
I've searched; and although I have found numerous threads about passing arrays - they don't seem to describe or solve the problem I'm having. I apologise if this has been answered and my search skills are as bad as my VBA. I decided not to stumble in the dark and registered so I could glean some information from you all.
So I'm working with multiple workbooks. In the control book; I loop through a series of workbooks - open each one and loop within that book to extract some data which I then consolidate back in the control workbook.
I have two sub-routines which do some work. The first creates 2 arrays; one 2 dimensional; and one single dimensional. To do this I defined the arrays upfront in my main sub-routine as follows:
Dim aCostingData() as Variant
Dim aMonthlyData() as Variant
...
ReDim aCostingData(1,0) as Variant
ReDim aMonthlyData(0) as Variant
Call FillDataArrays(aCostingData, aMonthlyData)
Call GenerateXMLText(aCostingData, aMonthlyData)
The two sub-routines are declared with the variables passed as "byref". The only difference being that in the first sub-routine, I "ReDim" the arrays, using the keyword preserve to resize as needed; but in the second, I have no need to ReDim the arrays - just to use them.
Sub FillDataArrays(ByRef aCostingData() as Variant, ByRef aMonthlyData() as Variant)
...
'Loop goes here with a counter, iCounter
...
ReDim Preserve aCostingData(1, iCounter)
'Fill Data and loop
...
'Loop goes here with a counter, iCounter
...
ReDim Preserve aMonthlyData(iCounter)
'Fill Data and loop
...
End Sub
Sub GenerateXMLText(ByRef aCostingData() as Variant, ByRef aMonthlyData() as Variant)
...
'Extracting the data in the arrays to a string, sXMLString as follows:
'Loop though the aCostingData array with iCounter
For iCounter = 0 to UBound(aCostingData, 1)
sXMLString = sXMLString & "<" & aCostingData(0, iCounter) & ">" & aCostingData(1, iCounter) & "</" & aCostingData(0, iCounter) & ">" & vbCrLf
Next
...
'Same for MonthlyData, but without the 2 dimensions
End Sub
The first call works like a charm and returns the filled arrays. The second hits an error, stating a type mismatch in the ByRef variable. Do you perhaps know why?
Bookmarks