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.

Solving Problems

There's nothing like the sense of accomplishment you get from solving a problem. When you're new, it's frequent that you get to solve problems. Well, maybe that's inaccurate everything you touch is a problem because you've never seen it before, you haven't learned enough yet.

Scratch, scratch, scratch your head sometimes for hours or days. Then, you get it. You've learned just enough to solve the problem and you feel accomplishment because:

1. You solved a problem
2. You learned something

Makes for a good day.

Tooting Your Horn

Software Developers are notorious introverts. There are very few who love to tell the world how good they really are at things. I'm improving, but it's not my default demeanor. It makes me uncomfortable to take credit, or to announce when I do something well. Sidebar - I'm very practiced at taking blame. I do it pretty smoothly.

People making hiring decisions, handing out assignments and deciding who's next to run the group are usually looking for great people to do those jobs. The greater the better. They are going to take the greatest one they can get (most of the time).

If they have to interrogate you to find out your level of greatness, they might take a pass. After all, the next guy in line is making it easy to find out how great he is. You don't want to bludgeon anyone over the head with self-serving arrogance, but it shouldn't be a secret either.

I try to make it part of my day to do something great, something unexpected, better than expected. The next step I have to master is making sure the right people find out about it.

Favorite Workplace Quote

I heard a quote from a friend of mine the other day. Not sure who originated it. Maybe his boss, maybe it's someone famous and this is a well known one.

They keep fuckin' it up, so we have to fuck it back down

Besides being hilarious maxim it's an organizational imperative that no matter who fucked it up, in the end, it has to be fucked back down.

The Hump

It's easy to go in early and home late when things are going your way. You're learning, your producing, progress is made. It's exactly the opposite that makes you a winner. Go in early and home late when things aren't the greatest. You should be there figuring out how to change it.

VMWare Remote Plugin and IE8

Internet Explorer 8 on a 64 bit Windows 7 doesn't get along well with VMWare Server. If you seeing this message:

Remote console plugin is not installed or could not be found

No matter how many times you install and remove the plugin, then you're likely problem is that you're using 64 bit Internet Explorer 8 and installing 32 bit plugin.

As far as I can tell, there is no 64 bit plugin with vmware server, luckily you can run 32 bit Internet Explorer 8 on Windows 7 64 bit. It should be installed. You might have to look through the start menu.

At least, until the plugin is usable with Firefox or Chrome.

Fiefdoms And Their Leaders

Seems like there's a natural tendency in workplaces to cloister, close, confine, protect. That's the default mode for many workers, their supervisors and management. I like to think of this as a little medieval fiefdoms that are carved out of an organization. I think the idea is that you want to keep solitary that which makes you unique and at the same time control how the organization judges you and your fiefdom (sometimes fiefdoms are single person entities).

When you get good at something, it's natural to try and protect what you know and use it to your full advantage. For a worker, I think the attitude is a little forgivable. Their job is not to maximize department output, decrease inefficiency, increase output. But what about managers that have this attitude? Sometimes managers are just longer term employees that got promoted up. I guess it's possible for them to bring their attitudes with them into the more senior roles.

The leaders are also not beyond the scrutiny of their managers in turn. So if a persons natural tendency is to spin, massage, or deflect the information used to judge their performance, likewise they will protect their departments or divisions in the same way.

The problem is that in this environment, creativity is stifled, fear is at maximum warp factor, mistakes are not forgiven, so everyone is really wearing professional handcuffs. The mantra - in the fiefdom, there is safety, let's all retreat to the fiefdom.

Wouldn't it be great if there was a way to change attitudes instantly from petty fief to benevolent and potent leader? Well, there isn't one; stop dreaming. It's hard work to change your attitude. A daily grind fighting your own worst enemy. But you have to do it if you want to get off the fiefdom and do something fulfilling.

Be Generous

My favourite blog is Seth Godin. He's marvelously insightful and often when I read his blog I think he's writing a letter directly to me.

One of my favourite sethisms is that you should be generous with your ideas and knowledge. You should be giving that away for free. It does no good to pinch every piece of information you have with both hands hoping to hold on to it and through that, make yourself indispensable.

That's not how indispensable works.

The great thing about generous is that once you've done something once, you tell others, then they can do it, and you can figure out something new. That's the part of working that I love. The new and exciting, not the brainless and repetitive.

Make the Switch

I spent 8 years developing software at small startups. Mostly embedded software, sometimes Windows tools for the embedded systems. About 2 years ago I switched to a big company that makes big software for big healthcare clients.

It was a huge change in daily routine.

Firstly, I went from being one of two developers on a project to being one of more than a dozen.

Secondly,  I went from being a senior guy who pushed the development agenda, and was confident about what I was creating to being a meek churchmouse. Although, this is probably more related to the big change from embedded to Windows/Unix world.

Thirdly, I found out, when there's that many people working on a project, everyone has a different agenda. More accurately, as the number of people involved in a project goes up, the chances of everyone getting along goes down.

I found it shocking when not everyone worked at creating art in their work. Some people couldn't wait to go home at the end of the day. This was so different from the world I came from, and the world that I wanted to bring to my new big company.

The truth is, they're everywhere. But don't let them get you down (they're going to try their hardest).