Version issues that plague me

I should begin by apologising once again for leaving the blog alone for a long time – work’s been crazy and it’s been tough finding the time to write anything. My main preoccupation at work recently has been building a data entry application over a fairly tight timeframe, and the process has thrown into stark contrast a lesson in Excel development I’ve had to learn the hard way.

The vast majority of the users in our business use Excel 97, while I use Excel 2007. There’s a lot of differences between the versions, both in terms of the Excel object model and VBA, so what this means for me is that if I build something for external use, it should be frequently and thoroughly tested in 97 throughout the process. I’ve had to have this lesson pounded into me several times by the cruel, hard fist of experience – there’s nothing like the sinking feeling you get when hours of work in a later version basically have to be tossed and an alternative approach found when you discover that your code won’t compile in 97. Or that a function you’re relying on to work a certain way just didn’t do that in 97.

There are some differences which are easy to work around. For example, if you use functions like Split, Join or Replace (which are not available in VBA in Excel 97), Daily Dose of Excel has published a set of VBA replacements which will run in 97: Split97, Join97 and Replace97. I’ve learnt to be aware of these sorts of issues. I’ve also learnt to avoid declaring variables as VBA enumeration members (such as vbMsgBoxResult or vbDayOfWeek) or defining custom enumerations. Handy as these may be, they will cause compile errors in 97. I usually declare these variables as Long, or substitute declaring a bunch of constants for a custom enumeration.

However, there’s always another surprise waiting to be discovered. In my current project I manage the user interface through a userform, and included in the project a user interface support class. This was a technique I learnt about in Professional Excel Development, where the authors suggest this as a way to separate the user interface layer from the business logic layer. Basically, the class acts as a layer of abstraction sitting between the two – it knows about how business logic is implemented, which means that the form doesn’t need to. The form lets the class object know when it needs new data, and the class object in turn calls the function from the business logic layer required to get the data, and passes it back to the form by way of a public event which the form is listening for. In my case I had a combobox cboJudicialOfficers which needs to be repopulated whenever a new judicial officer type is selected elsewhere in the form.

Here’s the code that initially went in the CUserInterface class:

Public Event GetJudicialOfficers(ByRef vJOList As Variant)

Public Sub JOTypeSelected(ByVal sType As String)
' Pick up change in Judicial Officer type from form
Dim vOfficers As Variant

' Get the list of JOs from the return
vOfficers = vUpdateJOList(sType)

    ' Pass the list back to the form
RaiseEvent GetJudicialOfficers(vOfficers)

End Sub

In the form, I created an instance of the class and an event handler to retrieve the data required:

Dim WithEvents mclsUIS as CUserInterface

Private Sub mclsUIS_GetJudicialOfficers(ByRef vJOList As Variant)
cboJudicialOfficers.List = vJOList
End Sub

When the new type is selected in the form, the class procedure JOTypeSelected is called, and the event handler listens for the list coming back in from the CUserInterface instance. I quite like the neat separation this affords the application – the form never has to query the business logic layer directly, so its own implementation is not at all tied to the implementation of the business logic. Of course, the only problem with this approach is that Excel 97 won’t have a bar of it. It’s perfectly ok to create event handlers for built-in objects like worksheets, pivot tables or the application itself, but VBA in 97 will not understand when a user-defined object starts using terms like Public Event and RaiseEvent. This left me with the option of getting the form to call the required functions directly, which I don’t like but at least it will compile (and run).

3 thoughts on “Version issues that plague me

  1. jonpeltier says:

    Don’t ignore the recommended practice of developing in the earliest version you have to support. Test in all versions, but do the actual development work in the earliest. This ensures that any problems are discovered at the soonest possible time during development.

    • geoffness says:

      That’s good advice Jon, and up until recently I was able to follow it (whether I always did follow it is another story). When I had 2007 installed on my work machine I had to have all other versions of Excel/Word/Powerpoint removed – which at that stage was 97 and 2003. I was not happy at all with that, but the rollout coordinator was immovable on that point. Once the rollout has been completed (hopefully within the next couple of months) this will be a non-issue as everyone will be using 2007, but until then I literally have to swap machines in order to test anything.

      • jonpeltier says:

        The rollout coordinator did you a disservice by not allowing you to properly support your users.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s