Using Analysis ToolPak funcions in VBA

C

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
 
Z

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
 
C

Conan Kelly

Zack,

Thanks for the help,

Conan


Zack Barresse said:
Hi there Conan,

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

HTH
 
T

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.
 
C

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top