EXCEL VBA
A Comprehensive Beginners Guide to Learn Excel VBA Step by Step
Dustin Adams
Copyright 2020 by Dustin Adams - All rights reserved.
The content contained within this book may not be reproduced, duplicated or transmitted without direct written permission from the author or the publisher.
Under no circumstances will any blame or legal responsibility be held against the publisher, or author, for any damages, reparation, or monetary loss due to the information contained within this book, either directly or indirectly.
Legal Notice:
This book is copyright protected. It is only for personal use. You cannot amend, distribute, sell, use, quote or paraphrase any part, or the content within this book, without the consent of the author or publisher.
Disclaimer Notice:
Please note the information contained within this document is for educational and entertainment purposes only. All effort has been executed to present accurate, up to date, reliable, complete information. No warranties of any kind are declared or implied. Readers acknowledge that the author is not engaged in the rendering of legal, financial, medical or professional advice. The content within this book has been derived from various sources. Please consult a licensed professional before attempting any techniques outlined in this book.
By reading this document, the reader agrees that under no circumstances is the author responsible for any losses, direct or indirect, that are incurred as a result of the use of the information contained within this document, including, but not limited to, errors, omissions, or inaccuracies.
Table of Contents
Introduction
Before we jump right in, you may be wondering if you need to know Excel VBA in 2020. A quick search on Indeed.com, just one of many job search sites shows that there are many jobs where Excel VBA is a requirement as shown below:
Source: indeed.com (2020)
Other examples of jobs that require knowledge of Excel VBA include:
Accounting specialists . Heavy Excel usage in recording transactions, preparing financial statements, budgeting and forecasting.
Marketers . Study market research information which invariably involves large databases with customer survey results
Human resources personnel . Generate reports on various employee-related aspects, manpower planning, management reviews
Statistical analysts . They work with quantitative data and identify patterns for the end-user.
The list presented here is far from exhaustive. As you can see, the job types and academic qualifications vary. Whether you are in the commerce field, computer science, engineering, mathematics, etc., if your job entails large Excel data, you need Excel VBA skills. If you are not yet employed or are thinking of going back to the job market, learning Excel VBA will strengthen your CV and widen the potential job pool.
If you are already employed in any one of these jobs or are thinking of a career in any of these fields, you are or will likely work with a lot of spreadsheet information in your database. Knowledge of VBA is vital in any of these jobs as VBA helps automate tasks in Excel, which improves productivity. VBA enables multitasking as time-consuming Excel tasks can be performed while you attend to other things and also introduces consistency in how tasks are performed, thus reducing human errors. Who wouldnt want to tap into any of these advantages?
Learning how to program goes beyond just watching a short video on how to create a specific application and then blindly following the steps that are detailed in the video. A lot of people make this mistake, and after countless hours with little success, realize they still cannot independently think of a concept that can automate tedious Excel processes and code these in VBA. This is a source of frustration amongst many people who set out on a VBA journey and what leads them to stop learning Excel VBA prematurely. Why this happens is when you are just following steps, but do not understand what is being done and why you are doing it, this lays an improper foundation for you to spring from.
This book takes a different approach, and is the perfect companion for anyone wanting to learn Excel VBA in a number of ways:
It lays a good foundation for all serious beginner Excel VBA programmers through providing all the theory that one may need to understand the process.
It also provides detailed instructions on how to navigate the VBA integrated development environment, and this familiarity makes the environment more user friendly.
It lays out a good foundation for you to write your own programs by laying out code that is commented on, on a line by line basis, such that you understand exactly what you are doing and why. This will tie in theory to practice and will enable you to think of your own variations of problems that you can solve through Excel VBA.
It has a section dedicated to helping you troubleshoot any errors that you may encounter during your Excel VBA learning journey.
Is written in an easy to read, easy to relate to many that will appeal to any reader with a passion to learn Excel VBA irrespective of academic and work background. Keep in mind that just like anything in life, you will have to start somewhere. If you want to start your Excel VBA journey, this book will be the best choice you ever made.
One of the assumptions that this book makes is that you are not reading this beginner Excel VBA book just to stop there, but that you will want to proceed to the intermediate and expert levels. As such, attention is given to laying out the foundation for proper VBA programming. By the end of this book, you should be conversant with fundamentals of VBA programming that will enable you to appreciate the ins and out of programming in general. You will also get to apply these concepts when you write your first VBA programs, which will give you a deeper understanding of VBA programming and give you a springboard from which you can start writing your own applications and to take VBA further.
This book has three distinct sections, the theory, that was laid out in the first two chapters, an introduction to the VBA Integrated Development Environment (IDE) which will walk you through the IDE and familiarize you with the tools that you should have on hand as you start programming. The third section is the practical aspect, where you will write your very first VBA code and incrementally be introduced to other concepts until the final chapter. Before you whip out your Excel workbook and launch your VBA Editor, please take the time to go through the first two chapters and make notes. This will equip you with most of the programming concepts that you will apply in the later chapters. The recommended approach after you are done with chapter one and two is to proceed with the book as you would with a practical course. You have to be hands-on and cannot read it like a novel. Should you feel there are concepts that you cannot understand, circle back to Chapter Two and take a look at the theory about the concept that you are struggling with. Through it all, have fun! VBA is a world full of possibilities, and you are well on your way to coming up with innovative solutions that will automate Excel processes and save you hundreds, if not thousands, of hours in the process.
Chapter 1 - Overview
Following its introduction with Excel 5.0 in 1993, it quickly became obvious that VBA was here to stay. It was well-received by both the programming community as well as the business leaders, which helped it achieve tremendous success. It is no surprise that today, almost three decades later, were still talking and writing about it and no doubt, we shall be doing so for some time to come. But what made this programming language and spreadsheet duet such a hit? And why are we still engaged by this duet today, despite the giant leaps that have been taken in the software industry in the last decade alone? We have almost certainly seen it all in recent years and still, VBA has something to offer that few, if any, can rival. As an aspiring VBA for Excel programmer or applications developer, you may be wondering what exactly you just signed up for. These are all fair questions and you certainly mustnt have to read to the end of the book to have them answered. This chapter discusses the need for Excel VBA programming and gives a breakdown of what is involved.