MS Office Excel 2007 12.0.6514.500 SP2 MSO (12.0.6425.1000) XP Pro, ver.2002, SP3 Hello, I've been trying to write a User Defined Function to calculate the Mean Kinetic Temperature (a weighted average temperature). Its returning a #Value! I can do the calculations fine in the worksheet using array formulas and dynamic named ranges, but not in VBA. I'm pretty sure it's to do with the way I'm using the array. Currently I've been testing it with a single column of temperature data. But, it will be used with many columns of data (normally 12, sometimes less, occasionally up to 36). I'm very much a novice and haven't used arrays before.
Mean Kinetic Temperature. My approach, assuming you are starting with a column of temperatures, would be: 1) calculate k at each temperature in an adjacent column 2) use the =AVERAGE () function on this column to get the average k 3) Algebraically rearrange the Arrhenius equation to get T at k, and use that formula in Excel to get the mean kinetic temperature. May 7, 2014 - One very interesting Q&A refers to Mean Kinetic Temperature Calculations (MKT). Mean kinetic temperature is a simplified way of expressing.
For info: I'll be adding the option of converting it to and from degrees C, F and Kelvin just for completeness. The data is presented in columns of variable length (from a few thousand to 10s of thousands of lines). Do I need to the code to work out how big the array is and how many dimensions it requires before I do the calculations? The function will be used in its simplest form like: =MKT(B2:B32000) or MKT(YValues1) or =MKT(B2:M32000) or =MKT(YValues1:YValues12) Merry Christmas (etc)! Code: Function MKT(arr As Double, Optional TemperatureFormatResults As Variant, Optional TemperatureFormatData As Variant) 'Mean Kinetic Temperature 'Results reported in degrees Celsius by default 'Data array must be currently in degrees Celsius 'Conv is the conversion factor from Kelvin to degrees C 'GasConst is the gas constant 8.314472(15)JK−1mol−1 'DeltaH is the is the activation energy (typically within 60–100 kJmol-1 for solids or liquids) 'I'll add the option to change this from the default value. Dim i, N As Long Dim Sum, GasConst, DeltaH, Conv As Double If IsMissing(TemperatureFormatResults) Then TemperatureFormatResults = 'C' If IsMissing(TemperatureFormatData) Then TemperatureFormatData = 'C' Conv = 273.15 GasConst = 8.314472 DeltaH = 10.
![Examples Examples](http://opentextbc.ca/introductorychemistry/wp-content/uploads/sites/17/2014/01/Distribution-of-molecular-speeds-of-O2-gas-at-three-temperatures.jpg)
GasConst N = UBound(arr) Sum = 0 Select Case UCase(TemperatureFormatResults) 'Might be tidier to just calculate in K then convert result? 'Results in Celsius Case Is = 'C' For i = 1 To N Sum = Sum + Exp(-DeltaH / (GasConst. (arr(i) + Conv))) Next i MKT = (DeltaH / GasConst) / (-Log(Sum / N)) - Conv Exit Function 'Results in Kelvin Case Is = 'K' For i = 1 To N Sum = Sum + Exp(-DeltaH / (GasConst. (arr(i) + Conv))) Next i MKT = (DeltaH / GasConst) / (-Log(Sum / N)) Exit Function 'Results in Fahrenheit Case Is = 'F' For i = 1 To N Sum = Sum + Exp(-DeltaH / (GasConst.
(arr(i) + Conv))) Next i MKT = 9. ((DeltaH / GasConst) / (-Log(Sum / N)) - Conv) / 5 + 32 Exit Function End Select End Function. Code: Function MKT(rng As Range, Optional TemperatureFormatResults As Variant, Optional TemperatureFormatData As Variant) 'Mean Kinetic Temperature 'Results reported in degrees Celsius by default 'Data array must be currently in degrees Celsius 'Conv is the conversion factor from Kelvin to degrees C 'GasConst is the gas constant 8.314472(15)JK-1mol-1 'DeltaH is the is the activation energy (typically within 60–100 kJmol-1 for solids or liquids) 'I'll add the option to change this from the default value.
Dim i, N As Long 'note only the last variable here is defined as Long, i is a variant Dim Sum, GasConst, DeltaH, Conv As Double 'note only the last variable here is defined as Double, the rest are variants Dim cell As Range If IsMissing(TemperatureFormatResults) Then TemperatureFormatResults = 'C' If IsMissing(TemperatureFormatData) Then TemperatureFormatData = 'C' Conv = 273.15 GasConst = 8.314472 DeltaH = 10. GasConst 'N = UBound(arr) Sum = 0 Select Case UCase(TemperatureFormatResults) 'Might be tidier to just calculate in K then convert result? Code: Function MKT(rng As Range, Optional TemperatureFormatResults As Variant, Optional TemperatureFormatData As Variant) As Double 'Mean Kinetic Temperature 'Results reported in degrees Celsius by default 'Data array must be currently in degrees Celsius 'Conv is the conversion factor from Kelvin to degrees C 'GasConst is the gas constant 8.314472(15)JK-1mol-1 'DeltaH is the is the activation energy (typically within 60–100 kJmol-1 for solids or liquids) 'I'll add the option to change this from the default value.
Dim I As Long Dim N As Long Dim Sum As Double Dim GasConst As Double Dim DeltaH As Double Dim Conv As Double Dim arr If IsMissing(TemperatureFormatResults) Then TemperatureFormatResults = 'C' If IsMissing(TemperatureFormatData) Then TemperatureFormatData = 'C' Conv = 273.15 GasConst = 8.314472 DeltaH = 10. GasConst arr = rng.ValueNote I don't even think the orignal code would get past the function header - it took me quite some time to get anything working. And I only took it as far as what I posted since I have no idea what mean kinetic whatevers are. John Arrays and ranges are totally different things, in this context anyway.
A range is an object, whereas an array is collection of items - normally values but could be of other things. I think what might be confusing is the use of the term 'array formula'. The main problem with you originall code was could have been that VBA is not going to accept a range as a parameter when you've specified an array. It can be quite particular of data types for the paramaters that are being passed.
![Definition Definition](http://gubbagroup.com/wp-content/uploads/2016/01/pharma-300x140.jpg)
As for the #REF! Error, that might not actually be a problem with the code - it could be a problem with the data or ranges you are trying to work with. Best way to find out would be to debug the code. Start by setting up a breakpoint somewhere in it (F9), then goto the worksheet and enter a formula that uses the UDF. The debugger should then goto the code at the specified breakpoint, and you can then step through it using F8.
You can also set up other breakpoints, create watches etc. Another idea would be to try starting small, in the examples you posted the ranges were covering a fair range of data.