Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: #name error in VBA function-

1. I agreed to these rules
Join Date
26th April 2012
Posts
12

## #name error in VBA function-

I have been writing functions in vba for a template for designing heat exchangers. It returns #name errors when i try.
I have several functions I think i may have a problem with most of them. but they are written in such a way that
they depend on each other so that if the first returns an error the rest wont show anything.
The first is for calculating correction factor fc taken from Serth see code. I am stuck, see code.

VB:
```Option Explicit
'Correction Factor calculations for the LMTD
'source Robert R Serth, heat Transfer principles and applications.
'For any number of shell side passes and any even number of tube side passes as follows
'LMTD is calculated as
' N is the nymber of shell side passes

Function Calcfc(Tis As Double, Tos As Double, Tit As Double, Tot As Double, N1 As Double, N2 As Double) As Double

'Correction Factor calculations for the LMTD
'source Robert R Serth, heat Transfer principles and applications.
'For any number of shell side passes and any even number of tube side passes as follows
' N is the nymber of shell side passes
'R P and alpha are ratios used to calculate the correction factor
Dim R As Double, P As Double, S As Double, alpha As Double, n As Double
Dim num As Double, den As Double, F As Double 'the numerators and denominators of the functions to ease writing the functions

'Where  Ta (Tis)= inlet temperature of the shell-side fluid
'Tb (Tos) = outlet temperature of the shell-fluid
'ta (Tit= inlet temperature of the tube side fluid
'tb(Tot) = oulet temperature of the tube side fluid

R = (Tis - Tos) / (Tot - Tit)

P = (Tot - Tit) / (Tis - Tit)

n = N1 * N2

alpha = ((1 - R * P) / (1 - P)) ^ (1 / n)

If R = 1 Then
S = P / (n - (n - 1) * P)
num = (S * 2 ^ 0.5)
den = (1 - S) * WorksheetFunction.Ln((2 - S * (2 - 2 ^ 0.5)) / (2 - S * (2 + 2 ^ 0.5)))

F = num / den

Else
alpha = ((1 - R * P) / (1 - P)) ^ (1 / n)
S = (alpha - 1) / (alpha - R)
num = ((R ^ 2 + 1) ^ 0.5) * WorksheetFunction.Ln((1 - S) / (1 - R * S))
den = (R - 1) * WorksheetFunction.Ln((2 - S * (R + 1 - (R ^ 2 + 1) ^ (1 / 2))) / (2 - S * (R + 1 + (R ^ 2 + 1) ^ (1 / 2))))
F = num / den
End If

Calcfc = F

End Function

```
Any body with any idea.
Thanks

Excel Video Tutorials / Excel Dashboards Reports

2. ## Re: #name error in VBA function-

- Which line throws an error?
- Please add a small attachment to illustrate the problem. This helps people to help you.

3. I agreed to these rules
Join Date
26th April 2012
Posts
12

## Re: #name error in VBA function-

OzMVP
Thanks,
I think that it has something to do with my workbook because when i copy the function
to a different workbook it works very fine. But it throws an error in my workbook on the last line in the
Else block. (F=num/den). My workbook has 28 modules with many functions some within the same module.
I am still new to vba with some fundamental knowledge in java.
I still cant figure out the problem with the workbook
All The Best

Excel Video Tutorials / Excel Dashboards Reports

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno