looking for resources on VBA for Excel

[johnnymac]

I have been writing some VBA code to do some conversions for GPS data. I could probably find code for what I want to do on the web, but I am curious if anyone knows of a good reference for the basic VBA language. I ask because there are functions in Excel that I cannot seem to get in VBA.

For example, the Microsoft Excel Help chm file lists Asin as a member of WorkbookFunction object, but I get an error that it is not supported if I use it. I can, however just enter y = asin(x) and I will get the arcsin of x. In this help file, there does not seem to be any reference to functions that are available in VBA - only object members. The one that has me puzzled at the moment is INT(), which is neither a WorkbookFunction member nor an available function in VBA. I can use an If (x<0, RoundDown(x,0), RoundUp(x,0)), but I am more interested right now in finding an accurate list of functions available in VBA.

My current code is bouncing back and forth between using and not using the WorkbookFunction.trigfunction() and just the trigfunction().

[AB33]

You can access (use) most, if not all, Excel's built-in functions in VBA. All you need is to precede the function name with Application() or Worksheetfunction().

[Norie]

Workbookfunction?

Do you mean WorksheetFunction?

If you do you'll find a full list of the worksheet functions you can use, via WorksheetFunction, in the Object Browser (F2) under WorksheetFunction.

You can also use the Object Browser to search for VBA functions, for example you'll find the Int function in the Conversion class.

[shg]

In the Object Browser (F2 in the VBE), select VBA from the Library dropdown and below that, enter Math. That will list the native math functions: Abs, Atn, Cos, ...

There are short routines you can write for the derived trig functions that will be much faster than WorksheetFunction.

[johnnymac]

I wrote "WorkbookFunction" in my post, but I was really using WorksheetFunction. Sorry about that error. Been cutting and pasting the phrase in my code and just wrote in wrong in my post. I do get lots of errors when I try this. For example, cos, sin, and tan cannot be preceded with the WorksheetFunction. or I get an error that the function is "not supported." For all of the trig functions, it is one or the other.

[shg]

For example, cos, sin, and tan cannot be ...

Because they are native VBA functions.

[johnnymac]

OK..I see that in the Object Browser. Cosh, Cot, and Coth, but no Cos. Which matches my experience with this. Thanks for the point to the Math class. That is very useful to know about. I will be looking there in the future.

[johnnymac]

Thanks. That was exactly what I was looking for - a list of the native VBA functions. Didn't have the right search word native to find it.