Creating a vba function from a long scientific formula - vba noob needs expertise - #1 of 2

[lolivieri]

Greetings Mr. Excel.

Thanks in advance for your assistance! I am running Microsoft Office Professional Plus 2007 on a Windows XP pro service pack 3 federal machine.

I have a long scientific formula having to do with soil moisture that was given to me by a colleague. In it, he describes the equation, performs some algebra to solve for what we are looking for, and provides me with some vba code to create an excel function. Though I have many years experience in Excel, I have never created functions in vba, although I use formulas of my own creation (and one of yours!), and often use Excel's canned functions to perform a variety of calculations. Below is what he gave me.

"Average values for selected soil retention and hydraulic conductivity parameters for No. 2 G.E. sand texture (Carsel and Parrish, 1988).


θ_r =0.045 cm³ cm³

θ_s =0.43 cm³ cm³

∝ = 0.145 1 cm^-1

n= 2.68 dimensionless

l = 0.5

K_s = 712.8 cm d^-1

S_e = (θ - 0.045)/ (0.43 - 0.045)


Public Function vg_h_fTheta(Se As Double, Alpha As Double, n As Double) As Double
vg_h_fTheta = -(1 / Alpha) * (Se ^ (n / (1 - n)) - 1) ^ (1 / n)
End Function


Public Function vg_h_fTheta(Se As Double, Alpha As Double, n As Double) As Double
vg_h_fTheta = -(1 / 0.145) * (Se ^ (2.68/ (1 - 2.68)) - 1) ^ (1 / 2.68)
End Function"

Where Se is a parameter calculated from the constants above and an observed parameter in our data set.


I have used the red text above as a formula, with a cell reference for the Se parameter, and come up with a value of:-89.7964510987934.

I have also followed simple instructions to create a new module in vba and entered the entire colored text from: Public Function….. End Function, exactly as given. But because I don’t understand vba, I don’t know how to tell it that either Theta (our measured parameter) or Se (a parameter I can calculate in a separate column) needs to be entered in the function. i.e. =vg_h_fTheta(cell reference) will produce the calculated value upon enter. Right now I get a #VALUE error, although the function vg_h_fTheta appears in the function list now.

Two Questions: 1. Do you see any apparent error with the formula that would make a return value of -90 obviously wrong? (parenthesis, exponents, rounding error (As Double?)...etc?) 2. How in the world do I write vba code to produce a function for this that will allow a cell reference input for the Se parameter.

Thank YOU so much, sorry this is really complex. I hope you can help.

Laura

 

[iyyi]

You Enter the code as follows...

Public Function vg_h_fTheta(Se As Double, Alpha As Double, n As Double) As Double
vg_h_fTheta = -(1 / Alpha) * (Se ^ (n / (1 - n)) - 1) ^ (1 / n)
End Function

You use it in your Spreadsheet this way:
=vg_h_fTheta(B1,B2,B3)
where B1:B3 are Se, Alpha and n. Change B1:B3 to the real reference of your parameters.

 

[lolivieri]

Thank you so much!

So, I did this and got:

"compile error: Function call on left-hand side of assignment must return Variant or Object" Which highlights the 'Public Function....As Double' statement in yellow and sets the cursor on the function name.

Now what?

 

[lolivieri]

Nevermind! Thanks so much. I got it. Typo fixed.