Recompiling removes error?

[Geoff]

I have a large VBa applciation in Word which started development in Word XP.

The application has to be amended occasionally to meet new requirements.

For the last year I have been dogged by a weird error.

Part of the program uses a random access file to save and recover data. The data is a user defined type (made up of other types). I have checked it rigorously to ensure that all string lengths etc are specified.

The application works perfectly. Then I change a bit of code which is nothing to do with the data storage/recovery side. I recompile and save the template. When I run it and try to get data from the database I get a "bad record length" error.

This happens even if the new code has nothing to do with the data, and hasn't yet been run.

I can force it to complie again. I rem off a block of code (again, nothing to do with the data part), recompile, unrem it and recompile again. Eventually this works - usually at the fifth or sixth attempt - and I have a fully working application again until I next to to amend it.

The compiled application is used daily by about 300 colleagues without any problems. Until the next change.

This is driving me mad!

All suggestions welcome.

Geoff

[TonyJollans]

Sometimes VBA projects can get corrupted, and all sorts of weird things can happen. It is worth recreating the project from scratch - export all the modules, and import them into a brand new template - to see if that removes the issue.

[Geoff]

Thanks.

I did try that without success but I think I'll try it again.

The export-import did reduce the size from 3.3MB to 1.6MB, but the next little code tweak-compile-save shot it back up to 3MB.

Geoff

[Paul_Hossler]

There are module size limits in VBA.

Ron Bovey has a Word Code Cleaner that automates the Export/Import process.

http://word.mvps.org/Faqs/MacrosVBA/TemplateBloat.htm

I saved it as a 2007 template and it runs fine and does the job

If you export the modules and user forms to a folder, check and see if any is approaching the 64K limit.

Try to split the ones close to 64K into two modules and see if that makes it more reliable

Also, the way I understand VBA to work is that the source is in P-code (pseudo code) format, and when you compile it (Alt-D or opening the template) it expands to its normal (working) length. I believe that it's not true compliled code, but interpetted.

Paul

[Geoff]

Many thanks.

I didn't know about the size limit.

None of the modules exceeds 30k, but one form (coincidentally the last complete form I added before the problems started, but one that is not run before it crashes!) has .frx 48k and .frm 23k. So I guess I need to break that down a bit, but it won't be easy.

Maybe it is time to get to grip with the concept of child forms rather than using a 4-tab multi-page.

Thanks to the link to the code cleaner. I will give that a whirl.

Meanwhile, that post also says this:

The Word Code Cleaner cannot remove bloat caused by extensive customizations of Command Bars or Key Bindings. If a project contains substantial customization of this sort, or if you frequently modify the customization and save the changes, the project will eventually become corrupt, even if you do regularly clean it with the Word Code Cleaner.

My template includes a toolbar (from which the various forms are called up). Each time I add a form it has to be added to a submenu on the toolbar. Is that the sort of behaviour he means?

Geoff

[fumei]

"My template includes a toolbar (from which the various forms are called up). Each time I add a form it has to be added to a submenu on the toolbar. Is that the sort of behaviour he means?"

Yes.

"None of the modules exceeds 30k, but one form (coincidentally the last complete form I added before the problems started, but one that is not run before it crashes!) has .frx 48k and .frm 23k. So I guess I need to break that down a bit, but it won't be easy.

Maybe it is time to get to grip with the concept of child forms rather than using a 4-tab multi-page."

I do not think I agree.

"has .frx 48k and .frm 23k."

Is this VBA code, or a compiled VB DLL? You use of "compile", and the reference if .frx and .frm make me wonder.

[Paul_Hossler]

reference if .frx and .frm make me wonder.


If you go into the VBE and Export a UserForm, it gives you a .frx and .frm file

Paul

[fumei]

Yes, true...but what is the code, or use, that would bring them BACK into a project? Because until they are...they are just files named .frx, and completely useless to a VBA project.

So say you have Office app (Word, Excel..whatever) and you have its VBA project open. Until you DO something to import a .frm or .frx, they are as relevant as a .jpg, or a .wpd or a .tif or a .rlx or a ....

A VBA project does NOT use a .frm in any way until it is actually imported into the project. Assuming, you have Exported and removed it from the project. if you have, the exported .frm is NOT useable by the porject. Yes, it is a file, but it is not a useable file until you import it back in (again assuming you removed it). if you have NOT removed it...then that .frm is simply a kind of back-up file.

Yes, a VBA project can dynamically load/import .frm files.

I want to clarify if that is - in fact - what the OP is doing.

This is my point. Normally, there is no mention of a .frx and .frm to a VBA project. A VB project, sure; but not VBA.

[Paul_Hossler]

After I run the Code Cleaner, I check the size of the exported files to see if any approaching the 64K limit, so I just figured that's what the OP was doing also. That's the only way I know to check the module size.

Paul

[fumei]

And indeed you are correct, that is the only way to check the module size. However, the point is again...is this VBA?

[Geoff]

Yes, it is VBa.

If I export module Y, I get Y.bas as a file. If I export from frmForm, it generates frmForm.frm and frmForm.frx I have been adding the two to check for the size limit.

I have no idea how to check the size of the compiled form. I thought VBa compiled on the fly, and that you could not save compiled elements? Is it possible to export a form as a .dll?

Turning to the toolbar question, I assume that the answer is to generate the toolbar from code each time the template is run. I remember doing this years ago, and will have to work out how to do it again. As far as I can remember, it gets tricky when you want to add items to a sub-menu under a toolbar button. Can anyone point me to a fairly simple guide?

Similarly, because a few of my users use Word 2007, I need to know how to construct a toolbar in that version. And, I suppose, a bit of code to check which version is in use!

Meanwhile, may I say a big thank you to everyone who is trying to help.

Geoff

[fumei]

"Is it possible to export a form as a .dll?"

Not with VBA. VB, yes.