Multiple Regression

[Yash0071]

Hello,

This is my first thread. Im doing Multiple regression and need the following:

y= Cells B2:B15;
X1:X4= Cells C2:F15


1. I want a VBA code to do regression analysis and get an output exactly like Excel Data->Data analysis->Regression (Output Summary)

Thank you.

 

[Yash0071]

Hello, thanks for trying to help me. I got my result to convert Linest() matrix into columns '=TOCOL(LINEST(A2:A15,B2:E15,TRUE,TRUE))' I write a VBA snippet for this. Now I want to know does anyone know the formula to find Significance in excel. Next is RMSE=R-Square? If not what is the excel formula.

 

[Monty]

Try This!

Sub MultipleRegressionAnalysis()
    Dim ws As Worksheet
    Dim rngY As Range, rngX As Range
    Dim lr As Long
    
    ' Set worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your worksheet name
    
    ' Find last row of data
    lr = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
    
    ' Define ranges for Y and X variables
    Set rngY = ws.Range("B2:B" & lr)
    Set rngX = ws.Range("C2:F" & lr)
    
    ' Perform regression analysis
    Call RegressionAnalysis(rngY, rngX)
End Sub

Sub RegressionAnalysis(rngY As Range, rngX As Range)
    Dim RegOutput As String
    
    ' Perform regression analysis
    RegOutput = Application.WorksheetFunction.LinEst(rngY, rngX, True, True)
    
    ' Display regression output
    MsgBox RegOutput
End Sub

 

[Yash0071]

Thank you! I'm always executing my other code and I get a debug error highlighted in yellow and the line is this 'RegOutput = Application.WorksheetFunction.LinEst(rngY, rngX, True, True)'. But I'll try this and get back.

 

[Monty]

Great..Please do let us know!

 

[Yash0071]

Debug Error: Type Mismatch. AI tools try to overcome it and then Error 1004 comes up or Error 13 again Etc.

 

[Monty]

Modified as required

Sub MultipleRegressionAnalysis()
    Dim ws As Worksheet
    Dim rngY As Range, rngX As Range
    Dim lr As Long
    
    ' Set worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your worksheet name
    
    ' Find last row of data
    lr = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
    
    ' Define ranges for Y and X variables
    Set rngY = ws.Range("B2:B" & lr)
    Set rngX = ws.Range("C2:F" & lr)
    
    ' Perform regression analysis
    Call RegressionAnalysis(rngY, rngX)
End Sub

Sub RegressionAnalysis(rngY As Range, rngX As Range)
    Dim RegOutput As Variant
    
    ' Perform regression analysis
    RegOutput = Application.WorksheetFunction.LinEst(rngY, Application.Transpose(rngX), True, True)
    
    ' Display regression output
    MsgBox RegOutput(1, 1) ' This assumes the first element of the output array is the slope coefficient
End Sub

 

[Yash0071]

Thank you! My Excel is giving 'Product deactivated' message. Does pasting this code in the second line sort this problem please 'Application.EnableCancelKey = xlDisabled'?

 

[Monty]

Thank you! My Excel is giving 'Product deactivated' message. Does pasting this code in the second line sort this problem please 'Application.EnableCancelKey = xlDisabled'?

No, the line 'Application.EnableCancelKey = xlDisabled' will not solve the 'Product deactivated' message issue in Excel.
This message typically indicates a problem with the activation status of your Microsoft Office product.
You should ensure that your Office subscription or license is active and that there are no issues with your Microsoft account or activation key.

 

[Yash0071]

Thank you! But I'm still getting the debug error after trying modified code. Anyways, I have finished off my model with a different means using codes to Auto populate and =TOCOL(LINEST()).

 

[Monty]

Thank you! But I'm still getting the debug error after trying modified code. Anyways, I have finished off my model with a different means using codes to Auto populate and =TOCOL(LINEST()).

Okay... you're still encountering an error with your code.
Debugging can be a bit tricky, but let's try to identify the issue. Can you provide more details about the specific error message you're receiving?

 

[Yash0071]

Hello,
Sorry for the delay. 'RegOutput = Application.WorksheetFunction.LinEst(rngY, Application.Transpose(rngX), True, True)' the same thing is highlighted in Error and I've tried to paste it in Module & Sheet View code but none works.
Thank you!

 

[Monty]

Hmmmm...Strange..lets try with modified code with some additional error handling:

Sub MultipleRegressionAnalysis()
    Dim ws As Worksheet
    Dim rngY As Range, rngX As Range
    Dim lr As Long
    
    ' Set worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your worksheet name
    
    ' Find last row of data
    lr = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
    
    ' Define ranges for Y and X variables
    Set rngY = ws.Range("B2:B" & lr)
    Set rngX = ws.Range("C2:F" & lr)
    
    ' Perform regression analysis
    Call RegressionAnalysis(rngY, rngX)
End Sub

Sub RegressionAnalysis(rngY As Range, rngX As Range)
    Dim RegOutput As Variant
    
    ' Perform regression analysis
    On Error Resume Next ' Resume execution if there's an error
    RegOutput = Application.WorksheetFunction.LinEst(rngY, Application.Transpose(rngX), True, True)
    On Error GoTo 0 ' Disable error handling
    
    If IsArray(RegOutput) Then
        ' Display regression output
        MsgBox "Slope coefficient: " & RegOutput(1, 1) ' This assumes the first element of the output array is the slope coefficient
    Else
        MsgBox "Error occurred during regression analysis."
    End If
End Sub

 

[Yash0071]

good Evening!

I tried that modified code and some progress is there as Debug error is not showing but a normal error is showing up.

Regards

 

[Hui]

Hi
Referring to Post #14 just above

These lines
Set rngY = ws.Range("B2:B15" & lr)
Set rngX = ws.Range("C2:G15" & lr)

Should be

Set rngY = ws.Range("B2:B" & lr)
Set rngX = ws.Range("C2:G" & lr)


and
RegOutput = Application.WorksheetFunction.LinEst(rngY, Application.Transpose(rngX), True, True)
should be

RegOutput = Application.WorksheetFunction.LinEst(rngY, rngX, True, True)