When I started at Bates Group, LLC one of my first assignments was to debug an Excel VBA macro. Knowing nothing about the language I fought my way through the bug and fixed the macro. After that I quickly decided to learn more about the language. Since “Hello World” only gets me so far, I decided to do something a little tougher. What better way to do that than to think back to my college assignments?
Back then one of the assignments I had was to write a random walk function. Imagine standing next to a lamppost on the street. From the lamppost you can take a step in one of four directions; North, South, East, or West. You take a step in a random direction and then look at where you are. From your new location you take another step in a random direction and you keep taking these random steps for a while. Finally you stop and look up, how far away from the lamppost are you?
The following function does that only much faster than you or I could. It takes 20,000 steps total and colors them along the way. Every 2,000 steps it will change colors leaving a cool trail as it goes along.
Public Sub TakeAWalk()
Workbooks("themikecom_vba_helloworld.xls").Activate
ActiveWorkbook.Worksheets("Board").Select
' Where on the sheet should we start?
ActiveSheet.Range("EE150").Select
' How many steps per turn should we take?
STEPS_PER_TURN = 2000
' How many turns should we take?
TURNS = 10
For j = 3 To (TURNS + 3)
For i = 0 To STEPS_PER_TURN
' Should we step east or west?
randomX = Int(4 * Rnd)
' Should we step north or south?
randomY = Int(4 * Rnd)
' Move west-east
Select Case randomX
Case 2 ' Move one step west
If ActiveCell.Column < 1 Then ' Do not overstep the west border
ActiveCell.Offset(0, -1).Select
End If
' Case 1 - Stay in the same spot
Case 0 ' Move one step east
If ActiveCell.Column <= 255 Then ' Do not overstep the east border ActiveCell.Offset(0, 1).Select End If End Select ' Move north-south Select Case randomY Case 2 ' Move one step north If ActiveCell.Row > 1 Then ' Do not overstep the north border
ActiveCell.Offset(-1, 0).Select
End If
' Case 1 - Stay in the same spot
Case 0 ' Move one step south
If ActiveCell.Row <= 65535 Then ' Do not overstep the south border
ActiveCell.Offset(1, 0).Select
End If
End Select
' Leave a trail
ActiveCell.Interior.ColorIndex = j
Next i
Next j
End Sub
With that done I wanted to add another function to learn how to create a menu. I came up with the square flower. This function will generate a square of random size with each section of the square filled with a different color. This function taught me some tricks about looping in VBA, some ways are a lot faster than others.
Public Sub Flower()
Workbooks("themikecom_vba_helloworld.xls").Activate
ActiveWorkbook.Worksheets("Board").Select
Dim start As Range
Dim Length As Integer
Dim Width As Integer
Dim Color As Integer
' The starting point of the flower
Set start = ActiveCell
' The maximum size of the flower
size = Int(57 * Rnd)
' Ignore boundry errors for now
On Error Resume Next
For z = 0 To size
' Generate a random color for this row
Color = Int((56 - 1 + 1) * Rnd + 1)
' Left side
Range(start.Offset(0, 0), start.Offset(Length, 0)).Interior.ColorIndex = Color
' Bottom side
Range(start.Offset(Length, 0), start.Offset(Length, Length)).Interior.ColorIndex = Color
' Upper side
Range(start.Offset(0, 0), start.Offset(0, Width)).Interior.ColorIndex = Color
' Right side
Range(start.Offset(0, Width), start.Offset(Width, Width)).Interior.ColorIndex = Color
Set start = start.Offset(-1, -1)
Length = Length + 2
Width = Width + 2
Next z
On Error GoTo 0
End Sub
So what did I learn after all of this? Mostly that I have a strong dislike for VBA. It works well for small projects with small data sets. However those small projects quickly expand into real programs which need to be maintained. You are better off doing it right the first time instead of maintaining a large clunky macro.
Download the complete macro here. You will need to enable macros in your security settings to get them to work. Once enabled, select “Random Walk” from the “theMike.com – Hello World VBA” menu. This will start a random walk which will finish after a couple of seconds. The “Square Flower” menu item will create a square flower under your cursor.