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

--
Regards,
Zack Barresse, aka firefytr
To email, remove NOSPAM


"Conan Kelly" <CTBarbarin at msn dot com> wrote in message
news:%[email protected]...
> 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
>

[Conan Kelly]

Zack,

Thanks for the help,

Conan


"Zack Barresse" <[email protected]> wrote in message news:[email protected]...
> Hi there Conan,
>
> You must set a reference to it. Tools | References | atpvbaen.xls (atpvbaen = Analysis ToolPak VBA ENglish, so adjust for
> language).
>
> HTH
>
> --
> Regards,
> Zack Barresse, aka firefytr
> To email, remove NOSPAM
>
>
> "Conan Kelly" <CTBarbarin at msn dot com> wrote in message news:%[email protected]...
>> 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
>>
>
>

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

--
Regards,
Tom Ogilvy



"Zack Barresse" <[email protected]> wrote in message
news:[email protected]...
> Hi there Conan,
>
> You must set a reference to it. Tools | References | atpvbaen.xls
(atpvbaen
> = Analysis ToolPak VBA ENglish, so adjust for language).
>
> HTH
>
> --
> Regards,
> Zack Barresse, aka firefytr
> To email, remove NOSPAM
>
>
> "Conan Kelly" <CTBarbarin at msn dot com> wrote in message
> news:%[email protected]...
> > 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
> >
>
>

[Tom Ogilvy]

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

--
Regards,
Tom Ogilvy

"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> 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.
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "Zack Barresse" <[email protected]> wrote in message
> news:[email protected]...
> > Hi there Conan,
> >
> > You must set a reference to it. Tools | References | atpvbaen.xls
> (atpvbaen
> > = Analysis ToolPak VBA ENglish, so adjust for language).
> >
> > HTH
> >
> > --
> > Regards,
> > Zack Barresse, aka firefytr
> > To email, remove NOSPAM
> >
> >
> > "Conan Kelly" <CTBarbarin at msn dot com> wrote in message
> > news:%[email protected]...
> > > 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
> > >
> >
> >
>
>

[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




"Tom Ogilvy" <[email protected]> wrote in message news:[email protected]...
> 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.
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "Zack Barresse" <[email protected]> wrote in message
> news:[email protected]...
>> Hi there Conan,
>>
>> You must set a reference to it. Tools | References | atpvbaen.xls
> (atpvbaen
>> = Analysis ToolPak VBA ENglish, so adjust for language).
>>
>> HTH
>>
>> --
>> Regards,
>> Zack Barresse, aka firefytr
>> To email, remove NOSPAM
>>
>>
>> "Conan Kelly" <CTBarbarin at msn dot com> wrote in message
>> news:%[email protected]...
>> > 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
>> >
>>
>>
>
>