Practical VBA for Practically Anyone
Gerry McSweeney
For when you know what you want to do
But dont know how
Copyright 2021 Gerry McSweeney
All rights reserved. No part of this book may be reproduced in any form or by any electronic or mechanical means, including information storage and retrieval systems, without written permission from the author, except in the case of a reviewer, who may quote brief passages embodied in critical articles or in a review. Trademarked names appear throughout this book. Rather than use a trademark symbol with every occurrence of a trademarked name, names are used in an editorial fashion, with no intention of infringement of the respective owners trademark. The information in this book is distributed on an as is basis, without warranty. Although every precaution has been taken in the preparation of this work, neither the author nor the publisher shall have any liability to any person or entity with respect to any loss or damage caused or alleged to be caused directly or indirectly by the information contained in this book.
For Linda even though shell never read it
Preamble
What This Book Is Not (And Doesn't Claim To Be)
This Book Is Not a Book
To my mind a book is a weighty tome usually consisting of several hundred pages, split into chapters, paragraphs, sentences, etc. A book normally has a narrative flow which will take you from start to end, introduction to conclusion. You can start a book by opening it at a random page and reading from there, however in most cases it's not recommended.
This book - and despite the above I will refer to it hereon as a "book" - does have loose chapters, etc and it can be read from virtual cover to virtual cover. However it's not very long - more like a novella but without the gratuitous sex or unexpected twist at the end. It's also meant to be dipped into when you have a specific problem that you're dealing with. It might be that you only need to "dip in" very rarely but if it solves your problem then I think it's still (your) money well spent.
This Book Is Not a VBA Primer
This book will not teach the beginner how to write code.
If you're totally new to programming or to VBA then there are many excellent books which will take you through the whole subject in a methodical and structured manner. I have several sitting on the desk beside me. As with most things there are also lots of guides and videos on the internet. The phrase "Google is your friend" comes to mind.
This book will be of value to both experienced developers and to those just starting out on the VBA journey. It assumes you already know what a variable is, that you're familiar with loops and conditions, that you know the difference between a subroutine and a function, etc, etc.
I came to VBA as someone who was already a veteran programmer and experienced Excel use. What I didn't know was how to code in VBA.
Using a combination of written material and my friend Google I've built up a detailed understanding of how to write VBA and use it to make my working life easier. In doing so I've learned, created, and refined a large number of techniques for achieving specific results using VBA.
This book brings together many of those techniques. It distils 10 years of trial and effort into a single place so you don't need to go through that same pain.
This Book Is Not About Objects
You may have heard it said that VBA is an Object-based language or at least that it can be used to create Business Objects.
This may well be the case but if you want to learn about Object-based programming then this book isn't for you. I've tried creating Business Objects in VBA. It's only left me frustrated and full of admiration for those guys who can do it. For me it was more trouble than it was worth.
That's not to say that I can't see the benefits.
I particularly like the "black-box" approach that Object-based programming brings. You have a function or subroutine that does something. You give it a set of parameters. Assuming the parameters are valid it returns a result or performs a task. You don't need to know what's happening inside the function or subroutine. You only need to know that its outputs are predictable.
The majority of techniques in this book operate to this "black-box" principle. Feed them the right parameters and you'll get a predictable output. You don't need to know what's happening inside - though the curious amongst you will probably be interested in finding out.
You'll also see that many of the techniques start with "Get" or "Set". And yes, this is something I've borrowed from Object-based programming. But there the similarity ends.
This Book Isn't Just About Excel
VBA covers the whole family of Microsoft products.
My own focus over recent years has been on working with Excel and Access and linking them together. So most of the techniques shown relate to these products.
However I've also included snippets relating to Word, PowerPoint, and Outlook with a bit of Scripting and SQL scattered here and there.
As your own familiarity with VBA increases you'll see that the same principles apply across the whole family. So if your main interest is in VBA for Word then don't assume that there's nothing for you here.
This Book Isn't The Only Way
In VBA there are often many different ways to achieve the same result.
A Google search for a particular problem might lead you to an interminable thread of argument and counter-argument about the efficiency or elegance of the various solutions on offer. That's assuming that any of them work in the first place.
The techniques offered here represent my way (or ways) of solving a particular problem. As my own knowledge has grown some of them are very different from how I tackled the problem in the first place, even if that first solution worked.
I'm also a great believer in clarity over ultra-efficiency. Decades in IT have taught me that too many programmers are simply too clever for their own good - or certainly for the good of the poor sods who have to unravel the pieces long after the genius programmer has fled the scene.
The upside of this is that what you'll find in this book should be relatively easy to follow and include in your own VBA programs. The downside is that you or someone else may be able to figure out a better way. That's ok. Use what works for you or the specific environment you work in.
Things You Need
If you're serious about VBA - and because you're reading this book I assume you are - then you'll need a legitimate copy of the Office suite.
Specifically you'll need a copy of Office 2007 or later.
Most of the techniques shown here will work on earlier versions of Office but no allowance is made for any that don't. If you are running an earlier version then please be aware of this.
Ideally, your copy of Office will also include Access.
If your interest is solely in VBA for Excel then there's still plenty for you here. However there's a lot of material relating to VBA for Access and it'd be a shame for you to miss out.
Things You Need to Know
I said at the start that this book is not a VBA primer.
However there are certain techniques within the techniques that need to be explained because they may be unfamiliar to you, even if you've already coded in other languages.
Object Variables
Put simply, an Object Variable is a variable that refers to an object.
Confused?
It may become clearer if I say that the object in this case is a VBA Object eg an Excel worksheet, an Access form, a PowerPoint slide, etc, etc.
