Help with Function

[nutttom]

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]

How are you calling it?

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

[nutttom]

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]

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]

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]

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]

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]

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]

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

[Aflatoon]

It returns 1000 for me after fixing that.

[nutttom]

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]

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]

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]

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]

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]

Thanks Paul.

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

How do I run the Option Explicit to debug?

[Paul_Hossler]

 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]

BTW, it's good to name your variables meaningfully like you did.

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

[nutttom]

BTW, it's good to name your variables meaningfully like you did.

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

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]

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)