Using Analysis ToolPak funcions in VBA

[Conan Kelly]

Hello all,

I have the Analysis ToolPak installed so the EOMONTH function is available for me to use in Excel, but when I enter
"Application.WorksheetFunction." in VBA, it doesn't appear in the dropdown list.

Can I use the EOMONTH function in VB code? If so, how?

Thanks for any help anyone can provide,

Conan Kelly

 

[Zack Barresse]

Hi there Conan,

You must set a reference to it. Tools | References | atpvbaen.xls (atpvbaen
= Analysis ToolPak VBA ENglish, so adjust for language).

HTH

 

[Conan Kelly]

Zack,

Thanks for the help,

Conan

Hi there Conan,

You must set a reference to it. Tools | References | atpvbaen.xls (atpvbaen = Analysis ToolPak VBA ENglish, so adjust for
language).

HTH

 

[Tom Ogilvy]

You don't have to set a reference, but the Analysis Tookpak VBA has to be
loaded.

If not reference, use application.Run as shown form the immediate window:

? format(Application.Run ("atpvbaen.xla!eomonth",date,2),"mm/dd/yyyy")
06/30/2006

If the reference is set, you don't use application or
application.WorksheetFunction because it isn't a built in function.

? format(eomonth(date,2),"mm/dd/yyyy")
06/30/2006

so you call it like you would any vba function.

 

[Tom Ogilvy]

Post was intended for the OP. Inadvertently replied to Zack's post.

 

[Conan Kelly]

Zack & Tom,

Thanks again for all of your help. It is much appreciated.

Anyone who knows SQL Server DTS packages and saving them as VB files, I have a question posted at microsoft.public.sqlserver.dts
that I sure could use some help with. Swing on by and take a look.

Conan