From MultiArray to Function

[logratio]

Hi,
Can I compute at run-time (without writing the data on sheet) a function like the one below where “rngDataFit” is a MultiArray or a variable containing data from a MultiArray?

Application.Min(Range(rngDataFit))

For instance, the MultiArray(r1006, c10) contains the data.
I’d like to use, f.i., the data in the MultiArray(r6 to 1006, c2).

I know how to handle the array.
My question is: Can I do that without writing the data on sheet?

Thanks,
Logratio

 

[byundt]

Logratio,
I'm not quite sure what you mean by MultiArray.

If you want to find the minimum of an array that contains other arrays, then you can use a recursive VBA function for the purpose. To illustrate, consider the following:

Sub MultiArrayTester()
Dim array1 As Variant, array2 As Variant
array1 = Array(1, 2, 3, 4, 5)
array2 = Array(array1, 2.5, 3.5, 4.5)
MsgBox myMin(array2)
End Sub

Function myMin(array1 As Variant) As Variant
Dim x As Variant, temp As Variant
myMin = 1E+128
For Each x In array1
    If IsArray(x) Then
        temp = myMin(x)
    Else
        temp = x
    End If
    If IsNumeric(temp) Then
        If temp < myMin Then myMin = temp
    End If
Next
If myMin = 1E+128 Then myMin = ""
End Function

If you trace the execution of the function, you will see that it calls itself when it hits the array element that contains an array. I believe you can go up to seven levels of recursion.

Brad

 

[logratio]

I mean a multidimensional array, one with more than one column of data.
Thanks for the attempt, I need computing several stats and am looking for a way to pass data from the array to the function, avoiding the sheet. The Min case is an easy one
Logratio

 

[byundt]

Are you needing to pass information from the worksheet into the function? If so, you can set a variant to the values in a worksheet range. This is much faster than accessing the cells one at a time.

Sub MultiArrayTester()
Dim array1 As Variant
array1 = ActiveSheet.Range("A1:D25").Value      'Grab the values from the worksheet
MsgBox myMin(array1)    'Find the minimum and report it
'Do more stuff
ActiveSheet.Range("A1:D25").Value = array1      'Send the array (including modifications) back to the worksheet
End Sub

Function myMin(array1 As Variant) As Variant
myMin = Application.Min(array1)
End Function

Brad

 

[TonyJollans]

Hi Logratio,

I don't fully understand your question, but the Application.Min Function takes a two-dimensional array as an argument; it doesn't have to be a Range and the data doesn't have to be on a Sheet.

You've got more of a problem if your multidimensional array has more than two dimensions and as for other functions, well that depends what they are

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[logratio]

My point is that I cannot (want not) call, for instance, code such as:

array1 = ActiveSheet.Range("A125").Value
Application.Min(array1)

I need to bypass the sheet and send the data from the array directly to the function
Logratio

 

[byundt]

Logratio,
The following statement in last code posted sends data from an array directly into the function. It then displays the result in a message box.
MsgBox myMin(array1) 'Find the minimum and report it

The posted code was using the worksheet cells as a convenient source of data. Any other means of creating a one or two-dimensional array would work just as well.

I don't believe I am understanding your real need--and you may not be understanding my attempts to explain. I fear we are making the discussion too abstract. Could instead describe the specifics of what you want to accomplish?
Brad

 

[logratio]

My issue is to pass to a function (like Min) all values in one of the columns of a multidimensional array created at run-time.
I Think I have found the solution.
What I need is to use INDEX:

INDEX(ArrayName;0;2)

This way I get all elements stored in column 2 of the array and use them to compute a function, f.i. Min().

I haven't tested this yet. I do it tomorrow,
Thanks for your kind help
Logratio

 

[logratio]

bad news
INDEX(ArrayName;0;2) seems to work on sheet but not with an array at run-time. I am still stuck...
Logratio

 

[logratio]

...my last
I get the solution if I transfer one column of data from the multidimensional array to a single array myArrayA and use, for instance, the funtion:

Application.Max(myArrayA)

How can I use 'directly' in the function one column of data from the multidim array?

 

[TonyJollans]

Hi Logratio,

A few more details of what you really want would help here!

If you have a 2-dimensional array, you can use INDEX to return a 'row' or a 'column' from it which you can then pass to MIN, for example:

[blue]Application.Min(Application.Index([i]ArrayName[/i], , 2))[/blue]

but in general VBA arrays can not be sliced and diced the way cells on a worksheet can. Why are you so keen not to use a worksheet?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[logratio]

You got it Tony, thanks!
This is exactly what i was looking for.
I appreciate your help,
Logratio