Excel VBA Error Message for User Defined Function

Question

Hey Everyone, Just learning VBA this week so my formatting isn't great. I'd appreciate any advice. I've been trying to build a function in VBA which would calculate velocity of a fluid based on input conditions based on temperature. This is something that's extremely time intensive to do by hand especially when I need to do it for several thousand data sets. It's pretty much a guess and check solver focusing on one specific condition h (convective coefficient for any ME nerds) and if that condition is met, it is supposed to return the velocity value u used to find the calculated value of h within the function. Given that decimal places are important for some variables and not others, especially when multiplied by values as low as 10^-9, I formatted all variables as singles and then used the round function to limit the number of decimal places. When I was going to solve the function excel kept crashing, so I wrote in a counter for my while loops in the hopes that once the loops went through a certain number of iterations, if the value had not been found, the function would return the closest found value for u. I originally started the counter at 0 and wrote count=count+1 at the beginning of the while loop. This was giving an error message so I wasn't sure if a variable could be reassigned as some sort of expression of itself so I created another variable "lc" to change the value of the counter. This is where I am currently at and I'm still receiving an error message when I plug values in that should result in an answer I already know. I'm at my wits end with this and don't know where I'm making a mistake. If anyone is willing to give their time, could you please let me know if there is an error with my syntax that is preventing the function from processing? Much appreciated. Here's my code.

so in the cell in my excel spreadsheet I was calling =find_v(1010,0.0025,0.64,3.77) then below is the code in the module for find_v

Public Function find_v(H As Single, v As Single, k As Single, d As Single, p As Single) As Single

Dim u, hcalc, Re, nR, nu, f, nf, nhcalc, L, M, lv, count, lc As Single

u = 150

Re = u * d / v

nR = Round(Re, 1)

Re = nR

f = 1 / (0.79 * Log(Re) - 1.64) ^ 2

nf = Round(f, 3)

f = nf

L = 0

M = u

count = 0

hcalc = (f / 8) * (Re - 1000) * p * k / (d + 12.7 * d * (-1 + p ^ (2 / 3)) * (f / 8) ^ (1 / 2))

nhcalc = Round(hcalc, 1)

Do While hcalc <> H

lv = u

u = (M + L) / 2

nu = Round(u, 3)

u = nu

lc = count

count = lc + 1

If (count = 100) Then

hcalc = H

ElseIf (hcalc > H) Then

M = lv

Re = u * d / v

f = 1 / (0.79 * Log(Re) - 1.64) ^ 2

nf = Round(f, 3)

f = nf

hcalc = (f / 8) * (Re - 1000) * p * k / (d + 12.7 * d * (-1 + p ^ (2 / 3)) * (f / 8) ^ (1 / 2))

nhcalc = Round(hcalc, 1)

hcalc = nhcalc

ElseIf (hcalc < H) Then

L = lv

Re = u * d / v

f = 1 / (0.79 * Log(Re) - 1.64) ^ 2

nf = Round(f, 3)

f = nf

hcalc = (f / 8) * (Re - 1000) * p * k / (d + 12.7 * d * (-1 + p ^ (2 / 3)) * (f / 8) ^ (1 / 2))

nhcalc = Round(hcalc, 1)

hcalc = nhcalc

End If   

Loop

If (Re < 4500) Then

f = (3.03 * (10 ^ -12) * Re ^ 3) - (3.67 * (10 ^ -8) * Re ^ 2) + (1.46 * (10 ^ -4) * Re) - 0.151

nf = Round(f, 3)

f = nf

hcalc = (f / 8) * (Re - 1000) * p * k / (d + 12.7 * d * (-1 + p ^ (2 / 3)) * (f / 8) ^ (1 / 2))

nhcalc = Round(hcalc, 1)

hcalc = nhcalc

L = 0

M = 10

count = 0

Do While hcalc <> H

lv = u

u = (M + L) / 2

nu = Round(u, 3)

u = nu

lc = count

count = lc + 1

If (c >= 100) Then

hcalc = H

ElseIf (hcalc > H) Then

M = lv

Re = u * d / v

f = (3.03 * (10 ^ -12) * Re ^ 3) - (3.67 * (10 ^ -8) * Re ^ 2) + (1.46 * (10 ^ -4) * Re) - 0.151

nf = Round(f, 3)

f = nf

hcalc = (f / 8) * (Re - 1000) * p * k / (d + 12.7 * d * (-1 + p ^ (2 / 3)) * (f / 8) ^ (1 / 2))

nhcalc = Round(hcalc, 1)

hcalc = nhcalc

ElseIf (hcalc < H) Then

L = lv

Re = u * d / v

       f = (3.03 * (10 ^ -12) * Re ^ 3) - (3.67 * (10 ^ -8) * Re ^ 2) + (1.46 * (10 ^ -4) * Re) - 0.151

nf = Round(f, 3)

f = nf

hcalc = (f / 8) * (Re - 1000) * p * k / (d + 12.7 * d * (-1 + p ^ (2 / 3)) * (f / 8) ^ (1 / 2))

nhcalc = Round(hcalc, 1)

hcalc = nhcalc

End If

Loop   

End If

find_v = u

End Function

Answer 1

Ok.  Let me start over.

Your equations were off, and I was missing some zeros in v.

Let's do a simple check on what we think is correct:

h=1010;

v=.00000058;

k=.64;

d=.025;

p=3.77;

If we solve for u, we get 5.973.., which is close to your stated value of 6.

u=5.97307890452143;

re=u*d/v

257460.

f=1/(0.79*Log(re)-1.64)^2

0.0148637

(f/8)*(re-1000)*p/(1+12.7*(p^(2/3)-1)*(f/8)^(1/2))

1010.

It "appears" to match the h value in your vba code, but it doesn't match the value in the new equation.

Is it suppose to match h instead of h*d/k ??

In the above, I assume we are trying to solve for u and get 5.97...

Is that correct?

And not:

h*d/k

39.4531

= = = = = = =

So, the other question:  What is:

 f = (3.03 * (10 ^ -12) * Re ^ 3) - (3.67 * (10 ^ -8) * Re ^ 2) + (1.46 * (10 ^ -4) * Re) - 0.151

Answer 2

Hi.  If interested in further study, let's try this in vba to see if we can get 5.97307...

I made this a sub, and not a function, but it's easy to convert.

We take the zero-function of what we found earlier.

We have a lot of constants, so we group as many as we can together.

Within the loop, we factor some common calculations to shrink the size of the vba line.

So, with this method, if we start with a guess of 150 like you did in your code, the next guess will overshoot it's guess, and pick a number that is slightly less than zero.  This will trigger an error in the next loop.  (ie Log(-x)  )

So, we would have to build in a check to see that we don't go negative.

So, we could try 150, get an error, and change the start to 90.  That too will go neg.  We then try 65.

No error here, so now it converges in only 7 steps.

Again, something for you to look at.

Sub Demo()

    ' Function inputs

    Dim h, v, k, d, p

    h = 1010

    v = 0.00000058

    k = 0.64

    d = 0.025

    p = 3.77

    Dim Nu, De

    'constants

    Dim k1, k2, k3

    ' Loop temp variables

    Dim k4, k5

    Dim C As Long ' Counter

    Dim Old As Double

    Dim U As Double

    'Group some constants here

    k1 = -1250000 * p

    k2 = (1250 * d * p) / v

    k3 = 5474560350# * (p ^ (2 / 3) - 1) / 12192437

    Old = 0

    U = 65

    Debug.Print U

    Do While Old <> U And C <= 20

        Old = U

        k4 = Log(d * U / v)

        k5 = k3 * Sqr(1 / (164 - 79 * k4) ^ 2)

        Nu = (79 * k4 - 164) ^ 3 * (k5 + 1) ^ 2 * U * (h - (k1 + k2 * U) / ((164 - 79 * k4) ^ 2 * (k5 + 1)))

        De = 79 * k1 * (k5 + 2) + k2 * U * (-(79 * k4 * (k5 + 1)) + 243 * k5 + 322)

        U = U - Nu / De

        Debug.Print U

        C = C + 1

    Loop

    Select Case d * U / v

    Case Is >= 4800

        Debug.Print "Re is ok"

    Case Else

        Debug.Print "Re is not ok.  Using other equation"

        ' Call other equation

    End Select

End Sub

Starting with 65, it converged to the solution in only 7 steps.

65

 9.48077481721725E-02

 3.36485375272576

 5.86076491979712

 5.97293255059299

 5.97307890427606

 5.97307890452143

 5.97307890452143

Re is ok

========================================

To understand recursion, one must first understand recursion.

========================================

Answer 3

Dana, really appreciate your comments. The reason why there are two functions for f is that when the Reynolds number("re") gets below 4800, the flow of the fluid changes from turbulent to transitional. As a result it affects the convective heat transfer coefficient. it is supposed to match h instead of h*d/k as I'm trying to match the theoretical h to the one I have. Thanks again, I'll take note of your changes.