Everybody faced problems with array initialization, error handling for dinamics and so on. Today, first time in my life (and that’s almost 20 year worth of coding) I found a bug out of the blue. Or what I think to be so. Typical example incoming:

On Local Error Resume Next
Dim sArray() As String, C As Long, D As Long

D = [102 or 99]
For C = 100 To D
	ReDim sArray(UBound(sArray) + 1)
	If Err.Number > 0 Then
		ReDim sArray(0)
		Err.Clear
	End If
	sArray(UBound(sArray)) = CStr(C)
Next
Debug.Print UBound(sArray)
On Local Error GoTo 0

' D = 102 — Ubound(sArray) = 2
' D = 99  — UBound(sArray) = Error

This example is stupid on purpose, but gets us to the point. If D is set to 99, then sArray is gonna be uninitialized. Which means that we have to set another error handling to check wether or not the array contains something. And this generally brings us pain, it’s always very, very, very ugly to do, but until now it was the only way, aside setting up a base 0 array with data starting from 1, like this:

ReDim sArray(0) As String
ReDim sArray(UBound(sArray) + 1)

sArray(UBound(sArray)) = 1
For C = 1 To UBound(sArray)
	Debug.Print sArray(C)
Next

Another ugly way to do the job, but as I said a little above, there weren’t many ways to do it. Until I found something so stupid to be genial. Let’s see a slightly modified version of the same code.

Dim sArray() As String, C As Long, D As Long
sArray = Split(""," ")

D = [102 or 99]
For C = 100 To D
	ReDim sArray(UBound(sArray) + 1)
	sArray(UBound(sArray)) = CStr(C)
Next
Debug.Print UBound(sArray)

' D = 102 — Ubound(sArray) = 2
' D = 99  — UBound(sArray) = -1

You probably won’t understand that, so I’ll explain: the Split() of a 0 length string with a non-0 length string, brings us an amazing Array(0 To -1). You heard it right, 0 to -1. What does this mean?

1. Empty arrays can checked with a (UBound(Array) = -1).
2. Incremental arrays available from scratch, from index 0.
3. Possibility to return safely arrays without the need to define a count variable to set the array lenght.
4. …
5. Profit!

This is one of the most stupid amazing things I ever seen, and it caught me so off-guard I totally didn’t expect it. Could even make it to The Daily WTF?!?