CODEWITHSUNDEEP
delphiexcelautomationms-office
onnodb
onnodb

Reputation: 5261

Checking for the Variant value "Nothing"

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

Upvotes: 6

Views: 14769

Answers (3)

Rob Kennedy
Rob Kennedy

Reputation: 163287

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.

Upvotes: 10

onnodb
onnodb

Reputation: 5261

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.

Upvotes: 4

Craig Stuntz
Craig Stuntz

Reputation: 126557

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

Upvotes: 2

Related Questions