excel - accessing bessel function from vba 1

[electricpete]

I have the analysis tool-pak add-in and the analysis VBA toolpak add-in loaded.

I can access bessel functions from my worksheet. For example
besseli(1.2,0)

But I cannot access them from VBA.
besseli(1.2,0) in the immediate window gives "error - sub or function not defined)

application.worksheetfunction.besseli(1.2,0) gives error: "object does not support thsi propery or method"

How can I get bessel in vba?

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[electricpete]

By the way, I'm using MS Excel 2000 on Windows XP

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[IRstuff]

Still, why can't you simply stick the function into a separate worksheet, have it crank the answer, the copy the result to where you want?

TTFN

FAQ731-376

 

[electricpete]

I didn't think of that, but it certainly gets the job done.

Sub test1()
Dim range1 As Range
Dim double1 As Double
Dim answer As Double
Worksheets("sheet1").Select
Set range1 = Range("A1")
double1 = 1.2
range1.Formula = "=+besseli(" & double1 & ",0)"
answer = range1.Value
Debug.Print answer
End Sub

The above code outputs 1.39372557200049 which is the correct answer.

I checked a little more on why vba didn't recognize besseli, when the whole purpose of the vba analysis toolpak is to give us access to the functions in the regular analysis toolpak.

I found the answer in "Excel 2002 Power Programming with VBA" by John Walkenbach, Chapter 30 ("Frequently Asked Questions") which states:

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”)

Now I'm curious how we were supposed to know about this mysterious atpvbaen.xls? Is it in the help somewhere that I missed?

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[electricpete]

An interesting thing about the workaround. It works if you put the formula into the worksheet cell as was suggested. But it doesn't work if you use the evaluate function (which is supposed to be the same thing as evaluating the string formula in a worksheet cell....apparently not in this case).

I tried the following and it gives a compiler error on the line involving besseli (with atpvbaen.xls unchecked), but works fine to ouptut the sin if I comment out the besseli line.

Sub test1()
Dim double1 As Double
Dim answer As Double
double1 = 1.2
answer = Evaluate("sin(" & double1 & ")")
Debug.Print (answer)
answer = Evaluate("=+besseli(" & double1 & ",0)")
Debug.Print answer
End Sub

(Generates a compiler error which can be eliminated by commenting out the besseli line or by checking atpvbaen.xls).

So apparently the evaluate(formulastring) function does not send the formulastring to the same parser/interpretter as the excel worksheet does. Weird.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[prost]

OK, I must be missing something..
Once you check the add-in Analysis ToolPak - VBA, then check the atpvbaen.xls Reference, shouldn't this statement work?
besselS = Application.WorksheetFunction.BesselI(a, b)

Object doesn't support this property or method.

I understand the workaround, which works of course, but all that business about adding the Anal. Toolpak-VBA and that mysterious atpvbaen.xls seems like just a waste of time if you can't access functions like the Bessel functions with a simple worksheetfunction call.

 

[prost]

After some searching, I am now marginally more enlightened than I was before. Here

http://www.eng.auburn.edu/~tplacek/courses/3600/notes8.pdf

There is a description of the steps to use this mysterious atpvbaen.xls. Once you check the Analysis ToolPak VBA and teh atpvbaen.xls Reference, you can see what has been added to your list of available functions by going to View/Object Browser, then select atpvbaen.xls in the pull down menu in the upper left of the Object Browswer. Lower right, you will see a list of available functions. At the very bottom, it shows you how to use those functions. To access Bessel functions, don't be a dope like I was and use:
stuff = Application.WorksheetFunction.BesselI(a, b)
instead, use:
stuff = BesselI(a, b)

right in your program, as you would use Application.WorksheetFunction.