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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : All lightning effects with instructions : http://www.mediaf…

696 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