Hi ASokolik --

You're right, it has to do with what you're passing as an argument -- at
least, I assume that when you call the Mean function that you're using
something such as this:

=Mean(A1:A10)

But A1:A10 is a range, not a memory array. Try something like:

Option Base 1
Option Explicit

Function Mean(rng As Range) As Single

Dim Sum As Single
Dim i As Integer
Dim arr() As Single

Sum = 0
ReDim arr(rng.Cells.Count)
For i = 1 To UBound(arr)
arr(i) = ActiveSheet.Cells(i, 1)
Next i
For i = 1 To UBound(arr)
Sum = Sum + arr(i)
Next i

Mean = Sum / UBound(arr)
End Function

--
C^2
Conrad Carlberg

Excel Sales Forecasting for Dummies, Wiley, 2005


"ASokolik" wrote in message
...
I am trying to create a function in VBA (possibly an add-in in the future)
to
calculate Relative Standard Deviation (RSD). I would like to be able to
use
an equation such as: =RSD(values), where the values are cell references (a
variable quantity) chosen by the user.

To do this I've copied scripts for "Mean" and "StdDev", then wrote a small
function for RSD that calls those two. When I try to implement RSD in the
worksheet (or even just Mean), the cell returns an error: #VALUE! I think
the problem has something to do with the Arr() variable used as the
argument,
as I've tried some simpler functions with array arguments and can't get
them
to work, but using standard non-array arguments works fine. I can write a
subroutine that calls the functions and displays the correct result, but I
would like to be able to use RSD as a typical function in my worksheet.

If I could get Mean to work properly, I should be able to apply that
knowledge to StdDev and RSD. The text for Mean is listed below. I've
tried
quite a few modifications to try to make it work, so if someone could get
it
to work and reply with the correct function text (or with another function
that performs similarly), I'd appreciate it. Thanks.

Function Mean(Arr() As Single)
Dim Sum As Single
Dim i As Integer

Sum = 0
For i = 1 To UBound(Arr)
Sum = Sum + Arr(i)
Next i

Mean = Sum / UBound(Arr)
End Function