Passing a UDF as return type

**Junoon** · 05-05-2012, 05:26 PM

Hi,

I have a sheet which has data like this:

Code:

BUCKETS	NAD1	REL1	ADD1	TEL1	ALT1	BEL1	FRA1	NAD2	REL2	ADD2	TEL2	ALT2	BEL2	FRA2
1	4	3	2	4	1	2	2	4	2	2	2	1	2	2
1	3	3	2	3	2	3	3	1	2	1	2	2	2	2
1	2	4	2	4	3	2	3	4	5	2	4	3	3	4
1	2	1	1	1	2	1	1	2	2	2	2	2	2	2
2	3	3	2	3	3	2	3	3	4	2	4	3	2	3
2	3	3	2	4	1	2	1	2	1	1	1	2	1	1
2	4	3	2	3	2	2	1	2	5	2	5	1	3	2

I am dumping this sheet into a Multi-dimensional Array (1-D array having 2-D arrays). i want to calculate Means of each column per BUCKET Number 1,2 etc without looping i.e.

PHP Code:


        Mean= Sum(.Index(.transpose(Arr),0,i))/Count(.Index(.transpose(Arr),0,i))

You will see that the headers are repeating themselves viz., NAD1, NAD2 etc. these are measures for Actions 1, 2 etc. i have another 1-D array which has the Action names in sequential order.

Code:

Arr() contains:
Action1 name
Action2 name
Action3 name
Action4 name
Action5 name
etc..

What i want to do is match the Actions numbers (names) to the 1,2 etc of the Measures to display the means in a Result sheet in this way:

Code:

             NAD  REL  FRA  BEL ALT ADD
Action1   1.2   1.1   2.5  4.3  2.1  2.5
Action2   6.3   5.5   3.2  1.2  1.1  3.2
Action3   7.7   2.2   8.2  1.1  2.1  3.1
Action4   4.3   5.6   3.6  2.7  2.9  1.3
etc..

do you think i would need to define a UDT to get this desired format like this:

Code:

Type Means
      ActionName as string
      NAD as double
      CAD as double
      ADD as double
      ALT as double
      REL as double
      BEL as double
End Type

Dim ArrMeasure() as Means

How do i put the Action Names & calculate the Means for these Actions for the 6 different Measures and put them into such an Array? SEE ATTACHED SAMPLE FILE which has code written and there is a UDF too to grab Action names, but dont know how to Pass this UDF to a variable.