More than a “Hello World” in VBA

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

Macro Flower ShotWith 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.Excel Random Walk

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.

Creating an Audiobook for the iPod

iPodiPods treat audiobooks differently than music files. For one you can leave an audiobook listen to something else, and later pick up where you left off. An iPod won’t do this when you are listening to music file. Furthermore an audiobook can have chapter markings making it easier to find a chapter in a longer book.

iTunes makes it easy to create a music file for the iPod. You can insert a CD and iTunes will import it at the push of a button. Creating a proper audiobook however, is difficult with iTunes. You could import a whole CD as a single file but what if your audiobook covers multiple CDs?

I’ve had this problem for a while now. My initial solution involved importing each track and numbering them in a way that they would line up in a playlist. This works, but it’s annoying. I can play the book but if I stop I have to remember which chapter I left off with. If I don’t go back to the book for a couple of days I forget where I am and have to start over. A couple of weeks ago I finally had enough and went searching for a better solution.

The solution to my problem came from a program called Chapter and Verse by lodensoftware.com.  The program will take a list of AAC (.m4a) files and convert them into one single audiobook (.m4b).  This makes creating an audiobook from a CD very easy.  Simply import the CD using iTunes in the AAC format and then use Chapter and Verse to make the conversion.

Chapter and Verse - Main screen

Chapter and Verse is not perfect however.  If files are not in the AAC format it will have to convert them into this format before creating the audiobook.  If you use a free audiobook solution such as librivox.org this means it’ll have to convert each file before combining them into one.  Not too big of a deal, just plan a little extra time when creating an audiobook from a set of mp3s.

Quick Audiobook Tutorial

Creating an audiobook is very easy in Chapter and Verse.  First download and install the application.  You will need to make sure iTunes is installed as well because it is used by Chapter and Verse to convert files.

Once Chapter and Verse is installed start the application and it will load to an empty project screen.  Each project contains a set of AAC audio files which will end up being the chapters in this simple tutorial.

Begin by clicking the “Add Files” button to add the audio files you want to be a part of the audiobook.  The open file dialog only shows MP4 files by default so be sure to change the filter if you are adding say .mp3 files.

If you added a file that is not in the AAC format Chapter and Verse will attempt to convert the file using iTunes.  In that case click “Yes – Convert” on the conversion screen.  iTunes will open for the conversion, do not close it until it’s done.  When Chapter and Verse is done with the conversion your files will be added to the “Input Files” tab.

Click on the “Chapters” tab to change each chapter’s name.  On this tab you can rename each chapter in the audiobook.  For my audiobooks I generally set each chapter name to be the same as that of the file.  There are several options here that you may wish to explore.  Near the bottom of the tab there is an “Input Files” dropdown where you can choose prefixes for the chapter names.  I usually to choose the <Filename> option and leave the rest of the settings at their defaults.

The “Metadata” tab allows you to change the file information for the audiobook.  I usually set the title because that is displayed in the track listing on the device.

Finally make sure “Autobuild” is on and then click the “Build Audiobook” button.  Chapter and Verse will ask you where to save the file and then will create the book.

So to sum it all up

  1. Click Add Files to add your audio tracks, Chapter and Verse will convert them if necessary.
  2. Edit the chapter titles on the “Chapters” tab.
  3. Edit the final file information on the “Metadata” tab.
  4. Build the audiobook

 

Ahh programming for a living…  There’s nothing quite like working on someone else’s problem for hours on end trying to get the dumb thing to do what you want.

Alright I’ll admit it; there are some days when I would rather be at home working on one of my own projects.  Those days I find it very hard to focus because one thought runs through my head.  “My own projects are fun and exciting and this is soooo BORING, I want to go home!”

But since my fun projects don’t put food on the table I have to snap out of it and get my head back into what actually does.  I find on those days it’s best to medicate the problem a little with my trusty iPod.  With my iPod I can do something for myself while still working and lately I have been on an audiobook and podcast kick.

iPods treat audiobooks and podcasts differently than they do music files.  For one you can leave a podcast, listen to something else, and later pick up where you left off.  An iPod won’t do this when you are listening to music.  Furthermore an audiobook can have chapter markings making it easier to find a chapter in a longer book.

iTunes makes it easy to create a music file for the iPod.  You can insert a CD and iTunes will import it at the push of a button.  Creating a proper audiobook however, is impossible with iTunes.  Oh sure you could import a whole CD as a single file but what if your audiobook covers multiple CDs?

I’ve had this problem for a while now.  My initial solution involved importing each track and numbering them in a way that they would line up in a playlist.  This works, but it’s annoying.  I can play the book but if I stop I have to remember which chapter I left off with.  If I don’t go back to the book for a couple of days I forget where I am and have to start over.  A couple of weeks ago I finally had enough and went searching for a better solution.

The solution to my problem came from a program called Chapter and Verse by lodensoftware.com.  The program will take a list of AAC (.m4a) files and convert them into one single audiobook (.m4b).  This makes creating an audiobook from a CD very easy.  Simply import the CD using iTunes in the AAC format and then use Chapter and Verse to make the conversion.

Chapter and Verse is not perfect however.  If files are not in the AAC format it will have to convert them into this format before creating the audiobook.  If you use a free audiobook solution such as librivox.org this means it’ll have to convert each file before combining them into one.  Not too big of a deal, just plan a little extra time when creating an audiobook from a set of mp3s.