Derek Harlan - Beginning Microsoft Excel VBA Programming for Accountants: A Practical and Project Based Approach

Beginning Microsoft Excel VBA Programming for Accountants:

A Practical and Project Based Approach

Derek Harlan

www.DerekHarlan.com

Copyright 2017 by Derek Harlan

All rights reserved. This book or any portion thereof may not be reproduced or used in any manner whatsoever without the express written permission of the publisher except for the use of brief quotations in a book review.

Although every precaution has been taken to verify the accuracy of the information contained herein, the author and publisher assume no responsibility for any errors or omissions. No liability is assumed for damages that may result from the use of information contained within.

Printed in the United States of America

First Printing, 2017

ISBN 978-1981190959 (print book)

www.DerekHarlan.com

Benfords Analysis image courtesy of Grzegorz Knor

Intuit and QuickBooks are trademarks and service marks of Intuit Inc., registered in the United States and other countries.

Microsoft, Excel, Visio and Windows are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries.

Beginning Microsoft Excel VBA Programming for Accountants is an independent publication and is neither affiliated with, nor authorized, sponsored, or approved by, Microsoft Corporation

All other trademarks are the property of their respective owners

About the Author

D EREK HARLAN IS A CERTIFIED Public Accountant (CPA) with nearly a decade of experience in the accounting and financial reporting. Prior to this he held various roles in the Information Technology industry ranging from customer desktop support to systems management and began computer programing at the age of 10. He holds both a Bachelors & Masters of Science degrees in Accounting from University of Illinois at Chicago. Derek lives with his wife and son in the suburbs of Chicago, Illinois where he tinkers with an old IBM 5150 and enjoys working on 80s era computers.

Acknowledgements

I D LIKE TO THANK MY father, Charles, who found a reluctant student when he tried to teach his then six year old son BASIC computer programming. Id like to thank my Grandfather, Ray, who a few years later encouraged and helped that same boy to learn computers.

Id like to thank my wife, Katie, who put up with my writing process and encouraged me to finish.

Lastly, to those who offered advice and support - thank you.

Introduction

T HANK YOU FOR PURCHASING Beginning Microsoft Excel VBA programming for Accountants. Microsoft Excel is probably the most used spreadsheet program in the world. I wrote this book to help accountants and finance workers get a leg up on using this tool to truly make your life easier and your work days more productive.

In this book youll learn basic programing techniques specifically related to Microsoft Excels Visual Basic for Applications programing language or VBA for short. If youre a complete newcomer, dont let this intimidate you. Well work through it from the very beginning. The book will then be crowned by three projects which will give you a taste of how to design and build a complete application and can also be used as starting off points for your own projects.

Its my hope that youll find ways to automate many of the repetitive tasks you do on a daily basis. Through some code youll be able to make the mind numbing tasks less dreadful and improve your personal productivity. You may even be able to solve long-standing problems and become the offices go-to Excel guru [i]

T HE BOOK WAS WRITTEN for financial accountants who are working with Excel on a daily basis. For people who create workpapers and reconciliations in Excel this is for you. Although, anyone with a desire to learn VBA will benefit from this book, the examples will have a slight finance slant to them, especially later in the book.

T O GET THE MOST OUT of this book Im assuming you have a couple things. First, you have some knowledge of financial accounting and/or bookkeeping. Essentially an understanding of Debit and Credit and General Ledger is about as deep as this book goes. Im also assuming you have access to an IBM compatible PC or Laptop with a desktop version of Microsoft Excel installed. The examples in this book were made in Excel 2010 however they are compatible with desktop versions of 2013 & 2016. Also, I assume you have basic windows knowledge, e.g. how to move and find files, how open /close applications etc. Lastly, I hope you have a desire to learn and practice the examples in this book.

T HE EXAMPLES IN THIS book were written in Microsoft Excel 2010 however they will also work in later desktop versions of Excel. Youll need a computer that has and can run Microsoft Excel 2010 or later (again desktop versions only, the web versions and Microsoft Office 360 do not support VBA). It shouldnt matter if you have a 32-bit or 64-bit version of the software. Youll also need internet access to download the example files, which I highly suggest you do. Although none of the exercises in this book require internet access. See Appendix A for information on how to download the sample files.

T HIS BOOK CONSISTS of five chapters. Organized into two parts Part 1 will consist of the first 3 chapters and will cover the basics of VBA and give you the building blocks to create your own applications. Part 2 will consist of chapters 4 and 5 each having a practical project being built from the ground up and showing what you can do with the knowledge gained in Part 1.

Part I

Chapter 1 will introduce you to the basics of programing and familiarize you with the keywords and features of VBA

Chapter 2- will introduce you to the Excel Object Model which will build upon the knowledge learned in chapter 1 and cover specifics features to work with Excel workbooks and worksheets

Chapter 3- will introduce you to design forms so that you can create a user interface consisting of buttons and other elements.

Chapter 4- will teach you basic design and error handling techniques as well as how to use the built in debugging tools to troubleshoot your program.