User Defined Formula (UDF) Problem - Results is either 0 or #value error

[Capitan]

Hi,

I'm new to VBA so please bear with me.

I'm trying to create a UDF function that will calculation turbulent friction faction using churchill equation
The arguments include reynolds number, absolute roughness (mm), internal diameter (mm).
I've followed several tutorials in formulating the following code but the only results i get is either 0 or a #value error.

Public Function turbulentFrictonFactor(reynoldsNumber As Double, absoluteRoughness As Double, internalDiameter As Double) As Double


'Constants declaration
Dim constB As Double
Dim constC As Double
Dim denominator1 As Double
Dim denominator2 As Double
Dim denominatorSum As Double
Dim item1 As Double
Dim item2 As Double


'Calculation of constB
denominator1 = (7 / reynoldsNumber) ^ 0.9
denominator2 = 0.27 * (absoluteRoughness / internalDiameter)
denominatorSum = denominator1 + denominator2


constB = (2.457 * Log(1 / denominatorSum)) ^ 16


'Calculation of constC


constC = (37530 / reynoldsNumber) ^ 16


'Calculation of turbulent friction factor.
'where item1 = (8/Re)^12
'where item2 = 1/(B+C)^1.5


item1 = (8 / reynoldsNumber) ^ 12
item2 = 1 / (constB + constC) ^ 1.5


turbulentFrictionFactor = 8 * (item1 + item2) ^ 1 / 12


End Function

I would appreciate if someone can point out where i am going wrong?
Thanks in advance

 

[offthelip]

You have misspelt the name of the function is should be turbulentfrictionfactor, not turbulentfrictonfactor

Another reason for avoiding verylong function names

 

[Leith Ross]

Hello Captain,

You can avoid the spelling issue in the future by placing Option Explicit at the top of your Code Module. This will check that every variable name in the Sub or Function procedure has been declared. If not then a message will alert you.

 

[offthelip]

Option Explicit is a bit like using a spell checker in WORD, it all works fine until you have misspelt a variable as another valid name. Then it gets 10 times worse to find it than without it because one always assumes the names are correct.
It also adds a lot of unneccessary lines of code: defining a list of variables most of which are variants, but might be longs, strings or booleans. Knowing the type of these variable from declarations rarely adds anything to the understanding of good code.
Personally I never use it, I prefer to choose sensible names ( short enough to be able to spot misspellings easily, but long enough to give some meaning) and then add comments to the code. I reckon one line of comment is worth two lines of declarations .
If you don't use option explicit you will get much better at writing code without errors in it because you have to take care with the spelling of variables

 

[RoryA]

Avoiding something that helps in 95% of cases just because of that 5% doesn't make a lot of sense to me, though obviously you can write code however you like. For most people, especially beginners, Option Explicit is generally considered a good idea.

Also, declaring variables is not really about making the code more legible, it's about efficiency and controlling data types. I'd be surprised if most of your variables need to be Variant.

Just my opinion.

 

[Capitan]

Hello all,

I've corrected the spelling error and included Option Explicit but I'm still not getting the answers that i expect.

However when i change the formula to below the function works?

Public Function turbulentFrictonFactor(reynoldsNumber, absoluteRoughness, internalDiameter)


turbulentFrictionFactor = (8 * ((8 / reynoldsNumber) ^ 12 + 1 / ((2.457 * Log(1 / ((7 / reynoldsNumber) ^ 0.9 + (0.27 * absoluteRoughness / internalDiameter)))) ^ 16 + ((37530 / reynoldsNumber) ^ 16)) ^ 1.5) ^ (1 / 12))


End Function

The above code does the job but it makes it harder for someone to check the formula hence my preference for my first proposal

 

[Norie]

Have you tried stepping through the original code?

You can do that by first setting a breakpoint (F9) at the start of the code and then entering the formula in a cell on a worksheet.

P.S. I would try that myself but I've no idea what to use for appropriate input(s).

 

[steve the fish]

Far as i can see the only difference between the UDF calc and the formula calc is this line:

turbulentFrictionFactor = 8 * (item1 + item2) ^ 1 / 12

which should be:

turbulentFrictionFactor = 8 * (item1 + item2) ^ (1 / 12)

 

[offthelip]

I'd be surprised if most of your variables need to be Variant.

I use variants all the time because it is the only type that one can read or write to or from the worksheet. Even if the whole range on the worksheet is full of numbers or strings or dates, excel vba insists that we load it into a variant array.
Most of my vba involves interaction with the worksheet, so I do find most of my variables are variants because they have to be.

it's about efficiency and controlling data types.

Unfortunately because of what I said above, I can't actually control data types in VBa because lots of items which I am thinking of as numbers of strings etc are forced to be variants.
In terms of efficiency of the code the difference between longs, variants and strings is trivial, unless you have a huge number. Looking at this forum I find so many examples where people are dutifully declaring ranges and then looping though the cells, when using more efficient code such as using variant arrays (declared or not) would be much faster. So it is not an "efficient" way of making your code "efficient" I think writing a line of comment instead of each line of declaration is a much better way to code in VBA, it won't make your code more efficient but it will make your code much better code to my way of thinking.
Note: I am not inherently against declaring data types ( I am an ex ADA programmer where it really is useful) , just in Excel VBA I find it doesn't really help because loads of variables have to be variants, which rather destroys the purpose to my thinking.

 

[steve the fish]

Which of the variables in the opening post need to be variant? It isnt helping the question really.