Convert range to array for self-defined excel vba function

Question

I have a pre-defined vba function that I've been using for other sub-routines. For example,

Function MyFunction(MyArray() As Double) As Double

    MyFunction = WorksheetFunction.Sum(MyArray) / WorksheetFunction.Var(MyArray)

End Function

I would like to use this function in my worksheet calculations. Let's say these are the values in cells A1:A5 of my worksheet:

1

2

3

4

5

However, when I try to use my predefined function , I get the #Value! error back. =MyFunction(A1:A5) leads to #Value! when it should have given me 6 (=15/2.5)

My guess with what's wrong is that I'm inputting a range when my function requires an array. What's the easiest way to get around this problem? Thanks!

Answer 1

Change...

Function MyFunction(MyArray() As Double) As Double

To...

Function MyFunction(ByRef MyArray as Variant) As Double

'---

Jim Cone

Portland, Oregon USA

free & commercial excel programs http://jmp.sh/K95N3ee

https://goo.gl/IUQUN2 (Dropbox)

Answer 2

Thanks for your response James. This worked but I did have to change some of the code in my user-built function that was meant to handle arrays instead of ranges. For example, I was using UBound(MyArray) and had to replace it with WorksheetFunction.Count(MyArray) to avoid errors. Not a big deal but a heads up in case others encounter the same issue

> , I was using UBound(MyArray) and had to replace it with WorksheetFunction.Count(MyArray)

Hi.  This may not apply, but just a heads up:

If you use a custom function like:

=MyFx(A1:A5)

And your function is:

Function MyFx(MyArray)

    MyFx = WorksheetFunction.Count(MyArray)

End Function

Just be aware that 'Count will not count Blanks, and Text cells.

You may get an invalid value for UBound in your actual function.

Answer 3

Thanks for your response James. This worked but I did have to change some of the code in my user-built function that was meant to handle arrays instead of ranges. For example, I was using UBound(MyArray) and had to replace it with WorksheetFunction.Count(MyArray) to avoid errors. Not a big deal but a heads up in case others encounter the same issue

Answer 4

Thanks Dana but I still can't get my pre-defined function to work. I've edited my question to hopefully clarify what I'm asking. Am I calling my function incorrectly? If I transpose the input first, that doesn't help. Your demo is a subroutine and not a function so I'm not sure how it applies to my question. Sorry if this is really basic. Thanks.

Answer 5

I have a pre-defined vba function that I've been using for other sub-routines:

Function MyFunction(MyArray() as Double)

  'my code

End Function

I would like to use this function for my worksheet calculations. However, when I select a range in the worksheet as input, I get the #Value! error:

MyFunction(A1:A5) leads to #Value!

My guess with what's wrong is that I'm inputting a range when my function requires an array. What's the easiest way to get around this problem? Thanks!

Hi.  If I understand your question correctly, see if stepping thru this codes helps.

Step thru the first two assignments (via F8) and then look at the locals windows.

This should show you the differences.

One way to match the range with an array might be:

Sub Demo()

    Dim v, A

    v = Range("A1:A5")

    A = Array(1, 2, 3, 4, 5)

    ' Match V to an array

    v = WorksheetFunction.Transpose(v)

End Sub