Results 1 to 5 of 5
  1. #1
    5 Star Lounger Ruff_Hi's Avatar
    Join Date
    Feb 2001
    Location
    New York, New York, USA
    Posts
    768
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Histogram in VBA (Excel XP)

    I'm sure there is a way to do this as I seem to remember doing it sometime in the past (getting old, forgetting things). I want to perform a histogram in VBA. I've recorded a macro that does one and now I want to loop thru others. I cannot get it to run because it "ATPVBAEN.XLA!" could not be found. Don't I have to reference it or something first? Help re which file to include as a reference would be appriciated.
    (Location Australia, then UK, but now USA. Heart, outlook, attitude, etc always Australian)
    Quote: "All Happiness is the release of internal pressure"

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Histogram in VBA (Excel XP)

    Hi,
    You need to load the Analysis Toolpak VBA add-in, then under Tools-References in the VB Editor, select atpvbaen.xls.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    5 Star Lounger Ruff_Hi's Avatar
    Join Date
    Feb 2001
    Location
    New York, New York, USA
    Posts
    768
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Histogram in VBA (Excel XP)

    Thx Rory,

    The atpvbaen.xls wasn't explicitly mentioned in the list (what a list, pages and pages of it) but I used the browse button and found it.

    The code I have is ...

    Application.Run "ATPVBAEN.XLA!Histogram", ActiveSheet.Range("$A$2:$A$50001") _
    , ActiveSheet.Range("$J$2"), ActiveSheet.Range("$H$2:$H$13"), False, False _
    , False, False

    ... and now I get an error message The macro " could not be found.
    (Location Australia, then UK, but now USA. Heart, outlook, attitude, etc always Australian)
    Quote: "All Happiness is the release of internal pressure"

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Histogram in VBA (Excel XP)

    Tim.
    Did you load the add-in via the main Excel window first? If so, the atpvbaen.xls entry should appear just below your current references. You should then be able to simply use the format:
    histogram ActiveSheet.Range("$A$2:$A$50001"), ActiveSheet.Range("$J$2"), ActiveSheet.Range("$H$2:$H$13"), False, False , False, False
    Does that not work?
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    5 Star Lounger Ruff_Hi's Avatar
    Join Date
    Feb 2001
    Location
    New York, New York, USA
    Posts
    768
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Histogram in VBA (Excel XP)

    Arrrg - I'm a great big boso! I just loaded it in the vba add-in part, not the excel add in part. Turn to excel and there it is, as large as life (ANALYSIS TOOLPACK - VBA).

    I couldn't seem to get it to ignore the "replace output" statement (disable warnings didn't work) so I had to put in code to clear that spot prior to running the histogram.

    Thx Rory.
    (Location Australia, then UK, but now USA. Heart, outlook, attitude, etc always Australian)
    Quote: "All Happiness is the release of internal pressure"

Posting Permissions

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