CODEWITHSUNDEEP
vbaexceltrigonometry
jainashish
jainashish

Reputation: 5193

How to calculate/define ArcSin function in VBA?

How to implement the VBA code of the arcsin function (defined below)?

Definition: The arcsin function is the inverse of the sine function. It returns the angle whose sine is a given number. For every trigonometry function, there is an inverse function that works in reverse. These inverse functions have the same name but with 'arc' in front. (On some calculators the arcsin button may be labelled asin, or sometimes sin-1.) So the inverse of sin is arcsin etc. When we see "arcsin A", we understand it as "the angle whose sin is A"

sin30 = 0.5 Means: The sine of 30 degrees is 0.5

arcsin 0.5 = 30 Means: The angle whose sin is 0.5 is 30 degrees.

Upvotes: 0

Views: 18655

Answers (3)

Rocky Scott
Rocky Scott

Reputation: 466

WorksheetFunction.ASIN is 4.7 times slower than the VBA version.

(Also, search "Derived Math Functions" in Excel VBA help.)

I did the speed comparison in Excel 2010 using kernal32 functions 'QueryPerformanceCounter()' and 'QueryPerformanceFrequency()'. I would be curious if newer versions yield different results.

Here is the function I tested (with check to avoid #DIV/0! error):

Public Function ASin( _
      ByVal x As Double _
      ) As Double

   Const PIover2 As Double = 1.5707963267949

   If (x = 1) Then
      ASin = PIover2
   ElseIf (x = -1) Then
      ASin = -PIover2
   Else
      ASin = Atn(x / Sqr(-x * x + 1))
   End If

End Function

I tested the function using 10,000,000 iterations, calling the two functions in different orders and groups to account for any "priming the well" effects (I suppose). (You can see below that I called VBA function 10M times then WorksheetFunction 10M times twice, etc.) Here are the speed test results:

'WorksheetFunction is about 4.7 times slower than VBA
'    VBA(1)   WSF(1)   WSF(2)   VBA(2)   VBA(3)   WSF(3)   WSF/VBA
'    1.983    9.383    9.377    1.968    1.976    9.410    4.753

Upvotes: 2

Teasel
Teasel

Reputation: 1340

I don't really understand your question here. The arcsin function already exists in VBA, you can use it with :

WorksheetFunction.Asin(myValue)

Use of the arcsin function

Dim myValue As Double
myValue = 0.1234

MsgBox CStr(WorksheetFunction.Asin(myValue))

There you can print the result of the arcsin function for a value as Double.

Upvotes: 3

jainashish
jainashish

Reputation: 5193

The following code will help to implement the ARCSIN function based on given definition:

    Option Explicit
    Public Const Pi As Double = 3.14159265358979
    Private Function ArcSin(X As Double) As Double
      If Abs(X) = 1 Then
        'The VBA Sgn function returns an integer (+1, 0 or -1),
        'representing the arithmetic sign of a supplied number.
        ArcSin = Sgn(X) * Pi / 2
      Else
        'Atn is the inverse trigonometric function of Tan,
        'which takes an angle as its argument and returns
        'the ratio of two sides of a right triangle
        ArcSin = Atn(X / Sqr(1 - X ^ 2))
      End If
    End Function

Upvotes: 2

Related Questions