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

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
---------------
Jacob Skaria


"ker_01" wrote:

> 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