Intensive basic VBA course - which variables to teach?

Question

Which variable types are the bare minimum to teach non-programmers who want to be able to create and modify simple VBA (MS Office) macros?

I start by showing them the concept of variables through using an Excel cell as a temporary storage, then using undefined variables, then show them a simple Dim SummedValue with no type declaration. Then comes the types, and I always struggle.

String, Integer, Boolean, Double seem the most important, but maybe Long(integer) is better, or just Double is enough for all the numbers? Also, Object variables are important too with the Set statement. I don't bother showing them arrays variables. Or am I better of telling them to use Variant for everything?

They will be making simple macros for themselves, maybe a few colleagues. Usually start off from a recorded code. They should be able to read and comprehend other's macros too.

Context:

I regularly hold intensive company VBA trainings. These are typically 2 day long trainings (yes, I know, totally not optimal, but I can rarely make the organizing HR understand this). So, 2 days Beginner Training, where I teach them the basics from macro recording through modifying the existing code to the IF statement. Then in a few weeks it is followed by a 2 days long Intermediate Training. This is where I teach them the variables and loops - and make them practice a lot.

Answer 1

As you are teaching business people, not computer scientists (though what I say may also be true for teaching them). I would start on the more practical.

Teach them stuff that is useful. Date, Currency (Don't use float types for currency, it will cost you your job, and someone a lot of money), Decimal (A float type with base 10 radix: it has less unexpected behaviour), and string.

Integer has some value, but manly for performance. If when doing loops you use foreach, then integers are not needed. Also looping is simpler and safer.

Booleans are only needed when doing conditionals, and conditionals are over used.

---

In addition:

Static or dynamic

If you teach not to declare types first, then that is what you will get.

Assignments

We often teach assignment early, this is crazy. In structure and interpretation of computer programming, they covered almost everything I know, and then started to apologise, because they were about to introduce assignment (mutation). As soon as you introduce assignment, you make is very hard to reason about how a program works.

I have taught types when teaching functions. Functions take values (of a type), and return values (of a type). This in some ways looks like assignment, but is not. It is safer, and makes code easier to understand.

Answer 2

With only two days, stick to variants. VBScript works fine even though all it has is variants. Python is in effect much the same (dynamic typing where type is an attribute of the thing named rather than of the name itself) which is one of the reasons why Python has become more popular for teaching the basics of programming. By design, VBScript is a simplified version of VBA designed for light-weight programming. It is telling that when Microsoft developers were deciding how to create a streamline version of Visual Basic, they decided to just stick with variants. In 2 days you won't have enough time to delve into the semantic issues which type declarations would raise.

If you go this route, I would recommend still emphasizing the importance of using Option Explicit and explicit Dim statements, even though they would be in some sense superfluous. You could at least mention other types, and tell the students that if they want to master VBA programming they will need to learn about such things.

A compromise possibility would be to use Variant for non-object types but introduce Range and Worksheet variables for objects. The explicit declaration of these types might remind students that they need to Set the values of those variables rather than simply assign to them, and would also introduce them to IntelliSence.