Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Nov 2001
    Location
    The Woodlands, Texas, USA
    Posts
    16
    Thanks
    1
    Thanked 5 Times in 1 Post

    Analysis Toolpak - VBA (Excel 2000 SR1)

    When I Add-in the "Analysis Toolpak - VBA" it lists duplicates of each function name in my Paste Function dialog, whether or not I also have the non-VBA Add-in selected as well. One listing will be all capitals, the other will be heading case-d.
    1) Do I need both Add-ins for any reason?
    2) If the almost-duplicated function names are supposed to be there, which should I select for Worksheet calculations?
    Thanks very much.

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Analysis Toolpak - VBA (Excel 2000 SR1)

    The UPPERCASE functions are the standard worksheet functions.
    The MixedCase functions are the VBA wrappers so that you can call
    them from your own code.

    If you have both "Analysis Tool Pak" and "Analysis Tool Pak VBA"
    add-ins loaded, you'll see both in the function list.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    New Lounger
    Join Date
    Nov 2001
    Location
    The Woodlands, Texas, USA
    Posts
    16
    Thanks
    1
    Thanked 5 Times in 1 Post

    Re: Analysis Toolpak - VBA (Excel 2000 SR1)

    Thanks very much.

  4. #4
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Analysis Toolpak - VBA (Excel 2000 SR1)

    Jan Karel,

    I'm a little confused - especially after looking at lists in both Excel 2000 SR1 and VBA.

    I now have both Analysis Toolpak and Analysis Toolpak-VBA checked.

    A. For accessing Excel functions from within Excel, I would be using the UPPER case functions. For example:
    =round(4.3,0)
    No problem. Excel makes "round" into all upper case.

    B. For accessing VBA functions from within VBA, I would just include the name of the function in my code statement. For example:
    i=round(4.3,0)
    No problem. VBA even capitalizes the "r".

    C. For accessing Excel functions from within VBA, I would just use a statement like
    i = Application.WorksheetFunction.Round(4.3, 0)
    But it seems that not every Excel function is reachable thru this type of construction. For example Excel's mod doesn't seem reachable (VBA considers its mod to be an operator).

    Within VBA, I even get prompted differently (the Tooltips feature?) when using the construction under B vs C for round.

    With Analysis Toolpak-VBA checked, I do also see some Mixed Case functions, as you indicated. But you say these are there so you can "call them from your own code". What is "them" - the VBA functions or the excel functions? By talking about "code", it almost implies you're calling excel functions from within VBA. However, given the above, it would seem we already have a way of doing that. So I must conclude that the Mixed function names allows access from excel to the VBA functions that exist. Is this correct?

    Is there any rationale for what functions from one environment are available to the other? For example, round from excel seems to be available to VBA as noted in C but not mod. Similarly the other way around.

    BTW: there are other mixed case functions that get added if you check other add-ins. For example, I see a bunch of Query... functions that seem to be available thru the MS Query addin (I unchecked the Anal Toolpak-VBA; some functions disappeared but the set of Query... functions was still there).

    Thanks.

    Fred

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Analysis Toolpak - VBA (Excel 2000 SR1)

    The Application.WorksheetFunction method gives you the opportunity to use *part* of the *standard* worksheet functions. (Most of the missing ones are already available in VBA itself). What you are doing is make a call from VBA to another program (XL) to execute a function.

    User Defined Functions in Add-ins (or in workbooks you've created yourself) can also be used in other workbooks. Once one sets a reference to a workbook or add-in, its functions become available for "external" use: you can call them from other workbooks and capitalisation is adapted according to the names of the functions. One can use those functions in both VBA and in worksheet formulas.

    Since the VBA ATP add-in has VBA functions, one can use these in other workbooks as long as the add-in is installed. But the functions in the ATP are no Worksheet Functions in the sense that the Application.WorksheetFunction method sees them, they are user defined functions.

    I don't know why there are two add-ins for the ATP, one for the worksheet and one for VBA. Seems to me they should be using the same algoritms and arguments, thus needing to be there only once.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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