Welcome to the Excel VBA Handbook. In this course, you will learn how to build 10 VBA projects. The purpose of each project is to:
- Show you how to build a VBA Application from scratch.
- Provide you with properly written code that you can use in your own projects.
- Provide you with the knowledge to create a wide variety of useful VBA projects.
- Show you the steps to create professional-quality code.
- Help you master creating projects in Excel VBA.
There are 10 projects included in the course. Each project comes with:
- Video tutorials explaining each step and concept used.
- The full instructions(in pdf format) to show you how to build each application.
- A Zip file with the relevant workbooks and data – see contents below.
The contents of the Zip file are:
- The fully documented source code of the completed project in an xlsm workbook.
- An empty xlsm template workbook you can use for your own attempts to build the project.
- All the data required to run the application. For some applications, the data will be in a separate workbook and for others, it will be in the same workbook as the source code.
To open the zip file, first download it to a folder on your computer. Then right-click on the zip file and select “Extract All…”.
Sometimes you may just want to build the project and skip the explanations. To make this easy, all the instructions to build the project have the following background:
These colored sections contain the instructions to build the application
Add code to get the user settings
- Update the CreateTeamResults so it has the following code:
Public Sub CreateTeamResults() ' Get user settings from worksheet Dim sYear As String, sWorkbook As String, sUserTeam As String sYear = cnReport.Range("C4") sWorkbook = cnReport.Range("C5") sUserTeam = cnReport.Range("C6") End Sub
Any code you see outside of the above sections are for example only e.g.
Dim sFilename As String sFilename = ThisWorkbook.Path & "\" & cnReport.Range("C5")
Setting Yourself up to Win
“Take things always by their smooth handle” – Thomas Jefferson
Assign some time each day
If you like you can go through this book in a single day. The reality is that most people do not have the time. However, anyone can find 60 minutes a day if they are serious about learning VBA.
60 minutes may not seem like much. But in only 20 days you will have 20 hours done. These will be quality hours. You will amaze yourself on how much you learn in this time. If you do not have an hour then 30 minutes a day can also be very effective.
Type the Code
I cannot emphasize this enough. The more you practice the better you become. The best way to learn VBA is by typing the code. Errors will occur and you will learn about the language by fixing them. You will also understand the code much better when you are typing it one line at a time.
Break it down
Break your tasks down into small parts. Don’t be overwhelmed. Each Macro is a series of tasks that happen in order. Take them one at a time. When one is complete go to the next. This seems self-evident but I even forget it myself sometimes.
Don’t let errors stop you
Many people give up when they type in a line of code and get an error message. Most of these errors are simple to fix. They are usually caused by a typing error.
Take a deep breath and look at the error message. They may seem cryptic at first but most of the time tell you what is causing the problem. You can also check your code against the code in the working version of the application.
If you still cannot resolve an issue then add a question to the course discussion.
Close all workbooks
When you are working through the application it is a good idea to first close any Excel workbooks not connected to the application. It will make what you are doing much clearer.
Enjoy Learning VBA
Last but not least – enjoy the process. Lots of people love writing code. In fact, thousands of people do it in their spare time for fun. If you enjoy using Excel then the chances are you will really enjoy programming Macros.
Let’s get started
Now that we have completed the introduction, let’s have a quick look at some essential tips for using VBA…