Results 1 to 3 of 3

Thread: error in excel macro with regression application

  1. #1

    error in excel macro with regression application



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.

    - 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

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,250
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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!
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    ok it works!

    thank you very much

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •