can I use WorksheetFunctions ON arrays from VBA??

[fern]

Hi,

*i hope someone out there can understand the convoluted explanatio
i've written below... but, to cut to the chase, i'm looking to use
worksheetfunction (like countif & average) on part or all of an array
is that even possible or, if not, what alternatives do i have???*

I have a huge amount of data that excel is unwilling to store for m
(very long numbers in 10,000columns x 10,000rows) so I've decided t
move it all into an array so that I can summarise it from there. And
couldn't save it as an array matrix of 10000x10000 elements because
don't know how to reference a specific _range_ of elements within th
array [as you would a range of cells on a sheet... e.g. Range(A1:A3) o
Range(Cells(1,1),Cells(3,1))].

So my solution was to save each 'column' of data into its own singl
dimension array (since I only need to access the 'columns' as a whole
not certain parts of them) and then save each of those arrays as one o
the 10000 elements in an 'umbrella array' (i.e. an array of arrays). Fo
example (tho this obviously isn't a very efficient example):

Code
-------------------
'first fill each of the 'column' arrays
arr1 = (1,2,3...,10000)
arr2 = (1,2,3...,10000)
.....
arr10000 = (1,2,3...,10000)
'then move each of those into the 'umbrella array' in turn
umbArr(1) = arr1
umbArr(2) = arr2
....
umbArr(10000) = arr1000
-------------------

You see, doing this lets me refer to the element arr2(513) by sayin
umbArr(2)(513). And I thought that it would also let me use th
'columns' (arr1, arr2 etc) from WITHIN WorksheetFunctions. But it's no
working...

Specifically, I need to calculate the number of elements in eac
'column' of the matrix (=each arr1-arr10000 array) that equal or excee
the first element in that array. If I were dealing with cells on
worksheet, then I could use something like:

Code
-------------------
NumExtreme = WorksheetFunction.CountIf(Range(Cells(1,2),Cells(10000,2)),Range(1,2)) + WorksheetFunction.CountIf(Range(Cells(1,2),Cells(10000,2)), ">" & Range
-------------------

But VBA won't let me do this:

Code
-------------------
NumExtreme = WorksheetFunction.CountIf(arr2, arr2(1)) + WorksheetFunction.CountIf(arr2, ">" & arr2(1)
-------------------
Or this:

Code
-------------------
NumExtreme = WorksheetFunction.CountIf(umbArr(2), umbArr(2)(1)) + WorksheetFunction.CountIf(umbArr(2) , ">" & umbArr(2)(1)
-------------------

So, other than temporarily loading all of those values into a workbook
using a CountIf on the cells, and then erasing those cells once I have
result, I don't know how to solve this dilemma... I'm stuck

Any suggestions you can give would be most appreciated.
Thanks so much,

 

[fern]

Is my only option to loop through each element in the umbArr array (or
to identify each 'column' array individually, e.g. arr1, arr2 etc) and
then to loop through each element in that (e.g. arr1(1), arr1(2), etc)
-by doing something like this:

Code:
--------------------
For Each u In umbArr
For i = 1 to 10000
If umbArr(u)(i) >= umbArr(u)(1) Then
NumExtreme = NumExtreme + 1
End If
Next i
Next u
--------------------

(yes I know that's an ugly code but it's just a quick example).

Anyway, is that my only option? To loop through 10000 rows for each of
the 10000 columns?? Grrr - that's precisely what I was wanting to
avoid.

Hey, can I use Case Select on arrays? Would that work better, faster,
at all??

Ahh, I'm so confused & frustrated...

 

[Tom Ogilvy]

build an array of indexes that match your condition. Then use that with
each column array to calculate the value yourself.

--
Regards,
Tom Ogilvy

Hi,

*i hope someone out there can understand the convoluted explanation
i've written below... but, to cut to the chase, i'm looking to use a
worksheetfunction (like countif & average) on part or all of an array.
is that even possible or, if not, what alternatives do i have???*

I have a huge amount of data that excel is unwilling to store for me
(very long numbers in 10,000columns x 10,000rows) so I've decided to
move it all into an array so that I can summarise it from there. And I
couldn't save it as an array matrix of 10000x10000 elements because I
don't know how to reference a specific _range_ of elements within the
array [as you would a range of cells on a sheet... e.g. Range(A1:A3) or
Range(Cells(1,1),Cells(3,1))].

So my solution was to save each 'column' of data into its own single
dimension array (since I only need to access the 'columns' as a whole,
not certain parts of them) and then save each of those arrays as one of
the 10000 elements in an 'umbrella array' (i.e. an array of arrays). For
example (tho this obviously isn't a very efficient example):

Code:
--------------------
'first fill each of the 'column' arrays
arr1 = (1,2,3...,10000)
arr2 = (1,2,3...,10000)
.....
arr10000 = (1,2,3...,10000)
'then move each of those into the 'umbrella array' in turn
umbArr(1) = arr1
umbArr(2) = arr2
....
umbArr(10000) = arr10000
--------------------

You see, doing this lets me refer to the element arr2(513) by saying
umbArr(2)(513). And I thought that it would also let me use the
'columns' (arr1, arr2 etc) from WITHIN WorksheetFunctions. But it's not
working...

Specifically, I need to calculate the number of elements in each
'column' of the matrix (=each arr1-arr10000 array) that equal or exceed
the first element in that array. If I were dealing with cells on a
worksheet, then I could use something like:

Code:

WorksheetFunction.CountIf(Range(Cells(1,2),Cells(10000,2)),Range(1,2)) +
WorksheetFunction.CountIf(Range(Cells(1,2),Cells(10000,2)), ">" & Range)

--------------------

But VBA won't let me do this:

Code:

WorksheetFunction.CountIf(arr2, ">" & arr2(1))WorksheetFunction.CountIf(umbArr(2) , ">" & umbArr(2)(1))

 

[Tom Ogilvy]

The time should be almost instantaneous for each column. As I said,
building an index array for the first column elements that meet the
condition should reduce the amount of work you do on each array.

 

[fern]

build an array of indexes that match your condition. Then use that with
each column array to calculate the value yourself.Tom Ogilvy Wrote:
As I said,building an index array for the first column elements that
meet the
condition should reduce the amount of work you do on each array.

Ok, call me dense Tom but I don't completely understand what you're
telling me to do... Are you saying that this new array ("IndArr")
should have "1" entered into each of its elements (since the condition
I need to compare the columns with is always in the first
index/position - i.e. row 1)?? Or should IndArr contain arr1(1),
arr2(1), etc instead? Or do I go all the way up to referencing umbArr
as well? Or do I do something totally different & brilliant instead?

And once I've built this array, do I use it as as the 'criteria'
argument in my CountIf function? Or am I completely missing your
point?

Sorry to sound so dumb...

 

[Tom Ogilvy]

Dim v() as Long, i as Long, j as Long
Dim tot as double
Redim v(1 to 10000) as Long
' get a list of "rows" that meet the filter criteria
for i = lbound(arr(1)) to ubound(arr(1))
if arr(1)(i) > 20 then
j = j + 1
v(j) = i
end if
Next
Redim Preserve v(1 to j)

' Now process those "rows" for each "column"
for i = 2 to 10000
tot = 0
for j = 1 to ubound(v)
tot = tot + arr(i)(v(j))
next
debug.print "Column=" & i, "sum=" & _
format(tot,"#,##0.00"), "Avg=" & format(tot/ubound(v),"#,##0.00")
Next i

 

[fern]

Thanks Tom. I appreciate the extra help!
I'll give it a try this afternoon & let you know how it goes.