Category: Programming

Categories
Programming

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.

Categories
Programming

Starting a Windows Forms .NET Application

This week at work I have been redesigning the flow of our internal application. When Sally gets into work the first thing she does is launch this internal application. The application opens to a login form which disappears when she enters her credentials. A secondary screen then 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());
        }
    }
}

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.

The solution to this problem is in an overload to the Application.Run method that takes an ApplicationContext property. In fact the documentation at MSDN 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)
    {
        NewWindow window = sender as NewWindow;            
        if (window != null)
        {
            window.FormClosed -= this.window_FormClosed;
        }

        --this.mWindowCount;

        if (this.mWindowCount <= 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.

Full source code for the project.