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
End Sub
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
End Property
Public Property Let Path(ByVal sPath As String)
msPath = sPath
End Property
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.
To make your code compact but readable you can write it like this (add spaces):
The same works with Dim and Const statements
Petr
So, as you start to change your coding techniques, are you going to change your VBA Framework application to match…?
Change
into a function so you can return a boolean to indicate success or a long (i.e., a user defined type) to indicate something useful about what happened within the procedure (the type of error, the type of data, or whatever).
Per Jon’s comment, I’ve started using Functions with sting returns. The strings are public variables named like xErrOK (=”OK”), xErr1 = “Error Type 1?, etc. If I hit an error in the function, I return that value to the caller which invokes an error process with messages, etc. (I like to use If Function1 = xErr1 then Err.Raise(1001) – or something like that.)
Not in the same class as the approach used in “Professional Excel Development”, but works for me. I can provide clearer explanation if anyone wants.
Scott: Good question. I suppose I should.
Jon: I use PED error handling. But for a procedure surrounded by an On Error Resume Next, it didn’t seem necessary to return anything. There are some situations where I use On Error Resume Next, that it would make sense to return something, like
Set wb = GetWorkbook(sWorkbookName)
If Not wb Is Nothing Then
rather than
On Error Resume Next
Set wb = Workbooks(sWorkbookName)
On Error Goto 0
If Not wb Is Nothing
But for the collection example, I don’t care what happened.
Dick,
Seeing as you are listening to me at last, I only use functions, never subs. I was persuaded that every procedure should return a result, even if it is only to signify success or failure. I took the principle on board, but I admit that I prefer my return indicator to a Byref (and last) argument, probably a throwback to use of API calls.
On your property code thing, I don’t agree with that, but your style is your style. I am a real pedant, I type my properties from scratch every time. The one that always gets me is a Property Let that takes a parameter, catches me every time.
Anyway, back to my point. If MZ-Tools won’t format as you want, write a simple VBE procedure yourself and assign it to a toolbar button, DDoE-Tools!
Dick –
Like Bob, I tend to make my procedures into functions. If I don’t need the result, I can still call it as a sub and ignore the return value. But somewhere else in the project I may decide I want that return.
Like you said, it depends.
@ Jon and Bob,
Sounds like you guys would get on well with F#? It did strike me when I was reading about F#, that well, you could just write all you code as functions in any(most) languages, so why have one that only (not only anyway!) uses functions.
Now I’m 100% sure that I have totally missed the point, but as I have always said ignorance is no reason for not forming a opinion!
As always interesting reading, good work Dick! Nice ideas Jon Bob and Alex.
Good point Jon. I lose nothing by writing it as a function and just calling it as a sub.
Bob: Yes, DDoE Tools. I’ve often thought of that. I use about five things from MZ-Tools and a few of them to almost what I want. Those five things are important, mind you, but there’s a lot in MZ that I simply don’t use. So I wouldn’t have to replicate everything in MZ for my purposes. I really hate programming in the VBE though – too much text manipulation.