I have a function that is set up to accept ranges as
arguments, so that I can invoke it from the spreadsheet.
In the function code I loop through the cells of these
ranges.

I would like this function to also accept arrays as
arguments from VBA code. Is there a way to get the
function to work in both cases, whether the arguments are
ranges or arrays?

Here's the function:

Function WtdHarmean2(Numerator, Denominator, Weights)

Application.Volatile

'Create an array of weighted reciprocals
Dim N As Integer
Dim RCount As Integer
Dim SumRecips As Double
Dim SumWeights As Double

SumRecips = 0
SumWeights = 0
N = Weights.Count

For RCount = 1 To N
'Clean up input data to prevent errors
If IsNumeric(Denominator.Cells(RCount).Value) And _
IsNumeric(Numerator.Cells(RCount).Value) And _
Numerator.Cells(RCount).Value < 0 Then

'Data is okay to use: sum the weighted
reciprocals
SumRecips = SumRecips + (Denominator.Cells
(RCount).Value / Numerator.Cells(RCount).Value) *
Weights.Cells(RCount).Value
SumWeights = SumWeights + Weights.Cells
(RCount).Value 'sum weights

Else: 'Don't count the data, assign it a
value and weight of zero
SumRecips = SumRecips + 0
SumWeights = SumWeights + 0
End If
Next RCount

'WtdHarmean2 is the reciprocal of the mean of the
array of reciprocals
WtdHarmean2 = 1 / (SumRecips / SumWeights)

End Function

write your function to work with arrays

then as the first thing

Dim vArrNum as Variant, vArrDenom as Variant
Dim vArrWeights as Variant
vArrNum = Numerator
vArrDenom = Denominator
vArrWeights = Weights

--
Regards,
Tom Ogilvy


"Stratuser" wrote in message
...
I have a function that is set up to accept ranges as
arguments, so that I can invoke it from the spreadsheet.
In the function code I loop through the cells of these
ranges.

I would like this function to also accept arrays as
arguments from VBA code. Is there a way to get the
function to work in both cases, whether the arguments are
ranges or arrays?

Here's the function:

Function WtdHarmean2(Numerator, Denominator, Weights)

Application.Volatile

'Create an array of weighted reciprocals
Dim N As Integer
Dim RCount As Integer
Dim SumRecips As Double
Dim SumWeights As Double

SumRecips = 0
SumWeights = 0
N = Weights.Count

For RCount = 1 To N
'Clean up input data to prevent errors
If IsNumeric(Denominator.Cells(RCount).Value) And _
IsNumeric(Numerator.Cells(RCount).Value) And _
Numerator.Cells(RCount).Value < 0 Then

'Data is okay to use: sum the weighted
reciprocals
SumRecips = SumRecips + (Denominator.Cells
(RCount).Value / Numerator.Cells(RCount).Value) *
Weights.Cells(RCount).Value
SumWeights = SumWeights + Weights.Cells
(RCount).Value 'sum weights

Else: 'Don't count the data, assign it a
value and weight of zero
SumRecips = SumRecips + 0
SumWeights = SumWeights + 0
End If
Next RCount

'WtdHarmean2 is the reciprocal of the mean of the
array of reciprocals
WtdHarmean2 = 1 / (SumRecips / SumWeights)

End Function

I get it, thanks a million.

-----Original Message-----
write your function to work with arrays

then as the first thing

Dim vArrNum as Variant, vArrDenom as Variant
Dim vArrWeights as Variant
vArrNum = Numerator
vArrDenom = Denominator
vArrWeights = Weights

--
Regards,
Tom Ogilvy


"Stratuser" wrote
in message
...
I have a function that is set up to accept ranges as
arguments, so that I can invoke it from the spreadsheet.
In the function code I loop through the cells of these
ranges.

I would like this function to also accept arrays as
arguments from VBA code. Is there a way to get the
function to work in both cases, whether the arguments
are
ranges or arrays?

Here's the function:

Function WtdHarmean2(Numerator, Denominator, Weights)

Application.Volatile

'Create an array of weighted reciprocals
Dim N As Integer
Dim RCount As Integer
Dim SumRecips As Double
Dim SumWeights As Double

SumRecips = 0
SumWeights = 0
N = Weights.Count

For RCount = 1 To N
'Clean up input data to prevent errors
If IsNumeric(Denominator.Cells(RCount).Value)
And _
IsNumeric(Numerator.Cells(RCount).Value)
And _
Numerator.Cells(RCount).Value < 0 Then

'Data is okay to use: sum the weighted
reciprocals
SumRecips = SumRecips + (Denominator.Cells
(RCount).Value / Numerator.Cells(RCount).Value) *
Weights.Cells(RCount).Value
SumWeights = SumWeights + Weights.Cells
(RCount).Value 'sum weights

Else: 'Don't count the data, assign it
a
value and weight of zero
SumRecips = SumRecips + 0
SumWeights = SumWeights + 0
End If
Next RCount

'WtdHarmean2 is the reciprocal of the mean of the
array of reciprocals
WtdHarmean2 = 1 / (SumRecips / SumWeights)

End Function



.

It would be expecting two dimensional arrays - so if you want to pass 1D
arrays, you would need to add some checking or convert ranges to 1 D arrarys
using application.Transpose.

--
Regards,
Tom Ogilvy


"Stratuser" wrote in message
...
I get it, thanks a million.

-----Original Message-----
write your function to work with arrays

then as the first thing

Dim vArrNum as Variant, vArrDenom as Variant
Dim vArrWeights as Variant
vArrNum = Numerator
vArrDenom = Denominator
vArrWeights = Weights

--
Regards,
Tom Ogilvy


"Stratuser" wrote
in message
...
I have a function that is set up to accept ranges as
arguments, so that I can invoke it from the spreadsheet.
In the function code I loop through the cells of these
ranges.

I would like this function to also accept arrays as
arguments from VBA code. Is there a way to get the
function to work in both cases, whether the arguments
are
ranges or arrays?

Here's the function:

Function WtdHarmean2(Numerator, Denominator, Weights)

Application.Volatile

'Create an array of weighted reciprocals
Dim N As Integer
Dim RCount As Integer
Dim SumRecips As Double
Dim SumWeights As Double

SumRecips = 0
SumWeights = 0
N = Weights.Count

For RCount = 1 To N
'Clean up input data to prevent errors
If IsNumeric(Denominator.Cells(RCount).Value)
And _
IsNumeric(Numerator.Cells(RCount).Value)
And _
Numerator.Cells(RCount).Value < 0 Then

'Data is okay to use: sum the weighted
reciprocals
SumRecips = SumRecips + (Denominator.Cells
(RCount).Value / Numerator.Cells(RCount).Value) *
Weights.Cells(RCount).Value
SumWeights = SumWeights + Weights.Cells
(RCount).Value 'sum weights

Else: 'Don't count the data, assign it
a
value and weight of zero
SumRecips = SumRecips + 0
SumWeights = SumWeights + 0
End If
Next RCount

'WtdHarmean2 is the reciprocal of the mean of the
array of reciprocals
WtdHarmean2 = 1 / (SumRecips / SumWeights)

End Function



.