Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Using functions in VBA

1. Senior Member
Join Date
18th October 2004
Posts
106

## Using functions in VBA

Hi

I'm trying to using the Excel COS function in VBA (rather than on the worksheet), but I'm having problems. Do you have to declare these "built-in" functions in your code?

Mike

Excel Video Tutorials / Excel Dashboards Reports

2. How are you trying to do it?

You should just be able to use VBA Cos like this:
VB:
```MsgBox Cos(90)

```

Excel Video Tutorials / Excel Dashboards Reports

3. Senior Member
Join Date
18th October 2004
Posts
106
Hi Norie

Yeah it works for Cos, but not for these:

ACos()
Degrees()

These three functions I just can't seem to get to work in VBA.
Are they allowed in VBA, or is it just in Excel?
And if it is just in Excel, is there anyway I can transfer them into VBA?

Thanks
Mike

Excel Video Tutorials / Excel Dashboards Reports

4. Direct from the help files. Note the piece at the end on how to calculate Radians.

Cos Function

Returns a Double specifying the cosine of an angle.

Syntax

Cos(number)

The required number argument is a Double or any valid numeric expression that expresses an angle in radians.

Remarks

The Cos function takes an angle and returns the ratio of two sides of a right triangle. The ratio is the length of the side adjacent to the angle divided by the length of the hypotenuse.

The result lies in the range -1 to 1.

To convert degrees to radians, multiply degrees by pi/180. To convert radians to degrees, multiply radians by 180/pi.
You can also search for "Derived Math Functions" which will explain how to create your own functions for the ones that do not exist.

Or you can also use the Application.Worksheetfunctions object.

application.WorksheetFunction.degrees(1)
application.WorksheetFunction.Acos(1)

5. Senior Member
Join Date
18th October 2004
Posts
106
Thanks Andy

I've noticed that if you put "Application." infront the Excel function, it will work in VBA.

Mike

Excel Video Tutorials / Excel Dashboards Reports

6. Senior Member
Join Date
18th October 2004
Posts
106

## Square Root function

Hi
Managed to get the functions working OK in VBA except "Square Root"
Does anyone know this one?
I've tried 'sqrt()' and 'Application.sqrt()'but neither work.
Thanks
Mike

Excel Video Tutorials / Excel Dashboards Reports

7. Try this

9 ^ (1 / 2)

The ^ represents the 'to the power of' operator and square root of x = x to the power 1/2

Excel Video Tutorials / Excel Dashboards Reports

8. The square root function in VBA is SQR(number)

Norie's method is also useful for all other root calculations.
e.g.
27^(1/3) ' cubed root

9. Senior Member
Join Date
18th October 2004
Posts
106
Thanks folks

Excel Video Tutorials / Excel Dashboards Reports

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno