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)?

Answer 1

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.

Answer 2

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

Answer 3

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.