Writing a user-defined worksheet function
 

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))

Writing a user-defined worksheet function
 

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


"Russell Seguin" wrote:

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))

Writing a user-defined worksheet function
 

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

Writing a user-defined worksheet function
 

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


"Russell Seguin" wrote:

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))

Writing a user-defined worksheet function
 

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

Russell


"JMB" wrote:

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


"Russell Seguin" wrote:

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))

Writing a user-defined worksheet function
 

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

Thanks!

Russell

"merjet" wrote:

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

Writing a user-defined worksheet function
 

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"

"Russell Seguin" wrote:

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

Thanks!

Russell

"merjet" wrote:

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