Converting range to variant is easy; how do I handle rows vs colum

[ker_01]

I've created some code for myself that works pretty well. I input two
(vertical) ranges of data via a UDF that are then processed and modify my
sheet as desired. Here is a simplified example of the piece of code I need to
tweak

Dim RangeToArray as Variant

RangeToArray = Sheet1.Range("A1:A4").value
'Actual range is entered via a UDF, so it could be anywhere- this is just an
example

for i = lbound(RangeToArray) to ubound(RangeToArray)
TempCellValue = RangeToArray(i,1)
'do stuff
Next

Now I need to share it with other users, and I want the code to handle
different range entries without blowing up.

Situation: User places data in a horizontal range instead of a vertical range

My Approach: Check ubound of the range. If =1, then either it is a
horizontal range, or there is only one data point. Loop the horizontal range
and pull the data values out into a format that is compatible with the rest
of my code.

Problem: In my testing, I haven't been able to figure out how to find the
max number of [horizontal] elements "i" in RangeToArray(1,i)
[note, i is now the second parameter for a horizontal array]

Ubound doesn't seem to work on this; I tried variations like
ubound(RangeToArray(2)). Should I just make an obscenely high fixed loop (i=
1 to 1000000) and just catch the out of bounds error when it occurs, or is
there a better way to find the width of a horizontal range that has been
passed to a variant?

Thank you!
Keith

 

[Jacob Skaria]

Ubound(RangeToArray)
OR
Ubound(RangeToArray,1) will return the upper bound of the first dimension


Ubound(RangeToArray,2) will return the upper bound of the second dimension

If this post helps click Yes