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

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

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

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

"ker_01" wrote in message
...
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

help! subscript out of range error when trying to return multiple
 

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
--
I appreciate any feedback.