1004 error when using ATPVBAEN.XLAM!Regress

[Allan_Urban]

Hi all

Im in a bit of trouble here. I need to do a OLS regression and i recorded a
macro using the Regression from the VBA Tool Pack.

But whenever i run it, I get the following error:

Run-time error '1004':
Method 'Run' of object '_Application' failed

the code looks as follows:
Application.Run "ATPVBAEN.XLAM!Regress", ActiveSheet.Range("$B$2:$B$60")
ActiveSheet.Range ("$A$2:$A$60"), False, False, ,
ActiveSheet.Range("$D$8") _
, False, False, False, False, , False

I have no idea how to fix it, so I hope someone is able to help me.
Using 2007 btw.

 

[Jacob Skaria]

Allan, check out the below link from Microsoft

http://support.microsoft.com/kb/192642

 

[Allan_Urban]

Yeah, already tried that and it doesn't fix the problem. Instead i get
another error:

Analysis Toolpak - VBA.htm could not be found. Check the spelling of the
file name, and verify that the file location is correct.

 

[Jacob Skaria]

Try copy paste the below code. Also make sure you have loaded Analysis
ToolPak - VBA

http://office.microsoft.com/en-us/excel/HP100215691033.aspx

Application.Run "ATPVBAEN.XLAM!Regress", _
ActiveSheet.Range("$B$2:$B$60"), _
ActiveSheet.Range("$A$2:$A$60"), _
False, False, , ActiveSheet.Range("$D$8"), _
False, False, False, False, , False


If this post helps click Yes

 

[Allan_Urban]

Works like a charm!

Thank you very much.

 

[Allan_Urban]

It seems like you have to run the regression manually when you open the sheet
before it works.

Is there a way to bypass this?

 

[Jacob Skaria]

Save this as a macro and Call this macro from Workbook_Open event.

VBE>VBAProject(Your_Filename) >Microsoft Excel Objects>double click 'This
Workbook' and in the code panel you can list down the workbook events.

If this post helps click Yes

 

[Allan_Urban]

Doesn't work. I still have to Data > Data analysis > Regression >...
manually before the macro works.