VBA Project limits?

Topic

New Reply

I had everything running just fine in a rather complex VBA project in Word 2007, then I added a couple more modules, and sure ’nuff, crash on compile sometimes, crash on run (on code that ran fine before), crash on save the project…. whenever it feels like. The total 2007 dotm is about 1200K. The size of the project portion of the ZIP file is about 1600 uncompressed.

I’ve tried saving the original template as a doc, re-saving the doc as a template and importing all the code back in again, and I’m in the same boat.

So… am I cursed? Is there a limit (number of modules, size of project, number of forms) I’m supposed to not exceed?

Generally, I’m just plain miserable. I’ve got another four or so major functions to add to this project, and hopes are not high.

Reply | Quote

Replies

I’m quite confident you haven’t hit a limit on the size of the project itself. My largest project had significantly more code.

How large is your largest module (line count)?

Since you have already tried cleaning the code by exporting/importing the modules I guess that can be ruled out.
You could take the cleaning up a step further and copy/paste out all your code into plain text files (use notepad). Then use a fresh word document and recreate the modules from scratch pasting the code in from the notepad files. That definitely rules out any garbage possibly stashed away in exported modules (although they are normally clean).

Of course for userforms this method cannot be used. You could however move the code behind the forms to a notepad file as well, empty the module and then export the forms to be on the safe side.

Reply | Quote

I’m quite confident you haven’t hit a limit on the size of the project itself. My largest project had significantly more code.

How large is your largest module (line count)?
…
Of course for userforms this method cannot be used. You could however move the code behind the forms to a notepad file as well, empty the module and then export the forms to be on the safe side.

The largest module is 128K (about 2800 lines) in .bas form, which so far as I can tell is only a text file. That one is about 3X the size of any other .bas or .frm file
One of the forms has a .bmp background, and is over 521K — that was one of the last things added before things went south.

What’s so frustrating here is that there’s no indication of anything being wrong: the code is correct, there’s no warnings that I’m doing something beyond the VBA limits.

Previous versions of this project have used a COM Add-In. I’ve been trying to back away from that because customers complain about having to run an installer will elevated privs, versus just pointing everyone at a Workgroup Template folder. But it looks like I won’t be able to add all this functionality, unless I get lucky on a rebuild this morning.

Reply | Quote

I’d split up the largest module in at least two modules. There is some sort of a 64k limit on module size, but it isn’t consistent. You may have hit it here though.

Reply | Quote

I’d split up the largest module in at least two modules. There is some sort of a 64k limit on module size, but it isn’t consistent. You may have hit it here though.

I’ve done that, but I don’t think that’s the culprit (still crashing).
The most likely suspect right now is a form with a 500+K image in its background (created by our marketing people, natch). Reloading that form, deleting the image and replacing it with smaller ones is still trouble, but I’m guessing it pegged some memory block, and there’s already trash in the project file. I’m going to rebuild that form from scratch (at least it’s relatively simple).

If this next attempt doesn’t work, I’m throwing in the towel. Thankfully, this project is on spec, and not a dependency on an ongoing project. But I’d be a hero if I got it working.

Reply | Quote

Well, I got it running without constant crashes.
The culprit was the dialog with a large background image.
However, changing to a JPG (instead of the BMP) gave me the same result, as did creating small images for the important content (company and product logos) placed in picture controls.

The primary symptom is that when opening the template, or creating a new doc based on the template, then going into VBA, checking the “Debug” menu would show the Compile menu item available, which is normally only available if you have made a change. Of course, choosing that menu would cause the crash.

This was happening even rebuilding that form from scratch, after rebuilding the entire template starting with a doc created from Normal.dotm (which is pretty darn empty except for a few utility routines — no goofy styles), connecting to the references (ADO 2.8, Scripting Runtime, and a company licensing object), and importing each piece of code back in.

The funny thing is, I have another form with picture controls on it, and it doesn’t die. The only difference I can see is that the picture control is in a frame.

(By the way, if there’s a way to put pics in a combo box without bringing in a 3rd-party control, I’d appreciate it. Another programmer created a messy little thing using a spin control and a frame, and changing the position of the image in the frame to simulate a choice by the position)

Reply | Quote

Well, you could perhaps have one pic control and use the loadpicture command to change its picture. That way you can leave the picture control empty in design mode?

Reply | Quote

Well, you could perhaps have one pic control and use the loadpicture command to change its picture. That way you can leave the picture control empty in design mode?

Innnnnteresting. I hadn’t tested to see whether an empty pic control eliminated the death spiral.
The only other downside is the need to make the pictures available in the distribution as separate files. Not a huge deal, but an annoyance (the system then needs to be able to find them — that’s the nasty part)

Reply | Quote

Just put them in the same folder or in a folder immediately below the folder that holds your Word file and this shouldn’t be a problem.

Reply | Quote