Accessing the Bessel Function within VBA in Excel 2007

[Bob B]

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?

 

[Gary''s Student]

You do not need cell references to use these functions:

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

 

[JLGWhiz]

Check under engineering functions:

http://office.microsoft.com/en-us/excel/HP052042111033.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]

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.

 

[Jerry W. Lewis]

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

 

[jimthompson5802]

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

 

[Jerry W. Lewis]

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