Using Regression from Analysis Toolpak in VBA

[mcb345]

I am trying to use the regression tool from the Analysis Toolpak to run a bunch of regressions. I have everything set up and the code seems to be fine but when I try to run it I get the following error:

Run-time error '1004':

Cannot run the macro". The macro may not be available in this workbook or all macros may be disabled.

Note that none of the above is a typo. If I hit debug it highlights the following in my code:

     Application.Run "ATPVBAEN.XLAM!Regress", Range("CF2:CF10"), Range("CE2:CE10"), False, False, , _
ActiveSheet.Range("$CG$1"), False, False, False, False, , False

I have checked to ensure that I have added atpvbaen.xls in my available references.

I have checked to ensure that all macros are enabled.

 

[kalak]

I copied your code into a module, filled range CE2:CF10 with some random numerical data, ran that bit of code, and it worked quite OK.

Have you tried running the regression manually, i.e. without the macro attempt, to ensure that it really is the macro at fault?

If it does work manually, then have you tried recording the steps using the macro recorder?

Have you tried what I did in my opening paragraph above, where I was unable to get your error. That could suggest that the problem is really elsewhere in a part of your code that you haven't posted.

If you continue to have trouble with the Analysis toolpak regression, there's other regressions around such as Excel's LINEST, or available elsewhere on the internet, or I've probably got one on my computer somewhere.

As one further point, I see you specify your output range as being on the ActiveWorksheet but not your input ranges. It's generally good practice, at least for this kind of work, to ensure that you do specify the locations of your ranges. maybe something like:

Sub redr()

With ActiveSheet
    
    Application.Run "ATPVBAEN.XLAM!Regress", .Range("CF2:CF10"), .Range("CE2:CE10"), False, False, , _
.Range("$CG$1"), False, False, False, False, , False

End With

End Sub

Note the dot, period, fullstop, Chr(46) or whatever you call it, in front of each range.

 

[par60056]

It is funny. I wrote a macro just like your's and it worked yesterday. After closing Excel and then using a different file today it is giving me the same error you got.

I can run the regression manually and it works fine.

I am running these for somebody else because Excel Mac doesn't support the Analysis Toolpack and I am running Excel 2010 Windows. So I have no idea what the regressions is supposed to do. When he supplies them to me I have the 2 ranges and the output location in cells. It is tedious to have to enter the ranges manually so I was really hoping to write a macro to do this.

Did you find a solutions?

 

[Tykki]

I also had problem that sometimes it worked, and sometimes not. For now, solution seems to call "[VBA Functions and Subs].auto_open" in beginning. It seems that atpvbaen lost sometimes some values from its memory, and those need to load back.