Is there a way to assign a NaN to variables (eg. individual array elements) in VBA?

Any hints will be appreciated.

__________________
Tim

Nope.

You can, however, "roll your own."

The means to do this are up to the programmer.

For instance, you could create a User-Defined type with 2 elements, one of which holds a value, the other a Boolean indicating whether the item is numeric. Then you could make an array of these types.

Why is it that you want to do this? Perhaps understanding your task would commend a solution not obvious from what you have said so far...

Thanks.
The reason I need to do this is in passing many data-series in the form of a 2D array. Each series may start at different index values, so I need to designate the non existent data with a NaN.

I can create a method in a com-server which passes a Nan to its client, but without testing I can't tell what actually ends up in the vba variable. I thought to ask before I actually do this.

I could be wrong but I do think that I have seen Nan for variable-values in the watch window during debugging in the past.

Tim

If you make your elements Variants in VBA, you can set the non-existent data to Null, which can be tested with IsNull().

NaN is a java-specific concept (not whether something is numeric, but the “NaN” designation), as far as I know.

What language is the com-server written in?

Thanks.
These arrays can be numerous and each quite large. So using variant is nit memory efficient.
particularly because the arrays are passed to a compiled com-server to do computation.
The com-server would be written in ms-C v6 or fortran-90 where Nan surely is an often-used device.



Tim

I saw something yesterday that lead me to suspect that NaN is included in VB.NET...

If you have an array, and an element of it is NaN, what is stored in the memory location referenced by that element?

There are a few abstract "numbers" defined by the IEEE FP-standard
for exmaple:
Signed zero
Denormalized number
Signed infinity
Not a Number

IEEE Floating-Point Values

Name Quantity Exponent Significand
Not a Number NaN E = Emax + 1 sig not equal 0

Not a Number (NaN) results from an operation involving one or more invalid operands. For instance 0/0 and SQRT(-1) result in NaN. In general, an operation involving a NaN produces another NaN. Because the fraction of a NaN is unspecified, there are many possible NaNs depending on what the significand is. In this case, any value will do.


Tim

I finally tried passong the Nan from fortran or C, and it seems to work fine.
The display in vba's watch window is "-1.#QNAN" however, explicit assignemnt of this value to a single or variant does not even compile.
A necessary technicality is to set the intent to "out" and not "inout" in the com-interface. I also made the Nan as the return value.

I further tested that passing this value back to fortran comes back as a Nan.
The value also copies fine from one vba variable to another.

Still it would be a little nicer to do it all in vba to be sure the semi-accessible QNAN representation won't go away in some future version.

Tim

It looks as if the indicator of NaN is that the exponent is higher than the maximum allowed exponent.

I use implicit assignation a lot in my IIS VB Web app. There is no way to directly assign Empty to a variable, yet sometimes I want to set something back to Empty (which is explicitly defined, and is different than Null).

So I keep an inviolate variable that has the value of Empty, and set things equal to it. Tedious, but effective.

Looked in Help and Object Browser to no avail for QNAN...

Yes you are correct about the meaning.
If anybody is interested, post a reply here and I can send you my com-dll for free.
(unless there is a code repository here.)
It also has things like putting the caller thread to sleep; another thing I cannot do in vba.


Tim