Tanh Function in VBA

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
 
R

Rick Rothstein

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

HyperbolicTangent = WorksheetFunction.Tanh(YourNumber)
 
Joined
Mar 15, 2016
Messages
3
Reaction score
0
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
 
Joined
Mar 15, 2016
Messages
3
Reaction score
0
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
 
Joined
Mar 15, 2016
Messages
3
Reaction score
0
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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top