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.
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.
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
Do
TempName = ModName & “i”
If ModExists(TempName) Then
i=i+1
Else
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
To which I would promptly blame Ross for bringing it up.
I’ve heard that .net solves this