Practical limits of Word/VBA apps

Question

The company I work for has an internally developed Word2003/VBA application that's already about 6.5 mb in size and they're looking to add an additional 200+ macros to it, which, I'm assuming, will make it much larger. This seems to me to be a terrible idea, but finding resources to redevelop the tool with VSTO or some other more useful technology will be a challenge.

So here are my questions:

• Is it okay, in a pinch, to have a business dependency on a Word template with macros that's 10, 20, or 30 mb?
• Is there a Microsoft-defined or suggested limit (file size, number of macros, level of complexity) to how far you can/should extend a VBA app before it becomes unusable?

In essence, I'd like to know if there is a compelling technical reason to bite the bullet and redevelop this app.

Answer 1

I would look at moving some of those macros to an Add-in. Still VBA, but can be used on multiple files and gets the code away from the data (which is a programming win, generally).

--Edit-- As for scaling and size... I think it's all in the design. If it is thrown together, performance will suffer. The file can handle the overall size, but there are limits to Module size (64k), and procedure size (not well documented, but the IDE will let you know when you've hit it). If you start to approach 30mb of text only then you're going to want to find another solution.

You haven't mentioned security, but since this has to do with medical information it should be said that VBA is not secure. If the code is proprietary, and being offered to outside sources you might want to wrap it up in an .xll and install as an add-in. This might actually offer a faster runtime as well.

Answer 2

This will give you the limits of Word 2003/VBA: Operating parameter limitations and specifications in Word. Given the limit is 150 macros, it doesn't look like they will be able to continue to use this to add 200+ macros.