How to test if a OleVariant contains an interface

Question

One of the annoying things with working with Excel through its automation interface is the weak typing.
A return value can contain ~~anything~~ different types.
How do I test if the variant returned by caller is an ExcelRange interface?

function TAddInModule.InBank: boolean;
var
  ExcelAppAsVariant: OleVariant;
  test: string;
  Caller: OleVariant;
begin  //Exception handling omitted for brevity. 
  Result:= false;
  ExcelAppAsVariant:= ExcelApp.Application;
  Caller:= ExcelApp.Application.Caller[EmptyParam, 0];
  if IDispatch(Caller) is ExcelRange then begin //E2015 Operator not applicable 
    Result:= lowercase(Caller.Parent.Name) = 'bank' 
  end;
end;

(Strangly enough the as operator works (IDispatch(Caller) as ExcelRange).Parent; compiles just fine).

The following code works, but seems overly verbose:

if VarIsType(Caller, varDispatch) then begin 
  IUnknown(Caller).QueryInterface(ExcelRange, ICaller) 
  if Assigned(ICaller) then ICaller......

There is also no built-in function VarIsInterface(Variant, Interface).
How do I test that an OleVariant contains a given interface?

See also: How to cast OleVariant to IDispatch derived?

EDIT
Thanks all, I used the following to do the testing, because Excel mixes interfaces and OleStrings as possible return values.

if VarIsType(Caller, varDispatch) and Supports(Caller, ExcelRange) then begin

Interfaces have always supports `as` for type-casting. The strange thing is that `is` doesn't work, not that `as` does. — Rob Kennedy, Jun 03 '15 at 16:08

score 4 · Accepted Answer · answered Jun 03 '15 at 13:46

4

I'd probably use Supports for this:

if Supports(Caller, ExcelRange) then
  ....

This resolves to the same code as given by @Stijn but the Supports call is rather more concise.

answered Jun 03 '15 at 13:46

David Heffernan

601,492
42
1,072
1,490

The coolest thing about `supports` is that it has an overload that returns the converted interface. – Johan Jun 03 '15 at 13:55
You don't need that here of course, but in many cases those overloads are very useful indeed – David Heffernan Jun 03 '15 at 13:56
Is `Supports` replacing Johan's `VarIsType` check, too, or just the `QueryInterface` + `Assigned` calls? – Rob Kennedy Jun 03 '15 at 16:06
@Rob It's closest to `if IDispatch(Caller) is ExcelRange then begin`. So if `Caller` does not have `IDispatch` then a variant conversion exception is raised. My reading of the code is that it is safe to assume that `Caller` has an `IDispatch` in it. – David Heffernan Jun 03 '15 at 16:10
Yes, I suppose that's my interpretation of Johan's first code block, too. The second code block doesn't seem to keep that same assumption, though. It checks the variant type first, *then* type-casts to `IDispatch`. – Rob Kennedy Jun 03 '15 at 16:15
After some additional testing, it turned out that Caller is not always a IDispatch, it can be a string as well (What was Microsoft thinking), so the test for VarIsDispatch is actually needed. I have not been able to trigger this scenario myself, but would rather not chance it. – Johan Jun 03 '15 at 16:24

score 3 · Answer 2 · answered Jun 03 '15 at 16:38

3

The System.Variants unit has VarSupports() functions for testing/extracting an interface from an (Ole)Variant:

function VarSupports(const V: Variant; const IID: TGUID; out Intf): Boolean; overload;
function VarSupports(const V: Variant; const IID: TGUID): Boolean; overload;

For example:

Caller := ExcelApp.Application.Caller[EmptyParam, 0];
if VarSupports(Caller, ExcelRange) then
  Result := LowerCase(Caller.Parent.Name) = 'bank';

if VarSupports(Caller, ExcelRange, ICaller) then 
  ICaller.DoSomething;

answered Jun 03 '15 at 16:38

Remy Lebeau

555,201
31
458
770

What functionality does this offer over plain Supports? – David Heffernan Jun 03 '15 at 16:46
1

In this particular situation, not much. But it does help ensure the `Variant` contains an obtainable interface, even from custom Variant types. And besides, it is a Variant-specific RTL function. You have a `Variant`, you want an interface from it, so why not use a function that is designed *specifically* for that purpose? If you call `Supports()` directly, you are not really invoking native `Variant` functionality, you are implicitly converting the `Variant` to an `IInterface` first and then querying it for the desired interface. So why not let the RTL handle that for you? – Remy Lebeau Jun 03 '15 at 16:55
the rtl does handle it for you with Supports. I suspect these amount to the same thing here. – David Heffernan Jun 03 '15 at 16:58
There's nothing to choose in this case. `VarSupports` just makes use of `IVarInstanceReference` if it is available. Which it won't be here. And otherwise just calls `Supports`. Which is what you refer to when you talk about custom variant types. It makes sense to use `VarSupports` when you have a variant, as a general rule I guess, even if the behaviour, in this case, will be identical. – David Heffernan Jun 03 '15 at 17:12
The problem is that if the Variant does not contain an interface this will still generate an exception: `Exception class EVariantTypeCastError with message 'Could not convert variant of type (OleStr) into type (Unknown)'.` This is a missed opportunity IMO, because a simple test inside the VarSupports of (`if VarIsType(AVariant,[varDispatch, varInterface])` would have fixed this. As such it does not do the whole job. – Johan Jun 03 '15 at 18:30
1

@Johan: There is no `varInterface`, you are thinking of `varUnknown` instead (which means `IUnknown`, not an unknown type). Either way, `VarIsType()` does not account for `varTypeMask` and `varByRef`, but `(Ole)Variant` does during converions. In this particular case, that does not matter since `varByRef` should not be present, but it might matter in other situations, in which case you would need `if VarIsType(AVariant, [varDispatch, varDispatch or varByRef, varUnknown, varUnknown or varByRef]) then`, or alternatively `if (VarType(AVariant) and varTypeMask) in [varDispatch, varUnknown] then`. – Remy Lebeau Jun 03 '15 at 21:24

score 2 · Answer 3 · answered Jun 03 '15 at 13:45

2

The code is indeed, regretably, verbose, but close to what I typically use:

if IUnknown(Caller).QueryInterface(ExcelRange, ICaller)=S_OK then

answered Jun 03 '15 at 13:45

Stijn Sanders

35,982
11
45
67

How to test if a OleVariant contains an interface

3 Answers3