5 Replies Latest reply: Sep 4, 2013 5:12 PM by Mathias Vanden Auweele RSS

    Problem using a vba custom function in the load script

    Mathias Vanden Auweele

      Dear all,

       

      I am trying to load weather observation data but I need to an additional parameters (the wet bulb temperature) to this table. Because it is not a straight-forward calculation, rather an iteration, I need to implement a custom function defined in a vba script. The problem is that other fields in the loaded table are influenced by this vba calculation. For example, where before the load I only have 30 unique records in the field LOCATION, after the load I have 682. I have the impression that the extra records are somehow a mix of other field records. For example: "cloudy-1.03" is one new record. Another: Showers07.

       

      This is the load script part:

       

      WEATHER_OBSERVATIONS_TEMP:
      LOAD *,
           LOCATION,
           WetBulbTemp(tempm,hum,pressurem) as TEMPERATURE_WET_BULB
      RESIDENT WEATHER_OBSERVATIONS_TEMP2;
      

       

      This is the VBA code:

       

      
      Public Function WetBulbTemp(Ta,U,Ps)
          ' Ta = ambient (dry bulb) air tempeature (°C)
          ' U = relative humidity (%)
          ' Ps = air pressure (mBar or HPa)
          
          ' Tw = intermediate result for wet bulb temperature
          
          ' formula from http://www.knmi.nl/samenw/geoss/avw/RIS-doc.pdf
          
          ' initialise wet bulb temperature and vapor pressure
          T = Ta
          E = VaporPressure(Ta)*U
          F = 1
          
          do while abs(F) >= 0.0001
              F = (VaporPressure(T)-E)/(5419*VaporPressure(T)/(273.15+T)^2+0.000646*Ps)
              T = T - F
              E = E+0.000646*Ps*F
          loop
      
          WetBulbTemp = T
      End Function
      
      Public Function VaporPressure(T)
          VaporPressure = 6.11213*exp(17.5043*T/(241.2+T))
      End function
      

       

      Can anyone explain what is going on here? I already tried changing the vba script to:

      Public Function WetBulbTemp(Ta,U,Ps)
          WetBulbTemp = 1
      End Function
      

       

      But the problem remains...