# Off Topic > Tips and Tutorials >  >  VBA educational material

## vba_php

I am currently teaching 2 beginners the VBA language. One in Asia and one in Europe. So I thought I would post these 31 lessons I have written for them. If you are a beginner, you might be able to make good use of this material. You can thank me later. I wish you happy learning.  :Smilie: 

*On google's cloud drive:*
Lessons 1-8 => https://drive.google.com/file/d/1d7b...ew?usp=sharing
Lessons 9-16 => https://drive.google.com/file/d/1RcG...ew?usp=sharing
Lessons 17-23 => https://drive.google.com/file/d/1xsi...ew?usp=sharing
Lessons 24-31 => https://drive.google.com/file/d/1qGK...ew?usp=sharing
ALL lessons => https://drive.google.com/file/d/1BiH...ew?usp=sharing

----------


## 6StringJazzer

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.

----------


## vba_php

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.

----------


## 6StringJazzer

> [...]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).

----------


## vba_php

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:




> #1: x is declared but not used



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




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




> #4: i is declared but not used



same as #1.




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




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




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




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




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




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




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




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

----------


## vba_php

Jazzer,

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

----------


## 6StringJazzer

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:





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

----------


## rorya

More important point about #5 I'd have said is that Yes/No is not a VBA value at all. But I agree with 6SJ: the fact that True/False equate to -1 and 0 when _coerced_ to a number does not really mean that -1/0 are _representations_ of True/False.

----------


## vba_php

> 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.   :Wink:   that is an arguing point among seriously technical people only.




> The code for #15 is:
> 
> 
> 
> ```
> 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.

----------


## vba_php

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.

----------


## rorya

But if you're going to teach people, you should teach them the facts.  :Wink:

----------


## omahaNative_1023

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

----------


## 6StringJazzer

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



The Google drive files aren't there but they are all attached directly to the post. Just click on a Zip file to download.

----------


## omahaNative_1023

oh tank you.  i did not see that.

----------


## chenxin

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

----------


## 6StringJazzer

> ```
> Please Login or Register  to view this content.
> ```
> 
> 
> is not [a good practice] either.



It is an excellent practice. You are setting a boolean variable to the value of a boolean expression. What could be simpler?

BTW please note you are responding to a post that is over 10 2 years old.

----------


## povictory

Thank you!

----------


## fatkinglet

link is dead, thx

----------


## 6StringJazzer

Because this thread is two years old and is a discussion about specific training materials provided by the OP rather than topics of general interest, and also because the OP has been banned so cannot further participate in the discussion, I am closing the thread.

The files attached in post #1 will continue to be available for download.

*fatkinglet*, read post #13.

----------

