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