Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
In multiple sheet copy error subscript out of range ?? HELP | Excel Programming | |||
Subscript out range error when redimensioning dynamic array | Excel Programming | |||
Array problem: Key words-Variant Array, single-element, type mismatch error | Excel Programming | |||
Redim 2D Array Subscript Out Of Range Error | Excel Programming | |||
Subscript Out of Range error in Array... | Excel Programming |