VBA Coding - Best practices - Pt#1

It's been a while since I started to develop my own Office add-ins. Initially for MS Excel, then in some particular cases, for MS Project and MS Powerpoint as well.

In the beginning I used to spend too much time getting data from different sources, creating some 5th dimension formulas, tailoring my reports by adjusting spaces, date and time, etc. Such valuable time to spend in reports was a headache for me because the data usually came from important projects that I needed to be focused on.

The solution: I started to develop my own tools by programming the recurring tasks I had to perform in my reports.

Without a proper documentation about naming Conventions, I learnt by my own mistakes naming my variables in Hungarian Notation.

At that time I was more interested in speed-up my recurring tasks than proper develop a reliable and maintainable solution. Not that the Hungarian Notation is evil for coding but as more as we can avoid it, the better.

The first good practice is to declare all the variables in your code.

To ensure that you will not forget to declare them, you can set up the VBE (Visual Basic Editor) on Tools > Options > (Require Variable Declaration).

Once your variables are declared in your code, you can question yourself why do you need to prefix all the variable types anyway?

Your code shows the different variable types, you don't need to jeopardise the readability of your code by adding more text to it.

See for the BMI function the variable naming convention adopted below. The second example is straightforward even for a simple solution like that.

Ex#1: Function BMI(dblWeight As Double, dblHeight As Double) As Double

Ex#2: Function BMI(Weight As Double, Height As Double) As Double

Sarcastically paraphrasing Joel Spolsky "(...) This is the real art: making robust code by literally inventing conventions that make errors stand out on the screen."

Remember that dblUsing boolHungarian intNotation vrtMakes strReading vrlCode dblDifficult.

Juliano Lima