Evaluate an array with worksheet.function

[scottcolbury]

Not sure if this is possible.
I want to evaluate an array, actually a defined section of an array with a worksheet function. I know how to do this with a worksheet range like this...

x = Evaluate("Worksheet.Function.Sum(.Range(" & .Cells(1, 2).Address & ":" & .Cells(2, 2).Address & "))")

but what I need to do is something like this...

x = Evaluate("Worksheet.Function.Sum(" & array(1, 2)  & ":" & array(2, 2)& ")")

...where all my data is stored in the array and I pass in what range of data to calculate on. Also if there is a way to do this on a section of a collection that would be great to know!

thanks in advance
s_c

 

[jindon]

You can't do it and you need to loop.

 

[northwolves]

Not sure if this is possible.
I want to evaluate an array, actually a defined section of an array with a worksheet function. I know how to do this with a worksheet range like this...

x = Evaluate("Worksheet.Function.Sum(.Range(" & .Cells(1, 2).Address & ":" & .Cells(2, 2).Address & "))")

but what I need to do is something like this...

x = Evaluate("Worksheet.Function.Sum(" & array(1, 2)  & ":" & array(2, 2)& ")")

...where all my data is stored in the array and I pass in what range of data to calculate on. Also if there is a way to do this on a section of a collection that would be great to know!

thanks in advance
s_c

Do you means:

Sub macro1()
[a1:b3] = [{1,2;3,4;5,6}]
arr = [{"a1","b1";"a2","b2";"a3","b3"}]
MsgBox Evaluate("Sum(" & arr(1, 2) & ":" & arr(3, 2) & ")") 'b1+b2+b3=2+4+6=12
End Sub

Regards
Northwolves

 

[jindon]

↑
Scott

It's just a trick.

 

[scottcolbury]

Thanks Northwolves.

But how do I pass in the values of my array dynamically?
For instance, if I have

myarray(1 to 10, 1 to 100)

but only want to pass in the values stored in

myarray(2, 50 to 75)

Also, the number of values will change... something like

myarray(2, 5 to 10)
myarray(2, 50 to 75)
myarray(2, 5 to 95)

Can I change the [a1:b3] array dynamically? And how do I deal with the values in the 'arr' variable?

Thanks in advance
s_c

 

[jindon]

Scott

His code is just a joke.

What his code is doing is storing the cell address in the array and used it to refer the range.
So, if you want that code, you must have array that hold the individual addresses of the range.

That makes no sense to use array.

 

[northwolves]

Thanks Northwolves.

But how do I pass in the values of my array dynamically?
For instance, if I have

myarray(1 to 10, 1 to 100)

but only want to pass in the values stored in

myarray(2, 50 to 75)

Also, the number of values will change... something like

myarray(2, 5 to 10)
myarray(2, 50 to 75)
myarray(2, 5 to 95)

Can I change the [a1:b3] array dynamically? And how do I deal with the values in the 'arr' variable?

Thanks in advance
s_c

Why not using a loop such as "for..next... ",It's easy:

Sub Test()
Dim arr, brr, i As Long
arr = [mmult(row(1:10),column(a:cv))] 'arr(1 to 10, 1 to 100),values as row*column
ReDim brr(57 To 90)' define an array to store the data
For i = 57 To 90'Loop
brr(i) = arr(2, i)
Next
MsgBox Join(brr, ",")'Show the result
End Sub

Regards
Northwolves

 

[northwolves]

You may use API (copymemory) to copy the elements you want to a new array with the following UDF without any loops.

Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (pDst As Any, pSrc As Any, ByVal ByteLen As Long)    'API to use
Function subarray(ByRef myarray, ByVal row As Long, ByVal startcol As Long, ByVal endcol As Long)    'Return a subarray from myarray(row,startcol) to myarray(row,endcol)
    Dim arr
    ReDim arr(startcol To endcol)    'Define a new array
    myarray = Application.Index(myarray, row, 0)    ' All elements of only the (row)th myarray
    CopyMemory arr(startcol), myarray(startcol), (endcol - startcol + 1) * 16    'Get elements from myarray(row,startcol) to myarray(row,endcol)
    subarray = arr    'Return the value
End Function

Sub Test()
    Dim arr, brr
    arr = [mmult(row(1:10),column(a:cv))]    'arr(1 to 10, 1 to 100),values as row*column
    brr = subarray(arr, 3, 57, 90)
    MsgBox Join(brr, ",")    'Show the result
End Sub

Regards
Northwolves