In versions of Excel prior to Excel 2007 one could address VBA Bessel
functions (as opposed to Worksheet functions) by ensuring one had a VBA
reference to ATPVBAEN.XLA. The object browser had all sorts of obscure
mathematical functions in it. With Excel 2007, these functions have been
removed from this library. I see no other libraries that have them. One can
always use the WORKSHEETFUNCTION that gets you to these math functions.
However I do not want to reference worksheet cells directly , just local
variables, due to a possible performance hit. Can I use local variables in
the argument list instead of cell references in WORKSHEETFUNCTION.

Also how would I maintain one code base that will run in Excel 2007 and
earlier versions, if I am forced down this path of using WORKSHEETFUNCTION? I
can use the application.version function to test for different Excel versions
but I get a compile error in the pre-Excel 2007 code fragment in Excel 2007
when it cannot see the bessel function itself (as the library is missing) Any
suggestions?
--
Bob B

You do not need cell references to use these functions:

Sub vncx()
y = Application.WorksheetFunction.BesselJ(0.5, 1)
End Sub


--
Gary''s Student - gsnu200804


"Bob B" wrote:

> In versions of Excel prior to Excel 2007 one could address VBA Bessel
> functions (as opposed to Worksheet functions) by ensuring one had a VBA
> reference to ATPVBAEN.XLA. The object browser had all sorts of obscure
> mathematical functions in it. With Excel 2007, these functions have been
> removed from this library. I see no other libraries that have them. One can
> always use the WORKSHEETFUNCTION that gets you to these math functions.
> However I do not want to reference worksheet cells directly , just local
> variables, due to a possible performance hit. Can I use local variables in
> the argument list instead of cell references in WORKSHEETFUNCTION.
>
> Also how would I maintain one code base that will run in Excel 2007 and
> earlier versions, if I am forced down this path of using WORKSHEETFUNCTION? I
> can use the application.version function to test for different Excel versions
> but I get a compile error in the pre-Excel 2007 code fragment in Excel 2007
> when it cannot see the bessel function itself (as the library is missing) Any
> suggestions?
> --
> Bob B

Check under engineering functions:

http://office.microsoft.com/en-us/ex...042111033.aspx

Also from previous posting:

Can I use Analysis ToolPak functions in my VBA code?
Yes, but it takes a few extra steps. In Excel, choose Tools/Add-Ins, and
place a
check mark next to the add-in named Analysis ToolPak - VBA. Then activate your
VB project and choose Tools/References. Place a check mark next to
atpvbaen.xls
to create a reference. Then you can use any of the Analysis ToolPak functions
in your code. For example, the following statement uses the Analysis ToolPak’s
CONVERT function, and converts 5,000 meters to miles:
MsgBox CONVERT(5000, “m”, “mi”)



"Bob B" wrote:

> In versions of Excel prior to Excel 2007 one could address VBA Bessel
> functions (as opposed to Worksheet functions) by ensuring one had a VBA
> reference to ATPVBAEN.XLA. The object browser had all sorts of obscure
> mathematical functions in it. With Excel 2007, these functions have been
> removed from this library. I see no other libraries that have them. One can
> always use the WORKSHEETFUNCTION that gets you to these math functions.
> However I do not want to reference worksheet cells directly , just local
> variables, due to a possible performance hit. Can I use local variables in
> the argument list instead of cell references in WORKSHEETFUNCTION.
>
> Also how would I maintain one code base that will run in Excel 2007 and
> earlier versions, if I am forced down this path of using WORKSHEETFUNCTION? I
> can use the application.version function to test for different Excel versions
> but I get a compile error in the pre-Excel 2007 code fragment in Excel 2007
> when it cannot see the bessel function itself (as the library is missing) Any
> suggestions?
> --
> Bob B

I am not sure I understand the replies. Let me keep things simple. This
applies only to Excel 2007. In Office 2007 I open a new work book, go to
Excel options, then add-ins and verify that the add-in 'Analysis ToolPak' and
'Analysis ToolPak - VBA' are there. I then open the VBA editor and got to
Tools, references and verify that 'atpvbaen.xls' (which is an Excel 2007
version) is checked. Click OK.

I then go to the object browser (F2) in the VBA editor window, select all
libraries and look at the window 'Members of Globals' . The engineering
function BesselK function is not there. In Excel 2003 the function BesselK
was there (and is actually in the Office 2003 'atpvbaen.xls' library). Where
is it now in Excel 2007? The same is true of the CONVERT function. It is not
in Excel 2007 but does show up in Excel 2003.
--
Bob B


"JLGWhiz" wrote:

> Check under engineering functions:
>
> http://office.microsoft.com/en-us/ex...042111033.aspx
>
> Also from previous posting:
>
> Can I use Analysis ToolPak functions in my VBA code?
> Yes, but it takes a few extra steps. In Excel, choose Tools/Add-Ins, and
> place a
> check mark next to the add-in named Analysis ToolPak - VBA. Then activate your
> VB project and choose Tools/References. Place a check mark next to
> atpvbaen.xls
> to create a reference. Then you can use any of the Analysis ToolPak functions
> in your code. For example, the following statement uses the Analysis ToolPak’s
> CONVERT function, and converts 5,000 meters to miles:
> MsgBox CONVERT(5000, “m”, “mi”)
>
>
>
> "Bob B" wrote:
>
> > In versions of Excel prior to Excel 2007 one could address VBA Bessel
> > functions (as opposed to Worksheet functions) by ensuring one had a VBA
> > reference to ATPVBAEN.XLA. The object browser had all sorts of obscure
> > mathematical functions in it. With Excel 2007, these functions have been
> > removed from this library. I see no other libraries that have them. One can
> > always use the WORKSHEETFUNCTION that gets you to these math functions.
> > However I do not want to reference worksheet cells directly , just local
> > variables, due to a possible performance hit. Can I use local variables in
> > the argument list instead of cell references in WORKSHEETFUNCTION.
> >
> > Also how would I maintain one code base that will run in Excel 2007 and
> > earlier versions, if I am forced down this path of using WORKSHEETFUNCTION? I
> > can use the application.version function to test for different Excel versions
> > but I get a compile error in the pre-Excel 2007 code fragment in Excel 2007
> > when it cannot see the bessel function itself (as the library is missing) Any
> > suggestions?
> > --
> > Bob B

Most (all?) functions that were in previous versions of ATP are native Excel
functions in 2007, and so are not duplicated in the 2007 ATP. That is why
you reference Bessel functions as WorksheetFunction.BesslK() in 2007, but not
in earlier versions.

Jerry

"Bob B" wrote:

> I am not sure I understand the replies. Let me keep things simple. This
> applies only to Excel 2007. In Office 2007 I open a new work book, go to
> Excel options, then add-ins and verify that the add-in 'Analysis ToolPak' and
> 'Analysis ToolPak - VBA' are there. I then open the VBA editor and got to
> Tools, references and verify that 'atpvbaen.xls' (which is an Excel 2007
> version) is checked. Click OK.
>
> I then go to the object browser (F2) in the VBA editor window, select all
> libraries and look at the window 'Members of Globals' . The engineering
> function BesselK function is not there. In Excel 2003 the function BesselK
> was there (and is actually in the Office 2003 'atpvbaen.xls' library). Where
> is it now in Excel 2007? The same is true of the CONVERT function. It is not
> in Excel 2007 but does show up in Excel 2003.
> --
> Bob B
>
>
> "JLGWhiz" wrote:
>
> > Check under engineering functions:
> >
> > http://office.microsoft.com/en-us/ex...042111033.aspx
> >
> > Also from previous posting:
> >
> > Can I use Analysis ToolPak functions in my VBA code?
> > Yes, but it takes a few extra steps. In Excel, choose Tools/Add-Ins, and
> > place a
> > check mark next to the add-in named Analysis ToolPak - VBA. Then activate your
> > VB project and choose Tools/References. Place a check mark next to
> > atpvbaen.xls
> > to create a reference. Then you can use any of the Analysis ToolPak functions
> > in your code. For example, the following statement uses the Analysis ToolPak’s
> > CONVERT function, and converts 5,000 meters to miles:
> > MsgBox CONVERT(5000, “m”, “mi”)
> >
> >
> >
> > "Bob B" wrote:
> >
> > > In versions of Excel prior to Excel 2007 one could address VBA Bessel
> > > functions (as opposed to Worksheet functions) by ensuring one had a VBA
> > > reference to ATPVBAEN.XLA. The object browser had all sorts of obscure
> > > mathematical functions in it. With Excel 2007, these functions have been
> > > removed from this library. I see no other libraries that have them. One can
> > > always use the WORKSHEETFUNCTION that gets you to these math functions.
> > > However I do not want to reference worksheet cells directly , just local
> > > variables, due to a possible performance hit. Can I use local variables in
> > > the argument list instead of cell references in WORKSHEETFUNCTION.
> > >
> > > Also how would I maintain one code base that will run in Excel 2007 and
> > > earlier versions, if I am forced down this path of using WORKSHEETFUNCTION? I
> > > can use the application.version function to test for different Excel versions
> > > but I get a compile error in the pre-Excel 2007 code fragment in Excel 2007
> > > when it cannot see the bessel function itself (as the library is missing) Any
> > > suggestions?
> > > --
> > > Bob B

Bob,

I'm encountering the same problem you described. In my case, I want to use
the functions for complex arithmetic. Like you I have VBA code that works in
verisons prior to Excel 2007. However, when I run the VBA in 2007, I
encounter the problem of not finding the complex arithmethic functions, such
as Complex, ImSum.

I have the same requriement to have the code run in both 2007 and earlier
versions of Excel. I've checked the atpvbaen.xls entry in Tools > Reference.
But none of this works in 2007.

Have you solved the problem or are you still searching for a solution?

Jim T

"Bob B" wrote:

> I am not sure I understand the replies. Let me keep things simple. This
> applies only to Excel 2007. In Office 2007 I open a new work book, go to
> Excel options, then add-ins and verify that the add-in 'Analysis ToolPak' and
> 'Analysis ToolPak - VBA' are there. I then open the VBA editor and got to
> Tools, references and verify that 'atpvbaen.xls' (which is an Excel 2007
> version) is checked. Click OK.
>
> I then go to the object browser (F2) in the VBA editor window, select all
> libraries and look at the window 'Members of Globals' . The engineering
> function BesselK function is not there. In Excel 2003 the function BesselK
> was there (and is actually in the Office 2003 'atpvbaen.xls' library). Where
> is it now in Excel 2007? The same is true of the CONVERT function. It is not
> in Excel 2007 but does show up in Excel 2003.
> --
> Bob B
>
>
> "JLGWhiz" wrote:
>
> > Check under engineering functions:
> >
> > http://office.microsoft.com/en-us/ex...042111033.aspx
> >
> > Also from previous posting:
> >
> > Can I use Analysis ToolPak functions in my VBA code?
> > Yes, but it takes a few extra steps. In Excel, choose Tools/Add-Ins, and
> > place a
> > check mark next to the add-in named Analysis ToolPak - VBA. Then activate your
> > VB project and choose Tools/References. Place a check mark next to
> > atpvbaen.xls
> > to create a reference. Then you can use any of the Analysis ToolPak functions
> > in your code. For example, the following statement uses the Analysis ToolPak’s
> > CONVERT function, and converts 5,000 meters to miles:
> > MsgBox CONVERT(5000, “m”, “mi”)
> >
> >
> >
> > "Bob B" wrote:
> >
> > > In versions of Excel prior to Excel 2007 one could address VBA Bessel
> > > functions (as opposed to Worksheet functions) by ensuring one had a VBA
> > > reference to ATPVBAEN.XLA. The object browser had all sorts of obscure
> > > mathematical functions in it. With Excel 2007, these functions have been
> > > removed from this library. I see no other libraries that have them. One can
> > > always use the WORKSHEETFUNCTION that gets you to these math functions.
> > > However I do not want to reference worksheet cells directly , just local
> > > variables, due to a possible performance hit. Can I use local variables in
> > > the argument list instead of cell references in WORKSHEETFUNCTION.
> > >
> > > Also how would I maintain one code base that will run in Excel 2007 and
> > > earlier versions, if I am forced down this path of using WORKSHEETFUNCTION? I
> > > can use the application.version function to test for different Excel versions
> > > but I get a compile error in the pre-Excel 2007 code fragment in Excel 2007
> > > when it cannot see the bessel function itself (as the library is missing) Any
> > > suggestions?
> > > --
> > > Bob B

Since former ATP functions are native Excel functions in 2007, there are only
two ways to have one set of VBA code work in both versions:

1. Write your own alternative routines for former ATP functions (if done
well, this would be more accurate for complex arithmetic than using the
former ATP functions, since you could maintain full precision by using a pair
of Doubles to represent a complex number instead losing several bits by
truncating to a 15 decimal digit string representation).

2. Use Application.Version to distinguish Excel 2007 from earlier versions
and choose the appropriate form of formulas based on the Excel version
(untested, but sound in principle).

Jerry

"jimthompson5802" wrote:

> Bob,
>
> I'm encountering the same problem you described. In my case, I want to use
> the functions for complex arithmetic. Like you I have VBA code that works in
> verisons prior to Excel 2007. However, when I run the VBA in 2007, I
> encounter the problem of not finding the complex arithmethic functions, such
> as Complex, ImSum.
>
> I have the same requriement to have the code run in both 2007 and earlier
> versions of Excel. I've checked the atpvbaen.xls entry in Tools > Reference.
> But none of this works in 2007.
>
> Have you solved the problem or are you still searching for a solution?