Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Statistics in EXCEL VBA (EXCEL 97/2000)

    How does one use the statistical functions from the "Analysis Toolpack VBA" in EXCEL? I checked the help but couldn't find any call examples.
    A broader check on the Internet revealed only:
    Application(function)
    type statements and these simply call the EXCEL function (supported by the 'normal' "Analysis Toolpack"). As there is also the "Analysis Toolpack - VBA" I'd like to know how to call that one...

    Thanks,

    Erik Jan

  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: Statistics in EXCEL VBA (EXCEL 97/2000)

    Hi,
    You need to add a reference to the atpvbaen.xls (name may change depending on your language I think) library in the VBEditor and then you can call the functions in your code.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Statistics in EXCEL VBA (EXCEL 97/2000)

    Thanks Rory,

    I understand what you are suggesting. Just to clarify: you should EITHER add a reference OR load the VBA toolkit as Add-in, right?? If not, why would one load this add-in in the first place then???

    Erik Jan

  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: Statistics in EXCEL VBA (EXCEL 97/2000)

    Actually no, you do both. Loading the add-in will add the atpvbaen.xls to your list of available references, but doesn't select it - you have to do that manually.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Statistics in EXCEL VBA (EXCEL 97/2000)

    Rory, on your second point about selecting the add-in...is there a way to ensure that the add-in is selected say on a workbook open event? Sort of like addin.xla.selected=true?


    Thanks,

  6. #6
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Statistics in EXCEL VBA (EXCEL 97/2000)

    Rob Bruce has your answer in his <A target="_blank" HREF=http://www.rb-ad.dircon.co.uk/rob/excelvba/tips/index.htm>Tips & Tricks</A> page. Just click on his Using Analysis Toolpak Functions in VBA link. --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  7. #7
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Statistics in EXCEL VBA (EXCEL 97/2000)

    Thanks Sammy...pretty cool page Rob has got there.

Posting Permissions

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