Thanks very much Charles.

My app occasionally crashes Excel, usually (maybe only) when I'm copying a
range. This happens most with "large" (by my standards) workbooks. I suspect
that's because I manipulate the Excel object model more when I have more
data. This suspicion is supported by the fact that improvements to my
algorithm which reduce the amount of data manipulation also reduce the
number of crashes, even when the workbook size isn't reduced.

On the other hand, I've also overcome crashes by dividing a large workbook
into multiple small workbooks and paging the temporarily-unneeded ones to
disk. This implied that freeing Excel resources helped, although it might
just be that making this change "jiggled" the code in such a way that the
crash I was working on at the time went away. However, it made me think that
a version of Excel with more resources -- i.e. 64 bit Excel 2010 -- might
help.

We're seeing some demand for larger workbooks, but I don't expect to go over
a few hundred MB. Actually, with current reliability, we probably cannot get
that far, because there will be too many crashes if we process that much
data. This reliability issue is my priority, and the reason for my original
question.

I've crashed both Excel 2003 and Excel 2007, depending on the data. So far
no single set of data has crashed both of them. That is, if one of them
crashes I've always been able to work around the problem by switching to the
other. Another interesting observation is that Excel 2003 crashes have (so
far) always been reproducible while Excel 2007 crashes have (so far) always
been intermittent. That is, restarting my program "fixes" an Excel 2007
crash (at least temporarily) but never an Excel 2003 crash. Some years ago I
had one repeatable crash with Excel 2003 that, after month of working
together, Microsoft tech support and the developer they were working with
admitted was the result of a bug in a generic Office garbage collection
routine. A pointer in a linked list of cell formats was getting corrupted.
Unfortunately, they weren't prepared to fix this, although at that time
Excel 2003 was the latest version. Since that experience I haven't been as
enthusiastic about Excel 2003 reliability as many people are.

Unfortunately, since my program can run for an hour or more, restarting
isn't a good solution. Also, since we sell this program commercially, we
can't tell our customers to switch back and forth between Excel 2003 and
Excel 2007 to see which one works best for them.

I've done a lot of profiling. The results vary depending on the options
selected in my program, but typically the large items a

1) Creating new worksheets and setting the column widths in these sheets.
One of the reasons for the time variation is that some customers use only a
dozen relatively large sheets (in which case, obviously, creating them
doesn't take much time) while others use up to 1,000 small sheets. In the
latter case, a half hour can be spent creating the sheets and setting column
widths. Nearly half of this time is spent setting column widths (including
hiding columns) in the newly-created sheets. The positive aspect of this is
that these operations have been dead reliable (so far).

2) Excel calculating. Incidentally, the worksheets I calculate are tiny --
typically a few hundred or maybe a few thousand cells -- but I calculate
these tiny sheets tens of thousands of times, perhaps even hundreds of
thousands of times in extreme cases. Larger sheets are created by copying
data from many tiny sheets without much further calculation.

3) Miscellaneous other manipulations to the Excel object model, mainly
copying ranges (again, this is where Excel crashes on me), transferring
values and formulae between Excel and VB, and changing row heights. One
thing that really surprised me is that my app often runs faster when I copy
a single cell at a time, rather than a larger range in a single operation.
Probably this is because I use application logic to copy only cells which
changed, but when I copy an entire range I cannot omit cells which I know
didn't change.

4) My VB code. I put this last because there's not much you can say about
it, but it takes maybe 30-50% of the time.

I've spent a lot of time optimizing my app, mainly improving the algorithms
to reduce the amount of work done by Excel. This time has, naturally,
focused on the slow operations. The result is that (with the exception of a
few items like creating new sheets and changing column widths) I've "knocked
off the peaks" -- i.e. speeded up the slow operations -- and now have an app
that spends its time distributed over a pretty wide range of areas.

Your comments that VBA would slow my app down by only ~10% is very
interesting, and surprising. I would have thought that the difference would
be far greater than that, because of the difference between compiled and
interpreted code. Are you sure about that? Do you know why the difference
isn't greater? If the reason is that some operations would run faster,
offsetting increased time taken by others, maybe I should move part of my
app to VBA.

Incidentally, my DLL does call the Windows API, as well as another process
of my own. Also, a VB 2008 app calls directly into the DLL (Excel passes a
reference to the DLL to the managed app), but that doesn't take a lot of
time so I could pass the calls from the managed code to the VBA via Excel.
My source code, including forms, is 10MB

Anyway, based on your comments, it sounds like there would be little benefit
from using 64 bit Excel, so probably I should just continue to run in as a
32-bit app under WoW.

Thanks again.

Steve


"Charles Williams" wrote in message
...
Hi Steve,

AFAIK there is no way to make your 32-bit VB6 DLL run with 64-bit
Excel.

The only easy solution to VB6 DLLs with 64-bit Excel 2010 is to
convert it to VBA, which runs happily with 64-bit 2010 (you need to
convert any Windows API calls) - you may lose 10% or so execution
speed if the DLL does extremely heavy calculations (you lose security
of course).

If you profile your solution, is the time spent in
- Excel calculating,
- in transferring data between VB6 and Excel,
- in manipulating the Excel object model,
- or in calculations inside VB6?

I don't consider 80 MB workbooks as pushing Excel limits: Memory
capacity of Excel 2003 and 2007 will easily cope with 80MB workbooks,
so I don't see any advantage to 64-bit as long as you don't need more
than 1 or 2 gigabytes.

From a reliability point of view Excel 2003 is usually better than
Excel 2007. Excel 2010 seems very promising for reliability, but
really its too early to tell.

regards
Charles

On Sun, 16 May 2010 13:32:48 -0400, "Steve Flaum"
wrote:

We have an app which creates large Excel workbooks. For example, one
workbook has 1,000 worksheets. In other cases there are fewer worksheets
but
the Excel file can be 80 MB or larger. Since this sometimes crashes Excel
2007 and 2003, I'm considering using 64 bit Excel 2010. Would 64-bit Excel
be more reliable with workbooks this size than 32-bit Excel? Is 32-bit
Excel
2010 any more (or less) reliable with large workbooks than Excel 2007?

The problem with using 64-bit Excel is that the workbook uses a 32-bit
native code DLL. Specifically, the workbook executes a VBA macro with the
following statements:

Dim mCCalc As Object
Set mCCalc = CreateObject(strName, "")
mCCalc.Init Application

The 32-bit DLL executes many Excel methods, using the reference to Excel
passed in the 3rd line above.

The DLL is coded in VB 6.0, so we cannot compile it into a 64-bit DLL.
We've
looked into porting it to VB 2010, but that would be an
impractically-large
project because the DLL has about 25,000 lines of code, much of which
would
require manual conversion.

I've read that a 32-bit activeX control cannot be used with 64-bit Excel
2010, but this isn't an ActiveX control. Is there a way to use it with
64-bit Excel? For example, could Tlbimp.exe create a wrapper which would
make the 32 bit DLL look like a 64-bit managed-code DLL? If so, could I
call
the latter from VBA? What would this do to execution speed? (The current
design runs the DLL in process with Excel, but can nevertheless run for a
couple of hours.)

Thanks.

Steve