Macros for the Masses

The past few days I've been automating some chart creation activities using Word and Excel VBA. It was kinda cool. I wrote the macro in Word and added a reference to Excel 12 library in the Word's VBA editor (Alt-F11 -> Tools -> References -> Microsoft Excel 12.0 Object Library).

I wrote some nifty Word -> Excel interop to create the charts in excel, then copy/paste them into Word. Data manipulation in Word was not a priority so paste as picture was the solution rather than creating Word Graphs from the data.

Use Project, not Normal

After that, the cool part ended though. I needed the macros to work with Word 2003, but wrote them with Word 2007 - a major boner. Not only that, I found that the default macro location for Word is in Normal.dot and not in the document itself. It makes sense if you are writing macros for only your own benefit, but transferring macros requires them to be located within the document.

To solve the normal.dot problem, I just copy and pasted the text from the default location into the proper location.

But that was the easy part. To fix the other problem, I had to set up a Virtual Machine using VMWare Server and install Microsoft Office 2003. Then I had to recreate the reference to Microsoft Excel 11.0 Object Library.

That solves it, and it looks like the macro works when run in Microsoft Office 2007. However, when I looked at the referenced libraries from Word 2007, it appeared that the reference was upgraded to Excel 12. Safe to assume that if I make a change and save the document, the macros won't work in 2003 anymore because the reference will be wrong. Ugh.

So I need to maintain a purpose built VM to develop macros for office 2003. Gasp. Isn't there an easier way?

Optimistic Concurrency with C#

There's lots of ways to get Optimistic Concurrency with your database calls. I like row versions to guarantee exclusivity. You could use a database lock, but that's expensive and shouldn't be necessary where data contention is low. Don't use update times since the system time is rather course.

With this class, we expect that most of the time the optimistic lock will work. However, we will be relying on other data access clients to 'obey the rules'.

This data access class accesses only one table and relies on the version to change when a row modification is made. There's only one modification made here, but this simple class is easily expanded.

The lazy class ConcurrencyObject is just used to shuffle data around. It hides implementation from everyone other than the assembly it's created in. Useful if you separate the data access agents from the other parts of your code with libraries.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

namespace OcDac
{
    public class OCDataAgent
    {
        private readonly SqlConnection myConnection;
        private readonly SqlCommand selectPendingObjectCommand;
        private readonly SqlCommand updatePendingObjectCommand;
        public OCDataAgent()
        {
            myConnection = new SqlConnection();
            // build the connection string
            SqlConnectionStringBuilder bu = new SqlConnectionStringBuilder();
            bu.DataSource = "tcp:10.0.0.114, 1370";
            bu.InitialCatalog = "ocSample";
            bu.IntegratedSecurity = false; // Sql Server Authentification
            bu.UserID = "sa";
            bu.Password = "youwish";
            myConnection.ConnectionString = bu.ConnectionString;

            // This stmt is used to get the ID and Version of one object that's pending
            selectPendingObjectCommand = new SqlCommand { Connection = myConnection };
            selectPendingObjectCommand.CommandText = "SELECT TOP 1 ID, VERSION FROM OnlyTable WHERE STATUS='PENDING'";

            // Claim this object if it's row number hasn't been modified and has the correct ID
            updatePendingObjectCommand = new SqlCommand { Connection = myConnection };
            updatePendingObjectCommand.CommandText = "UPDATE OnlyTable SET STATUS='IN_PROCESS', VERSION=@new_version WHERE ID=@id AND VERSION=@version";
            updatePendingObjectCommand.Parameters.Add("@id", System.Data.SqlDbType.BigInt);
            updatePendingObjectCommand.Parameters.Add("@version", System.Data.SqlDbType.BigInt);
            updatePendingObjectCommand.Parameters.Add("@new_version", System.Data.SqlDbType.BigInt);

        }

        public void LockPendingObject(ConcurrencyObject co)
        {
            if (myConnection.State == System.Data.ConnectionState.Closed)
                OpenConnection();

            updatePendingObjectCommand.Parameters[0].Value = co.Id;
            updatePendingObjectCommand.Parameters[1].Value = co.Version;            
            updatePendingObjectCommand.Parameters[2].Value = co.Id + 1;
            
            // If the claim succeeded, then we can update our co otherwise exception
            var rowsAffected = updatePendingObjectCommand.ExecuteNonQuery();
            if (rowsAffected != 0)
                co.Id++;
            else 
                throw new Exception("Could not lock object using OC");
        }

        public ConcurrencyObject GetPendingObject()
        {
            if (myConnection.State == System.Data.ConnectionState.Closed)
                OpenConnection();
            var co = new ConcurrencyObject();

            var reader = selectPendingObjectCommand.ExecuteReader();

            while (reader.Read())
            {
                co.Id = reader.GetInt64(0);
                co.Version = reader.GetInt64(1);
            }
            reader.Close();
            return co;
        }

        private void OpenConnection()
        {
            myConnection.Open();
        }
        
    }    

    public class ConcurrencyObject 
    {
        internal Int64 Version { get; set; }
        internal Int64 Id { get; set; }
    }
}

Coding is Art

Programmers are sometimes a difficult bunch to work with. I'm no better.

We fancy ourselves as uber-logical like Spock and uber-analytical like Data from Start Trek and Star Trek TNG respectively (we also revel in a little nerdification mixed in for good measure).

Truth be told we are much more like van Gogh than we care to admit. Idealistic, anxious, frustrated, and misunderstood.

But honestly, when you think of individuals (real or imagined) with boat loads of talent who comes to mind? Spock or van Gogh? The tragedy of the talented?

VMWare Server Saves Me Big Bucks

I love VMWare.

My main problem is too many ideas. Not a problem on it's own, but I also find I must take the ideas to implementation. At least a little way to see how well they'll work out.

One problem that I ran into before I discovered VMWare Server was the constant need for more boxes. I always needed experimentation machines. My home office was filled with partly assembled computers that hosted my experiments. It's not that a single machine doesn't have the horsepower, more that during discovery, I hate being careful of existing folders, databases and so on. I'd rather rip and tear. That means empty boxes for every idea.

VMWare Running Windows 2003 Server on my Windows 7 laptop

Another issue that I constantly ran into before discovering virtualization with VMWare was constant re-installation of OS's. Sometimes you want to get a clean OS install so all the dependencies are known when installing software packages. With snapshots on VMWare server, you can install an OS once, then back it up forever. When ready for a new experiment, just dig out the snapshot rather than install from DVD again.

Not only does VMWare easily deal with these problems, but also adds these great benefits.

Unlike most virtualization platforms, the VM's created in VMWare Server are accessible from anywhere on the network through VMWare console. The VM's run on the VMWare server box, but the UI is presented on the client box. I can run VM's on powerful workstations and access them with my ancient laptop and get good performance while sitting on my living room couch. Don't laugh, it happens more often than you think!

Lastly, the VM's are mobile. I can run VMWare Server on my laptop and take my ideas on the road to show off once they are partially incubated. Not only that, but I can move the VM's around to different VMWare Servers without issue. That's important in case a box goes down.

Best of all, it's free. Thanks VMWare.

Principles of Uncertainty

Uncertainty the only certainty in software development projects

I was conversing with some colleagues today and the subject of 'high level' estimates came up. We were snidely commenting (as is our prerogative) on the tendency of our superiors to ask for these and our discomfort with providing them.

Programmers have an intense dislike of getting things wrong. It's kind of paralyzing because we never want to discuss anything in depth that we're not 100% certain of.

It's comical to watch a smart developer give a high level time or complexity estimate to management. He'll be aloof and careful not to say anything incriminating. Probably because it feels like an interrogation. Usually his interrogators will press for something concrete to write down on a piece of paper that can be used in planning, but it's not easy to get this from the developer.

I think the problem is that the kind of people that like software development and choose it as a career are naturally petrified of being wrong. This fear is compounded by the belief that for any problem there may be a number of ways to solve it, but we want to find the perfect one.

We also have a distrust for 'high level' anything and prefer not to be part of that whole deal. We'd much sooner spend weeks researching and analyzing the problem until we understand every detail, then deliver the answer...

Be ready in a couple days.

How Many Apartments for a UI Thread

With WPF, drawing and binding can be time consuming for large UI projects. If updating the UI takes a while, you need a second UI thread to display progress, but if you try to start a progress window from a worker thread, the framework will complain that the thread has too many apartments (I'm paraphrasing of course)!

The trick is to initialize the thread STA, start the thread message pump and use a dispatcher to send code to the thread for execution.

Use the following code to roll a progress indicator. Not shown is the ProgressDialog class which for me is a XAML window that exposes a progress bar (named bar) and a label (named label)

class ProgressIndicator
    {
        Thread t;
        ProgressDialog progdialog;
        bool running = false;
        public void Start()
        {        
            t = new Thread(ThreadFunc);
            t.SetApartmentState(ApartmentState.STA); // set the thread to STA
            t.Start();
            while (!running) ; // block until the thread is ready to accept updates
        }

        public void UpdateProgress(double Progress, String label)
        {
            progdialog.Dispatcher.BeginInvoke(
                (System.Windows.Forms.MethodInvoker)delegate() { progdialog.bar.Value = Progress; progdialog.label.Content = label; }
                                        );
        }

        public void Stop()
        {
            try
            {
                progdialog.Dispatcher.BeginInvoke(
                    (System.Windows.Forms.MethodInvoker)delegate() { progdialog.Close(); }
                                            );
                t.Abort();
                t.Join();
            }
            catch (Exception)
            {
            }
            finally
            {
                t = null;
            }
        }

        protected void ThreadFunc()
        {
            progdialog = new ProgressDialog();
            progdialog.Show();
            progdialog.Closed += (sender2, e2) =>
                progdialog.Dispatcher.InvokeShutdown();  // if main window closes...

            progdialog.bar.Maximum = 1;   // I always like dealing with 0 -> 1 progress
            running = true;
            System.Windows.Threading.Dispatcher.Run();  // start the message pump
            
        }
    }

Copy and paste the code and implement the ProgressDialog class with a progress bar named bar and a label named label and this should let you start and update a progress bar in a second UI thread.

Domain upgrade and switch to blogger

Well, I bought the robertmcintosh.ca domain and moved the blog to hosted blogger rather than self hosted wordpress.
I use wordpress for waterloobikes.ca blog, but overall it's not impressive. They insert ads where Google leaves that up to the blogger. Domain pointing is free at blogger, but paid at Wordpress.
Best news is I've got the domain. It's a huge move for my image as a contractor. No more org tld's and I can even use an email address for free from the registrar.
I think things are moving along...

Prepare to Program Series I

The first key to writing guud software is getting a proper sit going. You have to be comfortable if you're sitting at the terminal for 12-18 hours per day doing what you love. Some days I want to mount my keyboard and monitors above my bed so I can get a real guud sit going.


I don't because I think that might be crossing a line into domestic impropriety.

I love to write software especially when creating something new (fixing bugs is a regrettable but necessary exercise). But nothing gets in the way like an uncomfortable work zone. Take your home computer workstation (if you have one). Could you sit there all day?

I feel highly misunderstood when I get into a real good comfortable spot that feels just right. I'm often nearly laying down in my chair. I have my head back, looking down my nose at the monitors. Were it not for my fingers moving, you'd think I was sleeping with my eyes open.

Inevitably, not recognizing that I am in fact in the programmers zone, interrupts with - "are we keeping you awake". I like to respond - "you are now".

Continuous Integration

When I was a developer at a small company that sold products which happened to include software we did a lot of things different. The main reason for this is that software was a part of what we were producing, but not the main part. Elevators, electric dune buggies, CNC machines all have significant non-software development components.

The delta's show in how much attention is paid to the processes surrounding the development of each component. For example, one of our favourite ways to test software that was developed for CNC machines was to load the software onto a CNC machine and press buttons and use any new software that was implemented. The goal was to test the software for correctness and bugs. To us, this was a reasonable facsimile to a production environment.

I now work on a large team of software developers who are professionals at producing software. It's very time consuming or even impossible to test new software IRL because the production environment is not easily simulated. Continuous integration and unit testing allows me to develop software and be reasonably secure how it will perform in the field without having to 'press the buttons'.

Of course, running unit tests is not foreign to the previous shops in which I've practiced my craft. The problem with unit tests was almost always the same. The tests would be run during the development of a feature, then never used again. Instead, they might never see the inside of a version control system. They would be lost on next computer upgrade. It was hard to justify so much time writing unit tests that would only be used to aid the developer implement.

With Continuous Integration, it's different. The build and integrate process is continuous (imagine that). Each build is done on a dedicated system. All unit tests are automatically run and if everything goes ok, the build is deployed to testing fixtures which run continuously.

This process makes it more difficult for new code to break old code (as long as it's adequately tested). I wish I knew that before I wasted all those hours tracking down bugs by trying to manually set them up over and over with the user interface.