Tanh Function in VBA

  • Thread starter Thread starter adambush4242
  • Start date Start date
A

adambush4242

Does anyone know how to replicate the worksheet function Tanh in VBA? It
returns the hyperbolic tangent of a number. It doesn't exist in this syntax
in VBA, but I was wondering if there is a different syntax or easy code to
recreate it.

Thanks

Adam Bush
 
You can always reach out to the worksheet TANH function to do that...

HyperbolicTangent = WorksheetFunction.Tanh(YourNumber)
 
Hi all,
I would like to optimise the use of the Hyperbolic Tangeant in vba.
Both described methods have floors, the worksheet function seems more costly in terms of calculation time
The exp methods meets high numbers limitation... Perhaps there could be an exp method with a cut above and underneath certain values, perhaps with some error management, but would it work ?

Regards
Toto
 
Because I'm impatient I tried different methods

on 100 000 iterations the average times were
  • Application.Tanh : 0. 27 s (pretty slow)
  • Application.Worksheet.Tanh : 0.11 s (much better)
  • (Exp(x) - Exp(-x)) / (Exp(x) + Exp(-x)) : 0.03 s (lightning)
the problem is that the third method is much limited by the exp... indeed with X = 1000 it will cause an error

so I figured out 2 ways of dealing with this error, one with the on error, and the other with the if X >...


First one is

Function Hyperbolictangeante(x)
On Error GoTo gesterr
Hyperbolictangeante = (Exp(x) - Exp(-x)) / (Exp(x) + Exp(-x))
Exit Function
gesterr:
Hyperbolictangeante = Sgn(x)
End Function
​

Calculation time is around 0.046s so 50% slower but still very competitive (notice the smart use of the sgn function ILM)

Second one


Function Hyperbolictangeante2(x)
If Abs(x) > 709 Then
Hyperbolictangeante2 = Sgn(x)
Else
Hyperbolictangeante2 = (Exp(x) - Exp(-x)) / (Exp(x) + Exp(-x))
End If
End Function​


time is 0.03s: it looks like this version is better, and is even better when x is > 709 (0.015s)
as a matter of fact I wasn't expecting that, and was thinking that the error version would be more efficient, but it looks like the comparison and abs is less costly than the on error


Yours
Toto


Whole code of the test is here

Sub test()

Dim nbboucles As Double
Dim i As Double
Dim a, b, c As Double
nbboucles = 100000
x = 100

t0 = Timer

For i = 1 To nbboucles
a = Application.Tanh(x)
Next i

t1 = Timer

For i = 1 To nbboucles
b = Application.WorksheetFunction.Tanh(x)
Next i


t2 = Timer

For i = 1 To nbboucles
c = Hyperbolictangeante(x)
Next i


t3 = Timer

For i = 1 To nbboucles
c = Hyperbolictangeante2(x)
Next i


t4 = Timer

Debug.Print t1 - t0
Debug.Print t2 - t1
Debug.Print t3 - t2
Debug.Print t4 - t3
Debug.Print "-----------------"​

End Sub
Function Hyperbolictangeante0(x) 'won't work with abs(x) above 709
Hyperbolictangeante = (Exp(x) - Exp(-x)) / (Exp(x) + Exp(-x))
End Function
Function Hyperbolictangeante(x)
On Error GoTo gesterr
Hyperbolictangeante = (Exp(x) - Exp(-x)) / (Exp(x) + Exp(-x))
Exit Function
gesterr:
Hyperbolictangeante = Sgn(x)
End Function
Function Hyperbolictangeante2(x)
If Abs(x) > 709 Then
Hyperbolictangeante2 = Sgn(x)
Else
Hyperbolictangeante2 = (Exp(x) - Exp(-x)) / (Exp(x) + Exp(-x))
End If
End Function​
 
you could do a very small further optimization by doing

temp2 = Exp(-x)
Hyperbolictangeante3 = 1 - 2 * temp2 / (Exp(x) + temp2)

In fact optimization will depend on the distribution of the X, depending on the cases it is sometimes better, sometimes not...
 
Back
Top