Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old April 12th, 2005, 12:55 PM
Authorized User
 
Join Date: Jun 2004
Location: , , .
Posts: 24
Thanks: 0
Thanked 0 Times in 0 Posts
Default Is there a NaN in VBA?

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

Any hints will be appreciated.
__________________
Tim
  #2 (permalink)  
Old April 12th, 2005, 01:06 PM
Friend of Wrox
 
Join Date: Nov 2004
Location: Port Orchard, WA, USA.
Posts: 1,621
Thanks: 1
Thanked 3 Times in 3 Posts
Default

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...
  #3 (permalink)  
Old April 12th, 2005, 01:16 PM
Authorized User
 
Join Date: Jun 2004
Location: , , .
Posts: 24
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
  #4 (permalink)  
Old April 13th, 2005, 01:24 PM
Friend of Wrox
 
Join Date: Nov 2004
Location: Port Orchard, WA, USA.
Posts: 1,621
Thanks: 1
Thanked 3 Times in 3 Posts
Default

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?
  #5 (permalink)  
Old April 13th, 2005, 01:34 PM
Authorized User
 
Join Date: Jun 2004
Location: , , .
Posts: 24
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
  #6 (permalink)  
Old April 14th, 2005, 11:34 AM
Friend of Wrox
 
Join Date: Nov 2004
Location: Port Orchard, WA, USA.
Posts: 1,621
Thanks: 1
Thanked 3 Times in 3 Posts
Default

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?
  #7 (permalink)  
Old April 14th, 2005, 11:51 AM
Authorized User
 
Join Date: Jun 2004
Location: , , .
Posts: 24
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
  #8 (permalink)  
Old April 14th, 2005, 03:27 PM
Authorized User
 
Join Date: Jun 2004
Location: , , .
Posts: 24
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
  #9 (permalink)  
Old April 15th, 2005, 02:27 PM
Friend of Wrox
 
Join Date: Nov 2004
Location: Port Orchard, WA, USA.
Posts: 1,621
Thanks: 1
Thanked 3 Times in 3 Posts
Default

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...
  #10 (permalink)  
Old April 15th, 2005, 02:36 PM
Authorized User
 
Join Date: Jun 2004
Location: , , .
Posts: 24
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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




Similar Threads
Thread Thread Starter Forum Replies Last Post
NaN elayaraja.s XSLT 2 September 9th, 2008 04:10 AM
NaN spinout ASP.NET 2.0 Basics 4 August 5th, 2007 06:07 AM
Formatting NaN in a matrix gidsy BOOK: Professional SQL Server Reporting Services ISBN: 0-7645-6878-7 2 May 8th, 2007 10:50 AM
Actionscript NaN brainchild Flash (all versions) 1 March 21st, 2007 07:29 AM
format-number NaN rbdave XSLT 1 October 23rd, 2003 06:48 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.