So You've Inherited a VB6/VBA Project!

so_you_have_VB.txt

So You've Inherited a VB6/VBA Project!

Congratulations! You've inherited a large, complex, Visual Basic 6 or VBA
project written by another programmer. In terms of transmissible conditions...
well, at least it's not AIDS.

[Sidebar: why "Visual Basic 6 or VBA"? Well, in 2000-something, Microsoft
 introduced the .NET platform. This is a managed runtime in the vein of the
 Java virtual machine, and one of its selling points was the ability to
 compile multiple source languages to .NET assemblies. Visual Basic was chosen
 as one of the "flagship" languages for the platform due to its, er,
 distinguished history and fine reputation. Unfortunately for Microsoft MVPs
 the world over, the .NET infrastructure was actually quite well designed,
 and the "old" VB was not at all suited to the semantics of the new platform.
 So we got a new, object-oriented, generics-possessing, garbage collected
 "Visual Basic" for the new platform. Really, a C# in VB's clothing. A fine
 language, I'm sure, but something of a pointless subterfuge...
 
 Henceforth, when I say "Visual Basic", I mean VB6/VBA, not the new VB.NET.]

Visual Basic is not, to put it mildly, a well designed language. Visual Basic
for Applications, its incarnation in the macro engine of Microsoft's Office
suite, was something of an inspired hack given the even more terrible ad-hoc
macro languages it replaced, but at the end of the day it's still VB.

So this rambling document is an attempt to provide some hard-won advice on
wrangling the beast, at least until it can be humanely put down.

Circle I: Limbo | wherein dwell the virtuous pagans
Much of programming language choice can considered a matter of taste. With that
in mind, let us take a leisurely stroll through some features of VB which can be
considered merely to be in bad taste, rather than criminally negligent.

Parental Warning: Explicit Content
Visual Basic is, to use the lingo, a statically typed programming language.
Variables in the language have data types which are known to the compiler and
do not change at runtime. This is a good thing! Type systems help catch large
classes of errors as early in the programming process as possible.

However, the designers of Visual Basic did not want to intimidate their target
audience (children, the mentally infirm, investment bankers, Microsoft Excel
MVPs) with this typing discipline. It's all so computer-sciency! So, by default
VB will infer the types of variables for the programmer upon their first mention
in a program.

Type inference is very sexy! Very well-respected languages, mostly used by
academics and quantitative analysts, like Haskell and ML have done this for quite
some time. Even C++ is getting on the type inference bandwagon.

But unlike these languages, VB will always infer the god-forsaken Variant data
type for every variable not explicitly declared in the program. Variant is what
type theorists call an "Any" or "open sum" type. It's the Bob Harper nightmare,
the cheat code that professors use to beat down wimpy Python programmers in
debates about type systems.

Anyway, what matters for you is that Variant is inefficient in the common case,
and in any circumstance having variables spring into existence because of typos
is not desirable.

So, always, always...

RULE #1
Begin every program with
Option Explicit

This will force you to explicitly declare every variable you intend to use,
along with its data type, like so:
Dim i As Long
Dim time() As Double

For that matter, what are the data types available in VB?

RULE #2
Use the right data type for the right job!

VB provides a fairly anemic set of built-in types, but it's enough to get the
job done. No, sorry, that's not right at all. It's enough to call into C APIs
and thus get the Job Done.

First, the integral types:
Byte | 8-bit unsigned | use this for, eh, storing values that will never be
  above 255? Or, really, for bytewise processing of binary data.
Integer | 16-bit signed | never use this
Long | 32-bit signed | use this for 'counters' and 'indexes'
LongLong | 64-bit signed | this only exists on 64-bit Office, and one supposes,
  would be useful for indexing arrays on that environment
LongPtr | 32/64-bit unsigned | this only exists on 64-bit Office, and when you
  need it, you'll know. Sorry---it's not time for this one yet!

Next, the floating-point types:
Single | 32-bit | never use this. Seriously, it's 2015, and you're not writing
  a GPU shader for Call of Duty 5: Freedom Harder. Just don't.
Double | 64-bit | yes. This is your go-to, standby, "old reliable" floating
  point type. If you want decimal places, you don't really care how many, but
  generally desire some nice balance of performance and accuracy, use this.

A single, uh, fixed-point type?
Currency | 64-bit, maybe? | yeah... you're not going to need this. Just store
  currency values as an integer at the lowest granularity (pennies, etc.) and
  specify your rounding behavior directly.

The ever-important string type!
String | 32-or-64-bit pointer + 32-or-64-bit length + data | it's not the string
  type you deserve, it's the one you need right now. Or, well, get right now.
  It's ok. It stores characters and pretends reasonably well that they represent
  text. As long as you're not getting too exotic with the Unicode, you should be
  OK. The API is quite awkward.

The somewhat important boolean type:
Boolean | 8-bit, probably? | use this for storing True or False values. It's
  relatively rare you'll create these directly, generally creating them "en
  passant" in If statements and loops with comparisons; e.g.
If x > 5 Then
    Debug.Print x
End If
  at least notionally creates a temporary of Boolean type to contain the result
  of the `x > 5` comparison. You'll sometimes want a local Boolean variable to
  store the result of a test, or as a "condition flag".

The all-too-frequently-necessary Variant type:
Variant | big enough to fit any scalar type | you'll sometimes need this for
  interoperability with other code, particularly COM collections. Generally you
  will want to "box" more specifically-typed variables into Variants when
  necessary, rather than work with Variants all around.

[Sidebar: what's a "scalar type"? In short, it's a type that's "atomic" in the
 sense that it's not composed of other types. As a counter-example, arrays and
 structures are not scalar types. Why don't we just call them "atomic types"?
 That means something else! Don't worry, VB has no word for that concept...]

Base Jumping
Which brings us to arrays, and some rather odd choices made by the designers of
Visual Basic. It was, for a time, fashionable to allow programmers to declare
arrays with arbitrary index sets. For example, in Ada, a programmer may happily
create an array of 5 elements having the indices [57, 58, 59, 60, 61]. As some
mathematical formulas incorporate unusual indexing, this was met with some
happiness.

Except that it has the terrible, insurmountable, ridiculously obvious problem of
rendering interoperability either a) impossible or b) silly! Consider the poor
library author whose API contains a function which must take an array of values
and compute their square roots, returning a new array. Either we must:
  * pass around lower and upper bounds everywhere attached to their arrays,
      requiring every API boundary to check these and make no assumptions
      regarding initial nor final elements; or
  * store these indexing schemes function-locally as applied offsets, making the
      scientific programmer's "elegant notation" irrelevant to consumers of
      their code; or
  * do nothing, resulting in an unutterable disaster.

Visual Basic, unsurprisingly, allows variable-range indexing and adopted the
first option above to mitigate the sillier effects. An array may be declared as:
Dim x(79 To 83) As Double
and its lower and upper index bounds retrieved with `LBound(x)` and `UBound(x)`.

An array may also be declared as:
Dim x(70) As Double
in which case an array of 70 elements is created having indices either from 0 to
69 or from 1 to 70, depending upon the programmer's specified default array
base. The default default, and sane choice (there's math, don't ask), is 0.
But this brings us to...

RULE #3
Always explicitly set the array indexing base to 0

Every VB program you write should begin with:
Option Explicit
Option Base 0

Of course, VB being VB, you'll still get handed arrays from internal and
external APIs that don't conform to this standard (and don't get me started
on the COM collection classes)! Which implies...

RULE #4
Never, ever, ever assume anything about the indexing of an array you're given

Being handed an array as a function argument is a lot like being handed a
firearm: always assume it's loaded, and point it in a safe direction.

The correct way to loop over indices of an array:
Dim i As Long ' remember, Long is your friend for indexing
For i = LBound(some_array) To UBound(some_array)
    do_something_with some_array(i)
Next i

The correct way to get the number of elements in an array:
Dim count As Long
count = UBound(some_array) - LBound(some_array) + 1

We All Live In A Yellow Sub Marine(ByRef x As Long)
The topic of responsible argument consumption is a convenient segue to another
questionably tasteful VB design choice: the syntactic distinction between
"functions" and "subroutines".

VBA can be easily compiled as VB.NET code or compiled directly from the VBA to a VB.NET DLL via http://www.analystcave.com/vba-compiler-add-in-to-vb-net/. Fortunately both languages are so similar in syntax