# Tanh Function in VBA

Discussion in 'Excel Programming' started by adambush4242@hotmail.com, Jan 23, 2009.

1. ### Guest

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

, Jan 23, 2009

2. ### Dave PetersonGuest

You can use the worksheet function:

Application.Tanh(...)

Dave Peterson, Jan 23, 2009

3. ### JoelGuest

T = worksheetfunction.Tanh(123)

Joel, Jan 23, 2009
4. ### Rick RothsteinGuest

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

HyperbolicTangent = WorksheetFunction.Tanh(YourNumber)

Rick Rothstein, Jan 23, 2009
5. ### Gary''s StudentGuest

MsgBox (Application.WorksheetFunction.Tanh(0.1))
End Sub

Gary''s Student, Jan 23, 2009
6. ### Mike HGuest

and just be different

X = 0.7
Tanh = (Exp(X) - Exp(-X)) / (Exp(X) + Exp(-X))

Mike

Mike H, Jan 23, 2009
7. ### Guest

Thanks a lot for your help guys.
\

, Jan 23, 2009
8. ### Toto217

Joined:
Mar 15, 2016
Messages:
1
0
Both described methods have floors, the worksheet function seems more costly in terms of calculation time
The exp methods meets high numbers limitation...

So I made some tests on differents 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 also 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... I tried with random numbers... optimization is not clear

Toto217, Mar 15, 2016