How to sort the code so Subs are in alpha order?

[Jtorres13]

How to sort the code so Subs are in alpha order?

I tried to search for an answer but all posts were about code to srto something else. I want to sort THE CODE! LOL

I have a rather large module and keep adding subs (macros) to it. These are macros created in Word 2003 (VBA).

The subs are sorted in alpha order when I look in the drop down list of sub names, but, becasue I add them at the bottom of the code window, they are not sorted in alpha order.

Is there a way to sort the code window, so that the subs (routines, macros) are in alpha order, by name?

See pic.

 

[SkipVought]

hi,

So why does it matter? The dropdown IS in alpha order and gets you to the result in mere seconds.

Otherwise you'll have to COPY the entire module contents to an Excel sheet in Col A, and then construct a formula in column B that will propogate the procedure name thru each row of your procedure and in col C generate a unique sequential number.

Then you can sort on col B and col C.

Finally copy the data in col A and paste back into your module.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[MajP]

A lot of VBA developers use/swear by this

http://www.mztools.com/v3/download.aspx

It provides that feature and many more.

 

[Jtorres13]

Sorry, SkipVought. More detail: I'm comparing three VERY similar, rather large modules and the subs are not listed in the same order in the code window. It would be easier to go down the document comparing, than jumping around. I have a headache!

I'll try your Excel suggestion if MajP's freeware suggestion doesn't work. Has anyone else used mztools? I'm weary of viruses hidden in freeware.

Thanks, Majp. I'll check out the link from my home PC; not allowed to download at work.

 

[fredericofonseca]

I'ved used mztools but not intensively - no virus on the version I downloaded but as usual be cautious

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[MajP]

Read the extensive reviews on MZtools. It is very widely used by vba developers.

 

[UnsolvedCoding]

In this case I can argue both ways but have a question.

Why can't you create additional modules?

According to Microsoft we can have as many modules as we want.

If a module is getting to many subs in it start another module or break out your subs to individual modules or have a module hold similar subs.

For example I have one program that has over 20 modules each conataining several subs. One module holds only the information to sort and filter the workbook, another holds only the information for SQL and creating records sets connections, another holds different fill colors for cells, another one deals only with formating issues and another handles only Access.

Its much easier to deal with the subs when the module is named according to what the subs do.

One other thing I do is limit my subs at 30 to an absolute max of 40 lines of code and limit each module to about 4 subs or around an absolute max of 200 lines of code per module.

I find this to be very helpful when looking for information and with trying to stay organized.

 

[MajP]

You can also gain efficiency especially if you have some groups of procedures that may not get used and some that always get used together.

Organize and structure you modules to take advantage of Visual Basic's demand loading architecture. When a procedure is loaded from a module, the entire module is loaded into memory. By placing related procedures in the same module, you can reduce the number of loads the Visual Basic has to make.

 

[Jtorres13]

I ended up copying and pasting the code to Excel. Luckily, the code was indented and the sub names stood out from the rest of the code. I manipulated a bit, pasted into Word as a table and sorted.

I was able to compare the three projects and eliminate duplicate, obsolete subs, etc. MErged all three into one project. now every one will be using the same thing, easier to update.

Thanks for all your suggestions. I did not get to use mztools for this issue.

Thanks Majp and unsolvedCoding for the sugegstion of utilizing modules better. I will limit the number of subs per module and keep related subs in the same module.