Organizing VBA Routines, Sort Of

This is not a routine for just Visio VBA, but it will work for other Office Apps that use VBA.

VBA modules do not have to have unique procedure names. As long as you do not Run the module, VBA will not complain. The VBA editor does have a drop down alphabetical list for the current routines list that lets you select the next routine, but the underlining code is not necessarily sorted. You could have three routines with the same name and the first is in the middle of code, the second is at the end and the final one is at the beginning. VBA des give you an indication of relative location with a slider on the right side of the editor.

If I like order so much, why is my modules not already sorted? I have been working on a project to create Visual Visio Section shapes and the code needed a cleanup. It was in several Visio drawings that evolved over time. The routines were sorted, but by concatenating the code, it became a mess and I needed a way to organize the routines.

So, I combined all the routines from the various drawings and put them in a Word document, I then wrote some Word VBA code to search the document for “FUNCTION” or SUB and changed those lines to Heading1.

Sub AddHeading1()
Selection.WholeStory
Selection.Font.Size = 14
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
Selection.Find.Replacement.Style = ActiveDocument.Styles("Heading 1")

Selection.HomeKey Unit:=wdStory
With Selection.Find
    .Text = "sub "
    .Replacement.Text = ""
    .Forward = True
    .MatchWholeWord = True
End With
Selection.Find.Execute Replace:=wdReplaceAll
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
Selection.Find.Replacement.Style = ActiveDocument.Styles("Heading 1")
Selection.HomeKey Unit:=wdStory
With Selection.Find
    .Text = "function "
    .Replacement.Text = ""
    .Forward = True
    .Wrap = wdFindAsk
    .Format = True
    .MatchWholeWord = True
End With
Selection.Find.Execute Replace:=wdReplaceAll
End Sub

Of course, that was not perfect some of the comment lines were also changed. So by switching to Outline and just showing Level 1, the mistakes were visible and it was just a matter of changing the Style of those lines to be Normal. At that point, as a safeguard, I saved the document. If I messed up with the sort, I had a version I could go back to.

With the text in Outline mode and only showing Level 1, I selected all the code and switched to Home and then Sorted the selection. This gave me most of what I wanted. The problem was the Public and Private qualifies so I removed them and redid the sort. The only problem was all the Functions were at the beginning and the Subs at the end.

I tried the same thing by concatenating .BAS files, but ,BAS files have a starting line of ATTRIBUTE that gives the name of the module. I got around this by adding ATTRIBUTE to the lines that are changed to Heading1 aand those lines were grouped together and were easy to find and delete.

I hope that helps.

John… Visio MVP in x-aisle
JohnVisioMVP.ca