Excel - Analysis Toolpak - VBA add-in

Asked By VBA_EE_Eng on 29-Sep-08 11:44 AM
How do I use the analysis toolpak for VBA? I loaded the add-in, but I cannot
seem to find any of the functions, and it asks for a password to open the
atpvbaen project.




Gary Keramidas replied on 29-Sep-08 11:53 AM
you do not need to open it.

just use the functions, for example,

=eomonth(a1,1)

if a1 has a date in it

--


Gary
Peter T replied on 29-Sep-08 12:20 PM
If speed is important, eg calling the ATP multiple times, set a reference to
Atpvbaen.xls in Tools References. Otherwise use Application.Run

Sub test()
Dim start_date As Date, eom As Date
Dim months
start_date = Date
months = 12
' with a ref to the addin works fast
eom = EoMonth(start_date, months) ' with the ref
MsgBox eom

months = 1

' this works without the ref but much slower
eom = Application.Run("EoMonth", start_date, months)
MsgBox eom
End Sub

Regards,
Peter T