 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / April 2007Writing 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)) 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 > 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 [quoted text clipped - 7 lines] > > Pw=760*EXP(11.8571-(3840.7/(T+273.15))-(216961/(T+273.15)^2)) 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 What's the difference between a Function and a Public Function? Thanks! Russell > Put this in a standard module. > [quoted text clipped - 5 lines] > Hth, > Merjet 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" > What's the difference between a Function and a Public Function? > [quoted text clipped - 11 lines] > > Hth, > > Merjet 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 > 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 [quoted text clipped - 7 lines] > > Pw=760*EXP(11.8571-(3840.7/(T+273.15))-(216961/(T+273.15)^2)) Thanks! It was easier than I thought; I bet I could even figure out how to do functions with 2 arguements/variables... Russell > Try this: > [quoted text clipped - 25 lines] > > > > Pw=760*EXP(11.8571-(3840.7/(T+273.15))-(216961/(T+273.15)^2)) |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.