I reviewed all your lessons. The material is good as far as it goes but I am surprised that there is no mention of the Excel object model, and no examples specific to Excel (or any other Office app). The lessons are somewhat microscopic, in that they demonstrate the behavior of a feature but not how someone would actually use in a real situation. It even includes a lesson on DAO, which is a rather advanced topic that most users will never run into. The most powerful thing about VBA is the ability to integrate with Office apps.
I taught VBA for the Mitre Corporation in 2005-2007. My approach (and I'm not saying you have to take my approach) was to introduce it by showing simple examples of code generated by the macro recorder. My philosophy was to introduce the language by showing examples that they could apply immediately, rather than starting with syntax and control structures. Then I would show how to clean up the recorder code. Then I would move into other language constructs, like If/Else and Loops.
Other things I noted:
The capitalization is not the same as what is done automatically by the VBA editor. This could be confusing to a beginner when they type this code in.
#1: x is declared but not used
#3: I cannot think of a situation where you would convert an Integer to a Long. If you use an Integer where a Long is expected, VBA will convert it automatically. If there is such a situation, then it would be useful to include it in the example.
#4: i is declared but not used
#5: Although assigning numeric values to Boolean variables works, I would discourage this in VBA. It is more C thinking to do that sort of thing.
#6: x is declared but not used. Also, this is the first place where errors are discussed. I would go into that before asking the question. Also in C++ and Java, errors are thrown; in Ada and VBA they are raised.
#10: Fine as far as it goes but it would help to explain the difference between a const and a variable.
#12: This violates structured coding practice, and burying it in a cascading If statement can introduce bugs and complicate troubleshooting. In this case an "Exit Sub" does not enhance the logic. It can be removed, replacing the End If with and Else and adding End If after the final assignment of return. If the point is to illustrate Exit Sub then I would use a simpler example, like
#15: This is not a problem, but the whole If statement could be replaced with
#17: This code will raise a type mismatch error. Format returns a text string, and the result cannot be assigned to a Date variable.
#18: Will work but I discourage the use of Variant when you know at compile time what type of data will be assigned to it. Strong typing helps detects bugs.
#26: If Option Explicit is used, the first example will not compile. If Option Explicit is not used, hopefully the answer you are looking for to the question below is, "A Sub does not return a value."
I hope this is helpful.
Bookmarks