+ Reply to Thread
Results 1 to 14 of 14

VBA educational material

  1. #1
    Banned User!
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    2,014

    VBA educational material

    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.

    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
    Attached Files Attached Files
    Last edited by vba_php; 11-20-2020 at 07:59 AM.

  2. #2
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    18,627

    Re: 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.

  3. #3
    Banned User!
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    2,014

    Re: VBA educational material

    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.

  4. #4
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    18,627

    Re: VBA educational material

    Quote Originally Posted by vba_php View Post
    [...]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).

  5. #5
    Banned User!
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    2,014

    Re: VBA educational material

    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:
    Quote Originally Posted by 6StringJazzer View Post
    #1: x is declared but not used
    will change it if I have time. yes it's irrelevant. whopsie!
    Quote Originally Posted by 6StringJazzer View Post
    #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.
    Quote Originally Posted by 6StringJazzer View Post
    #4: i is declared but not used
    same as #1.
    Quote Originally Posted by 6StringJazzer View Post
    #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.
    Quote Originally Posted by 6StringJazzer View Post
    #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.
    Quote Originally Posted by 6StringJazzer View Post
    #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.
    Quote Originally Posted by 6StringJazzer View Post
    #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.
    Quote Originally Posted by 6StringJazzer View Post
    #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.
    Quote Originally Posted by 6StringJazzer View Post
    #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.
    Quote Originally Posted by 6StringJazzer View Post
    #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.
    Quote Originally Posted by 6StringJazzer View 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.

  6. #6
    Banned User!
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    2,014

    Re: VBA educational material

    Jazzer,

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

  7. #7
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    18,627

    Re: VBA educational material

    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:

    Quote Originally Posted by vba_php View Post
    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.

  8. #8
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    2016 primarily
    Posts
    6,312

    Re: VBA educational material

    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.
    Rory
    I drink, and I know things

  9. #9
    Banned User!
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    2,014

    Re: VBA educational material

    Quote Originally Posted by 6StringJazzer View Post
    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.
    Quote Originally Posted by 6StringJazzer View Post
    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.

  10. #10
    Banned User!
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    2,014

    Re: VBA educational material

    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.

  11. #11
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    2016 primarily
    Posts
    6,312

    Re: VBA educational material

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

  12. #12
    Banned User!
    Join Date
    01-17-2021
    Location
    Omaha, NE
    MS-Off Ver
    office 2016
    Posts
    211

    Re: VBA educational material

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

  13. #13
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    18,627

    Re: VBA educational material

    Quote Originally Posted by omahaNative_1023 View Post
    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.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  14. #14
    Banned User!
    Join Date
    01-17-2021
    Location
    Omaha, NE
    MS-Off Ver
    office 2016
    Posts
    211

    Re: VBA educational material

    oh tank you. i did not see that.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Analysis in Pivot and Charts for educational project
    By sabha in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 10-14-2015, 06:50 AM
  2. Replies: 4
    Last Post: 01-23-2014, 02:05 PM
  3. [SOLVED] Separate 'Material' equal 'Material' different
    By marreco in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-22-2012, 09:16 PM
  4. Need Help with Sum of Filtered Material
    By templeoftyler in forum Excel General
    Replies: 1
    Last Post: 06-05-2012, 07:56 AM
  5. Educational List
    By Waterworka1 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-27-2009, 12:15 PM
  6. [SOLVED] Material:help
    By SI in forum Excel General
    Replies: 1
    Last Post: 02-08-2006, 03:25 PM
  7. [SOLVED] making one material list from mulitple vendor material lists
    By In the beginning in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-07-2005, 11:08 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1