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
