Best coding practices in VBA (specifically, code structure, readability, compactness, and the TEBV & TBTO rules)

[cmowla]

Yesterday I became aware of the Hall of Fame Winners subforum (I am new here), so I guess we can find good practice examples in there, but I started this thread because me and @Marc L seemed to start a very good conversation worth seeing by everyone (and something that was becoming off-topic for the thread at hand), so Marc, I will continue the conversation here.

I know "good coding practice" is a broad topic, even within the confines of VBA in Microsoft Excel. The focus of this is regarding what this thread title reads. EBV and TBTO rules are acronyms for the following.

TEBV & TBTO VBA main rules aka Think Excel Before VBA and Think, But Think Object !​

The remainder of this post is my response to the linked to post. And I guess we can see where things go from there.

What is the best code : yours, mine ? In fact after years of practice, with complicated codes using arrays, loops, external ressources like Dictionary, etc …​

more on BASIC side than on Excel side, after the day a former colleague of a company I had left said they could not maintain my former codes​

so for any change they had to rewrite all the code as a more beginner way, maybe less efficient but easier to maintain,​

so the answer to this question is : the code the OP is able to understand but far above all the one he is able to maintain …​

​

Then after years I was able to well understand at least what a famous MVP had advised me​

since like a padawan I try to follow the TEBV & TBTO VBA main rules aka Think Excel Before VBA and Think, But Think Object !​

(Maybe more like a Mandalorian and his creed !)​

I think you are assuming that Excel users know how Excel formulas work (which it appears that the majority who ask questions on here neither know that or VBA).

With functions such as CONCAT now available post Office 2016, that kind of makes that less probable, because now we have arrays mixed in with single value manipulation in an Excel formula. I see that you are using Office 2010, but the point is Microsoft keeps changing (adding) the number of available Excel functions. This practice will make it very easy for code to work only in specific versions of Excel. As far as I know, VBA (with late binding to objects) allows for code to work on most (if not all) versions of Excel. Certainly using the basic keywords of VBA and universally used imported objects will guarantee compatibility much more than the former. (Not to mention that there are settings which affect Excel formula calculation, which complicates things more.)

In my experience, the easiest code for the user to maintain (which I define as never needing you (the coder) again) is one in which you don't nest all of the sub tasks that need to get done together (even if that makes the code way shorter). If a software request implies to us as the developers that A - F needs to get done, we don't just write one function/sub that does A - F in an elegant/compact way. We should write a function that each does one of those 6 tasks (where they each do just ONE very basic task). Within each of those 6 subs/functions, we provide comments for the condition of use (the assumptions of input and how to receive output). That way the client's future developers can easily modify the main sub to do maybe just 5 of those tasks in a different order with ease, if need be. (Take out what's not needed, reuse what is needed. Also, this is great practice for debugging.)

In my VBA collection, I have over 700 mini subs/functions, each with comments explaining any subtle (unobvious) conditions of use (when it's needed), and each which have a test sub. (I have been coding in VBA for a solid 2 years.) And I always design them such that the function/sub call gives a complete list of parameters. I never assume that a sub is acting on the active sheet unless that sub is strictly going to be called from Worksheet Events, for example. And if the user mentions a specific sheet, I still (aim to) provide Worksheet name as a parameter and simply pass the specific name of their sheet in the test sub, rather than hardcoding that specific name into the code body itself.

Also, obviously (well to me), if you are going to name variables, they need to be bluntly obvious. (Using single letters for names when you have the ability to make a variable name an entire sentence if need be, is bad practice IMO.)

According to post #9 or after running it take a glance to the formula in cell C7 for example so you should see here​

I started to ask myself « What Excel offers first to help to solve this need ? » (TEBV rule) before to write any codeline on VBA side …​

Yes, that makes sense because Excel formulas are far more efficient when filling in sheets with large amounts of data which follow a pattern than with conventional loops as you too mention here:

The classic example is some big data must be filtered then copied to another worksheet : often I saw answers with so many codelines​

- could be fast & efficient - but too complicated for a beginner to maintain the code​

instead of just using some quick enough Excel basics like an advanced filter requiring less than five codelines !​

But, after coding for this project, I can conclude that Excel formulas are not really needed at all because clearly the user wants to print out the data. How many pages does someone want to print out that the difference in time for it to make the generation of this 1/A column would matter? Especially since one of the variables to consider is the location of page breaks (which Excel formulas cannot detect? Assuming that there are 48 printable lines per page, then there would be a maximum of 1,048,576 /4821845 = 21845.333 pages that would need to be printed out . . . I don't think someone is going to print 20,000 pages!

So we could have just wrote a solid VBA function with loops instead, and it wouldn't have made that much difference in execution time based on the scope of use.

For the second main rule, I prefer to work directly with an appropriate object (like the With statement) rather than using useless variables

Variables are not useless if they are named properly to tell a story of what the code is doing. (And the name of functions, for that matter.) As someone who started coding with TI-BASIC back in 2006, I also have the talent to use minimum variables and even reuse the same variable for different data types (as I saw you do with the variable V here . . . I don't think type variant was meant to be used in this way. I think it was meant to allow functions to not break if someone passes a long instead of an integer, for example). But even if I know that a variable is not really needed for the code to run, I still may add it in for a transition step to make sense. (Especially if a function is sophisticated.)

To me, code is only well-written when it "tells a story" just by reading it as if it was any other technical writing. (Having a keen ability to trace the steps of what (specialized) code will do should not be a factor to consider.) Avoiding extra assignments is always ideal, but if they are avoided (but it would be better to have them for understandability), at least writing good comments can make up for not having the extra/unneeded transition variables.

But the point is, it's obvious that you code efficiently. What concerns me the most is that your (and it's not just you, I have seen others write similar type of code) requires "excessive deciphering" to be done by anyone who wants to learn from your work and adapt it to other projects. (As all too often, those who code in this way do not provide needed comments either.) And with all due apologies, I realize that we are coding here for free and should be allowed to code however we want (especially since the majority of those asking for help on here don't care about learning to code, we are posting code in a hurry, etc.). But this is just for the sake of this argument of what actual "good programming practice" should be in VBA and if we have the time and desire to revamp our code.

I try to make my code to be able to be read as an actual sentence in English (with a combination of comments but also the code itself). For example, if a line of code is to move data from a column A in sheet 1 to column B in sheet 2 on the condition that it's Wednesday, then I would prefer to write the code this way if possible: (@Marc L Would you define this as cumbersome?)

Sub Task_01()

If It_Is_Wednesday Then
    Move_Data_From_This_Column_In_This_Sheet_To_This_Column_In_This_Sheet "A", "Sheet1", "B", "Sheet2"
    DisplayMessage "The data has been moved" & HardReturn & "(It is Wednesday.)"
End If

End Sub

Sub Move_Data_From_This_Column_In_This_Sheet_To_This_Column_In_This_Sheet( _
targetColumnLetter As String, _
nameOfSheetToMoveFrom As String, _
destinationColumnLetter As String, _
nameOfSheetToMoveTo As String _
)
DisplayMessage "Done"
End Sub

Function It_Is_Wednesday()
It_Is_Wednesday = False
'If code for calendar days indicates it is Wednesday, then
'It_Is_Wednesday = True
End Function

Function HardReturn()
HardReturn = vbCrLf
End Function

Sub DisplayMessage(messag As String)
MsgBox messag
End Sub

And a very practical thing that I typically do (not as extreme as the above) is to create subs and name then after the common convention in other languages. For example, instead of writing ReDim Preserve in a main sub (appending to an array), I have written the following function so that I can literally "forget" about the default VBA code to do this and have it read much better in my main subs.

Function Append(arr As Variant, arg As Variant)
'Two possible errors from client subs:
'(1) arr is not of type variant.
'(2) arr is defined as Dim array() as Variant instead of ReDim array(1 to x) as variant.

    Dim lowerBOundOfInputArray As Integer
    lowerBOundOfInputArray = LBound(arr)

    Dim upperBoundOfInputArray As Integer
    upperBoundOfInputArray = UBound(arr)

    ReDim newArray(lowerBOundOfInputArray To upperBoundOfInputArray) As Variant
    newArray = arr
 
    ReDim Preserve newArray(lowerBOundOfInputArray To upperBoundOfInputArray + 1)
    newArray(upperBoundOfInputArray + 1) = arg
 
    Append = newArray

End Function

​

Maybe I'm pretty good but many are better than I am at this game …​

This is not a game that I want to play unless large amounts of data needs to be manipulated. At least VBA is translatable to other languages, but the exploitation of Excel formulas is not transferable. I have to have a very good reason for using the former. If code having 5 fewer lines means I have to spend 5 extra minutes in the future to remember what it does (or if it happens that I wrote a nice function in VBA that I want to translate to another language), it's not worth it. Maybe if you're interested in something like competing on codegolf to have the fewest number of characters to do a task (I think we should have something like that for VBA!), but not for understandably for a larger general audience who are not as specialized in Excel as we are.

 

[Marc L]

I agree with some parts but I disagree with others, maybe 'cause I used to write VBA codes the same way as yours …​

​

I think you are assuming that Excel users know how Excel formulas work

In fact not only formulas but the Excel basics like filters, advanced filters, sorts, etc … Even if they don't know but not difficult to learn.​

​

We should write a function that each does one of those 6 tasks

As it depends on the context … I met the case in some companies but that did not avoid the revert they can't maintain my codes​

'cause it is not the unique task by sub procedure which makes a code easier but also how the task is coded, even with obvious variables named properly …​

​

When I use a single letter name in forums often this letter means something if only the original poster try to understand the code​

like C for column, R for row, P for previous, V for Values as Variant, …​

​

So it seems we were not talking about the same subject as you are stating about some encoding normalization and on my side​

about using some direct Excel ways rather than some 'school coding', avoiding useless variables and working directly with Excel objects model …​

​

But, after coding for this project, I can conclude that Excel formulas are not really needed at all because clearly the user wants to print out the data.

Print out the data ? Maybe but maybe not as it was not the thread subject !​

The formula way simplifies the code and may help the original poster in the future to solve another need with a beginner level formula​

rather than going on a VBA path 'cause in the real life I won't waste any time to write a procedure for such basic need …​

​

So we could have just wrote a solid VBA function with loops instead

The way I wrote VBA codes in the old times but not always the best way …​

​

Variables are not useless if they are named properly to tell a story of what the code is doing.

I often saw such variables as useless, in particular in Excel forums, in particular for variables objects …​

'Cause in an old time when computers were less powerfull and as Excel is the worst application I know according to memory management​

I met Excel crashing at least once a day on each computer of the office.​

What is the benefit to create a worksheet variable to use it only once within the code without releasing it at the end of the procedure ?!​

Just a waste of ressources and time, in particular when inside a loop …​

​

I don't think type variant was meant to be used in this way.

As the variable must be Variant in its second allocation or the procedure crashes !​

​

But the point is, it's obvious that you code efficiently.

I merely try …​

​

but the exploitation of Excel formulas is not transferable

As it depends if the other language can access the Excel object model …​

​