Using an Array in a worksheet function

hyoung3

New Member
Joined
May 9, 2011
Messages
21
I am having some difficulty using an array in the worksheet function "LinEst" which is embedded in a user-defined function. I am trying to create a polynomial regression function that takes the desired degree as an input along with the known x and y values. I have confirmed that my arrays are accurate by placing them into a spreadsheet, but when I use the arrays in the Linest formula I get the #VALUE error.

I am unfamiliar with how worksheet functions accept arrays in VB.

Code:
Function Polynomial_Regression(Known_Ys As Range, Known_Xs As Range, Degree As Single)
    Dim strDegreeArray() As String
    Dim strXraisedArray() As String 'Multidimensional Array to hold X range and X raised to degree(y) Power
    Dim strTransposeXraised As Variant 'Variable to hold Transposed Xraised Array
    Dim strCoefficients() As String 'Array to hold coefficient to use in polynomial function
    Dim a, b, c As Integer  'Counter Variables
    Dim d, e, f As Single   'More Counter Variable
    Dim i, j, k As Object   'Counter variables for for loops


    ReDim strDegreeArray(0 To Degree - 1) As String
            a = 0
            For i = 0 To Degree - 1
                strDegreeArray(i) = a + 1
                a = a + 1
            Next i


    
    'create the multidimensional X raised to y power array
    ReDim strXraisedArray(0 To Degree - 1, 0 To Known_Xs.Count - 1) As String
        'First set Known_Xs into Array
        a = 0
            For Each i In Known_Xs
                strXraisedArray(0, a) = i
                a = a + 1
            Next i
        'Second populate array with calculation outputs
        a = 0
        b = 1
            For i = 0 To Known_Xs.Count - 1
                For j = 0 To Degree - 2
                    strXraisedArray(b, a) = strXraisedArray(0, a) ^ (b + 1)
                    b = b + 1
                Next j
                a = a + 1
                b = 1
            Next i
        'Transpose Array because by default arrays are in the 1row x multi column arrangement
        strTransposeXraised = Application.WorksheetFunction.Transpose(strXraisedArray)
        
    'ReDim Preserve strTransposeXraised(0 To UBound(strTransposeXraised), 0 To UBound(strTransposeXraised)) As Variant
    'ReDim strCoefficients(0 To Degree) As String
        
    
    Polynomial_Regression = Application.WorksheetFunction.LinEst(Known_Ys, strTransposeXraised)


End Function

Thanks in advance for the suggestions and help.
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
hyoung3,

What type of number are you expecting from?
Application.WorksheetFunction.LinEst(Known_Ys, strTransposeXraised)

Just type in your response what the number should look like?
 
Upvote 0
It should either be a single number, the first coefficient in an nth degree polynomial (for example, putting the formula in cell A1 would return one number), or a range of values, all coefficients in a nth degree polynomial (for example, selecting cells A1:E1 would return all five coefficients in a 4th degree polynomial). I hope I have answered your question sufficiently.
 
Upvote 0
Additionally, I realize that some of the code in there is unused. I have not taken it out because I'm not yet sure if I will need to use it later to either troubleshoot or to use in in the final output.
 
Upvote 0
hyoung3,

In VBA try adding to your Function:

Function Polynomial_Regression(Known_Ys As Range, Known_Xs As Range, Degree As Single) As Long

Or:

Function Polynomial_Regression(Known_Ys As Range, Known_Xs As Range, Degree As Single) As Double
 
Upvote 0
I am having some difficulty using an array in the worksheet function "LinEst" which is embedded in a user-defined function. I am trying to create a polynomial regression function that takes the desired degree as an input along with the known x and y values. I have confirmed that my arrays are accurate by placing them into a spreadsheet, but when I use the arrays in the Linest formula I get the #VALUE error.

I am unfamiliar with how worksheet functions accept arrays in VB.

Code:
Function Polynomial_Regression(Known_Ys As Range, Known_Xs As Range, Degree As Single)
    Dim strDegreeArray() [COLOR=#0000FF][B]As String[/B][/COLOR]
    Dim strXraisedArray() [B][COLOR=#0000FF]As String [/COLOR][/B]'Multidimensional Array to hold X range and X raised to degree(y) Power
    Dim strTransposeXraised As Variant 'Variable to hold Transposed Xraised Array
    Dim strCoefficients() [COLOR=#0000FF][B]As String [/B][/COLOR]'Array to hold coefficient to use in polynomial function
[COLOR=#FF0000][B]    Dim a, b, c As Integer  'Counter Variables
    Dim d, e, f As Single   'More Counter Variable
    Dim i, j, k As Object   'Counter variables for for loops[/B][/COLOR]


    ReDim strDegreeArray(0 To Degree - 1) As String
            a = 0
            For i = 0 To Degree - 1
                strDegreeArray(i) = a + 1
                a = a + 1
            Next i


    
    'create the multidimensional X raised to y power array
    ReDim strXraisedArray(0 To Degree - 1, 0 To Known_Xs.Count - 1) [COLOR=#0000FF][B]As String[/B]
[/COLOR]         'First set Known_Xs into Array
        a = 0
            For Each i In Known_Xs
                strXraisedArray(0, a) = i
                a = a + 1
            Next i
        'Second populate array with calculation outputs
        a = 0
        b = 1
            For i = 0 To Known_Xs.Count - 1
                For j = 0 To Degree - 2
                    strXraisedArray(b, a) = strXraisedArray(0, a) ^ (b + 1)
                    b = b + 1
                Next j
                a = a + 1
                b = 1
            Next i
        'Transpose Array because by default arrays are in the 1row x multi column arrangement
        strTransposeXraised = Application.WorksheetFunction.Transpose(strXraisedArray)
        
    'ReDim Preserve strTransposeXraised(0 To UBound(strTransposeXraised), 0 To UBound(strTransposeXraised)) As Variant
    'ReDim strCoefficients(0 To Degree) As String
        
    
    Polynomial_Regression = Application.WorksheetFunction.LinEst(Known_Ys, strTransposeXraised)


End Function

Thanks in advance for the suggestions and help.

Just a couple of comments about your code... the first has nothing to do with your problem (it is a stylistic problem you have carried over from other programming language you are familiar with) and the second may have something to do with your problem (I don't know for sure because I am not familiar with the LinEst function).

First, look at the red highlighted lines of code above... only variable 'c' gets declared as an Integer, 'f' as a Single and 'k' as an Object... 'a', 'b', 'd', 'e', 'i' and 'k' all get declared as Variants. In VB, all variable must be individually declared as to their data type, otherwise they default to Variants. Your statements should be...

Dim a As Integer, b As Integer, c As Integer 'Counter Variables
Dim d As Single, e As Single, f As Single 'More Counter Variable
Dim i As Object, j As Object, k As Object 'Counter variables for for loops

By the way, on modern 32-bit and above computers, you save absolutely nothing declaring a variable as Integer... just declare them as Long.

Okay, this second item might possibly be a problem to the LinEst function because it vectors through the WorksheetFunction object... why have you declared the arrays whose data type I highlighted in blue as String? From the sounds of the names you gave them, they sound like they will end up containing numeric values, not text values. If that guess by me is correct, then try changing their data types to, maybe, Single or Double and see if that helps any.
 
Upvote 0
Thank you for the stylistic notes as well as notes on declaring variables.

I have since gotten the function to return my expectation. It had everything to do with how the array(s) was set up.
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,194
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top