I'm looking for maximum flexibility as I call it. What that means is that
I'd like it to respond in basically the same way as the Average function,
where I can type in =average(a1:a10) or =average(a1,a5,b4,g32) and it will
average all of the cells I select. The range feature works very well for the
first, but if instead I want to select a "list" of cells (not sure if it
would technically be referred to as a list), it doesn't work.

This applies to the codes that both Conrad and JE McGimpsey left. Is there
another form of argument that would allow me to enter either a range or a
smattering of random cells as inputs? I tried "As List", but that didn't do
it.

By the way, all of the hints have been helpful, as Conrad's code gave me
extra insight into calling ranges and setting up arrays (and even before your
second reply I was making my own changes to fix the array references), while
JE's code showed me how to use XL's built-in functions within my own VBA
scripts (I was unaware that they were nested under Application).

Thanks again.

"Alan Beban" wrote:

How are you calling the function?

Alan Beban

ASokolik wrote:
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