Checking for the Variant value "Nothing"

Images
Related searches

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

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.

Checking for the Variant value "Nothing", The VarIsClear function includes your situation where the type is varDispatch and the value is nil . It also includes empty and "unknown" values,� 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.

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.

How to search a variant field for a value, Quite often, there is a need to check the variant field for a particular value. For example, we want to search the following table for all the� On the Valuestab, select to include or exclude the rows in the Signfield, select and operator in the Optionfield, and enter a lower and higher value (depending on the operator in Option,only a lower value is required). Choose Save & Close. Modify a variant. Navigate to Environment → SAP Systems.

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

The proper way to check Variant for null / detect nodes without data , what is the recommended way [in dot notation] to check if a node has data? given a Variant value in Flexscript, how it should be checked for� Maybe you can start out by assigning a dummy value to all dimensions in the array, and then check if the value is different from that dummy value. Or if you know that it can only contain a given type of value (say it can only be assigned a text value) you could maybe use VariantVar.ISTEXT, as this one would return false if nothing has been put in the dimension at all, and true if a text value has been assigned.

How to check the content of a Variant variable?, The var has 3 dimensions. Now I need to assign the values of this variant to three fields in my table called "Primary Key 1", "Primary Key 2" and� Check if a Variant is NULL. Once you've determined that a node has data and returns a meaningful value you can check it against nullvar or check if its type is a NULL type: Variant val; val == nullvar //true val.type == VAR_TYPE_NULL // true

VarType function (Visual Basic for Applications), vbDate, 7, Date value. vbString, 8, String. vbObject, 9, Object. vbError, 10, Error value. vbBoolean, 11, Boolean value. vbVariant, 12, Variant� The VBA VarType function returns information on the VBA Data Type of a given variable. It returns a number corresponding to one of the values of the VbVarType VBA Enum object. The Excel VBA VarType function comes useful in a variety of scenarios especially when we are passing Variant objects into a VBA Sub or … Continue reading VBA VarType Function →

Converts the type of variant value in this COleVariant object. void ChangeType(VARTYPE vartype, LPVARIANT pSrc = NULL); Parameters. vartype The VARTYPE for this COleVariant object. pSrc A pointer to the VARIANT object to be converted. If this value is NULL, this COleVariant object is used as the source for the conversion. Remarks

Comments
  • ?! You're right! I completely overlooked that one! I've accepted your answer, and will change my source code ;-) Thanks a bunch!
  • @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.
  • @mghie: I'm pretty sure those do not work with that pesky Nothing value :)
  • @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: Curious... My D2007 sources say Result := FindVarData(V)^.VType = varEmpty;, which is definitely something else... Is it?
  • @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?
  • Perhaps problematic is that VarIsEmpty does not handle varErrors returned by Excel.
  • 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!