Analysis Toolpak – VBA (Excel 2000 SR1)

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

Thanks very much.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote