VBA educational material

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

Please Login or Register  to view this content.

#15: This is not a problem, but the whole If statement could be replaced with

Please Login or Register  to view this content.

#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.

I will review the work when I have time Jazzer. I haven't even read your post yet. But some other person told me not to use the var called ""return"" because that is reserved words in VBA. but that wasn't the point I was trying to get across.

Originally Posted by vba_php

[...]some other person told me not to use the var called ""return"" because that is reserved words in VBA.

Generally good advice, because it can be confusing when maintaining the code. It is also a good practice to avoid names that are also used as methods in the application object model (just mentioning it; you don't do that in any of your examples).

It is not technically a reserved word because the language allows you to use it as a variable. The definition of "reserved word" is that the language reserves it for its exclusive use and the programmer may not use it for a name. "Long" is a reserved word. You won't be able to declare anything called Long.

Just as an illustration, and again, your examples don't do this but as long as I'm on the topic, suppose this code appears in a sub in a worksheet module:

Please Login or Register  to view this content.

In this code, the array Cells will hide the property Cells of the worksheet. That can be a mess if Cells is used later to refer to the worksheet as usual; it will still refer to the array, and a maintainer may not be able to figure out the original intent of the programmer (or the programmer may not be able to figure it out themself 6 months after they write it).

Jazzer,

here's my thoughts on your thoughts. And keep in mind, these lessons are intended for people who are total beginners, so some of your comments I'm going to discard as ""too advanced"" for them to consider:

Originally Posted by 6StringJazzer

#1: x is declared but not used

will change it if I have time. yes it's irrelevant. whopsie!

Originally Posted by 6StringJazzer

#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.

that is irrelevant. I picked the 2 data types out of thin air as an example. whether the scenarios is ever encountered does not matter. the purpose is to show the leaner the conversion function and its purpose.

Originally Posted by 6StringJazzer

#4: i is declared but not used

same as #1.

Originally Posted by 6StringJazzer

#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.

does that really matter? what are you suggesting I show them? true/false instead of -1/0 or 1/0? Essentially what I was trying to get at here is that machines think in 1's and 0's. true/false is really the same with regard to boolean types, but that does not get the point across. Really there is no difference.

Originally Posted by 6StringJazzer

#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.

your points regarding other languages are irrelevant. these lessons are about VBA. Mentioning other languages is not the point.

Originally Posted by 6StringJazzer

#10: Fine as far as it goes but it would help to explain the difference between a const and a variable.

That can be looked up on the internet. These lessons are examples only. The people that requested these lessons were told by me to look up the definitions of what they see in the examples on the internet if they want to know what the symbolisms and words are about.

Originally Posted by 6StringJazzer

#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

Please Login or Register  to view this content.

yes, the purpose is to understand the exit statement. the complex ''IF'' block might be a little too much. I suppose I agree.

Originally Posted by 6StringJazzer

#15: This is not a problem, but the whole If statement could be replaced with

Please Login or Register  to view this content.

no it can't. the purpose is to understand the rnd() function. If the learner thinks that it sometimes returns a value greater than 1, then obviously the answer will be incorrect. that is why there is a boolean check in it.

Originally Posted by 6StringJazzer

#17: This code will raise a type mismatch error. Format returns a text string, and the result cannot be assigned to a Date variable.

you are absolutely right. it should be changed. well done. thanks.

Originally Posted by 6StringJazzer

#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.

not sure your advanced advice here is even relevant. see my point at the beginning of this post.

Originally Posted by 6StringJazzer

#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."

yes that is true. not sure how I could've messed that one up! Hmmmm....I will change it. The purpose was to check to see if they know that 2 string variables concatenated together would throw an error. It has nothing to do with sub vs. function with regard to which one can give returns and which one cannot.

Jazzer,

Everything has been corrected that was technically an error. I did not change everything though. thanks for the input.

Thanks for taking the time to consider my comments. Although I still disagree with some aspects of this approach (aside from teaching VBA I also managed a training program and designed and taught object-oriented design and programming courses at a major federal contractor for two years internally and at NASA), it's your baby and I'm not going to bother you by sparring on it. I do want to address two points, however:

Originally Posted by vba_php

your points regarding other languages are irrelevant. these lessons are about VBA. Mentioning other languages is not the point.

OK, let's forget about other languages. The lesson says an error is "thrown" but in VBA the term is "raised."

no it can't. the purpose is to understand the rnd() function. If the learner thinks that it sometimes returns a value greater than 1, then obviously the answer will be incorrect. that is why there is a boolean check in it.

The code for #15 is:

Please Login or Register  to view this content.

My point about the If statement logic is not related to the use of rnd. The if statement logic is logically equivalent to

Please Login or Register  to view this content.

Originally Posted by 6StringJazzer

OK, let's forget about other languages. The lesson says an error is "thrown" but in VBA the term is "raised."

understood, but then again, you are delving into political and technical ""correctness"". and business people don't give 2 hoots about that. that doesn't make money. that is an arguing point among seriously technical people only.

Originally Posted by 6StringJazzer

The code for #15 is:

Please Login or Register  to view this content.

My point about the If statement logic is not related to the use of rnd. The if statement logic is logically equivalent to

Please Login or Register  to view this content.

I think I follow that. personally I think either way is just fine. but you have to remember, young people would not understand consolidation like this, more than likely. especially people outside of the USA who live in countries technically classified as 3rd world.

the other thing you guys might not realize about me, as in the difference when compared to you folks, is that, I am more of a product marketer, where you guys are more of product creators regarding the low level technical aspects. thus, what you care about and what I care about are 2 different things. but there is a place for both.

I just tried to download these files and they not there. I was interested in see what was there. Does anyone know what happened?

oh tank you. i did not see that.

Please Login or Register  to view this content.

i really don't think this is a good practice.

Please Login or Register  to view this content.

is not either. and input was a keyword in traditional BASIC. better not to use it as a token.
why not do like this?

Please Login or Register  to view this content.

The most important thing is to make the code logic clear. Then consider optimizing it. The mixing of code logic and VBA's proprietary features (variables can accept both integer and logical constants) makes things complicated rather than simple.

Thank you!

link is dead, thx