Returning an Array/Matrix in Excel VBA from a user defined function

Question

I'm new to Excel VBA and its been 5 years since I've done any VBA at all. I've written a UDF to do a basic regression, but I can't get it to output an array of regressed values. I select the range I want to output so and hit crtl+shift+enter, but it doesn't work. I've tried a few different things, but nothing does the trick. Here is my latest attempt:

Function REGRESSMEDIAN(x As Range, y As Range) As Double

Dim slope As Double, intercept As Double, count As Integer

count = x.count

Dim lny() As Double, regression() As Double
ReDim lny(1 To count), regression(1 To count)

Dim i As Integer

For i = 1 To count
    lny(i) = Application.WorksheetFunction.Ln(y(i))
Next i


slope = Application.WorksheetFunction.slope(lny, x)
intercept = Application.WorksheetFunction.intercept(lny, x)

Dim j As Integer

For j = 1 To count
    regression(j) = Exp(slope * x(j) + intercept)
Next j

    REGRESSMEDIAN = regression

End Function

This version doesn't run, but I had a version that did. My issue is that it won't output the array of regressed values. Even when I had it outputting an array, every cell was just the first value in the array. — C.Morton
– C.Morton, Commented Apr 27, 2017 at 22:51
Try changing the return type of the Function to Variant. So Function REGRESSMEDIAN(x As Range, y As Range) As Variant — Rich Holton
– Rich Holton, Commented Apr 27, 2017 at 22:59

Tim Williams · Accepted Answer · 2017-04-27 23:06:34Z

2

This test function:

Function tester()
    tester = Array("a", "b", "c")
End Function

will work fine as a UDF as long as your 3 output cells are in a row, not in a column. If they're in a column then you'll just see "a" in all 3 cells.

If you're trying to put the output in a column then this will work:

Function tester()
    tester = Application.Transpose(Array("a", "b", "c"))
End Function

answered Apr 27, 2017 at 23:06

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

C.Morton Over a year ago

Thank you, I changed the function type to variant and that gave me the first value in all columns. The transpose fixed that, it seems like the obvious answer in hindsight, I just wasn't aware that Excel VBA stores arrays as a row by default.

Tim Williams Over a year ago

I would not say it's "obvious" - just have to remember that Excel has a preference for putting things in rows...

L.Dutch · Accepted Answer · 2022-12-19 17:00:13Z

0

Try

Function REGRESSMEDIAN(x As Range, y As Range) As Double()

only putting the parentheses in the function declaration.

It will work.

edited Dec 19, 2022 at 17:00

L.Dutch

9663 gold badges19 silver badges40 bronze badges

answered Dec 13, 2022 at 5:14

Isidro Gil

313 bronze badges

1 Comment

Community Over a year ago

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.

Collectives™ on Stack Overflow

Returning an Array/Matrix in Excel VBA from a user defined function

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related