How do you ArcSin in Microsoft Visual Basic?

[Kevmcneilly]

I need to include the "Asin()" command in a code that I am writing, but every time I do it a "Compile Error: Sub or Function not defined" message pops up. I have tried it for just the "Sin()" command and it worked fine.

I tried the help menus and searching on the web, but there seems to be nothing on this.

This doesn't work:

ArcAngle = (180 / 3.14159) * ((Asin(Length / 4) / CLRadius))

But this does:

ArcAngle = (180 / 3.14159) * ((Sin(Length / 4) / CLRadius))

Any help?

 

[Nimrod]

You can access many of excels worksheet functions by using the following syntx ... :wink:

Public Sub demo()
    result = Application.WorksheetFunction.Asin(4 / 6)
    MsgBox result
End Sub

 

[dk]

G'day,

The ASin function belongs to the WorksheetFunction class in Excel's library so you need to prefix it with the text "WorksheetFunction". The Sin function is part of the VBA library which is why it works on its own.

E.g. this should work:

ArcAngle = (180 / 3.14159) * ((WorksheetFunction.Asin(Length / 4) / CLRadius))

If you're doing a lot of calculations and want to avoid having to type the text "WorksheetFunction" over and over you could do something like this:

Sub ArcSinExample()
Dim ArcAngle As Double
Dim Length As Double
Dim CLRadius As Double

Dim WF As WorksheetFunction

Length = 2
CLRadius = 5


'Set a variable WF to represent Excel's WorksheetFunction class
Set WF = Excel.WorksheetFunction


'Now you can use the variable WF in place of WorksheetFunction
ArcAngle = (180 / 3.14159) * ((WF.Asin(Length / 4) / CLRadius))


End Sub

EDIT: You may also be interested to know that the WorksheetFunction class also provides a function which will return the value of PI, rather than having to type it in all the time e.g.

'Now you can use the variable WF in place of WorksheetFunction
ArcAngle = (180 / WF.Pi) * ((WF.Asin(Length / 4) / CLRadius))

END EDIT

Hope that helps,

Dan

 

[Kevmcneilly]

Thanks a ton

 

[tusharm]

If you want to shy away from worksheetfunction, which after all is a method of the Excel object, you may want to check 'Derived Math Functions' (w/o the quotes) in XL VBA help.

 

[Kevmcneilly]

awesome, thanks for everyones help

mrexcel.com is amazing