help! subscript out of range error when trying to return multiple values from function (as variant a

[ker_01]

XL2003

I'm feeding two values into a function, and trying to get three back. I'm
posting simplified code here in the hopes that someone sees something in the
syntax that I'm unaware of (sample code shows the same symptoms). It errors
out when I try to assign the first value of the returned array, with a
'subscript is out of range' error for ConversionData(1).

I was under the impression that the function returns a variant array, so I
should be able to call each individual component later in my code. Where am
I going wrong? The three individual values are being assigned correctly in
the function, and as far as I can tell the transpose code is correct, it
just isn't coming back over to my sub. I tried debug.print xLMSTranslate(1)
but it autoprompts for (text, int) which is my input, not the output variant
array.

Thanks for any help !!!
Keith
'------------------------------------------------------------------

Public ConversionData As Variant 'Array (1 To 3)
Public IntermediateArray(1 To 50000, 1 To 50)


Sub MyMainSub()
ConversionData = xLMSTranslate("MyTextString", 1)
IntermediateArray(1, 7) = ConversionData(1) '*** errors out here
***
IntermediateArray(1, 8) = ConversionData(2)
IntermediateArray(1, 9) = ConversionData(3)
End Sub
'------------------------------------------------------------------

Function xLMSTranslate(BacklogArrayValue As String, SourceNum As Integer) As
Variant
Std = "641"
Bat = "B"
StT = "C"
xLMSTranslate = Application.Transpose(Array(Std, Bat, StT))
End Function

 

[ker_01]

Apparently, the transpose worked for filling worksheet cells (validating
array content) but was messing up how the array was used in my code. When I
removed the transpose, it worked as expected.

Sorry for the extra bandwidth,
Keith

 

[J Sedoff comRemove>]

You could also just change the code to this:
IntermediateArray(1, 7) = ConversionData(1, 1)
IntermediateArray(1, 8) = ConversionData(2, 1)
IntermediateArray(1, 9) = ConversionData(3, 1)

Since you transposed it, it becomes a 2D array, rather than just 1D, so you
need to include the 2nd dimension (the ",1)" ).

Hope this helps, albeit a little late! Jim