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)?
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.
Nothing is not the same as
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
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
- ?! 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
- @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
Emptyis 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!