VBA for Geomean of Array within Array

[Drew4]

Hello,
Would appreciate any help on a small issue.

I have an array of arrays [ArrOfArr(1 to 7, 1 to 2)] where each element in the array is another array containing non-negative double type numbers [ie arr1(1 to x), arr2(1 to y)]. I am trying to find the geomean of each array within the array of arrays.
- I have a geoval variable of type double that is intended to capture the geomean of each individual array within the ArrOfArr group.
- My understanding from the MSDN website is that the GEOMEAN function should work with an array (verbatim from MSDN: "You can also use a single array or a reference to an array instead of arguments separated by commas.")

Currently my macro sticks on the following line:

geoval = Application.worksheetfunction.geomean(ArrOfArr(1,1))

Can someone please explain what I am doing wrong? Thanks in advance /d4

 

[AlphaFrog]

It depends on how your Array-of-Arrays is dimensioned. I suspect you have a one dimensional array of one dimentional arrays.

This syntax references one element in a two dimensional array.
ArrOfArr(1,1)

Try this to reference one element in a one dimensional array. Which, as you say, is another array.
ArrOfArr(1)

Also, your array may or may not be zero-based e.g. the first element may be ArrOfArr(0)

 

[Drew4]

AlphaFrog, Thanks for replying. My ArrOfArr is actually a 2 dimensional array [ArrOfArr(1 to 7,1 to 2)]. I know the array is populated properly because when I access the values, ie, "ArrOfArr(1,1)(3)" the correct values are extracted. Also when I use the worksheet function with the raw values separated by commas the geomean function works. For example:

ArrOfArr(1,1) contains arr1 with the values (0.233,0.448, 0.58897).

"application.worksheetfunction.geomean(0.233,0.448, 0.58897)" works
"application.worksheetfunction.geomean(ArrOfArr(1,1))" does not work


Thanks
Regards/d4

 

[AlphaFrog]

This test seems to work.

[COLOR=darkblue]Sub[/COLOR] Macro1()
    
    [COLOR=darkblue]Dim[/COLOR] ArrOfArr(1 [COLOR=darkblue]To[/COLOR] 7, 1 To 2)
    
    ArrOfArr(1, 1) = Array(0.233, 0.448, 0.58897)
    
    x = Application.WorksheetFunction.GeoMean(ArrOfArr(1, 1))
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

So I suspect the problem has to do with how the arrays are defined or populated.

 

[Drew4]

AlphaFrog, thank you - went back through my calculations and source data - had "1.09E-4" in one of my cells that populated as a "1.09E-4" string instead of 0.0001086. Thank you for pointing me back to my data - got used to seeing a column full of 'E' notation and didn't occur to me that one of those was a string. Think some error checking will be useful in the future. Haven't been able to crack this since yesterday morning. Appreciate your generosity with your time. Regards, d4

 

[AlphaFrog]

I too considered the string theory after I posted. Glad you figured it out. Thanks for the feedback.

 

[pgc01]

I too considered the string theory ...

Wow!
Didn't realise we need to know advanced theoretical particle physics to solve a vba problem.

 

[Drew4]

You just couldn't resist....