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

# Problem using a vba custom function in the load script

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:
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...

• ###### Re: Problem using a vba custom function in the load script

I'd make sure those 'new' locations don't exist in WEATHER_OBSERVATIONS_TEMP2. That's where they seem to be coming from. I don't believe in QV randomly generating values like Showers07 from nowhere.

• ###### Re: Problem using a vba custom function in the load script

When I comment out the line with the VBA function then the script loads correctly and I have no issue with extra records...

• ###### Re: Re: Problem using a vba custom function in the load script

Ok I have removed as much as possible in order to zoom in to the problem. The script is the following:

WEATHER_OBSERVATIONS_TEMP2:
FROM
[WEATHER_OBSERVATIONS.QVD](qvd);

NoConcatenate

WEATHER_OBSERVATIONS_TEMP:
LOCATION_ORIGINAL as LOCATION_NEW
RESIDENT WEATHER_OBSERVATIONS_TEMP2;

The VBA script is:

Public Function WetBulbTemp(Ta)
WetBulbTemp = 1
End Function

LOCATION_ORIGINAL contains 27 records

LOCATION_NEW contains76 records

The orginal QVD contains 553.955 records. I have tried this on 2 computers and both give me the same problem. The number of records in LOCATION_NEW fluctuates with each reload...

• ###### Re: Problem using a vba custom function in the load script

Can you post that qvd file? Or create one (with sensitive data scrambled to make it unreadable) with enough data to reproduce the issue?

• ###### Re: Problem using a vba custom function in the load script

I seem to have found a workarround. Instead of loading the QVD first and then doing all the transformations in a resident load, I've now added an extra step. The VBA script runs in a seperate resident load. So in summary: