Checking for the Variant value "Nothing"

Question

This is something I ran into last year, and SO seems like a good place to document it :)

Q: When automating Excel (/Word/...) from Delphi, how can I check if an Excel function returned the variant Nothing (as it's called in VBA)?

Solution 1

The VarIsClear function includes your situation where the type is varDispatch and the value is nil. It also includes empty and "unknown" values, and custom variant types. I see it in my Delphi 2005 source; I don't know how much earlier it was included.

Solution 2

Curiously, VBA's Nothing is not the same as Unassigned, Null or Empty, so you can't use, e.g.:

// Worksheet is a TExcelWorksheet or OleVariant coupled to an open worksheet
MyRange := Worksheet.Range['MyRangeInTheWorksheet', EmptyParam]
if (MyRange = Null) then  // won't work!
  MsgBox('The range doesn''t exist!');

Instead, use this function:

function VarIsNothing(V: OleVariant): Boolean;
begin
  Result :=
    (TVarData(V).VType = varDispatch)
    and
    (TVarData(V).VDispatch = nil);
end;

// ...

if (VarIsNothing(MyRange)) then

---

Update

Apparently, the sources of the RTL unit Variants.pas have changed between Delphi 5 and 2007. According to @mghie (see comments), the function VarIsEmpty would have done the job in D5. However, in D2007, this does not seem to be the case anymore, so you'll probably need the above function again.

Also, note that VBA's Nothing is probably quite a special case; I don't think one encounters it too often with automation.

Solution 3

Does VarIsEmpty (different than VarIsNull) not do what you want?

Comments

[mghie]

@onnodb: If replacing "(MyRange = Null) " with "VarIsNull(MyRange) or VarIsEmpty(MyRange)" works I'd politely suggest accepting Craig's answer. No need to write custom functions for stuff that is already in the library and documented.

[Lieven Keersmaekers]

Perhaps problematic is that VarIsEmpty does not handle varErrors returned by Excel.

[Jason 'Bug' Fenter]

@mghie: I'm pretty sure those do not work with that pesky Nothing value :)

[Jason 'Bug' Fenter]

No, I'm pretty sure Empty is something quite different than Nothing --- the latter doesn't seem to have any equivalent in Delphi. (I might be wrong, of course, but I'm pretty sure I checked this). Thanks, though!

[mghie]

@onnodb: Well, have a look at the source code of VarIsEmpty() - it is a superset of your code, at least in the Delphi 5 version I'm looking at right now...

[mghie]

@onnodb: Indeed. Delphi 5 does what your function does, but in addition checks for varEmpty and varUnknown. Still, I doubt that such a change would have been made if it resulted in changed behaviour. Or would that be me being too starry-eyed?

[Jason 'Bug' Fenter]

@mghie: Yes, it's strange, but true. That line in my previous comment is the only one in the VarIsEmpty function in my D2007 sources of Variants.pas.

[Jason 'Bug' Fenter]

?! You're right! I completely overlooked that one! I've accepted your answer, and will change my source code ;-) Thanks a bunch!