Sinh function is giving me an error in VBA

[tcr0732]

I called to excel to bring in the sinh function and i keep getting an error.
code below
Sub Start()

End Sub
Sub heattransfer()
'Variable Declaration
Dim qty, qty2, L, w, T1, T2, x, y, pi, sinh, e As Double
Dim nmax, j, i, r As Integer

'acquiring variable values
T1 = Sheets("main").Cells(4, "D").Value
T2 = Sheets("main").Cells(5, "D").Value
L = Sheets("main").Cells(6, "D").Value
w = Sheets("main").Cells(7, "D").Value
x = Sheets("main").Cells(5, "G").Value
y = Sheets("main").Cells(5, "H").Value
nmax = Sheets("main").Cells(4, "J").Value
'bringing in excel function
sinh = WorksheetFunction.sinh()
pi = WorksheetFunction.pi()


'calculation
For i = 1 To nmax Step 1
'enter heat tansfer function here
qty = qty + (2 / (i * pi)) * (1 - (-1) ^ i) / sinh(i * pi * (w / L))
qty2 = qty
If i > 1 Then
e = Abs((qty - qty2) / qty)
If e <= Sheets("main").Cells(4, "K") Then
Exit For
End If
End If
Next i
Sheets("main").Cells(4, "M").Value = i
Sheets("main").Cells(4, "L").Value = qty

End Sub

 

[joeu2004]

sinh = WorksheetFunction.sinh()
pi = WorksheetFunction.pi()

Ostensibly, you should write:

set sinh = WorksheetFunction.Sinh
set pi = WorksheetFunction.Pi

However, the functons are not objects. So we must write:

set wf = WorksheetFunction

and use wf.Sinh and wf.Pi .

 

[Norie]

You can store the value returned by the worksheet function Pi in a variable as it takes no arguments but Sinh is a function, try this.

Sub heattransfer()
'Variable Declaration
Dim qty#, qty2#, L#, w#, T1#, T2#, x#, y#, pi#, e#
Dim nmax&, j&, i&, r&

    'acquiring variable values
    T1 = Sheets("main").Cells(4, "D").Value
    T2 = Sheets("main").Cells(5, "D").Value
    L = Sheets("main").Cells(6, "D").Value
    w = Sheets("main").Cells(7, "D").Value
    x = Sheets("main").Cells(5, "G").Value
    y = Sheets("main").Cells(5, "H").Value
    nmax = Sheets("main").Cells(4, "J").Value
    'bringing in excel function
    pi = WorksheetFunction.pi()


    'calculation
    For i = 1 To nmax Step 1
        'enter heat tansfer function here
        qty = qty + (2 / (i * pi)) * (1 - (-1) ^ i) / Application.sinh(i * pi * (w / L))
        qty2 = qty
        If i > 1 Then
            e = Abs((qty - qty2) / qty)
            If e <= Sheets("main").Cells(4, "K") Then
                Exit For
            End If
        End If
    Next i
    Sheets("main").Cells(4, "M").Value = i
    Sheets("main").Cells(4, "L").Value = qty

End Sub

[/code]

 

[Rick Rothstein]

You can store the value returned by the worksheet function Pi in a variable as it takes no arguments

pi = WorksheetFunction.pi()

Because it takes no arguments, you could use this shortcut notation instead...

pi = [PI()]

or, because it is so simple to type and just as self-documenting as using a variable named "pi", you could do away with the variable altogether and use [PI()] directly within the code whenever the value for Pi is needed. Of course, if you are using a With WorksheetFunction..End With block, typing .Pi would be simpler.

Alternately, for assigning to a variable named "pi", instead of calling out to the WorksheetFunction object, you could use the old VB standby for Pi...

pi = 4 * Atn(1)

As an aside, if you ever need a value for Pi and would be be okay with an accuracy to 6 decimal places, you can use this easy to remember division...

pi = 355/113

Why is that an easy to remember division? It is the first three odd digits double up...

113355

split in the middle...

113 - 355

with the larger half divided by the smaller half. See... simple to remember.