In this post we cover a number of simple concepts together which are each related to directing the flow of your programs1The featured picture here is a geeky reference to a Yoda condition. Although at first they look complex they are mostly very simple and it’s just a case of selecting the most appropriate one…
The key goal of this post is to give you the skills to make your code understandable to other humans. We will target readers who have only a basic knowledge of VBA coding and will develop a number of high level practices to make your code as easy to read, maintain and understand without making…
This is possibly the most common request from people who want to code in VBA … how to do “stuff” with files. There are two main ways I’m going to show you here and to really understand each we need a bit of background on objects and the Excel object model. Objects are remarkably simple but…
Variables can be a little tricky to get your head around at first. They are important concept and a good understanding of them leads to bug-free, easy to read and efficient code. The length of this post reflects the importance of variables and we will touch on them again and again as we work through…
I came across an annoying compiler error (and VBA quirk) the other day. This is one I have encountered before but never wrote down. It’s counter-intuitive and this makes it harder to track down. Consider the below set of code (as an example):
Public Function arrayBuilder() As String()
Dim localArray(0 To 1) As String
localArray(0) = "First Entry"
localArray(1) = "Second Entry"
arrayBuilder = localArray()
Public Sub testArrayBuilder_Wrong()
Dim outputArr(0 To 1) As String
outputArr = arrayBuilder()
Debug.Print " outputArray(0) = " & outputArr(0)
Debug.Print " outputArray(1) = " & outputArr(1)
When we try to compile the above code we get…