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

Do you have an known correct answer for 5 inputs so as to test the equation?

Answer 3

Thanks for your assistance Dana I had copied my input incorrectly. I had selected cells for values in the function so I was just inputting the values for the cells by hand. It should have read =find_v(1010,0.00000058,0.64,0.0025,3.77)

I corrected the c mistake and was still having the issues

the original equations used are as follows:

Re=u*D/v where u is the velocity D is a pipe diameter and v is the kinematic viscosity and Re is the Reynolds number

normally in a problem you're given velocity and asked to find the convective coefficient (H) but in a simulation I was running I was given H

The next equation is:

Nu=H*D/k=(f/8)*(Re-1000)*Pr/(1+12.7*(Pr^(2/3)-1)*(f/8)^(1/2)) which is where I derived the hcalc equation

In the above equation: f is a friction factor - equation shown below, Pr is the prandtl number - it's value p input parameter - it's dependent upon temperature of the fluid, and k is the thermal conductivity of the fluid. Nu is the nusselt number which is just given by h*D/k so it really isn't important and I just left it out.

f=1/(.79*ln(Re)-1.64)^2

Without rounding I really didn't see any way in which the equation would be able to stop until it reached the limit of characters for the Single assignment - at least not without a counter.

Any additional info or advice you could provide would be awesome. Thanks very much.

Answer 4

Kevin,

Your function requires five values.  You are only providing four.

I used a value of 2 for the p argument and the function returned 150.

Also, you must provided a data type for each variable or the variable becomes a Variant. So...

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

generates 12 Variants and one Single.

Further...  the c variable is not declared.

You can add Option Explicit as the first line in the module to prevent this.

'---

Jim Cone

https://goo.gl/IUQUN2

Answer 5

Hi.  I can't follow too well.

I would suggest posting the original equation, and what you are trying to solve.

I would then list the known inputs, and an known solution.

Rounding doesn't sound like a good idea in a recursion loop.

We can start with:

find_v(1010, 0.0025, 0.64, 3.77 )

You are passing 4 variables, but the function is expecting 5 inputs.

>  If (c >= 100) Then

I don't believe you defined c anywhere.