How to catch a VBA collection object into a Delphi array

Posted on 2004-03-27
Last Modified: 2010-04-05
Hi Experts,

I want to improve the performance of a Delphi code that reads and writes from and to an Excel workbook.

In this workbook the cells of interest are given names, and these names thus form a collection object "Names" in VBA. Using VBA the names object can simply be read into a variant as an array by:
  " Set MyArray = ActiveWorkbook.names ". However, in Delphi (D4,D7 and using ExcelXP) I was unsuccessful to the assignment done (MyArray defined as vaiant or OleVariant).

I have tried the statements :  MyArray := Wrkbook.names as ExcelXp.names
                                          or          := Wrkbook.names as names
                                          or          := Wrkbook.names  
                                          or even  := VarArrayof([Wrkbook.names]),  all without success.

Instead I now use the following code to read in the
   With WorkBk.Names DO
    For I := 0 to N-1 DO
      with item(i+1,emptyparam,emptyparam) DO
         ExcelNamesArray[I]    := name_;
         ExcelNamesArray_RC[I] := referstoR1C1;
         ExcelValuesArray[I]       := referstorange.value

I'd rather read and write all as an array, but perhaps this is impossible. I don't know.

The cells in the workbook are non-adjacent (unfortunately) and what I want is to read their names, their value and their ReferstoR1C1 properties into fast Delphi arrays. After a Delphi treatment the data is to be written back into the named cells in Excel.

Can anybody put me on the right track to do this efficiently, to get a fast performance, since it concerns a large number of individual cells in Excel.

Many thanks already,

Question by:JGMS
  • 5
  • 2
LVL 11

Expert Comment

ID: 10694895

Listening.... <SMile>

LVL 11

Accepted Solution

shaneholmes earned 250 total points
ID: 10694936
LVL 11

Expert Comment

ID: 10694939
ScreenConnect 6.0 Free Trial

Explore all the enhancements in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI, app configurations and chat acknowledgement to improve customer engagement!

LVL 11

Expert Comment

ID: 10694955
Delphi creates a wrapper around the OCX, and because Names is an interface in delphi, you will need an interface construct for an enumeration object....


Author Comment

ID: 10696273
Thanks Shane,

I have looked at the thread an copied the sample code to get the VB collections in Delphi. It does not compile since "CollectLib_TLB" is missing.
Any suggestions how to proceed?

Regards, JGMS
LVL 11

Expert Comment

ID: 10696287
Did you try removing that from the uses clasue and compiling?


Author Comment

ID: 10697922
Yes I did.
When compiling it says:
[Error] UCollect.pas(57): Declaration of 'Next' differs from declaration in interface 'IEnumVariant'
The declaration function reads:
      function Next(celt: Longint; out elt; pceltFetched: PLongint): HResult; stdcall;
whereas down in the code the celt parameter was assigned of Integer type. However, making it LonInt does has no effect.
The same error remains.

I quess the type library is needed anyway, don't you?


Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit If you want to manage em…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question