PDA

View Full Version : Help with Function



nutttom
10-07-2015, 03:04 AM
Hi can someone please help with my VBA function:
The WB Function is return a #VALUE! error and I can't understand way. Here is the code:
Thank you Tom

'========================================================================== ==========================================='
' Constants
'========================================================================== ==========================================='
Const CONST_01 = -5674.5359
Const CONST_02 = 6.3925247
Const CONST_03 = -0.009677843
Const CONST_04 = 0.000000622157
Const CONST_05 = 2.0747825E-09
Const CONST_06 = -9.484024E-13
Const CONST_07 = 4.1635019
Const CONST_08 = -5800.2206
Const CONST_09 = 1.3914993
Const CONST_10 = -0.048640239
Const CONST_11 = 0.000041764768
Const CONST_12 = -0.000000014452093
Const CONST_13 = 6.5459673
Const CONST_14 = 6.54
Const CONST_15 = 14.526
Const CONST_16 = 0.7389
Const CONST_17 = 0.09486
Const CONST_18 = 0.4569

Const R_WATER = 18.015268
Const R_AIR = 28.964546
'1) Equation 1: Get humidity from paritial vapour pressure and saturated vapour pressure.
Function get_humidity(ByVal paritial_vapour_pressure As Variant, ByVal saturated_vapour_pressure As Variant)
get_humidity = (paritial_vapour_pressure / saturated_vapour_pressure) * 100

End Function
'5) Equation 5: Get saturated vapour pressure from dry bulb.
Function get_saturated_vapour_pressure(ByVal dry_bulb As Variant)
If dry_bulb >= 0 Then
get_saturated_vapour_pressure = (Exp(CONST_08 / (dry_bulb + 273.15) + CONST_09 + CONST_10 * (dry_bulb + 273.15) + CONST_11 * (dry_bulb + 273.15) ^ 2 + CONST_12 * (dry_bulb + 273.15) ^ 3 + CONST_13 * Log((dry_bulb + 273.15)))) / 1000
Else:
get_saturated_vapour_pressure = (Exp(CONST_01 / (dry_bulb + 273.15) + CONST_02 + CONST_03 * (dry_bulb + 273.15) + CONST_04 * (dry_bulb + 273.15) ^ 2 + CONST_05 * (dry_bulb + 273.15) ^ 3 + CONST_06 * (dry_bulb + 273.15) ^ 4 + CONST_07 * Log((dry_bulb + 273.15)))) / 1000
End If
End Function
'6) Equation 6: Get humidity ratio from dry bulb vapour pressure and pressure.
Function get_humidity_ratio(ByVal dry_bulb_vapour_pressure As Variant, ByVal pressure As Variant)
get_humidity_ratio = R_WATER / R_AIR * dry_bulb_vapour_pressure / (pressure - dry_bulb_vapour_pressure)

End Function
'7) Equation 7: Get moisture content from wet buld, humidity ratio and dry bulb.
Function get_moisture_content(ByVal wet_bulb As Variant, ByVal humidity_ratio As Variant, ByVal dry_bulb As Variant)
If dry_bulb <= 0 Then
get_moisture_content = ((2830 - 0.24 * wet_bulb) * humidity_ratio - 1.006 * (dry_bulb - wet_bulb)) / (2830 + 1.86 * dry_bulb - 2.1 * wet_bulb)
Else:
get_moisture_content = ((2501 - 2.326 * wet_bulb) * humidity_ratio - 1.006 * (dry_bulb - wet_bulb)) / (2501 + 1.86 * dry_bulb - 4.186 * wet_bulb)

End If

End Function
'8) Equation 8: Get dry bulb vapour pressure from wet bulb.
Function get_dry_bulb_vapour_pressure(ByVal wet_bulb As Variant)
If (wet_bulb + 273.15) >= 0 Then
get_dry_bulb_vapour_pressure = Exp(CONST_08 / (wet_bulb + 273.15) + CONST_09 + CONST_10 * (wet_bulb + 273.15) + CONST_11 * (wet_bulb + 273.15) ^ 2 + CONST_12 * (wet_bulb + 273.15) ^ 3 + CONST_13 * Log((wet_bulb + 273.15))) / 1000
Else:
get_dry_bulb_vapour_pressure = Exp(CONST_01 / (wet_bulb + 273.15) + CONST_02 + CONST_03 * (wet_bulb + 273.15) + CONST_04 * (wet_bulb + 273.15) ^ 2 + CONST_05 * (wet_bulb + 273.15) ^ 3 + CONST_06 * (wet_bulb + 273.15) ^ 4 + CONST_07 * Log((wet_bulb + 273.15))) / 1000
End If
End Function
'9) Equation 9: Get partial vapour pressure from pressure and moisture content.
Function get_partial_vapour_pressure(ByVal pressure As Variant, ByVal moisture_content As Variant)
get_partial_vapour_pressure = (pressure * moisture_content) / (0.621945 + moisture_content)

End Function

'10) Equation 10: Get enthalpy from dry bulb and moisture content.
Function get_enthalpy(ByVal dry_bulb As Variant, ByVal moisture_content As Variant)
get_enthalpy = 1.006 * dry_bulb + moisture_content * (2501 + 1.86 * dry_bulb)

End Function
'11) Equation 11: Get pressure from altitude.
Function get_pressure(ByVal altitude As Variant):
get_pressure = 101.325 * (1 - 0.0000225577 * altitude) ^ 5.2559

End Function
'12) Equation 12: Get specific volume from dry bulb, moisture content and pressure.
Function get_specific_volume(ByVal dry_bulb As Variant, ByVal moisture_content As Variant, ByVal pressure As Variant):
get_specific_volume = (0.287042 * (dry_bulb + 273.15)) * (1 + 1.607858 * moisture_content) / pressure

End Function
'13) Equation 13: Get dew point temperature from partial vapour pressure.
Function get_dew_point_temperature(ByVal partial_vapour_pressure As Variant)
If dry_bulb <= 93 And dry_bulb >= 0 Then

get_dew_point_temperature = 6.54 + 14.526 * Log(partial_vapour_pressure) + 0.7389 * Log(partial_vapour_pressure) * Log(partial_vapour_pressure) + 0.09486 * Log(partial_vapour_pressure) ^ 3 + 0.4569 * partial_vapour_pressure ^ 0.1984

ElseIf dry_bulb < 0 Then

get_dew_point_temperature = 6.09 + 12.608 * Log(partial_vapour_pressure) + 0.4959 * Log(partial_vapour_pressure) * Log(partial_vapour_pressure)

Else
get_dew_point_temperature = "Error"

End If

End Function

Function hum_acc(ByVal humidity As Variant, ByVal humidity_loop As Variant)
If (humidity / humidity_loop) < 1.1 And (humidity / humidity_loop) > 0.9 Then
hum_acc = True
Else
hum_acc = False
End If
End Function

Function WB(ByVal altitude As Variant, ByVal dry_bulb As Variant, ByVal humidity As Variant) As Variant
Dim wet_bulb As Variant
Dim wb_loop As Variant
Dim saturated_vapour_pressure As Variant
Dim pressure As Variant
Dim dry_bulb_vapour_pressure As Variant
Dim humidity_ratio As Variant
Dim moisture_content As Variant
Dim partial_vapour_pressure As Variant
Dim hum_ok As Boolean
wb_loop = 0.001
wet_bulb = dry_bulb
Do
wet_bulb = wet_bulb - wb_loop
saturated_vapour_pressure = get_saturated_vapour_pressure(dry_bulb)
pressure = get_pressure(altitude)
dry_bulb_vapour_pressure = get_dry_bulb_vapour_pressure(wet_bulb)
humidity_ratio = get_humidity_ratio(dry_bulb_vapour_pressure, pressure)
moisture_content = get_moisture_content(wet_bulb, humidity_ratio, dry_bulb)
partial_vapour_pressure = get_partial_vapour_pressure(pressure, moisture_content)
humidity_loop = get_humidity(paritial_vapour_pressure, saturated_vapour_pressure)
hum_ok = hum_acc(humidity, humidity_loop)
Loop Until hum_ok = True
WB = 1000
End Function

Aflatoon
10-07-2015, 03:21 AM
How are you calling it?

Have you tried stepping through it to see where the error occurs?

nutttom
10-07-2015, 03:36 AM
How are you calling it?

Have you tried stepping through it to see where the error occurs?


Hi thanks for your quick response!

yes stepped through it and error occurs here:
Function hum_acc(ByVal humidity As Variant, ByVal humidity_loop As Variant)
If (humidity / humidity_loop) <= 1.1 And (humidity / humidity_loop) >= 0.9 Then
hum_acc = True
Else
hum_acc = False

The whole function is called via a the user defined function in excel spread.

Aflatoon
10-07-2015, 03:53 AM
What values are you passing to it?

And what are the values of humidity and humidity_loop when the function fails?

Also, why are you using Variants for what should clearly be numeric variables?

nutttom
10-07-2015, 04:12 AM
What values are you passing to it?

And what are the values of humidity and humidity_loop when the function fails?

Also, why are you using Variants for what should clearly be numeric variables?


The value of humidity is constant, set by the input value within the function.

The value of humidity_loop varies.

The idea is that the wet_bulb decreases slowly (by 0.001) until the functions that create the value of humidity_loop is near enough equal to humidity.

Then the Function should return the wet_bulb (I know it returns 1000 now but that was just to test).

For example if the value of humidity is 50 and dry_bulb is 20 then the loop should run until wet_bulb is found.

I'm very new to writing code sorry I'm very much a beginner. I have successfully written this code in Python but struggling within VBA.

Not sure why I'm using variant tba :/ just thought that was the correct way to do it.

Thanks you for helping

Aflatoon
10-07-2015, 04:36 AM
What I am asking is:
1. What are the initial values you pass to the WB function from the cell?
2. what are the actual values of humidity and humidity_loop when the function fails?

nutttom
10-07-2015, 04:52 AM
What I am asking is:
1. What are the initial values you pass to the WB function from the cell?
2. what are the actual values of humidity and humidity_loop when the function fails?

Sorry Aflatoon

1. and example would be

altitude = 0
dry_bulb = 20
humidity = 50

therefore:

wet_bulb = 19.999



humidity loop
99.99596024


partial vp
2.338709218


press
101.325


moisure c
0.014694444


humid ratio
0.014694865


dry bulb vp
2.338658829


sat vp
2.3388037




and there therefore the loop should fail and re run until humidity_loop = humidity within 0.001

i.e next wet_bulb would be 19.998 and so on..

for each wet_bulb value the above values should change to suit.

Aflatoon
10-07-2015, 05:01 AM
You need to be using Option Explicit at the top of your code. That would have alerted you to the fact that you misspelled a variable in the WB routine:


humidity_loop = get_humidity(paritial_vapour_pressure, saturated_vapour_pressure)
should read:

humidity_loop = get_humidity(partial_vapour_pressure, saturated_vapour_pressure)

Note the partial versus paritial

nutttom
10-07-2015, 05:18 AM
You need to be using Option Explicit at the top of your code. That would have alerted you to the fact that you misspelled a variable in the WB routine:


humidity_loop = get_humidity(paritial_vapour_pressure, saturated_vapour_pressure)
should read:

humidity_loop = get_humidity(partial_vapour_pressure, saturated_vapour_pressure)

Note the partial versus paritial

Thank you!!!

Although it still returns the same error and it seems to fail at the loop again :banghead:

Aflatoon
10-07-2015, 05:27 AM
It returns 1000 for me after fixing that.

nutttom
10-07-2015, 05:38 AM
It returns 1000 for me after fixing that.

I change the WB = 1000 to WB = wet_bulb as the is what it should do.

i.e. the wet_bulb used to calc the humidity_loop soon as the loop is finished

the WB = 1000 was just to test if the loop works.

I think I/we have got the loop working now but when I use the revised code it returns 0 :(

Again... I appreciated your help.

updatedcode:


' Constants
'========================================================================== ==========================================='
Const CONST_01 = -5674.5359
Const CONST_02 = 6.3925247
Const CONST_03 = -0.009677843
Const CONST_04 = 0.000000622157
Const CONST_05 = 2.0747825E-09
Const CONST_06 = -9.484024E-13
Const CONST_07 = 4.1635019
Const CONST_08 = -5800.2206
Const CONST_09 = 1.3914993
Const CONST_10 = -0.048640239
Const CONST_11 = 0.000041764768
Const CONST_12 = -0.000000014452093
Const CONST_13 = 6.5459673
Const CONST_14 = 6.54
Const CONST_15 = 14.526
Const CONST_16 = 0.7389
Const CONST_17 = 0.09486
Const CONST_18 = 0.4569

Const R_WATER = 18.015268
Const R_AIR = 28.964546
'1) Equation 1: Get humidity from paritial vapour pressure and saturated vapour pressure.
Function get_humidity(ByVal paritial_vapour_pressure As Variant, ByVal saturated_vapour_pressure As Variant)
get_humidity = (paritial_vapour_pressure / saturated_vapour_pressure) * 100

End Function
'5) Equation 5: Get saturated vapour pressure from dry bulb.
Function get_saturated_vapour_pressure(ByVal dry_bulb As Variant)
If dry_bulb >= 0 Then
get_saturated_vapour_pressure = (Exp(CONST_08 / (dry_bulb + 273.15) + CONST_09 + CONST_10 * (dry_bulb + 273.15) + CONST_11 * (dry_bulb + 273.15) ^ 2 + CONST_12 * (dry_bulb + 273.15) ^ 3 + CONST_13 * Log((dry_bulb + 273.15)))) / 1000
Else:
get_saturated_vapour_pressure = (Exp(CONST_01 / (dry_bulb + 273.15) + CONST_02 + CONST_03 * (dry_bulb + 273.15) + CONST_04 * (dry_bulb + 273.15) ^ 2 + CONST_05 * (dry_bulb + 273.15) ^ 3 + CONST_06 * (dry_bulb + 273.15) ^ 4 + CONST_07 * Log((dry_bulb + 273.15)))) / 1000
End If
End Function
'6) Equation 6: Get humidity ratio from dry bulb vapour pressure and pressure.
Function get_humidity_ratio(ByVal dry_bulb_vapour_pressure As Variant, ByVal pressure As Variant)
get_humidity_ratio = R_WATER / R_AIR * dry_bulb_vapour_pressure / (pressure - dry_bulb_vapour_pressure)

End Function
'7) Equation 7: Get moisture content from wet buld, humidity ratio and dry bulb.
Function get_moisture_content(ByVal wet_bulb As Variant, ByVal humidity_ratio As Variant, ByVal dry_bulb As Variant)
If dry_bulb <= 0 Then
get_moisture_content = ((2830 - 0.24 * wet_bulb) * humidity_ratio - 1.006 * (dry_bulb - wet_bulb)) / (2830 + 1.86 * dry_bulb - 2.1 * wet_bulb)
Else:
get_moisture_content = ((2501 - 2.326 * wet_bulb) * humidity_ratio - 1.006 * (dry_bulb - wet_bulb)) / (2501 + 1.86 * dry_bulb - 4.186 * wet_bulb)

End If

End Function
'8) Equation 8: Get dry bulb vapour pressure from wet bulb.
Function get_dry_bulb_vapour_pressure(ByVal wet_bulb As Variant)
If (wet_bulb + 273.15) >= 0 Then
get_dry_bulb_vapour_pressure = Exp(CONST_08 / (wet_bulb + 273.15) + CONST_09 + CONST_10 * (wet_bulb + 273.15) + CONST_11 * (wet_bulb + 273.15) ^ 2 + CONST_12 * (wet_bulb + 273.15) ^ 3 + CONST_13 * Log((wet_bulb + 273.15))) / 1000
Else:
get_dry_bulb_vapour_pressure = Exp(CONST_01 / (wet_bulb + 273.15) + CONST_02 + CONST_03 * (wet_bulb + 273.15) + CONST_04 * (wet_bulb + 273.15) ^ 2 + CONST_05 * (wet_bulb + 273.15) ^ 3 + CONST_06 * (wet_bulb + 273.15) ^ 4 + CONST_07 * Log((wet_bulb + 273.15))) / 1000
End If
End Function
'9) Equation 9: Get partial vapour pressure from pressure and moisture content.
Function get_partial_vapour_pressure(ByVal pressure As Variant, ByVal moisture_content As Variant)
get_partial_vapour_pressure = (pressure * moisture_content) / (0.621945 + moisture_content)

End Function

'10) Equation 10: Get enthalpy from dry bulb and moisture content.
Function get_enthalpy(ByVal dry_bulb As Variant, ByVal moisture_content As Variant)
get_enthalpy = 1.006 * dry_bulb + moisture_content * (2501 + 1.86 * dry_bulb)

End Function
'11) Equation 11: Get pressure from altitude.
Function get_pressure(ByVal altitude As Variant):
get_pressure = 101.325 * (1 - 0.0000225577 * altitude) ^ 5.2559

End Function
'12) Equation 12: Get specific volume from dry bulb, moisture content and pressure.
Function get_specific_volume(ByVal dry_bulb As Variant, ByVal moisture_content As Variant, ByVal pressure As Variant):
get_specific_volume = (0.287042 * (dry_bulb + 273.15)) * (1 + 1.607858 * moisture_content) / pressure

End Function
'13) Equation 13: Get dew point temperature from partial vapour pressure.
Function get_dew_point_temperature(ByVal partial_vapour_pressure As Variant)
If dry_bulb <= 93 And dry_bulb >= 0 Then

get_dew_point_temperature = 6.54 + 14.526 * Log(partial_vapour_pressure) + 0.7389 * Log(partial_vapour_pressure) * Log(partial_vapour_pressure) + 0.09486 * Log(partial_vapour_pressure) ^ 3 + 0.4569 * partial_vapour_pressure ^ 0.1984

ElseIf dry_bulb < 0 Then

get_dew_point_temperature = 6.09 + 12.608 * Log(partial_vapour_pressure) + 0.4959 * Log(partial_vapour_pressure) * Log(partial_vapour_pressure)

Else
get_dew_point_temperature = "Error"

End If

End Function

Function WB(ByVal altitude As Variant, ByVal dry_bulb As Variant, ByVal humidity As Variant) As Variant
Dim wet_bulb As Variant
Dim wb_loop As Variant
Dim saturated_vapour_pressure As Variant
Dim pressure As Variant
Dim dry_bulb_vapour_pressure As Variant
Dim humidity_ratio As Variant
Dim moisture_content As Variant
Dim partial_vapour_pressure As Variant
Dim humidity_loop As Variant
wb_loop = 0.1
wet_bulb = dry_bulb
Do While humidity_loop + 1 <> humidity Or humidity_loop - 1 <> humidity
wet_bulb = wet_bulb - wb_loop
saturated_vapour_pressure = get_saturated_vapour_pressure(dry_bulb)
pressure = get_pressure(altitude)
dry_bulb_vapour_pressure = get_dry_bulb_vapour_pressure(wet_bulb)
humidity_ratio = get_humidity_ratio(dry_bulb_vapour_pressure, pressure)
moisture_content = get_moisture_content(wet_bulb, humidity_ratio, dry_bulb)
partial_vapour_pressure = get_partial_vapour_pressure(pressure, moisture_content)

humidity_loop = get_humidity(paritial_vapour_pressure, saturated_vapour_pressure)

Loop
WB = wet_bulb
End Function

Sub test()
MsgBox WB(altitude, dry_bulb, humidity)
End Sub

Aflatoon
10-07-2015, 05:44 AM
1. That code still has typos in it, as you would know if you were using Option Explicit.
2. You can't supply a negative number to Log.

nutttom
10-07-2015, 05:54 AM
1. That code still has typos in it, as you would know if you were using Option Explicit.
2. You can't supply a negative number to Log.

ooops yes sorry, the Option Explicit is very handy, thank you.

Ahh ok think i'll need to bob in a if command so that a negative number to Log is never supplied.

Hope that will fix!

I'll let you know how I get on

Thanks again.

Paul_Hossler
10-07-2015, 05:58 AM
1. Also, an example of a workbook with the calls, macros, and any data would be helpful

2. If you do have to post code, using the [#] icon will add [ CODE ] ......... [/ CODE] tags to format the code more pretty



' Constants
'========================================================================== ==========================================='
Const CONST_01 = -5674.5359
Const CONST_02 = 6.3925247
Const CONST_03 = -0.009677843
Const CONST_04 = 0.000000622157
Const CONST_05 = 2.0747825E-09
Const CONST_06 = -9.484024E-13
Const CONST_07 = 4.1635019
Const CONST_08 = -5800.2206
Const CONST_09 = 1.3914993
Const CONST_10 = -0.048640239
Const CONST_11 = 0.000041764768
Const CONST_12 = -0.000000014452093
Const CONST_13 = 6.5459673
Const CONST_14 = 6.54
Const CONST_15 = 14.526
Const CONST_16 = 0.7389
Const CONST_17 = 0.09486
Const CONST_18 = 0.4569

Const R_WATER = 18.015268
Const R_AIR = 28.964546
'1) Equation 1: Get humidity from paritial vapour pressure and saturated vapour pressure.
Function get_humidity(ByVal paritial_vapour_pressure As Variant, ByVal saturated_vapour_pressure As Variant)
get_humidity = (paritial_vapour_pressure / saturated_vapour_pressure) * 100

End Function
'5) Equation 5: Get saturated vapour pressure from dry bulb.
Function get_saturated_vapour_pressure(ByVal dry_bulb As Variant)
If dry_bulb >= 0 Then
get_saturated_vapour_pressure = (Exp(CONST_08 / (dry_bulb + 273.15) + CONST_09 + CONST_10 * (dry_bulb + 273.15) + CONST_11 * (dry_bulb + 273.15) ^ 2 + CONST_12 * (dry_bulb + 273.15) ^ 3 + CONST_13 * Log((dry_bulb + 273.15)))) / 1000
Else:
get_saturated_vapour_pressure = (Exp(CONST_01 / (dry_bulb + 273.15) + CONST_02 + CONST_03 * (dry_bulb + 273.15) + CONST_04 * (dry_bulb + 273.15) ^ 2 + CONST_05 * (dry_bulb + 273.15) ^ 3 + CONST_06 * (dry_bulb + 273.15) ^ 4 + CONST_07 * Log((dry_bulb + 273.15)))) / 1000
End If
End Function
'6) Equation 6: Get humidity ratio from dry bulb vapour pressure and pressure.
Function get_humidity_ratio(ByVal dry_bulb_vapour_pressure As Variant, ByVal pressure As Variant)
get_humidity_ratio = R_WATER / R_AIR * dry_bulb_vapour_pressure / (pressure - dry_bulb_vapour_pressure)

End Function
'7) Equation 7: Get moisture content from wet buld, humidity ratio and dry bulb.
Function get_moisture_content(ByVal wet_bulb As Variant, ByVal humidity_ratio As Variant, ByVal dry_bulb As Variant)
If dry_bulb <= 0 Then
get_moisture_content = ((2830 - 0.24 * wet_bulb) * humidity_ratio - 1.006 * (dry_bulb - wet_bulb)) / (2830 + 1.86 * dry_bulb - 2.1 * wet_bulb)
Else:
get_moisture_content = ((2501 - 2.326 * wet_bulb) * humidity_ratio - 1.006 * (dry_bulb - wet_bulb)) / (2501 + 1.86 * dry_bulb - 4.186 * wet_bulb)

End If

End Function
'8) Equation 8: Get dry bulb vapour pressure from wet bulb.
Function get_dry_bulb_vapour_pressure(ByVal wet_bulb As Variant)
If (wet_bulb + 273.15) >= 0 Then
get_dry_bulb_vapour_pressure = Exp(CONST_08 / (wet_bulb + 273.15) + CONST_09 + CONST_10 * (wet_bulb + 273.15) + CONST_11 * (wet_bulb + 273.15) ^ 2 + CONST_12 * (wet_bulb + 273.15) ^ 3 + CONST_13 * Log((wet_bulb + 273.15))) / 1000
Else:
get_dry_bulb_vapour_pressure = Exp(CONST_01 / (wet_bulb + 273.15) + CONST_02 + CONST_03 * (wet_bulb + 273.15) + CONST_04 * (wet_bulb + 273.15) ^ 2 + CONST_05 * (wet_bulb + 273.15) ^ 3 + CONST_06 * (wet_bulb + 273.15) ^ 4 + CONST_07 * Log((wet_bulb + 273.15))) / 1000
End If
End Function
'9) Equation 9: Get partial vapour pressure from pressure and moisture content.
Function get_partial_vapour_pressure(ByVal pressure As Variant, ByVal moisture_content As Variant)
get_partial_vapour_pressure = (pressure * moisture_content) / (0.621945 + moisture_content)

End Function

'10) Equation 10: Get enthalpy from dry bulb and moisture content.
Function get_enthalpy(ByVal dry_bulb As Variant, ByVal moisture_content As Variant)
get_enthalpy = 1.006 * dry_bulb + moisture_content * (2501 + 1.86 * dry_bulb)

End Function
'11) Equation 11: Get pressure from altitude.
Function get_pressure(ByVal altitude As Variant):
get_pressure = 101.325 * (1 - 0.0000225577 * altitude) ^ 5.2559

End Function
'12) Equation 12: Get specific volume from dry bulb, moisture content and pressure.
Function get_specific_volume(ByVal dry_bulb As Variant, ByVal moisture_content As Variant, ByVal pressure As Variant):
get_specific_volume = (0.287042 * (dry_bulb + 273.15)) * (1 + 1.607858 * moisture_content) / pressure

End Function
'13) Equation 13: Get dew point temperature from partial vapour pressure.
Function get_dew_point_temperature(ByVal partial_vapour_pressure As Variant)
If dry_bulb <= 93 And dry_bulb >= 0 Then

get_dew_point_temperature = 6.54 + 14.526 * Log(partial_vapour_pressure) + 0.7389 * Log(partial_vapour_pressure) * Log(partial_vapour_pressure) + 0.09486 * Log(partial_vapour_pressure) ^ 3 + 0.4569 * partial_vapour_pressure ^ 0.1984

ElseIf dry_bulb < 0 Then

get_dew_point_temperature = 6.09 + 12.608 * Log(partial_vapour_pressure) + 0.4959 * Log(partial_vapour_pressure) * Log(partial_vapour_pressure)

Else
get_dew_point_temperature = "Error"

End If

End Function

Function WB(ByVal altitude As Variant, ByVal dry_bulb As Variant, ByVal humidity As Variant) As Variant
Dim wet_bulb As Variant
Dim wb_loop As Variant
Dim saturated_vapour_pressure As Variant
Dim pressure As Variant
Dim dry_bulb_vapour_pressure As Variant
Dim humidity_ratio As Variant
Dim moisture_content As Variant
Dim partial_vapour_pressure As Variant
Dim humidity_loop As Variant
wb_loop = 0.1
wet_bulb = dry_bulb
Do While humidity_loop + 1 <> humidity Or humidity_loop - 1 <> humidity
wet_bulb = wet_bulb - wb_loop
saturated_vapour_pressure = get_saturated_vapour_pressure(dry_bulb)
pressure = get_pressure(altitude)
dry_bulb_vapour_pressure = get_dry_bulb_vapour_pressure(wet_bulb)
humidity_ratio = get_humidity_ratio(dry_bulb_vapour_pressure, pressure)
moisture_content = get_moisture_content(wet_bulb, humidity_ratio, dry_bulb)
partial_vapour_pressure = get_partial_vapour_pressure(pressure, moisture_content)

humidity_loop = get_humidity(paritial_vapour_pressure, saturated_vapour_pressure)

Loop
WB = wet_bulb
End Function

Sub test()
MsgBox WB(altitude, dry_bulb, humidity)
End Sub

Paul_Hossler
10-07-2015, 06:08 AM
Using Option Explicit -- There's a scope issue since dry_bulb is not defined in or to this function



'13) Equation 13: Get dew point temperature from partial vapour pressure.

Function get_dew_point_temperature(ByVal partial_vapour_pressure As Variant)
If dry_bulb <= 93 And dry_bulb >= 0 Then
get_dew_point_temperature = 6.54 + 14.526 * Log(partial_vapour_pressure) + 0.7389 * Log(partial_vapour_pressure) * Log(partial_vapour_pressure) + 0.09486 * Log(partial_vapour_pressure) ^ 3 + 0.4569 * partial_vapour_pressure ^ 0.1984
ElseIf dry_bulb < 0 Then
get_dew_point_temperature = 6.09 + 12.608 * Log(partial_vapour_pressure) + 0.4959 * Log(partial_vapour_pressure) * Log(partial_vapour_pressure)
Else
get_dew_point_temperature = "Error"
End If
End Function


I'd guess you want to pass it in the function call????



Function get_dew_point_temperature(ByVal partial_vapour_pressure As Variant, ByVal dry_bulb as Variant)

nutttom
10-07-2015, 06:16 AM
Thanks Paul.

Fixed that but now get #VALUE! error...!

How do I run the Option Explicit to debug?

Paul_Hossler
10-07-2015, 06:32 AM
Function get_dew_point_temperature(ByVal partial_vapour_pressure As Variant, ByVal dry_bulb as Variant) As Double


My style when defining Function is to specify the type of value returned, i.e. Double in the example above


To attach a WB, go [Advanced] and use the icon that sort of looks like a paper clip

Option Explicit at the top of each module tells VBA that all variables must be explicitly Dim-ed. Really helps to catch typing errors, e.g.





Dim myAnswer as String


Msgbox myAnsewr




will flag the myAnsewr as not defined


On the menu for the VBE there's [Debug] and under that is [Compile VBA Project]. (or Alt-D, Enter) that will do syntax checks (I have auto syntax checks turn on under [Options]) and flag things like in the screen snip

Logic errors or negative numbers not allows have to be handled by your macro

Also, you have a scope issue here



Sub test()
' MsgBox WB(altitude, dry_bulb, humidity)
MsgBox WB(10000, 35, 75)
End Sub


since altitude, dry_bulb, and humidity are not 'visible' to the Sub test

Paul_Hossler
10-07-2015, 06:39 AM
BTW, it's good to name your variables meaningfully like you did. :thumb:thumb:thumb

Many times people will use cryptic names that no one else understands: A, X, N1, etc.:banghead::banghead::banghead:

nutttom
10-07-2015, 07:01 AM
BTW, it's good to name your variables meaningfully like you did. :thumb:thumb:thumb

Many times people will use cryptic names that no one else understands: A, X, N1, etc.:banghead::banghead::banghead:

What do you mean by attached WB? And where is the [advanced] location with paper clip icon?
The [Compile VBA Project] option is greyed out and cannot be selected?
By scope error do you mean I need to specify a range for each parameter?
What is the bulbs excel file, its blank when I opened it?

Sorry for all the questions but the help is great, thank you
VBA seems so much harder than Python!
If I can get this Function to work I will be so happy

p.s. the name help me to understand too I'd get lost otherwise.

Paul_Hossler
10-07-2015, 09:26 AM
1. My attachment in post #17 only had macro code, the WS were empty
2. I think you have to click in a macro to have the Compile menu item
3. Advanced is in lower right (1.jpg)
4. That give more options, including the 'paperclip' (2.jpg)
5. 'Scope' has to do with the lifetime (poor analogy) of a variable, not the Range. In on-line help, search for 'scope'. There's a nice write-up (3.jpg)

nutttom
10-07-2015, 09:58 AM
Thanks again Paul.

I will give it another go tomorrow... I've gotta crack this, its doing my head in.

nutttom
10-08-2015, 06:08 AM
Aflatoon and Paul thank you for your qucik reponses and assistance.

With your help I have cracked it!!!

:):):)

Thank you!!!

How can I pay you back for your help?

Aflatoon
10-08-2015, 06:16 AM
Thank you!!!

That will do nicely. :)