Delphi 7 with..do statement doesen't work with variant variable

Question

I'm working with Microsoft Excel via Delphi 7. It works fine but while formatting rows and ranges I have to write such long strings.

XLApp.Workbooks[1].WorkSheets[NameDoc].Range['A19:L19'].Font.Bold := true;

So I want to get rid of hard work and do it via "with..do" statement like this

with XLApp.Workbooks[1].WorkSheets[NameDoc] do
begin
  Range['A19:L19'].Font.Bold := true;
end;

But at compilation stage I see this error

Record, object or class type required

on string - "with..do".

I creating Excel object this way

XLApp: Variant;
XLApp := CreateOleObject('Excel.Application');

I consider that with..do statement doesen't works with variant type variable, but I want to know whether I'm right or not? And if I'm right is there any workaround to make it work?

Answer 1

Can with be used with a Variant?

No.

You can use with for types whose members are known at compile time. But variants, for which the . operator is evaluated at run time, do not fall into this category. Hence with is not available for variants.

The documentation says, with my emphasis:

A with statement is a shorthand for referencing the fields of a record or the fields, properties, and methods of an object. The syntax of a with statement is:

with obj do statement

or:

with obj1, ..., objn do statement

where obj is an expression yielding a reference to a record, object instance, class instance, interface or class type (metaclass) instance, and statement is any simple or structured statement.

Answer 2

Variant can be anything or nothing at all - compiler doesn't know it and cannot know: it is so called "dynamically typed value". Since it does not know - it does not know if there would be any members (properties, methods) and if there would - what names would they have.

To get the benefits of strong compile-time typing - including using of with but not only - you have to use interface variables, those that are provided by TExcelApplication component and underlying unit having those values "statically typerd" - thus providing for Delphi compiler to know value types when compiling, in before running. There are plenty of types like iWorsksheet, iRange and others in that unit.

• Borland Delphi 7 TExcelApplication.Connect works on office machines but not at client
• http://www.delphipages.com/forum/showthread.php?t=157889
• http://delphikingdom.ru/asp/viewitem.asp?catalogid=1270

However, since that is about reference-counting and lifetime I'd suggest you go with explicit use of temp variables rather than using with with and implicit invisible variables. Since you cannot control their lifespan and their clearance you might hit the wall in some unexpected place later. I did.

var tmpR: iRange; // assuming we have statically-typed API
// for example - providing we using ExcelXP or Excel2000 unit

tmpR := XLApp.Workbooks[1].WorkSheets[NameDoc];

tmpR.Range['A19:L19'].Font.Bold := true; // instead of with

with tmpR do // also possible but gives little benefit now
begin        //    when we made a dedicated temp var
  Range['A19:L19'].Font.Bold := true;
end;

tmpR := nil; // crucial unless the most short and simplistic functions
// just release hold on Excel's object - let it manage its memory freely,
// by letting Excel know your program no more uses that object.

Also read

• https://en.wikipedia.org/wiki/Automatic_Reference_Counting
• https://en.wikipedia.org/wiki/Component_Object_Model