Solved: Best place for macro storage?

[BoatwrenchV8]

I was told that storing macros in Normal.dot is a bad idea because if Normal.dot goes bad, the solution is to delete it and Word will create another copy of it. Poof, all your custom macros are gone. Been there, done that. However, this advice was given in regards to Word before 2003, I am now using Word 2010. What is the best place to put macros in Word 2010? Normal.dotm or a different template either in the STARTUP folder or as an add-in??

[macropod]

Hi Boatwrench,

Any template file, be it Normal.dot or anything else is subject to corruption. Hence, you should always keep a backup. Whilst deleting Normal.dot can resolve a number of issues - where Normal.dot has become corrupted - so too can re-naming Normal.dot to, say, AbNormal.dot. It that fixes the problem, you can then set about restoring a known good backup of Normal.dot, or recovering the contents of the corrupted one. Of course, if it didn't fix anything, simply deleting the new Normal.dot and re-naming the AbNormal.dot will get you back to where you were.

[fumei]

Paul and I have had some divergence of opinion on this before. I will reiterate my opinion.

It is true that any template file (be it Normal or anything else) is subject to corruption. HOWEVER, the greatest contribution to corruption is the amount of I/O (write or Input, and read or Output) operations. Of these two write operations are the biggest culprit. That is, changes (write operations) to the file cause the most potential corruption.

ANY changes to settings to Word - be it properties, GUI settings, or macro coding - cause write operations to Normal. In other words, it is a fairly common thing. It happens all the time.

On the other hand, dedicated macro containers usually only have write operations with...macro coding. Once stabilized, macro containers - dedicated code files (templates) - have minimal write operations. READ operations, sure - that is what they are for. But write operations, no.

Thus, dedicated code containers have much less I/O. Thus, they are much less prone to corruption. Further, in a corporate environment they can be made to be read only, in which case the potential for coruption is very significantly reduced.

Of course, as Paul points out, backup of ANY crucial file is important.

Nevertheless, I strongly feel that keeping important macro code in Normal is to be avoided. It is not just me. Microsoft itself recommends keeping macro code outside of Normal.

Fortunately, through the use of global template it is very easy to have the advantage of code in Normal (making it available everywhere), without it actually be in Normal.

In other words, yes, back a backup of a known, good Normal - via a rename, or a good copy in a different folder - is a good practice. But this is not the same reason for having your code procedures in its own template.

Further, it is also a good idea to have multiple templates. You can load many templates as globals, and you can load and unload dynamically on the fly. True this is only significant if you have MANY procedures. If you only have a few (say < 100), sure keep them in one. But if you have hundreds, then separating them into logical chunks is done for the same reason you chunk out long procedures into smaller ones...it makes debugging easier. It makes reuseable code more manageable.

[BoatwrenchV8]

Thank you both for your reply. I have 2 types of macros, the type that has to be modified in some way each time it is used and the type that doesn't. The total number is about 150. I think the plan will be to put the truly reusable, as they are, macros in a separate template but keep the ones i have to fiddle with in normal. Since the layout of the ribbon is not dependant on Normal.dotm and i have normal.dotm being backed up by McAfee, i should (i think) be safe.

I guess I am adopting both of your approaches to this.

Has anyone ever had any crashes because of the the number of macros in the default, NewMacros, module? I do not know if it is related or not but i had word crash frequently when i had more than 60 something macros in that module. Crashing stopped when i moved them to a different module. May be totaly unrelated but this issue is partly why i am looking into the best place to store my code.

[fumei]

Has anyone ever had any crashes because of the the number of macros in the default, NewMacros, module? I do not know if it is related or not but i had word crash frequently when i had more than 60 something macros in that module. Crashing stopped when i moved them to a different module.

That would make me highly suspicious! I would tear them apart until I found the problem, because this should NOT be normal. Pun intended.

I have 2 types of macros, the type that has to be modified in some way each time it is used and the type that doesn't.

Really? Please post an example of one that "has to be modified in some way each time it is used."

[BoatwrenchV8]

I have 2 types of macros, the type that has to be modified in some way each time it is used and the type that doesn't.

I need to clarify this further and it should of read, " I have 2 types of macros, the type that has to be modified in some way each time it is used on a different project and the type that doesn't."

Really? Please post an example of one that "has to be modified in some way each time it is used."

An example would be the code I posted in a different thread, located at: http://www.vbaexpress.com/forum/showthread.php?t=41783

I will have to change the file extension every time I am processing something other than an RTF file. For the current project I am involved with, it is fine. But when I get slightly different data or data from a different company for a different project, the tweaking has to happen.

...
[vba] 'Change the file extension to what you want to find.
'
'I would like to put a file name extension picker here.
' instead of having it hard coded.
'
' sFile = Dir(sPath & "*.doc")
sFile = Dir(sPath & "*.rtf")

'Loop through all .doc files in that path
Do While sFile <> ""
[/vba]
...

and also the clean up of the data I extracted from the RTF file using wildcards.
...
[vba] 'clean up the text from the conversion of the table to text

Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "(Omega)(*)(Table)"
.Replacement.Text = "\3"
.Forward = True
.Wrap = wdFindStop
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchAllWordForms = False
.MatchSoundsLike = False
.MatchWildcards = True
End With
Selection.Find.Execute Replace:=wdReplaceAll

With Selection.Find
.Text = "([a-z])(*)(^13)(*)([a-z])"
.Replacement.Text = "\1\2 \4\5"
.Forward = True
.Wrap = wdFindStop
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchAllWordForms = False
.MatchSoundsLike = False
.MatchWildcards = True
End With

Selection.Find.Execute Replace:=wdReplaceAll
[/vba]
...

There will be times the terms I am working with in the data file will be different. One time it will be table 123 <description>, next time it will be listing 123 <description>, another time it will be 123.01 <description>.

Please take the above into consideration when looking at my posting located at http://www.vbaexpress.com/forum/showthread.php?t=41783

[fumei]

You a variation like .doc versus .rtf?

These are strings. Simply have a string input parameter. Then...no need to have ANY change to the actual procedure code.
[vba]sFile = Dir(sPath & "*." & Inputbox("File extension? e.g. doc, rtf") & "") "[/vba]

OR, have the file extension (as string) an input parameter:[vba]

Sub ListAllDataTableHeadersV1(FileExt as String)
.....
sFile = Dir(sPath & FileExt )

....
End Sub[/vba]

Which is called from another procedure:[vba]
Sub DoIt()
Dim GetFileExt as String
GetFileExt = InputBox("File extension? Type e.g. doc, rtf")
GetFileExt = "*." & GetFileExt
Call ListAllDataTableHeaders(GetFileExt)
End Sub
[/vba]

[fumei]

I want to add an important point. The code above (like most of what I post) does NOT contain appropriate error-trapping. For example, I sure as heck would not have in my own code a string as a file extension, without SOME kind of error-trapping. At minimum some testing for common extensions (in this case "doc", or "rtf", or "txt").

[BoatwrenchV8]

hmm... didn't think to us an input box in the first post. in the second post about error trapping, I agree that I need to include some code for for that. Since the code currently is just for me and isn't going to be distributed to my boss or co worker, it isn't too big an issue. BUT... The day will come when it is distributed to my co worker to use. At that point the code will have to be as bullet proof as possible because she is not familiar what-so-ever with VBA.

I got some experimenting and home work to do. I am going to mark this thread as solved since my original questions has been answered and post an update to my other posting at http://www.vbaexpress.com/forum/showthread.php?t=41783
when I get done experimenting.

Thanks
Rich

[fumei]

Have fun with error-trapping something to bullet proof. It can sometimes be done (and some times not), but it often takes a great deal of effort. Maybe even 50% more work than any coding.

Two possible suggestions. First, take a good look at some of the code posted by Frosty. He often has some excellent error-trapping.

Second is in regard to error-trapping user text input. Where ever you can do NOT have user text input. They can type in the wrong thing. So...use a userform with a combobox and have them select something. In this case, say a simple userform with a combobox with doc, rtf, jpg, docx, docm (or whatever). They SELECT one, and THAT s what is use for your other code.

Boom. Thee is no possible way for them to type something incorrectly. Although...more error-trapping, make sure the control does not allow any typing new items, AND you trap for a cancel of the userform.

[BoatwrenchV8]

Will do and have been thinking about using a user form. hmm.