Course Outline – VBA from Beginner to Expert
It’s time to give me your feedback on the VBA course that I’m making! It’s for you guys after all and I want to make sure you find it useful.
You’ve been asking for this course for a very long time and since I’m stuck inside now I found some time to work on it.
Below is a rough outline of the sections that I am thinking of having in the course – nothing is finalized yet and so your feedback will have a major impact on what I put in the course.
Course Outline
- Introduction
- Introduction to the course.
- First Steps
- The basics of what macros are, how to create them, how to use the VBA editor window, adding comments to macros, running macros, commonly used keyboard shortcuts and other foundational topics that will help you better follow the course.
- Here, you will also make your first macro.
- Macro Recorder
- I am not sure if I should include a section about the macro recorder or not because I want to teach VBA here and not the macro recorder, but I think I will include this section anyway – let me know your opinion on this!
- Reference Cells and Ranges
- Getting and Inputting Data into the Worksheet
- Working with Worksheets
- Working with Workbooks
- Adding Logic and Control to Macros
- Project 1
- Smallish project to combine the previous topics – I haven’t decided what this will be yet.
- Prompt User Input
- Variables
- Working with Ranges
- I’m thinking that this will be a practical application type section that teaches you things like getting the next empty row, etc.
- Loops
- Practical Loops
- More practical application of loops, such as looping through a user-selected range of cells.
- Searching for Data
- Showing different ways to search worksheets and workbooks for data.
- Big Project
- Useful project that is bigger than the previous one and combines what was learned so far into something useful – I haven’t decided what this will be yet.
- Arrays
- All about them: making them, changing them, adding and removing things from them, etc.
- Events
- Workbook and worksheet events – probably 1 tutorial to go over a lot of the events and then a series of practical tutorials for some more useful events.
- Working with Other Macros
- Calling other macros, sending them values, etc.
- Debugging and Speeding up Macros
- I might split this into two sections or leave it like this.
- Security
- Macro related security and workbook related security that is handled through macros.
- UDF’s
- User Defined Functions
- Importing Data
- Tutorials relating to importing CSV’s probably.
- I do NOT plan to cover M Code here – I think that belongs in its own course.
- UserForms (?)
- NOT SURE – I might include this as a small section to get your feet wet with them but I’m not sure how much I should include since this could be its own large course.
- Charts (?)
- NOT SURE – I’m not sure that charts need an entire section for this course?!
- Email (?)
- NOT SURE this should have its own section.
- Resource Section: Useful Functions
- Helpful functions that you should know but which don’t fit well into the above sections.
- Resource Section: Working with Ranges
- Helpful things to do with ranges which would just clutter the other topics if they were included there.
- Additional Topics
- Your choice! What do you want?)
What’s Next?
The design of the course is not finished and I greatly welcome your input on what you would like to see in the course.
I want to make something that is useful for you and I’m ready to hear your thoughts!
Don
Hi Don,
This is great. I have long looked forward to a VB tutorial. I have not much knowledge about it but I would like to be able to automate most of my daily work. I think the content of this outline are all good so don’t take any out.
I am a Geotechnical Engineer from Ghana.
I appreciate all your free tutorials. You are blessed!
Regards,
Solomon
Thank you very much! I’m glad you benefit from my tutorials and I hope this course will also be able to help, though this one will be a premium course, so it won’t be free.
Nice
Thanks)
I am pretty much self taught in the Macro department, I found the recorder to be a great tool to learn with, and build upon.
Ok ok, good point. I think that I will mention it in that context and teach it from the perspective of a tool rather than a solution. In line with that, I think that I will introduce it, show how to use it and show how it can be used through the learning process if you get stuck on something. I’m thinking that it won’t be helpful in the context of this course to show people how to perform very specific automation related tasks using the macro recorder because each one of those things could be their own separate tutorial.
Hi Don, great outline! I have really enjoyed your lessons and look forward to your VBA class.
I work with CSV files that are produced by a tester that is appended each time a new test is ran. I have to manual take this data and populate it to a table that is linked to a chart so that the chart is updated automatically once the data is in the table.
I would like to be able learn how to read this updated file and update/append to a Table that updates a chart all automatically. This said, I think covering CSV’s, importing and working with multiple files and a brief section on this aspect of charts with linking to data would be good.
Including a section on the Macro Recorder is a good idea. It can give you some good ideas on building your code should you get stuck.
I like your videos and teaching methods. Although I do prefer to use Option Explicit. It helps avoid the misspelling of variables.
I will be looking forward to your tutorial.
Thanks for the input! I’m really glad that you enjoy my teaching methods))))
The reason that I don’t use Option Explicit almost ever is because I do not want to get people stuck with doing everything in such a structured manner from the start because I think that can lead to more confusion. In the course, I will talk about Option Explicit and my plan with topics like that is to ease people into them throughout the course and then, when the macros get bigger and more complex and structured, to use those kinds of things more.
Following this thought process, this is also why I don’t talk about Objects, Methods, and Functions using these names from the start of the course (and my other tutorials), because I think it’s easy to get confused and put-off.
Here is my favorite example of how to confuse someone, or maybe anyone before their morning coffee haha…. The Range property of the Range Object returns a Range Object and of course you can put Cells() within all of the examples haha. (https://docs.microsoft.com/en-us/office/vba/api/excel.range.range)
I agree
Thanks, I’m really glad to hear that))) I will for sure talk about CSV’s as everyone encounters them at some point and, actually, as I’m planning which practical projects to make in the course, the workflow that you mention here is a pretty good one for a practical course and a real-world example. If you have anymore thoughts about that workflow process and what it entails, I’d be very interested to hear it!)
Thanks Don, that’s a great outline I have been waiting for.
I’m glad you like it and that I am finally working on it)
VBA Corse v good please make
Will do! Working on it now) Stay tuned for when it’s released!
Looks very complete for a beginners course. A few suggestions:
– You have a lesson on importing data. Don’t leave that out. I would suggest you also spent time on exporting data. Both are used frequently in real world.
– For userform you could design a few simple ones. Maybe one asking a user for input, and a few as an addition to MsgBoxes. Simple OK or Yes/No userform.
– The macro recorder is something you always see in every course. It is somethimes usefull, especially in the beginning. You might use an example and also show how the code would be in proper VBA. Would give a student a wow feeling.
– Charts, sure why not. I am inclined to say, more is better. Just show how you can set up a chart from VBA. Usefull when people develop projects for other users. And a nice setup if they want to learn more about Dashboards. I have a (too) big Dashboard with LotusNotes of VBA.
-E-mail is something you almost never see in a beginners course. Good to see that this will be covered too. Good for a reporting feature in a project. Create a report and sent it by e-mail automatically. You also might do something with accessing websites. Get some data or click on elements.
– If you yourself have usefull functions or subs ( the ones in chapter 19) share them. You don’t want people to write code more than once. Everybody probably uses them. I do and I am sure you also. This your course will provide code that can actually be used in projects.
Looks all very promising and with more topics than in most beginners courses I have seen so far.
Thanks for all your great input!!!
Importing and Exporting will have their place, noted. I was going to focus on CSV files for this part of the course, but if you think something else should be in there, let me know.
Userform section, I think your thoughts about this are spot-on.
Macro Recorder – Based on the feedback, I will show it for sure, now, but I think that I won’t focus on it a lot. I want the user to have it as a good tool for when they forget things, especially formatting options. Your idea about the code-correction is a good idea too!
Charts – I think I’ll show how to make them and then leave any funky things someone might do up to them, whereas in other parts of the course I show a lot of variation on what you can do.
Email is so useful for people so I’m happy to include that. As for the accessing websites thing, I’m not sure what I should show about that because it can get very complex if the user has no idea what a website looks like under-the-hood. I’m open to hear more of your thoughts on what I might show for that.
I do want to have a practical section of useful bits of code and subs – my thought on this is to periodically add them to the course as well, but I don’t know about that yet, or if it will even matter to people.
Thanks so much for your input! I want to make a course that is useful for the people in the real world and I’m and very receptive to all of this feedback)
Hi Don. Just looked at your video how a user can select a file. Kind a forgot that you use a lot of comments. Keep doing that in your new course. And explain why this is so important. I also use a lot of comments. Whenever I add a new feature to one of my projects I always start with a step by step plan what to do.
Step 1 – Open file
Step 2 – Copy some data
Step 3 – Put that data in a new file
Step 4 – Close opened file
Step 5 – Save new file
Think first and then start coding. And indeed if you need to update your code after 6 months you don’t want to spent an hour trying to figure out what the code is actually doing and why. And you are the one who wrote it.
Last remark about comments in your course. It also feels like its a sort of a course book. I am a bit old school (my first ‘computer’ was a brand new ZX81) but being to read why something is done that way just feels a bit more solid and makes it easier to go back to instead of just the code itself.
I’m glad the comments are useful! I actually started with minimal comments in the course but then have been adding more and more since I got feedback that people don’t like it when the instructor assumes that you remember everything from the previous so many lessons. I think that I could actually turn each tutorial into a section for a book and I did worry that I was commenting too much, so I’m glad to hear that it seems helpful and not overkill. I figure that I can always include a non-commented version of the code if people want it anyway. I totally agree with you about commenting for the future because, let’s face it, if you don’t use something for 6 months, you will forget some things.
I like your step-by-step notes plan too! That’s a great way to design a macro and keep your thoughts in order!
Positive Feedback on the course outline.
On a personal interest woto hear on the ability to Load data on a Pwer Pivot via VBA.
Thanks for the input! I’ll make a note of that and see what I can do!
Can we have a list of all the VBA commands/codes with descriptions ie for a beginner …would be helpful as a reference guide when following this course. I have started a number of times to learn VBA ..then stop cause I am struggling …then start again ….( a few times ) ..what is the best way to stick with VBA …sorry to sound so ‘basic’ ..I am an advanced excel user …yet with VBA I feel a total novice …any ideas?? thankyou
Don’t be sorry! I want to make this as useful as possible for everyone and your input will help me do that! 🙂 I always hate it when things get too technical too quickly, so one thing I am doing is to avoid using technical terms from the start; I have also decided to HEAVILY comment the code in the workbooks that you download for each tutorial – actually, this part is taking me a lot longer than the actual tutorials and slowing me down quite a bit, but I think it’s important. I’d be very happy to hear more thoughts on how you think I could make it easier to learn. The common VBA code cheat sheet does sound interesting, do you have any thoughts on how that might look? I am also very open to adding more things to the course once it goes live so that I can adjust it to make it better.
I would recommend you including a section on the Macro Recorder in your tutorial. I use the Macro Recorder quite a lot. Sometimes to quickly build the structure of the code so that I can then edit it to make it more efficient / versatile, other times I use it to see how to automate a particular process if it is in an area that I am unfamiliar with. It maybe a lazy way to do it but if it gets the job done quickly and efficiently then what harm.
Error handling is an area that I have under utilised to date. I’m not sure if you are covering this topic in the tutorial but it is something I definitely need to work on.
I also use vba to access data in external file, move & rename photographs, export screenshots, export copies for multiple tabs to files. A section on this might be interesting.
Good point on the macro recorder! Also, I will definitely be including error handling, particularly from a practical perspective. It is threaded throughout tutorials where it should be used, and also explained there, as well as having a section specifically for it.
Dealing with files will also be covered throughout a couple sections.
Good morning Don!
I just read on what you are working on and it all sounds great! I am very interested in knowing how use VBA in my daily job. I work with a lot of different types of spreadsheets and would like to add functionality to them to make the process more efficient/effective. I can’t wait to take this course and more that you are willing to share with all of us.
Thank you for keeping me in the loop.
Alan
You are very welcome Alan! I’m glad that you are excited for the course! I think it will really take your vba/macro skills to a high level where you will be able to think of what you want to do and simply make it happen without a second thought. Thanks for the comment, and I will definitely keep you in the loop!
Don
Hi Don,
Thanks for the update.
I think it would be nice to start with the recording of macros with the macro recorder so persons can appreciate how it’s written in the background while learning to edit them before actually learning to write macros from scratch.
As for the areas that merit their own separate course I’d let you all decide, since I don’t know much more about those topics, or you could just mention what they would be for completeness sake