Access add in functions to VBA

[OCONUS]

New to creating custom functions. I'm trying to add workday, which is an
add-in, to a function I'm creating but "Application.Workday(a,b)" isn't
working. Any suggestions?

[Gary''s Student]

You are trying to use the Analysis ToolPak in VBA.

See:

http://www.cpearson.com/excel/ATP.htm

for complete instructions
--
Gary's Student


"OCONUS" wrote:

> New to creating custom functions. I'm trying to add workday, which is an
> add-in, to a function I'm creating but "Application.Workday(a,b)" isn't
> working. Any suggestions?

[OCONUS]

Thanks for the link, but the site tells me I need to select atpvbaen.xls in
my available references in VBA. Unfortunately, thats not there to select.

"Gary''s Student" wrote:

> You are trying to use the Analysis ToolPak in VBA.
>
> See:
>
> http://www.cpearson.com/excel/ATP.htm
>
> for complete instructions
> --
> Gary's Student
>
>
> "OCONUS" wrote:
>
> > New to creating custom functions. I'm trying to add workday, which is an
> > add-in, to a function I'm creating but "Application.Workday(a,b)" isn't
> > working. Any suggestions?

[OCONUS]

Figured out that I didn't have the Add-in for VBA. Sorry about that. But
now after I select atpvbaen.xls in the references is giving me an error
pop-up that says nothing but "400". Any suggestions?

"OCONUS" wrote:

> Thanks for the link, but the site tells me I need to select atpvbaen.xls in
> my available references in VBA. Unfortunately, thats not there to select.
>
> "Gary''s Student" wrote:
>
> > You are trying to use the Analysis ToolPak in VBA.
> >
> > See:
> >
> > http://www.cpearson.com/excel/ATP.htm
> >
> > for complete instructions
> > --
> > Gary's Student
> >
> >
> > "OCONUS" wrote:
> >
> > > New to creating custom functions. I'm trying to add workday, which is an
> > > add-in, to a function I'm creating but "Application.Workday(a,b)" isn't
> > > working. Any suggestions?

[Gary''s Student]

If you post some of you code, I can look at it tomorrow (Saturday)
--
Gary''s Student


"OCONUS" wrote:

> Figured out that I didn't have the Add-in for VBA. Sorry about that. But
> now after I select atpvbaen.xls in the references is giving me an error
> pop-up that says nothing but "400". Any suggestions?
>
> "OCONUS" wrote:
>
> > Thanks for the link, but the site tells me I need to select atpvbaen.xls in
> > my available references in VBA. Unfortunately, thats not there to select.
> >
> > "Gary''s Student" wrote:
> >
> > > You are trying to use the Analysis ToolPak in VBA.
> > >
> > > See:
> > >
> > > http://www.cpearson.com/excel/ATP.htm
> > >
> > > for complete instructions
> > > --
> > > Gary's Student
> > >
> > >
> > > "OCONUS" wrote:
> > >
> > > > New to creating custom functions. I'm trying to add workday, which is an
> > > > add-in, to a function I'm creating but "Application.Workday(a,b)" isn't
> > > > working. Any suggestions?

[OCONUS]

This is the code I'm trying to use:
Function ESD(payment_date, payment_type)
If payment_type = "ACH Deposit" Then
ESD = [atpvbaen.xls].WORKDAY(payment_date, 6)
Else
If payment_type = "Credit Card" Then
ESD = [atpvbaen.xls].WORKDAY(payment_date, 9)
Else
If payment_type = Application.Or("Check (Secured)", "Money
Order") Then
ESD = [atpvbaen.xls].WORKDAY(payment_date, 13)
Else
If payment_type = "Check (unsecured)" Then
ESD = [atpvbaen.xls].WORKDAY(payment_date, 20)
Else
End If
End If
End If
End If
End Function
The problem is when I select the aptvbaen.xls box and try to exit the
references screen, I'm getting and error message that says nothing but "400".

Thanks again for all your help
"Gary''s Student" wrote:

> If you post some of you code, I can look at it tomorrow (Saturday)
> --
> Gary''s Student
>
>
> "OCONUS" wrote:
>
> > Figured out that I didn't have the Add-in for VBA. Sorry about that. But
> > now after I select atpvbaen.xls in the references is giving me an error
> > pop-up that says nothing but "400". Any suggestions?
> >
> > "OCONUS" wrote:
> >
> > > Thanks for the link, but the site tells me I need to select atpvbaen.xls in
> > > my available references in VBA. Unfortunately, thats not there to select.
> > >
> > > "Gary''s Student" wrote:
> > >
> > > > You are trying to use the Analysis ToolPak in VBA.
> > > >
> > > > See:
> > > >
> > > > http://www.cpearson.com/excel/ATP.htm
> > > >
> > > > for complete instructions
> > > > --
> > > > Gary's Student
> > > >
> > > >
> > > > "OCONUS" wrote:
> > > >
> > > > > New to creating custom functions. I'm trying to add workday, which is an
> > > > > add-in, to a function I'm creating but "Application.Workday(a,b)" isn't
> > > > > working. Any suggestions?

[Gary''s Student]

There are two things you need to do, not just one:


In addition to Tools > Addins... > and checking the Toolpak VBA

You must (in the VBA window) pull-down:
Tools > References and check the atpvbaen.xls box as well.


Once both have been done, then:


Sub bigmac()
MsgBox (workday(12, 12))
End Sub

will work. No need to preface the WORKDAY() function with anything.

Any more problems ==> update the post. Otherwise, have a good day!!

--
Gary's Student


"OCONUS" wrote:

> This is the code I'm trying to use:
> Function ESD(payment_date, payment_type)
> If payment_type = "ACH Deposit" Then
> ESD = [atpvbaen.xls].WORKDAY(payment_date, 6)
> Else
> If payment_type = "Credit Card" Then
> ESD = [atpvbaen.xls].WORKDAY(payment_date, 9)
> Else
> If payment_type = Application.Or("Check (Secured)", "Money
> Order") Then
> ESD = [atpvbaen.xls].WORKDAY(payment_date, 13)
> Else
> If payment_type = "Check (unsecured)" Then
> ESD = [atpvbaen.xls].WORKDAY(payment_date, 20)
> Else
> End If
> End If
> End If
> End If
> End Function
> The problem is when I select the aptvbaen.xls box and try to exit the
> references screen, I'm getting and error message that says nothing but "400".
>
> Thanks again for all your help
> "Gary''s Student" wrote:
>
> > If you post some of you code, I can look at it tomorrow (Saturday)
> > --
> > Gary''s Student
> >
> >
> > "OCONUS" wrote:
> >
> > > Figured out that I didn't have the Add-in for VBA. Sorry about that. But
> > > now after I select atpvbaen.xls in the references is giving me an error
> > > pop-up that says nothing but "400". Any suggestions?
> > >
> > > "OCONUS" wrote:
> > >
> > > > Thanks for the link, but the site tells me I need to select atpvbaen.xls in
> > > > my available references in VBA. Unfortunately, thats not there to select.
> > > >
> > > > "Gary''s Student" wrote:
> > > >
> > > > > You are trying to use the Analysis ToolPak in VBA.
> > > > >
> > > > > See:
> > > > >
> > > > > http://www.cpearson.com/excel/ATP.htm
> > > > >
> > > > > for complete instructions
> > > > > --
> > > > > Gary's Student
> > > > >
> > > > >
> > > > > "OCONUS" wrote:
> > > > >
> > > > > > New to creating custom functions. I'm trying to add workday, which is an
> > > > > > add-in, to a function I'm creating but "Application.Workday(a,b)" isn't
> > > > > > working. Any suggestions?

[OCONUS]

Unfortunately, I've added the VBA Toolpak in the addins already. I'm trying
to select the atpvbaen.xls in the references now, but everytime I hit OK I'm
getting that 400 error. Could this be specific to some security settings
that my company has in place?

Also, I'm not sure I understand the bigmac sub you wrote.

"Gary''s Student" wrote:

> There are two things you need to do, not just one:
>
>
> In addition to Tools > Addins... > and checking the Toolpak VBA
>
> You must (in the VBA window) pull-down:
> Tools > References and check the atpvbaen.xls box as well.
>
>
> Once both have been done, then:
>
>
> Sub bigmac()
> MsgBox (workday(12, 12))
> End Sub
>
> will work. No need to preface the WORKDAY() function with anything.
>
> Any more problems ==> update the post. Otherwise, have a good day!!
>
> --
> Gary's Student
>
>
> "OCONUS" wrote:
>
> > This is the code I'm trying to use:
> > Function ESD(payment_date, payment_type)
> > If payment_type = "ACH Deposit" Then
> > ESD = [atpvbaen.xls].WORKDAY(payment_date, 6)
> > Else
> > If payment_type = "Credit Card" Then
> > ESD = [atpvbaen.xls].WORKDAY(payment_date, 9)
> > Else
> > If payment_type = Application.Or("Check (Secured)", "Money
> > Order") Then
> > ESD = [atpvbaen.xls].WORKDAY(payment_date, 13)
> > Else
> > If payment_type = "Check (unsecured)" Then
> > ESD = [atpvbaen.xls].WORKDAY(payment_date, 20)
> > Else
> > End If
> > End If
> > End If
> > End If
> > End Function
> > The problem is when I select the aptvbaen.xls box and try to exit the
> > references screen, I'm getting and error message that says nothing but "400".
> >
> > Thanks again for all your help
> > "Gary''s Student" wrote:
> >
> > > If you post some of you code, I can look at it tomorrow (Saturday)
> > > --
> > > Gary''s Student
> > >
> > >
> > > "OCONUS" wrote:
> > >
> > > > Figured out that I didn't have the Add-in for VBA. Sorry about that. But
> > > > now after I select atpvbaen.xls in the references is giving me an error
> > > > pop-up that says nothing but "400". Any suggestions?
> > > >
> > > > "OCONUS" wrote:
> > > >
> > > > > Thanks for the link, but the site tells me I need to select atpvbaen.xls in
> > > > > my available references in VBA. Unfortunately, thats not there to select.
> > > > >
> > > > > "Gary''s Student" wrote:
> > > > >
> > > > > > You are trying to use the Analysis ToolPak in VBA.
> > > > > >
> > > > > > See:
> > > > > >
> > > > > > http://www.cpearson.com/excel/ATP.htm
> > > > > >
> > > > > > for complete instructions
> > > > > > --
> > > > > > Gary's Student
> > > > > >
> > > > > >
> > > > > > "OCONUS" wrote:
> > > > > >
> > > > > > > New to creating custom functions. I'm trying to add workday, which is an
> > > > > > > add-in, to a function I'm creating but "Application.Workday(a,b)" isn't
> > > > > > > working. Any suggestions?

[Gary''s Student]

I am sorry that I cannot replicate yuor error message.

The tiny macro will just demonstrate that the WORKDAY() function will work
directly in VBA (once you succeed in getting the ToolPak to work).

If you have the time to update the post once you find the solution, others
may benefit from it.

Good Luck
--
Gary''s Student


"OCONUS" wrote:

> Unfortunately, I've added the VBA Toolpak in the addins already. I'm trying
> to select the atpvbaen.xls in the references now, but everytime I hit OK I'm
> getting that 400 error. Could this be specific to some security settings
> that my company has in place?
>
> Also, I'm not sure I understand the bigmac sub you wrote.
>
> "Gary''s Student" wrote:
>
> > There are two things you need to do, not just one:
> >
> >
> > In addition to Tools > Addins... > and checking the Toolpak VBA
> >
> > You must (in the VBA window) pull-down:
> > Tools > References and check the atpvbaen.xls box as well.
> >
> >
> > Once both have been done, then:
> >
> >
> > Sub bigmac()
> > MsgBox (workday(12, 12))
> > End Sub
> >
> > will work. No need to preface the WORKDAY() function with anything.
> >
> > Any more problems ==> update the post. Otherwise, have a good day!!
> >
> > --
> > Gary's Student
> >
> >
> > "OCONUS" wrote:
> >
> > > This is the code I'm trying to use:
> > > Function ESD(payment_date, payment_type)
> > > If payment_type = "ACH Deposit" Then
> > > ESD = [atpvbaen.xls].WORKDAY(payment_date, 6)
> > > Else
> > > If payment_type = "Credit Card" Then
> > > ESD = [atpvbaen.xls].WORKDAY(payment_date, 9)
> > > Else
> > > If payment_type = Application.Or("Check (Secured)", "Money
> > > Order") Then
> > > ESD = [atpvbaen.xls].WORKDAY(payment_date, 13)
> > > Else
> > > If payment_type = "Check (unsecured)" Then
> > > ESD = [atpvbaen.xls].WORKDAY(payment_date, 20)
> > > Else
> > > End If
> > > End If
> > > End If
> > > End If
> > > End Function
> > > The problem is when I select the aptvbaen.xls box and try to exit the
> > > references screen, I'm getting and error message that says nothing but "400".
> > >
> > > Thanks again for all your help
> > > "Gary''s Student" wrote:
> > >
> > > > If you post some of you code, I can look at it tomorrow (Saturday)
> > > > --
> > > > Gary''s Student
> > > >
> > > >
> > > > "OCONUS" wrote:
> > > >
> > > > > Figured out that I didn't have the Add-in for VBA. Sorry about that. But
> > > > > now after I select atpvbaen.xls in the references is giving me an error
> > > > > pop-up that says nothing but "400". Any suggestions?
> > > > >
> > > > > "OCONUS" wrote:
> > > > >
> > > > > > Thanks for the link, but the site tells me I need to select atpvbaen.xls in
> > > > > > my available references in VBA. Unfortunately, thats not there to select.
> > > > > >
> > > > > > "Gary''s Student" wrote:
> > > > > >
> > > > > > > You are trying to use the Analysis ToolPak in VBA.
> > > > > > >
> > > > > > > See:
> > > > > > >
> > > > > > > http://www.cpearson.com/excel/ATP.htm
> > > > > > >
> > > > > > > for complete instructions
> > > > > > > --
> > > > > > > Gary's Student
> > > > > > >
> > > > > > >
> > > > > > > "OCONUS" wrote:
> > > > > > >
> > > > > > > > New to creating custom functions. I'm trying to add workday, which is an
> > > > > > > > add-in, to a function I'm creating but "Application.Workday(a,b)" isn't
> > > > > > > > working. Any suggestions?

[IMADK]

I know this is more than 6 years later, but I just encountered the mysterious "400" error using Excel 2010! It occured the same way, trying to check atpvbaen.xls in "References."

I tried a few different things, and what worked for me was to "cut" my entire VBA code for the worksheet and pasted it into Notepad for safekeeping. Then I saved the excel file with the blank VBA, then reopen my excel file, open VBA editor, then enable atpvbaen.xls in References, hit OK, then save my workbook, then pasted my code back in and resaved.

No idea why that worked or what caused the problem to begin with, but it works now.