Fog Creek Software
Discussion Board




VBA Technical Riddle

In my long tradition of finding, diagnosing, and implementing previously undiscovered or undeveloped bugs in every piece of computing technology I touch, I have uncovered yet another beauty. [See my SQL Server -> Access Linking question for yet another;].

Basically, I am using Excel to produce well formatted reports for bonus calculations. [Before we get into it, I AM using Excel to do this and not any other reporting package]. The data is pulled in via ADO 2.5 and DB2. The reports are fairly large, consisting of a range from 10,000 to 30,000 cells. I have to print 66 of these. Well, the long and short is, it seems to be corrupting my memory. Once the batch is done, the system hangs or other programs begin to experiece strange glitches. Many of these seem related to out of memory errors, but a few of them also run the gamut from corrupted file errors to registry errors. Rebooting always fixes this process. The crashes occur randomly after the workbook has been generated, never in the same spot, but usually at the end of a report. Stepping through the system in the debugger prevents the error from occurring and the program itself sends no error messages.

Does anybody here know of problems with high volume Excel reports or anything of this nature? I know these tools weren't meant to do anything like this, but its bloody annoying with the randomness of this thing.

I have a new programmer working for me and we have named this his Excel Software Bug of Doom +5, or Carlos for short. He is having a blast, but was wondering whether you guys had any experience with this.

Dustin Alexander
Tuesday, September 23, 2003

Glad you have a sense of humor about it.  <g>  Just some random thoughts...

What's the Task Manager showing when the batch is processing -- like the memory usage?  Obviously, you want to check for memory leaks -- memory usage shouldn't be constantly rising.

Have you considered automating Excel externally from Visual Basic, rather than internally through VBA?  Perhaps it would help with stability.  If you use VB6, you shouldn't have to rewrite any code (and won't have to worry about COM interop issues with VB.Net.)

Would it be possible to process the batches in smaller chunks?  If you automated Excel through VB, you could programatically open an instance of Excel, process a single report, and then shut down that instance.  Lather, rinse repeat.  Hopefully, restarting Excel after each report would give itself the opportunity to clean up whatever resources are causing these glitches.

Robert Jacobson
Tuesday, September 23, 2003

Robert, fun with Excel. I always looked at bugs as challenging games that I'm paid to play.

Actually, this program started out in VB6, then moved into VBA because of this error. Externally, using COM, you experience similar lock ups, but after 3 reports. Right now, we manage to pound out an average of 16 before it cracks.

My initial idea was that this was in the COM implementation (thus the move into Excel). I'm told that this relies on the windows messaging API or a similar API that can lose sequence rather easily when presented with a high volume of messages. With the number of calls we are making, I can easily see the stack being overwhelmed and thrashing. But we are no longer using external COM and are still encountering the same error.

Currently we are sending one COM call per cell per format request. This is not ideal, and will be replaced with batch arrays once this bug has been isolated. I don't want the users to rediscover this on a high volume report. After all, reducing the work load is simply a work around, not a fix.

As for the managers, the memory seems to stay flat, as does the processor. Once the bug occurs, these tools are flaky at best, downright wrong at worst.

More interesting triva: I have tried this on Office 97 and 2000 on both Windows 98 SE and 2000 Pro and three different machines. Same bug. When I initial wrote this in the spring (as a one shot fix), I was running on a 64 MB RAM system and it killed after three reports. Upgrading to 512 MB of RAM did absolutely nothing to change the response time or occurrence rate of the problem, nor did switching computers, OS, or Office versions.

Dustin Alexander
Tuesday, September 23, 2003

Microsoft Windows is the _best_ game there is - you wander around until bam! BSOD

Alienware
Tuesday, September 23, 2003

Also, restarting or initializing the Excel instance externally is a no go. This does not clean up the system and seemed to make the problem worse, as it initiated memory issues. Excel is none to clean an implementation when it comes to memory clean up. 2K is better, but still seems to have some leaks.

Dustin Alexander
Tuesday, September 23, 2003

Our current hypothesis is that this is an implementation error in either the ODBC code or the Excel code. We're leaning towards Excel but working to isolate it as we speak. This would be a memory allocation issue that is causing the system to overwrite arbitrary sections of memory when a particular state in the application is created. This would account for the randomness of the crashes.

Dustin Alexander
Tuesday, September 23, 2003

This sounds really bizarre.  Sorry I can't be more helpful.

Just for the sake of asking, would it be possible to migrate your code to VB.Net, using Excel 2003 and Visual Studio Tools for Office?  Last week, I saw a demo of VS Tools for Office for a project that was similar to what you're doing -- it used an Excel template that automatically generated sales reports by grabbing data from a database.  Looked pretty cool.  Of course, no guarantees that it would work any better.  <g>

http://msdn.microsoft.com/vstudio/office/default.aspx

Robert Jacobson
Tuesday, September 23, 2003

I don't have any suggestions to offer to fix Excel, but I thought I would throw in my .02 on alternatives. I work with a group of users who always seem to be stretching the bounds of sanity with Excel, and I have had a good deal of success replacing their flaky reports with Perl scripts once the formats become stable.
I just use ActiveState Perl and the Spreadsheet::WriteExcel module. I haven't had any complaints from Excel97 or 2000 when reading the files that are created in this way.
I offer this because I started as a VB6 hacker, and I had almost no trouble picking up enough Perl to make this work....

Slashdot refugee
Tuesday, September 23, 2003

The technical situation is not a problem, as my team experience in almost every working language today. Its the political situation of our client, who is an exclusive MS shop of the VS 6 variety [no dot net support on the roadmap], and our users, who are moderately uncomfortable with Excel [much less PERL]. To be honest, I was considering alternatives, including PERL, HSSF, and writing our own format [which we've done for past projects], but this is a small project that will have to be embraced by the client's IS department. So, I'm stuck with VB. Thanks for the alternative suggestions, though.

Dustin Alexander
Tuesday, September 23, 2003

Does the Excel process actually shutdown when you are finished with it?  If not, it points to a problem with the VBA garbage collection.  Try explicitly setting references to Nothing when you are done with them and introducing yeilds into tight loops.

If that doesn't help, perfmon could be very useful - check the available system resources before and after running your scripts (and closing Excel) to see which resources have been consumed.

r1ch
Tuesday, September 23, 2003

VBA is apparently better behaved than external driving of Excel (VB, COM, whatever).

But the alternate file format is a good approach to consider: Can you use the XMLSS format, new in a recent (pre-2003, maybe XP) version?

mb
Tuesday, September 23, 2003

Dustin,

I've done work on large Excel reports before and never seen any issues.

The issue is probably to do with your code (for example, the code you are using to print).

Can you post it? If here is not a suitable location, I am sure we can help at http://peach.ease.lsoft.com/scripts/wa.exe?A0=visbas-l .

Seeya

Matthew
Tuesday, September 23, 2003

Dustin-

It sounds like you changed from using VB6 to automate Excel via COM to just running the entire reporting job from inside Excel, is that correct?  Just making sure.  (I had some half-formed thought that you might be having problems marshaling all that data between apps.)

It might be instructive to extract your data into ADO, save it to an intermediate CSV or tab-delimited file, open that from Excel and then apply formatting...

Oh!  I just saw something else:  "Stepping through the system in the debugger prevents the error from occurring and the program itself sends no error messages."  I sometimes get strange errors like this.  The only way I know of to avoid the problem is to sprinkle DoEvents statements throughout the code, which is a bit kludgey but seems to work.

Hmmm... actually, that might account for the improvement from "~3 reports before a crash" to "~16 reports before a crash" -- if you're not doing external COM automation (read: marshaling), your code can get further before some other thread gets pissy about being shoved to the background...

Sam Livingston-Gray
Tuesday, September 23, 2003

>It might be instructive to extract your data into ADO, save it to an intermediate CSV or tab-delimited file, open that from Excel and then apply formatting...

That would be my path/ bet also. Grab the data to a local csv file. Then load it up (much faster then some individual cell processing).  Breaking it up into series of steps tends to also reduce memory usage. Running ado/automation/cell merging stuff all at the same time tends to stran things.

Albert D. Kallal
Edmonton, Alberta Canada
kallal@msn.com
http://www.attcanada.net/~kallal.msn

Albert D. kallal
Wednesday, September 24, 2003

Dustin,

You can use the following type declarations and Win32 api calls to catch GPFs within your VBA code:

Private Declare Function SetUnhandledExceptionFilter Lib "kernel32" (ByVal lpTopKLevelExceptionFilter As Long) As Long

Private Declare Sub CopyExceptionRecord Lib "kernel32" Alias "RtlMoveMemory" (pDest As EXCEPTION_RECORD, ByVal LPEXCEPTION_RECORD As Long, ByVal lngBytes As Long)

Private Type EXCEPTION_RECORD
  ExceptionCode As Long
  ExceptionFlags As Long
  pExceptionRecord As Long   
  ExceptionAddress As Long
  NumberParameters As Long
  ExceptionInformation(EXCEPTION_MAXIMUM_PARAMETERS) As Long
End Type

Private Type EXCEPTION_DEBUG_INFO
    pExceptionRecord As Long
    dwFirstChance As Long
End Type

Private Type CONTEXT
  FltF0 As Double
  FltF1 As Double
  FltF2 As Double
  FltF3 As Double
  FltF4 As Double
  FltF5 As Double
  FltF6 As Double
  FltF7 As Double
  FltF8 As Double
  FltF9 As Double
  FltF10 As Double
  FltF11 As Double
  FltF12 As Double
  FltF13 As Double
  FltF14 As Double
  FltF15 As Double
  FltF16 As Double
  FltF17 As Double
  FltF18 As Double
  FltF19 As Double
  FltF20 As Double
  FltF21 As Double
  FltF22 As Double
  FltF23 As Double
  FltF24 As Double
  FltF25 As Double
  FltF26 As Double
  FltF27 As Double
  FltF28 As Double
  FltF29 As Double
  FltF30 As Double
  FltF31 As Double
  IntV0 As Double
  IntT0 As Double
  IntT1 As Double
  IntT2 As Double
  IntT3 As Double
  IntT4 As Double
  IntT5 As Double
  IntT6 As Double
  IntT7 As Double
  IntS0 As Double
  IntS1 As Double
  IntS2 As Double
  IntS3 As Double
  IntS4 As Double
  IntS5 As Double
  IntFp As Double
  IntA0 As Double
  IntA1 As Double
  IntA2 As Double
  IntA3 As Double
  IntA4 As Double
  IntA5 As Double
  IntT8 As Double
  IntT9 As Double
  IntT10 As Double
  IntT11 As Double
  IntRa As Double
  IntT12 As Double
  IntAt As Double
  IntGp As Double
  IntSp As Double
  IntZero As Double
  Fpcr As Double
  SoftFpcr As Double
  Fir As Double
  Psr As Long
  ContextFlags As Long
  Fill(4) As Long
End Type

Private Type EXCEPTION_POINTERS
  pExceptionRecord As EXCEPTION_RECORD
  ContextRecord As CONTEXT
End Type

In your Workbook_Open event, switch on the unhandled exception filter:

SetUnhandledExceptionFilter(AddressOf UnhandledExceptionFilter)

In your Workbook_BeforeClose event, remember to switch off the unhandled exception filter:

SetUnhandledExceptionFilter 0&

In your unhandled exception filter, you can turn a GPF into a normal VBA error and then trap it with your normal error handling:

Private Function UnhandledExceptionFilter(ByRef ExceptionPtrs As EXCEPTION_POINTERS) As Long
Dim Rec As EXCEPTION_RECORD, ExceptionText As String

'Get the current exception record.
Rec = ExceptionPtrs.pExceptionRecord

'If Rec.pExceptionRecord is not zero, then it is a nested exception and Rec.pExceptionRecord points to another EXCEPTION_RECORD structure.  Follow the pointers back to the original exception.
Do Until Rec.pExceptionRecord = 0
  CopyExceptionRecord Rec, Rec.pExceptionRecord, Len(Rec)
Loop

'Translate the exception code into a user-friendly string.
ExceptionText = GetExceptionText(Rec.ExceptionCode)

'Turn Win32 error into normal VBA error
VBA.Err.Raise Rec.ExceptionCode,
"ExceptionFilter.UnhandledExceptionFilter", ExceptionText

End Function

To get the exception text, complete the following function:

Private Function GetExceptionText(ByVal ExceptionCode As Long) As String
'This function receives an exception code value and returns the 'text description of the exception
Select Case ExceptionCode
  Case EXCEPTION_ACCESS_VIOLATION
    GetExceptionText = "Access violation"
  Case EXCEPTION_DATATYPE_MISALIGNMENT
    GetExceptionText = "Data type misalignment"
......
......
  Case Else
    GetExceptionText = "Unknown (&H" & Right("00000000" & Hex(ExceptionCode), 8) & ")"
End Select

End Function

Once you're able to trap the GPF, you might then be able to figure out whether it's related to VBA code or whether it's something internal to Excel.

HTH,

Mark
----
Author of "Comprehensive VB .NET Debugging"
http://www.apress.com/book/bookDisplay.html?bID=128

Mark Pearce
Wednesday, September 24, 2003

I am the coder that Dustin mentioned was working on the project. 

Thanks for all of the suggestions, however, the bug remains undefeated.  After fruitlessly testing out some of your suggestions this morning I spent the rest of the day narrowing the bug down to the fewest possible lines of code.  I've got it down to the point where I can be reasonably sure that it is something simple.  The code is just too sparse to be anything but a simple error or an MS error.  If it were an MS error, someone should have mentioned it somewhere, right ?  For those interested in attempting to replicate it, the code follows:

-da.bas-
Sub Main()
dim i as Integer
Dim nctr As New NCTrackingReport

for i = 0 to 1000
  nctr.produceSalesReport
next
end sub

-NCTrackingReport.cls-
Public Sub produceSalesReport()
  Dim wbBook As Workbook
  Set wbBook = Workbooks.Add

  wbBook.Close False
  Set wbBook = Nothing
end sub

I've put a watch on wkBook and it is getting set to nothing.  My watch on workbooks indicates that close is working as well, and a copy is not getting left inside the collection.  I am running this on a p3 800 with 128mb of ram and win98 se 2 (4.10.2222 a).  All the code is in excel 97 SR 2 - although, as Dustin mentioned, we have tried a variety of systems.  Still haven't retried since narrowing it down though.  Another note: Main() is called directly from a command button on a work sheet.  This is not a data error.

Dylan Bruzenak
Wednesday, September 24, 2003

Oh yeah, it fails at a random number of iterations(depending, I suppose on system resources).  It is usually around 300, although it managed to hit 1400 repetitions before I removed all the range code.  Strange that removing code made it worse...

Dylan Bruzenak
Wednesday, September 24, 2003

And yet another addendum:  After removing half of the range code it went from 17 iterations to 200, and removing more slowly moved it up to around 1400, until I removed all of the range code, at which point it dropped back down.  Chaos theory anyone ?

Dylan Bruzenak
Wednesday, September 24, 2003

You're creating 1000 new workbooks, each with 3 worksheets? Do you really need that many workbooks? Or do you just need a bunch of sheets?

Rick
Wednesday, September 24, 2003

I tried out your code on Excel 2000 on Windows XP Pro, but I couldn't reproduce your problem.  I still suspect that it may be some kind of garbage collection problem in VBA - if you add a delay into the loop (maybe even doEvents would do) does that help?

r1ch
Wednesday, September 24, 2003

I pulled a relatively late night tonight and found a work around for the bug(I hope).  Instead of creating that many worksheets, I just create one and then clear and rewrite it, stopping to save out for each report iteration.  The code I inherited was creating individual workbooks, and I got stuck in that mentality.  Sometimes you have to be a little tired to see the light.  I told it to output 10000 reports before I went home for the night, which should be a reasonable amount of overkill.  If it makes it through that I should be homefree.

1000 is just an arbitary number I chose to force it to fail - since I removed all the data I had to increase the work load to generate the error.  Once I could guarantee the occurance of the error I could test fixes for the least number of lines, and narrow it down to the specific cause of the problem.  Once I had that, I could start thinking about work arounds. 

Before I removed the data it failed after 17 reports. 

Delays didn't help at all - neither did shutting down excel manually and restarting it between reports.  DoEvents had no effect.

Something about the creation code leaves a memory footprint.  I found a few other people on the internet who were having this problem, but no direct solutions.  It seems to only crop up in certain absurd cases.  A very odd bug.  I read some things on Microsoft's site that indicate that some of this, at least, is excel 97 specific, so let's hope the work around cures the 2000 problems as well, otherwise I will have to go back to basics on that OS. 

Dylan Bruzenak
Wednesday, September 24, 2003

I was going to try this yesterday...but had to run out for dinner and lots of fun.

My first try it ran OK from the VB IDE. I then placed a button on the excel sheet, and got the out of memory. So, it is some UI refresh problem. Next thing I tried was putting in the old DoEvents trick. Sure enough, it now works.

If you add a doevents, then you can run to 2000 times no problem.

Try:

Public Sub produceSalesReport()

  Dim wbBook As Workbook
  Set wbBook = Workbooks.Add

  wbBook.Close False
  Set wbBook = Nothing
  DoEvents
 
End Sub

I am right now again in a hurry...but You might also just turn off the screen refreshing (don’t know off the top of my head how to do that).

Anyway, the DoEvents will work, but does slow the loop down. I am guessing that some interface update stuff (garbage collection) don’t occur in that loop unless you let Excel grab a gulp of air each time. This is not the first time I seen DoEvents fix trouble like this.

(tested on a a97, sr2b, and winME box, 2000 inters each time).


Albert D. Kallal  (MVP – Microsoft Access)
Edmonton, Alberta Canada
kallal@msn.com
http://www.attcanada.net/~kallal.msn

Albert D. kallal
Thursday, September 25, 2003

Application.ScreenUpdating = False

will turn off screen updates - but probably still need the DoEvents

DJ
Thursday, September 25, 2003

I tried those two things, and while they may work on the sample code I provided,  they do not work when I hook the rest of my statements up.  That was one bug out of 3 that I have identified.  Come monday I will make sure - but I was able to run through all my reports without a hitch at the end of the day today by removing two lines of code that appeared in several places in my code:

range(somenumber, somenumber2).font.bold = true
and range(somenumber, somenumber2).ColumnWidth = someothernumber

With actual reasonable numbers for the somenumbers, not variables.  Very, very strange. 

I am going to try doing the formatting once somehow instead of for every report.  Anyone know if there is a way to construct aggregate ranges ?

Dylan Bruzenak
Friday, September 26, 2003

The 'that' I refer to in my above statement is the workbook error.

Dylan Bruzenak
Friday, September 26, 2003

May I suggest "cleaning the vba code" by exporting the code. saving the workbook and closing it, then after opening, import the code back in?  I had similiar issues and this worked for me.

Ron Gatewood
Tuesday, May 18, 2004

*  Recent Topics

*  Fog Creek Home