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

    Adam Bush
     
    , Jan 23, 2009
    #1
    1. Advertisements

  2. You can use the worksheet function:

    Application.Tanh(...)



    wrote:
    >
    > 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


    --

    Dave Peterson
     
    Dave Peterson, Jan 23, 2009
    #2
    1. Advertisements

  3. Joel Guest

    T = worksheetfunction.Tanh(123)

    "" wrote:

    > 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
     
    Joel, Jan 23, 2009
    #3
  4. You can always reach out to the worksheet TANH function to do that...

    HyperbolicTangent = WorksheetFunction.Tanh(YourNumber)

    --
    Rick (MVP - Excel)


    ""
    <> wrote in message
    news:D...
    > 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
     
    Rick Rothstein, Jan 23, 2009
    #4
  5. Sub adam()
    MsgBox (Application.WorksheetFunction.Tanh(0.1))
    End Sub

    --
    Gary''s Student - gsnu200828


    "" wrote:

    > 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
     
    Gary''s Student, Jan 23, 2009
    #5
  6. Mike H Guest

    and just be different

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


    Mike

    "" wrote:

    > 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
     
    Mike H, Jan 23, 2009
    #6
  7. Guest

    Thanks a lot for your help guys.
    \
    Adam Bush

    "Mike H" wrote:

    > and just be different
    >
    > X = 0.7
    > Tanh = (Exp(X) - Exp(-X)) / (Exp(X) + Exp(-X))
    >
    >
    > Mike
    >
    > "" wrote:
    >
    > > 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
     
    , Jan 23, 2009
    #7
  8. Toto217

    Joined:
    Mar 15, 2016
    Messages:
    1
    Likes Received:
    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
    #8
    1. Advertisements

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 (here). After that, you can post your question and our members will help you out.
Similar Threads
  1. MarWun
    Replies:
    1
    Views:
    137
    Dick Kusleika
    Aug 6, 2003
  2. dannyboy

    Excel VBA - using yearfrac function in VBA

    dannyboy, Jul 5, 2004, in forum: Excel Programming
    Replies:
    2
    Views:
    139
    dannyboy
    Jul 6, 2004
  3. excelman
    Replies:
    3
    Views:
    242
    sebastienm
    Feb 9, 2006
  4. RB Smissaert

    VBA function to xll function

    RB Smissaert, Apr 14, 2006, in forum: Excel Programming
    Replies:
    1
    Views:
    116
    RB Smissaert
    Apr 14, 2006
  5. Replies:
    0
    Views:
    142
  6. Danny Young

    VBA to VBA Script Function

    Danny Young, Oct 12, 2006, in forum: Excel Programming
    Replies:
    10
    Views:
    182
    Danny Young
    Oct 22, 2006
  7. rael_lucid
    Replies:
    2
    Views:
    127
    rael_lucid
    Sep 28, 2007
  8. gromeg
    Replies:
    9
    Views:
    246
    gromeg
    Mar 13, 2009
Loading...