Public Sub TakeAWalk()
    Workbooks("theMikeCom_RandomWalk.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 &gt; 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 &lt;= 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

Public Sub Flower()
    Workbooks("theMikeCom_RandomWalk.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

As I graduated college I must admit being very ignorant about how Microsoft Office is used in the business world. For me in IT that meant supporting Office much more than I expected. As a programmer specifically I had no idea I would be working on business tools built for Office.

I got my first taste of these tools during my short stint in support at HP. While I was there one of my colleagues would use Excel to generate reports on systems we were maintaining. Data was pasted into Excel and we would run some macros to massage it into useful reports. Since the data sets were always small this was a perfect use for macros.

When I started at LECG I was surprised to learn that some businesses actually make a living from macros. Oh they don’t start out that way. First some business analyst comes up with a report that is really useful. Then that same analyst uses the “Record New Macro…” button to automate some of the report. From there it grows too big and is transferred to IT where it is extended and modified and becomes a general monstrosity. And after debugging a few of the horrible things I decided to learn a little more of the macro language.

What better way to do that than to think back to college. 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.

[code]Public Sub TakeAWalk()

Workbooks("theMikeCom_RandomWalk.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[/code]

With that done I came up with a bonus stupid trick, 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.

[code] Public Sub Flower()

Workbooks("theMikeCom_RandomWalk.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[/code]

So what did I learn after all of this? For one I have a strong dislike for VBA. It works well for small projects with small data sets. However business managers like to take those small projects and expand on them. 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 – Stupid Tricks” 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.

Ok, now I’m going to go and forget I know anything about VBA.