Incrementing Module Names

Ross of Methods In Excel fame points out some interesting programming choices by the softies at Microsoft. Start with two VBA Projects each containing Module1. Copy one of the modules to the other project and Excel appends a 1 to the end of the name to ensure it’s unique.

VBA Projects with copied modules

Now change the first Module1 to Module20. Copy that module to other project and then copy it again. The second copy will produce Module201, not Module21 as you might expect.

Copying Module20 produces Module201

While this certainly isn’t earth shattering, I wonder why they made the choices they did. They have to account for existing sheet names and appending a 1 is certainly one way to do it. But it seems to unnecessarily increase the name of the module which surely will cause problems at some point. It’s not like they’re guaranteed to have a unique name just by appending a 1. They still have to put it in a loop, ala

     TempName = ModName & “i”
     If ModExists(TempName) Then
          ModName = TempName
     End If
Loop Until ModName = TempName

This loop would be no more complicated by incrementing the existing numerical suffix and it would avoid any module name length issues. Well, it would a little more complicated but not by much. They’re just using the same algorithm here as they do to keep worksheet names unique, and you may be aware that that didn’t work out too well.

They could say, in their defense, that I can do this without a crash

Module with about 16 numbers after it

To which I would promptly blame Ross for bringing it up.

Posted in Uncategorized

One thought on “Incrementing Module Names

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.