Don't Write Clever Code
There are two problems with clever code. 1) The next person might not know what you were doing. 2) They might not know if *you* knew what you were doing.
Someone recently posted the following on LinkedIn:
The poster is both technically right and practically wrong.
He's technically right
I want to give the poster the benefit of the doubt here and assume that they would never actually declare variables this way in their own code. And I completely understand where they are coming from. If someone is throwing gotcha questions at you during a technical interview or test, it's awfully tempting to throw that back around in their face with a gotcha of your own. Hey, I'm more of a smart-ass than most, so I really do feel where they're coming from.
He's practically wrong
With all of that throat-clearing out of the way, I have to vehemently disagree with the following line (emphasis added):
If one were asked to define three variables, two of which are Variants and one is an Integer, then there's absolutely nothing wrong as shown.
Let me adjust the quote to something I can agree with:
If one were asked to define three variables, two of which are Variants and one is an Integer, then there's absolutely nothing wrong as shown if the compiler is the only one who will ever have to read the code.
Write your code for people, not computers
When you write code, you should write it so that people can read it.
This is much clearer than the sample code above:
Dim i As Variant, j As Variant, k As Integer
The same code could also be split so that each variable is declared on its own line:
Dim i As Variant Dim j As Variant Dim k As Integer
The problem in the sample code is that
j are implicitly declared as Variants. Implicit declarations are bad practice generally, but they are especially egregious in this case.
Other languages, like C++, allow you to declare multiple variables of the same type on one line with only a single type declaration.
When you write clever code that relies on intimate knowledge of the programming language (VBA in this case), you bake two dangerous assumptions into that code forevermore:
Assumption 1: Every person who reads your code knows what you are doing
You assume that the next person to come along that sees the line...
Dim i, j, k As Integer
...will understand that
j are Variants. This is a dangerous assumption. A reasonable programmer new to VBA would likely expect that all three variables have been declared as Integers.
Assumption 2: Every person who reads your code will know that you knew what you were doing when you wrote it
If I come along and see code like
Dim i, j, k As Integer, my first thought would be that an inexperienced programmer wrote this and probably didn't understand that
j had been implicitly declared as Variants.
I would then spend some amount of time (any amount is too much) combing through every other reference to
j in your code to see if it's being treated as a Variant or an Integer. This is a waste of my time, and I might never know for sure what you really intended.
Both of these assumptions are completely outside of your control. So I implore you, don't write clever code.
Image by Steve Buissinne from Pixabay
UPDATE [2021-07-15]: Added sample code to show what the "not clever" version of the code would look like. H/t @rafadomene.