Create #NA values in VBA vector.

[Dan Rie]

I would like to specify missing values in Excel VBA data vectors that are
subsequently transferred to cell values in a worksheet. I have tried to
define a variable (Dim fNAN as Double), then put a value in fNAN to
represent NA, but I haven't found a way to move a floating point value into
a cell value and make the cell act as an #NA. When I set fNAN = Empty, then
transfer Cell.Value=fNAN, the cell gets a zero value, which is
indistinguishable form legitimate zero values.

Another try was to put two lines into the VBA code as follows:
ActiveSheet.Range("A1").formula="=NA" followed by
fNAN=ActiveSheet.Range("A1").Value . This produces a "type mismatch". ( I
even threw in a "Calculate" to make sure that the value was created.)

Any suggestions?

(please reply to newsgroup - - or remove anit-spam characters in address
below)
rie-ampersand-world-dot-std-dot-com

Dan Rie
Scituate, MA

[Dave Peterson]

You can plop them in the worksheet with stuff like:

Range("a1").Value = CVErr(xlErrNA)
'or
Range("b1").Formula = "=na()"

But you won't be able to look at the value directly.

If IsError(Range("a1")) Then
MsgBox "it's an error"
End If

or
If iserror(Range("a1")) then
If CVErr(range("a1").Value) = CVErr(xlErrNA) Then
msgbox "it's na again"
else
msgbox "not an NA, but an error"
end if
else
msgbox "it's ok"
end if

Another option would be to look at the .text value

if range("a1").text = "#N/A" then

--

Dave Peterson
ec3...@msn.com

[Tom Ogilvy]

Also note that if the variable is typed as Double, it can not hold an error
value.

fNan# = cverr(xlerrNa)
produces type mismatch error.

? typename(cverr(xlerrNa))
Error

Regards,
Tom Ogilvy

"Dan Rie" <no...@aol.com> wrote in message
news:3Wei8.3183$44.11...@typhoon.ne.ipsvc.net...