error in excel macro with regression application

CrazyHorse

New member
Joined
Nov 3, 2011
Messages
2
Reaction score
0
Points
0
Hi all,


I'm trying to run a macro on different workbooks that tries to detect the number of rows in column J, and do a regression with J as the Y variable and K,L,M columns as the x variables.

I wrote down these codes using the 'rec' function of excel, and then adapting it to make variable the number of rows used (depending on the rows of the specific file):

Code:
Sub provaanova()
     Application.Run "ATPVBAEN.XLAM!Regress", ActiveSheet.Range("$J$2:$J$" & Range("J" & Rows.Count).End(xlUp).Row), _
     ActiveSheet.Range("$J$2:$J$" & Range("J" & Rows.Count).End(xlUp).Row), False, False, 99, "ANOVA", False, _
     False, False, False, , False
End Sub

and

Code:
Sub Provaregress()
r = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
     Application.Run "ATPVBAEN.XLAM!Regress", ActiveSheet.Range("$J$2:$J$" & r) _
        , ActiveSheet.Range("$K$2:$M$" & r), False, False, 99, "ANOVA", False, _
        False, False, False, , False
End Sub

but I have this error:

run time error 1004, run method of '_Application' failed. :confused:

- I translated it from my italian version of excel, so it may not be exactly the same message in the US version-

I tried also to just 'rec' the macro with excel and leave it as it is (i.e. with fixed number of rows) but while it works while recording it, then also that macro gives the error.:( even if i save the macro in the personal folder, close the file without saving and the use the macro in the same file it was created on, it gives error :(

Any idea on what that error means and how to solve it?

thanks a lot :)
 
Hi there, and welcome to the forum.

I don't do any work with regression, so I'm not totally sure I'm going to give you the right outcome, but I can tell you how to get the Analysis Toolpack (ATP) functions to work in VBA:

First, you need to install the Analysis Toolpack for VBA in Excel:
  • Excel 2003 and earlier: Tools-->Add-ins-->Analysis Toolpack - VBA
  • Excel 2007: Office Button-->Options-->Add-ins-->Go-->Analysis Toolpack - VBA
  • Excel 2010: File-->Options-->Add-ins-->Go-->Analysis Toolpack - VBA
Once you've done that, you need to pop into the VBE and you also need to set a reference to the atpvbaen.xls:
  • Tools-->References-->atvbaen.xls
(Mine ends in "en" for English, so yours may be different.)

Once you've done that, you can just use the ATP as native functions in the VBE:
Code:
Sub Provaregress()
    r = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
    y = regress(ActiveSheet.Range("$J$2:$J$" & r) _
      , ActiveSheet.Range("$K$2:$M$" & r), False, False, 99, "ANOVA", False, _
                False, False, False, , False)
End Sub

Hope that helps!
 
Back
Top