How to fix VBA regression code that works when executed without a button, but not with a button

Question

I'd like to tie my regression code to a button, but when I do, my regression code no longer works.

I have tried to be very explicit on where the ranges are, enabling more references (Solver, atpvbaen.xls, Ref Edit Control), and rewriting the syntax of my code.

Dim nextReg As Integer
Dim nextRegEnd As Integer
Dim regRowNum As Integer

Dim days As Integer
Dim cdd As Integer

nextReg = 1
nextRegEnd = 13
regRowNum = 20

days = 18
cdd = 20

Sheets("Reg_Work").Select

Do While Sheets("Reg_Work").Cells(regRowNum, 1).Value <> ""

    Sheets("Reg_Results").Activate
    'REGRESSION
    Application.Run "ATPVBAEN.XLAM!Regress", Sheets("Reg_Results").Range(Cells(nextReg, 1), Cells(nextRegEnd, 1)), Sheets("Reg_Work").Range("$I$4:$K$16"), False, True, , Sheets("Reg_Results").Cells(nextReg, 3), False, False, False, False, , False
    nextReg = nextReg + 21
    nextRegEnd = nextRegEnd + 21

Loop

I receive the error

"Run-time error '1004': Application-defined or object-defined error"

when I expect it to perform the regression.

Avoid `.Select` and `.Activate` and specify in which sheet `Cells(nextReg, 1), Cells(nextRegEnd, 1)` are. This should fix your errors. You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). • Also declare your variables as `Long`, Excel has more rows than `Integer` can handle. — Pᴇʜ, Sep 12 '19 at 14:36
Thank you for your responses. PEH, if you look at the regression, code, you'll see I did specify in which sheet each cell reference is located. Maybe I am misunderstanding what you offered. Thank you for the comment about changing the declarations to Long, that helps :). Mathieu Guindon, thank you for your response as well. Not sure how I'd reference the range of values I need with the code change you offered however, again I may be misunderstanding what you wrote/just don't know. I have tried it with and without application.run, and there was no change in the error message.Thanks again! — Lauren, Sep 13 '19 at 13:00
@lauren no in this code `Sheets("Reg_Results").Range(Cells(nextReg, 1), Cells(nextRegEnd, 1))` only your `Range` object has a sheet specified. But your two `Cells` objects have **no** sheet specified. It is acutally the same as if you write `Sheets("Reg_Results").Range(ActiveSheet.Cells(nextReg, 1), ActiveSheet.Cells(nextRegEnd, 1))`. Now if the active sheet is not `Reg_Results` it fails. See my answer below. — Pᴇʜ, Sep 13 '19 at 14:54

score 0 · Accepted Answer · answered Sep 13 '19 at 14:52

Make sure you specify a sheet for every Range and Cells object.

Note that in

Sheets("Reg_Results").Range(Cells(nextReg, 1), Cells(nextRegEnd, 1))

Only the Range object has a sheet specified but not the 2 Cells objects. It is the same as:

Sheets("Reg_Results").Range(ActiveSheet.Cells(nextReg, 1), ActiveSheet.Cells(nextRegEnd, 1))

But if ActiveSheet is not Sheets("Reg_Results") it fails. It should be

Sheets("Reg_Results").Range(Sheets("Reg_Results").Cells(nextReg, 1), Sheets("Reg_Results").Cells(nextRegEnd, 1))

Something like below should work:

Option Explicit

Public Sub YourProcedure()
    Dim nextReg As Long
    nextReg = 1

    Dim nextRegEnd As Long
    nextRegEnd = 13

    Dim regRowNum As Long
    regRowNum = 20

    Dim days As Long
    days = 18

    Dim cdd As Long
    cdd = 20

    Dim wsWork As Worksheet
    Set wsWork = ThisWorkbook.Worksheets("Reg_Work")

    Dim wsResults As Worksheet
    Set wsResults = ThisWorkbook.Worksheets("Reg_Results")

    Do While wsWork.Cells(regRowNum, 1).Value <> vbNullString
        'REGRESSION
        Regress wsResults.Range(wsResults.Cells(nextReg, 1), wsResults.Cells(nextRegEnd, 1)), _
            wsWork.Range("$I$4:$K$16"), False, True, , _
            wsResults.Cells(nextReg, 3), False, False, False, False, , False
        nextReg = nextReg + 21
        nextRegEnd = nextRegEnd + 21
    Loop
End Sub

PEH thank you! I understand now and that solved my problem. – Lauren Sep 13 '19 at 16:48 — Lauren, Sep 13 '19 at 16:48

How to fix VBA regression code that works when executed without a button, but not with a button

1 Answers1