VBA fills blank numeric values with zeroes - workaround?

[Colleyville Alan]

I am writing an app that will query mutual fund performance data from an
Access table, load it into memory and then write formatted info to Excel. I
have created a user-defined type of several different variables, then, upon
completion of the query, I declare an array of that type whose size matches
the number of records in the results table. I then loop through the results
table and assign each value to my array.

I check each element to see if it is null before assigning it to the array,
lest I get a type mismatch error for assigning a null to a variable of type
double. For example, to load the returns of a fund for the last ten years
into the array, I use the following:

If ![10-Years] <> "" Then
FundPerfArray(iCtr).dbl10Years = ![10-Years]
End If

Where "FundPerfArray" is an array of the user-defined type. The problem is,
when it encounters a null value, the array holds a zero in its place. When
I get to Excel, it writes a zero in the cell representing 10 years. Now I
could change zeroes to blanks, but then a fund could actually have exactly
zero for that time, unlikely as it sounds.

On another NG, I was told that this is because VBA assigns a value of zero
to numeric variables, but I have yet to figure out how best to address the
problem.. What is the best workaround for this? Should I simply use a
variant array and check the values held when writing them to Excel? Is
there a better solution?
Thanks

 

[Jim Rech]

Just speaking generically, Alan, when a variable has to be able to hold a
number and a blank then it should be declared as a variant. That doesn't
mean that all the elements in your user defined data type have to be
variants, just, in this case, the "10 year" element.

 

[Jerry W. Lewis]

VBA does support a null value, but Excel does not. You have to decide
what you want Excel to show in that case, and assign it in your VBA
routine. The most obvious returns would be #N/A (not plotted in graphs)
or "" (looks blank, but plots as zero). Either of these return values
would require you to declare your variable as Variant instead of Double,
to permit the possibility of multiple data types.

Jerry

 

[Colleyville Alan]

VBA does support a null value, but Excel does not. You have to decide
what you want Excel to show in that case, and assign it in your VBA
routine. The most obvious returns would be #N/A (not plotted in graphs)
or "" (looks blank, but plots as zero). Either of these return values
would require you to declare your variable as Variant instead of Double,
to permit the possibility of multiple data types.

Jerry

Thanks

 

[Colleyville Alan]

Just speaking generically, Alan, when a variable has to be able to hold a
number and a blank then it should be declared as a variant. That doesn't
mean that all the elements in your user defined data type have to be
variants, just, in this case, the "10 year" element.

Thanks - I understand the path now.