Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Defined Function for Wind Chill
Hello,
I was working on a user-defined function for the Wind Chill Index. Here is what I came up with so far: Function WindChill(WindSpeed_MPH, Temp_F°) WindChill = Format((91.4 - (0.474677 - 0.020425 * WindSpeed_MPH + 0.303107 * WindSpeed_MPH ^ 0.5) * (91.4 - Temp_F°)), "0.0°") End Function I got the formula off the internet. I have seen where you can also have a third parameter to define whether the temperature is Fahrenheit or Celsius, but I won't worry about that as I never use Celsius, but I'm sure that could be done as well. If I were to add it, I would make it that if the third parameter was not used, then it would assume that you were wanting to use Fahrenheit. I was wanting to improve it somewhat. For instance, to put a message right in the cell if two parameters haven't been provided, one or the other was null, or not numeric. Since parameters can come from a cell feeding the parameter, or put directly in the cell with the function, I wasn't sure what was the best way to handle that was. Any ideas? I wasn't able to get isblank(), etc. to work. Also, when the function parameter wizard (I think that's the term) is used, is there any way for me to provide help for that box? Thanks, Carroll Rinehart |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Defined Function for Wind Chill
The normal way it to let excel handle it for you
Function WindChill(WindSpeed_MPH as Double, Temp_F° as Double) Excel would then return #Value! if the values were non numeric and would notify the user during entry if an argument were missing. This is the way other functions work and it would be best to mimic their behavior. If you insist on your approach, you would need to use a paramarray so you can have avariable number of arguments, then loop through it. If you want the optional argument with a default value, then that is explained as well. Look at the Function Statement in Excel VBA Help. highlight the word Function in your function and hit F1. Choose VBA and you should see the help. -- Regards, Tom Ogilvy "Carroll" wrote in message oups.com... Hello, I was working on a user-defined function for the Wind Chill Index. Here is what I came up with so far: Function WindChill(WindSpeed_MPH, Temp_F°) WindChill = Format((91.4 - (0.474677 - 0.020425 * WindSpeed_MPH + 0.303107 * WindSpeed_MPH ^ 0.5) * (91.4 - Temp_F°)), "0.0°") End Function I got the formula off the internet. I have seen where you can also have a third parameter to define whether the temperature is Fahrenheit or Celsius, but I won't worry about that as I never use Celsius, but I'm sure that could be done as well. If I were to add it, I would make it that if the third parameter was not used, then it would assume that you were wanting to use Fahrenheit. I was wanting to improve it somewhat. For instance, to put a message right in the cell if two parameters haven't been provided, one or the other was null, or not numeric. Since parameters can come from a cell feeding the parameter, or put directly in the cell with the function, I wasn't sure what was the best way to handle that was. Any ideas? I wasn't able to get isblank(), etc. to work. Also, when the function parameter wizard (I think that's the term) is used, is there any way for me to provide help for that box? Thanks, Carroll Rinehart |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Defined Function for Wind Chill
Functions in VBA don't reallly support help for the arguments. Laurent
Longre has built a free addin to support this function. It can be downloaded at: VBA functions don't support text descriptions in the Function wizard. http://longre.free.fr/english/index.html#FunCustomize -- Regards, Tom Ogilvy "Carroll" wrote in message oups.com... Hello, I was working on a user-defined function for the Wind Chill Index. Here is what I came up with so far: Function WindChill(WindSpeed_MPH, Temp_F°) WindChill = Format((91.4 - (0.474677 - 0.020425 * WindSpeed_MPH + 0.303107 * WindSpeed_MPH ^ 0.5) * (91.4 - Temp_F°)), "0.0°") End Function I got the formula off the internet. I have seen where you can also have a third parameter to define whether the temperature is Fahrenheit or Celsius, but I won't worry about that as I never use Celsius, but I'm sure that could be done as well. If I were to add it, I would make it that if the third parameter was not used, then it would assume that you were wanting to use Fahrenheit. I was wanting to improve it somewhat. For instance, to put a message right in the cell if two parameters haven't been provided, one or the other was null, or not numeric. Since parameters can come from a cell feeding the parameter, or put directly in the cell with the function, I wasn't sure what was the best way to handle that was. Any ideas? I wasn't able to get isblank(), etc. to work. Also, when the function parameter wizard (I think that's the term) is used, is there any way for me to provide help for that box? Thanks, Carroll Rinehart |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Defined Function for Wind Chill
Not sure, but some references give a newer version of that equation. If so,
here's is one of a few ways... Function WindChill(Temp, MPH, Optional Using_Fahrenheit As Boolean = True) '// = = = = = = = = '// Use False in 3rd argument for Temp in C '// = = = = = = = = Dim V, T V = MPH T = Temp 'Default is in F° '// If need, convert C° to F° If Not Using_Fahrenheit Then T = 32 + 1.8 * T WindChill = 0.4275 * V ^ 0.16 * T + 0.6215 * T - 35.75 * V ^ 0.16 + 35.74 WindChill = Round(WindChill) End Function -- Dana DeLouis Win XP & Office 2003 "Carroll" wrote in message oups.com... Hello, I was working on a user-defined function for the Wind Chill Index. Here is what I came up with so far: Function WindChill(WindSpeed_MPH, Temp_F°) WindChill = Format((91.4 - (0.474677 - 0.020425 * WindSpeed_MPH + 0.303107 * WindSpeed_MPH ^ 0.5) * (91.4 - Temp_F°)), "0.0°") End Function I got the formula off the internet. I have seen where you can also have a third parameter to define whether the temperature is Fahrenheit or Celsius, but I won't worry about that as I never use Celsius, but I'm sure that could be done as well. If I were to add it, I would make it that if the third parameter was not used, then it would assume that you were wanting to use Fahrenheit. I was wanting to improve it somewhat. For instance, to put a message right in the cell if two parameters haven't been provided, one or the other was null, or not numeric. Since parameters can come from a cell feeding the parameter, or put directly in the cell with the function, I wasn't sure what was the best way to handle that was. Any ideas? I wasn't able to get isblank(), etc. to work. Also, when the function parameter wizard (I think that's the term) is used, is there any way for me to provide help for that box? Thanks, Carroll Rinehart |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Defined Function for Wind Chill
Wouldn't you want to convert back to provide an answer in Centigrade of Not
Using_Fahrenheit? -- Regards, Tom Ogilvy "Dana DeLouis" wrote in message ... Not sure, but some references give a newer version of that equation. If so, here's is one of a few ways... Function WindChill(Temp, MPH, Optional Using_Fahrenheit As Boolean = True) '// = = = = = = = = '// Use False in 3rd argument for Temp in C '// = = = = = = = = Dim V, T V = MPH T = Temp 'Default is in F° '// If need, convert C° to F° If Not Using_Fahrenheit Then T = 32 + 1.8 * T WindChill = 0.4275 * V ^ 0.16 * T + 0.6215 * T - 35.75 * V ^ 0.16 + 35.74 WindChill = Round(WindChill) End Function -- Dana DeLouis Win XP & Office 2003 "Carroll" wrote in message oups.com... Hello, I was working on a user-defined function for the Wind Chill Index. Here is what I came up with so far: Function WindChill(WindSpeed_MPH, Temp_F°) WindChill = Format((91.4 - (0.474677 - 0.020425 * WindSpeed_MPH + 0.303107 * WindSpeed_MPH ^ 0.5) * (91.4 - Temp_F°)), "0.0°") End Function I got the formula off the internet. I have seen where you can also have a third parameter to define whether the temperature is Fahrenheit or Celsius, but I won't worry about that as I never use Celsius, but I'm sure that could be done as well. If I were to add it, I would make it that if the third parameter was not used, then it would assume that you were wanting to use Fahrenheit. I was wanting to improve it somewhat. For instance, to put a message right in the cell if two parameters haven't been provided, one or the other was null, or not numeric. Since parameters can come from a cell feeding the parameter, or put directly in the cell with the function, I wasn't sure what was the best way to handle that was. Any ideas? I wasn't able to get isblank(), etc. to work. Also, when the function parameter wizard (I think that's the term) is used, is there any way for me to provide help for that box? Thanks, Carroll Rinehart |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Defined Function for Wind Chill
Tom,
I see you've added "double" to each parameter. I wanted to do something like this, but I've not seen a good discussion of the various data types, when to use, etc. Any links that you are aware of? Carroll |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Defined Function for Wind Chill
Thanks. I remember reading something about not everyone agreeing with
the formula for the Wind Chill Index. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Defined Function for Wind Chill
Here's one link that lists both equations:
http://www.crh.noaa.gov/lsx/vortex/newwindchill.htm#Old -- Dana DeLouis Win XP & Office 2003 "Carroll" wrote in message oups.com... Thanks. I remember reading something about not everyone agreeing with the formula for the Wind Chill Index. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Defined Function for Wind Chill
You ask good questions. Tom has already given you the answer as to how
XL by default would do things. While I usually let XL handle missing arguments I don't like the way XL handles errors -- the error messages, restricted to #NUM!, #VALUE!, and the like are too cryptic for my taste. Here's one way to approach your idea. Function WindChill(optional WindSpeed_MPH, optional Temp_F) as variant if ismissing(windspeed_mph) then windchill="Hey, if you don't give me a windspeed in MPH," _ & " I cannot give you a windchill result!" exit function end if if ismissing(temp_f) then windchill="Tch, tch! If you don't give me a temp in" _ " farenheit, I cannot give you a windchill result!" exit function end if ... A more practical approach, one that I would use for a simple function: Function WindChill(WindSpeed_MPH as double, Temp_F as double, _ optional Use_Celcius as boolean=false) as variant if windspeed_mph<0 then windchill="Say what? You cannot have a negative wind speed!" exit function end if ... -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article .com, says... Hello, I was working on a user-defined function for the Wind Chill Index. Here is what I came up with so far: Function WindChill(WindSpeed_MPH, Temp_F°) WindChill = Format((91.4 - (0.474677 - 0.020425 * WindSpeed_MPH + 0.303107 * WindSpeed_MPH ^ 0.5) * (91.4 - Temp_F°)), "0.0°") End Function I got the formula off the internet. I have seen where you can also have a third parameter to define whether the temperature is Fahrenheit or Celsius, but I won't worry about that as I never use Celsius, but I'm sure that could be done as well. If I were to add it, I would make it that if the third parameter was not used, then it would assume that you were wanting to use Fahrenheit. I was wanting to improve it somewhat. For instance, to put a message right in the cell if two parameters haven't been provided, one or the other was null, or not numeric. Since parameters can come from a cell feeding the parameter, or put directly in the cell with the function, I wasn't sure what was the best way to handle that was. Any ideas? I wasn't able to get isblank(), etc. to work. Also, when the function parameter wizard (I think that's the term) is used, is there any way for me to provide help for that box? Thanks, Carroll Rinehart |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Defined Function for Wind Chill
On 27 Mar 2005 12:31:29 -0800, "Carroll" wrote:
Function WindChill(WindSpeed_MPH, Temp_F°) WindChill = Format((91.4 - (0.474677 - 0.020425 * WindSpeed_MPH + 0.303107 * WindSpeed_MPH ^ 0.5) * (91.4 - Temp_F°)), "0.0°") End Function I got the formula off the internet. The formula you are using is the old definition. A few years ago it was "updated" to : = 35.74 + 0.6215*Temp - 35.75*Wind ^0.16 + 0.4275*Temp*Wind^0.16 for °F For °C I just apply the conversion to the °F result. --ron |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Defined Function for Wind Chill
At this NOAA web site, if you fill in a speed 0-3, you get an error message.
Their function returns the error message... "Winds need to be above 3 MPH and below 110 MPH." Looks like the temperature has a valid range also... "Temperatures need to be above -50 °F and below 50 °F" "Temperatures need be to above -50 °C and below 10 °C" I was messing around with the equation. If working in C°, I believe either of these will work. wc = Round(13.1266666666667 + 0.6215 * t - 12.2611111111111 * v ^ (0.16) + 0.4275 * t * v ^ (0.16)) or wc = Round((5 * (1539 * t - 44140) * v ^ (0.16) + 33 * (339 * t + 7160)) / 18000) -- Dana DeLouis Win XP & Office 2003 "Tushar Mehta" wrote in message om... I know the formula is the same one as on the NOAA web site, but it needs a 'threshold' to be applicable. To see why, just set the wind speed to zero. The NOAA web site implies -- but doesn't explicitly state that it is valid for V = 3. See http://www.erh.noaa.gov/er/iln/tables.htm -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... On 27 Mar 2005 12:31:29 -0800, "Carroll" wrote: Function WindChill(WindSpeed_MPH, Temp_F°) WindChill = Format((91.4 - (0.474677 - 0.020425 * WindSpeed_MPH + 0.303107 * WindSpeed_MPH ^ 0.5) * (91.4 - Temp_F°)), "0.0°") End Function I got the formula off the internet. The formula you are using is the old definition. A few years ago it was "updated" to : = 35.74 + 0.6215*Temp - 35.75*Wind ^0.16 + 0.4275*Temp*Wind^0.16 for °F For °C I just apply the conversion to the °F result. --ron |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Defined Function for Wind Chill
Oops. Here's the web site:
http://www.nws.noaa.gov/om/windchill/ At this NOAA web site, if you fill in a speed 0-3, you get an error message. Their function returns the error message... "Winds need to be above 3 MPH and below 110 MPH." Looks like the temperature has a valid range also... "Temperatures need to be above -50 °F and below 50 °F" "Temperatures need be to above -50 °C and below 10 °C" -- Dana DeLouis Win XP & Office 2003 <snip |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Defined Function for Wind Chill
On Tue, 29 Mar 2005 07:57:35 -0500, Tushar Mehta
wrote: I know the formula is the same one as on the NOAA web site, but it needs a 'threshold' to be applicable. To see why, just set the wind speed to zero. You mean that an OAT of 32°F with no wind is NOT equivalent to a temp in the 60's? It sure seems that way around here (downeast Maine) after a long, cold winter :-)) --ron |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Defined Function for Wind Chill
In article ,
says... {snip} You mean that an OAT of 32°F with no wind is NOT equivalent to a temp in the 60's? It sure seems that way around here (downeast Maine) after a long, cold winter :-)) --ron LOL! Yeah, I know what you mean. Yesterday, the temp. was in the low 40s and people were out in shorts. [No, not me. Walking home from the gym without proper protection is a prescription for problems. {g}] -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
user defined function | Excel Worksheet Functions | |||
user defined function | Excel Worksheet Functions | |||
User Defined Function | Excel Worksheet Functions | |||
User-defined data type; Error: Only User-defined types... | Excel Programming | |||
User-Defined Function pre-empting Built-in Function? How to undo???? | Excel Programming |