How to recognize a range as an array?

[Kelvin Stott]

Hello,

I have created several functions in VBA that operate on arrays created by other functions in VBA, however they don't recognize cell ranges as arrays. For example, the UBound function doesn't work with a range argument within my function.

How can I get my functions to recognize cell ranges as arrays, as well as arrays that I have created directly in VBA?

Any help would be appreciated, thanks.

Kelvin

 

[Kyle123]

It depends on the function, cell ranges can be easily converted into 2 dimensional arrays:

Dim rng as Variant
rng = Sheet1.Range("A1:A100").value

To make them into single dimensioned arrays, you need to transpose them:

Dim rng as Variant
rng = application.transpose(Sheet1.Range("A1:A100").value)

For more than one row & column combinations, you'll need to work with them as a 2 dimensional array as per the first example

 

[Kelvin Stott]

Thanks, but how do I get the following function to work when Data can be either a cell range, or an array created by another VBA function:

Function UBD(Data) 'Where Data can be either a range or an array

UBD = UBound(Data)

End Function

 

[pgc01]

Hi Kevin

You question is very incomplete.

Case array:

From which dimension do you want the ubound?

Case range:

What do you mean by Ubound in the case of a range?
The number of rows? The number of columns?
Is the range always contiguous? In case of several areas which is the one you want to examine?


It's not possible to write a code with so many things to define.

Here is a code with the following assumptions:

- In case the parameter is a range I assume it's a contiguous range and consider Ubound as the number of rows of the range
- In case the parameter is an array I assume you want the Ubound of the first dimension of the array


Hope this example helps.

Sub Test()
Dim r As Range
Dim vArr As Variant

vArr = Array(2, 3, 4, 5)
MsgBox "Ubound of array (2, 3, 4, 5): " & UBD(vArr)

Set r = Range("C3:F4")
MsgBox "Ubound of range C3:F4: " & UBD(r)

End Sub
 


Function UBD(vData As Variant) 'Where Data can be either a range or an array
 

If IsObject(vData) Then
    If TypeOf vData Is Range Then UBD = vData.Rows.Count
ElseIf IsArray(vData) Then
    UBD = UBound(vData, 1)
Else
    UBD = "N/A"
End If
 
End Function

 

[Kelvin Stott]

Sorry for any confusion, the UBound was just an example, not important to the function.

I really just need a simple function that will create a one-dimensional numerical data array from alternative forms of data input:

Function GETARRAY(Data) -> 1-dimensional array of values (double data type), where Data may be...

Another data array -> first dimension
Range -> first row or column (whichever is the longest)
Text string -> Split with commas
Cell reference containing any of the above

Any more thoughts, please?

 

[mikerickson]

Consider the code

Dim myArray as Variant

myArray = Range("A1:B10").Value

Range A1:A10 is 10 rows by 2 columns, so myArray is a two dimensional array, as if it had been dimmed
Dim myArray(1 to 10, 1 to 2).

If instead, we use myArray = Range("A1:A10").Value, myArray would be dimmed (1 to 10, 1 to 1).

Its that second dimension 1 to 1 that confuses folks.

Try code like this

Dim myOneDimensionalArray as Variant

If SomeRange.Columns.Count = 1 Then
    myOneDimensionalArray = Application.Transpose(SomeRange.Value)

ElseIf SomeRange.Rows.Count = 1 Then
    myOneDimensionalArray = Application.Transpose(Application.Transpose(SomeRange.Value))

End If

 

[Kelvin Stott]

Thanks, but why double-transpose with a single row?

Also, how do I recognize and split a text array into numbers?

 

[mikerickson]

Transpose(1 to N, 1 to 1) results in a one-dimesional array (1 to N)

Range("A1:E1").Value is a (1 To 1, 1 To 5) array
Transpose(Range("A1:E1").Value) is a (1 To 5, 1 To 1) array
Transpose(Transpose(Range("A1:E1").Value)) is a one dimensional array (1 To 5)

 

[pgc01]

Hi

An alternative to the double Tanspose() in the case of a horizontal vector is the worksheet Index() function:

...
ElseIf SomeRange.Rows.Count = 1 Then
    myOneDimensionalArray = Application.Index(SomeRange.Value,0)
...

 

[Kelvin Stott]

Thank you!