Writing a user-defined worksheet function

[Guest]

On an MS site, it referred to a document called "Writing a user-defined
worksheet function". I am a long-time Excel user, but don't have VB
experience. I want to create my own finctions that will calculate various
chemical parameters from user input arguements. Can anyone point me int he
right direction?

My needs are pretty simple; I'd like to do things like create a function to
calculate the vapor pressure of water at a specified temperature, so I can
just type "=Pw(25)" to get the answer for 25 °C

Pw=760*EXP(11.8571-(3840.7/(T+273.15))-(216961/(T+273.15)^2))

 

[Guest]

Try this:

Hit Alt-F11
Insert/Module

Paste the following into the code pane that appears. Then, close the VBA
editor and enter =PW(25) in a cell. I get 23.75898 using the formula you
posted.
For more info on VBA see
http://www.mvps.org/dmcritchie/excel/excel.htm
and scroll down to Excel lessons and tutorials (a little over halfway down
the page).


Function PW(T As Double) As Double
PW = 760 * Exp(11.8571 - (3840.7 / (T + 273.15)) - (216961 / (T + 273.15) ^
2))
End Function

 

[merjet]

Put this in a standard module.

Public Function pw(TempC As Double) As Double
pw = 760 * Exp(11.8571 - (3840.7 / (TempC + 273.15)) - (216961 /
(TempC + 273.15) ^ 2))
End Function

Hth,
Merjet

 

[Guest]

Try this:

Hit Alt-F11
Insert/Module

Paste the following into the code pane that appears. Then, close the VBA
editor and enter =PW(25) in a cell. I get 23.75898 using the formula you
posted.
For more info on VBA see
http://www.mvps.org/dmcritchie/excel/excel.htm
and scroll down to Excel lessons and tutorials (a little over halfway down
the page).


Function PW(T As Double) As Double
PW = 760 * Exp(11.8571 - (3840.7 / (T + 273.15)) - (216961 / (T + 273.15) ^
2))
End Function

 

[Guest]

Thanks! It was easier than I thought; I bet I could even figure out how to
do functions with 2 arguements/variables...

Russell

 

[Guest]

What's the difference between a Function and a Public Function?

Thanks!

Russell

 

[Guest]

Public functions can be used by procedures in other modules while private
functions cannot be accessed by procedures in other modules.

If not specified, functions are public by default - so the answer to your
question is there is no difference.

Check VBA help for "Function Statement"