Everyone has their unique ways of coding in VBA. I know I do and someday I’m going to write them all down. I’m changing my method for a couple of things. First, I’m going to follow Bob’s advice and put all my error-on-purpose code in a separate procedure. So instead of having this in the middle of a procedure
mcolMyCollection.Add oMyObject, CStr(oMyObject.ID)
On Error GoTo ErrorHandler
I’ll call it like
Public Sub AddToColl(col As Collection, vToAdd As Variant, sKey As String)
On Error Resume Next
col.Add vToAdd, sKey
On Error GoTo 0
I think that little extra work is worth it so that I never put the wrong On Error statement in my code.
My next change is not so cut and dried. When I create a class module, I generally end up with a lot of Property statements that do nothing but read and write to a module-level variable. One might look like this:
Path = msPath
Public Property Let Path(ByVal sPath As String)
msPath = sPath
It’s an important piece of code, to be sure. But I never use it. I’m not looking at it, changing it, or generally doing anything with it. Ever. So now I want to write it like this:
Public Property Let Path(ByVal sPath As String): msPath = sPath: End Property
Same code, just out of the way. Here are my problems with this change: I like well formatted, easily read code. I would rather have something that’s easy to read than something that doesn’t take up room (but only if I intend to read it). My other problem is that I don’t actually write property statements like this. For property statements that only read and write to a module-level variable, I create a Public variable and use MZ-Tools to create the property statement. Honestly, I probably couldn’t write a property get/let combo from scratch correctly the first time. I let MZ create all the gets and lets and I create the read-only properties as the code dictates that I need them. So I’d be adding another step in setting up the class, just to make it slightly more readable.
I suppose I could just write some code that does it for me. Anyway, I’m going to try it, but I don’t know how long the second one will last if I don’t have an automated procedure.
As long as we’re discussing coding techniques, Rob commented:
the UI wouldn’t be so gigantic if you didn’t habitually leave such huge spaces both between your controls and at the edges of your dialog
Quite right. Design has never been my strong suit, and that particular problem has always been a problem of mine. I’m going to re-layout that userform and record some standards that I can use in the future. Standards like: How far controls should be from the side/top of the form. How far controls should be from each other. How many lines to show in a listbox. They won’t be immutable rules that will make every userform look great. But they will give me a good place to start that will combat my tendency to spread everything out. I think I’ll end up with more compact, better looking forms. Or they’ll still be crappy looking forms, there will just be less of them to look at.