userform crashes some computers; not mine

[Ken]

I have a rather involved userform (~350 textboxes spread through a 5
page multipage control) that is populated with data and data analysis
from a SQL query that is attached to a worksheet. It runs fine on my
computer; but crashes on everyone else's computer. The error message
they get is the useless one about e-mailing Microsoft; no line number,
error message or anything to help me track down the issue and the
computers freeze. When I debug the code on a machine that it fails
on, and step through the code which takes a about a minute it works.
I can accelerate that process by running to the cursor after the major
loops and it always works. Subsequent to stepping through the code
once, it always works for the remainder of the session. The user
closes the file and reopens and it crashes; unless I step through the
code once. I have cleaned the code, I have compared the VBA
references and they are all the same, all the computers are running
Excel 2003 SP3. There are only a few hundred rows of data and the
file is only 5M so I don't think it is a hardware limitation on the
other machines. None of the computers have any problem running the
query to get new data or refreshing the query.

I am now totally out of things to even consider that could cause the
failure on everyone else's computers unless they step through the code
first. Does anyone have any ideas?

Thanks

Ken

 

[Rick Rothstein]

I have no idea what may be causing your problem, but here is a method that
might help you narrow down the location where the problem is occurring at...
ASSUMING your program actually starts to run before crashing and that the
crash does not remove the UserForm from the screen. Just copy paste the
following code line into your program at several locations (the more the
better) throughout your code on any one of the computers that crash when
running your code...

Me.Caption = Val(Me.Caption) + 1

Then, when your program crashes, the number displayed in the UserForm's
Caption will tell you how far you got before the crash. You can then Delete
all of the above lines (Replace All the statement's text with "") and then
copy/paste it starting with each line after the crash point's location for,
say, 10 lines or so. Then when you run the program, you will get a new crash
point location. If it is within the lines you pasted the above statement,
you are done and you have the line causing you your problems; if it isn't
within the lines you pasted, delete them and do the next 10 lines or so.
Eventually, you should be able to hone in on the trouble spot.

 

[Harald Staff]

Hi Ken

This is a really low-tech reply, but when stepping works and running
doesn't, then it usually helps to add DoEvents here and there in the code.
Here and there would be places where it matters that an operation is
completed before the code continues.

HTH. Best wishes Harald

 

[Ken]

Rick/Harold

Thanks for your suggestions. They are similar to one of the things
that I had done which was to put messages boxes in various places to
track the progress before failure or generally slow things down, for
cases like this where the e-mail to Bill Gates screen is what I get in
place of a useful error message. When I did that and stepped through
the code it worked, when I ran the code it crashed immediately. I
will file the new techniques away for use in the future.

My SQL guy noticed that I had an arr(120) declared as variant that I
used to pass a batch of 106 textboxes to a subroutine where my query
data was processed and displayed. I initialized only the first 106
textbox's values to zero. When I changed the dim statement to (106)
eliminating the excess capacity it started working on the computers
that it previously failed on. As far as I can tell there is no
difference in the Excel 2003 installations on the computers; but, the
two computers that it originally did not fail one have both Excel 2003
and Excel 2007 installed. The code worked fine in 2003 and 2007 on
both. I had heard of problems supposedly caused by having both 2003
and 2007 on the same machine. I have had not had any problems, other
than I still don't like the ribbon. Maybe this is a case where the
2007 installation made something about 2003 more tolerant. Maybe not.

Thanks again for your quick responses to this question, and all your
other contributions to this group.

Ken