views:

259

answers:

2

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.

+2  A: 

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.

guitarthrower
Got it. So do you know of any guidance out there on how far you can scale Word/VBA apps?
see edits. hope that helps.
guitarthrower
This is very helpful. Thanks.
+5  A: 

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.

Otaku
+1 for reading through Word specifications
Alison
This is exactly what I was looking for. Thank you.
+1 indeed. also note on that link that max file size is 32mb (for text, not including graphics. Not the 50-100mb I had listed in my answer.
guitarthrower
What is their definition of "macro"? I have templates with more than 150 VBA subs/functions.
Foole
interesting. i wasn't about to write 151 to test this out. my theory here is that a macro is a sub routine, not a function, because i have yet to find in the help files the term *macro* used with a function.
Otaku