May 10
24

You Can Do Stupid Stuff With VBA

As I graduated college I must admit being very ignorant about how much 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 my first job.  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.

Macro Flower ShotWhen I started at my second job 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.

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

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.

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

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.  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 – 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.

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.

Tags:
Posted in Programming by mike.hanson. No Comments

May 10
16

Creating an Audiobook for the iPod

Ahh programming for a living…  Most days it’s easy get into a rhythm and looking up for the first time find that it’s already time to go.  But every so often there are those days that just won’t quit.  On those long days I find it’s best to plug in my iPod and get cranking on a problem.  A little background noise can do wonders and lately I have been on an audiobook kick.

iPod

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 - 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.

Creating an audiobook is very easy in Chapter and Verse.  First download and install the application.  Then fire it up and follow the instructions below.

  1. When Chapter and Verse loads it opens to an empty project.  Each project will contain all of the chapters in each audiobook you create.
  2. Each file you import becomes a new chapter in your audiobook.  Click the “Add Files” button to begin adding audio files to your 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.
  3. 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 converstion, 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.
  4. Whenever a new chapter is added Chapter and Verse will first validate the files, then generate some chapter data, and finally merge in your tracks.  If you have a lot of files to add this can become a little annoying.  To turn it off briefly click the “Autobuild On” button to change it to “Autobuild Off”.
  5. Click on the “Chapters” tab to change the chapter name.  For most of my audiobooks I like to choose the <Filename #> option.
  6. Click on the “Metadata” tab and change the file information if you would like.
  7. 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.

That’s pretty much as easy as it gets!

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.

Tags:
Posted in Software by mike.hanson. No Comments

Apr 10
10

Starting that Windows Forms .NET Application

One of the pleasures that I look forward to are those “Duh” moments.  Those moments signal that I am still learning new things and therefore not falling behind the ever moving technology curve.  Not yet at least.

This week at work I have been redesigning the flow of our internal application.  When Sally gets into work on Monday, after a long weekend of partying, the first thing she does is launch this internal application.  It then opens a login form which disappears when she enters her credentials.  Immediately, a secondary screen shows up asking which set of data she would like to work with.  She chooses what she’s been assigned to work on and waits for the data to load in a third screen.

The code required to launch such an application is very straight forward.  The login form needs to be displayed and when it is dismissed the secondary form can be shown.  The third screen can be launched off of the second and will stay alive as long as the second is open.

Program.cs

///
/// Entry point for the test application
///
static class Program
{
    ///
    /// The main entry point for the application.
    ///
    [STAThread]
    static void Main()
    {
        Application.EnableVisualStyles();
        Application.SetCompatibleTextRenderingDefault(false);
 
        // Ask the user to login
        Login.LoginScreen login = new Login.LoginScreen();
        Application.Run(login);
 
        if (login.DialogResult == DialogResult.OK)
        {
            // Run the main application
            Application.Run(new NewWindowApp.EntryPoint().Context);
        }
    }
}

Take a look at lines 17 and 22 in the code sample from Program.cs.  The calls to Application.Run(Form) start a message loop on the current thread enabling the form to receive Windows messages.  Application.Run(Form) essentially says to Windows, “Here take this form and show it to the user.”  The form that is supplied can launch other child forms but when it dies so does the whole application.

My goal this week has been to make Sally’s job a little easier by removing the secondary screen and building its functionality into the third screen.  Now normally this would be as simple as replacing line ten with Application.Run(new ThirdForm());.  However that will not work in this case because the third form has a very useful “New Window” button.

Oh, it will work just fine for a while.  Sally can click the button leaving her with two views of the application.  However the message loop is only hooked up to the first form and if it is closed both forms will die off.

I wracked by brain for a while trying to work my way around this limitation.  I could create an invisible secondary form and then fired the third from that.  But that feels like a kludge and I hate kludges.  Nothing I could think of was an optimal solution and this simple task become very frustrating.

It seems that the deeper the frustration the larger the slap to the forehead when the “Duh” moment hits.  The solution to my problem is in an overload to the Application.Run method that takes an ApplicationContext property.  In fact the documentation at MSDN (http://msdn.microsoft.com/en-us/library/ms157901.aspx) shows a partial solution to my problem.

Instead of supplying Windows with a Form to display to the user I now supply a custom ApplicationContext object.  This custom context can manage the new window call and keep the application alive no mater which form is closed.

Context.cs

///
/// Does the work of firing off new application windows.
///
internal class Context : ApplicationContext
{
    // The number of windows currently open
    private int mWindowCount;
 
    ///
    /// Initializes a new instance of the  class.
    ///
    public Context()
    {
        this.NewWindow();
    }
 
    ///
    /// Creates and shows a new window.
    ///
    public void NewWindow()
    {
        NewWindow window = new NewWindow(this);
 
        window.FormClosed += new FormClosedEventHandler(window_FormClosed);
 
        ++this.mWindowCount;
 
        window.Show();
    }
 
    // Close out the application when all windows have been exited
    private void window_FormClosed(object sender, FormClosedEventArgs e)
    {
        --this.mWindowCount;
 
        if (this.mWindowCount &lt;= 0)
        {
            this.ExitThread();
        }
    }
}

It’s important to note that this object knows how many windows there are.  Also note that as each window is created by NewWindow() a listener is added to the FormClosed event.  These two elements allow the context to know when the last form is closed and therefore when to finally exit.

There you have it, a simple solution to a simple problem.   Though, most of the simple problems are bound to cause most of those “Duh” moments.

Full source code for example project.

/// <summary>

/// Does the work of firing off new application windows.

/// </summary>

internal class Context : ApplicationContext

{

// The number of windows currently open

private int mWindowCount;

/// <summary>

/// Initializes a new instance of the <see cref=”Context”/> class.

/// </summary>

public Context()

{

this.NewWindow();

}

/// <summary>

/// Creates and shows a new window.

/// </summary>

public void NewWindow()

{

NewWindow window = new NewWindow(this);

window.FormClosed += new FormClosedEventHandler(window_FormClosed);

++this.mWindowCount;

window.Show();

}

// Close out the application when all windows have been exited

private void window_FormClosed(object sender, FormClosedEventArgs e)

{

–this.mWindowCount;

if (this.mWindowCount <= 0)

{

this.ExitThread();

}

}

}

Tags: ,
Posted in Programming by mike.hanson. No Comments