VB Access Excel

I’ve been helping another dev hit a deadline last week.

I fleshed out a VB6 exe form from a prototype he had done. I wrote a class for the client dll to access local and server based .mdbs.

I used Access’s QBE grid to generate the SQL, then stuck it in the class (the db is rebuilt frequently so we decided to leave the SQL in the app rather than the db). I wonder why I feel bad about not keeping the queries in the db? its only like LINQ after all.

I then dropped the recordset into Excel and created a half decent graph. Getting the recordset in took less than 5 minutes, coding the graph took much longer. Normally I just set one up in advance and populate the worksheet then point the graph at the data. To save install hassles in this case I decided to code it from scratch. For me that means hours of tidying up macro recorder code. (which is still better than manually coding it).

I did finally manage to get rid of all those select/selections and get a reasonable chart. But did manage to pick up a dangling reference problem. First time through everything worked great, next time all sorts of things crashed. I’ve had this problem before, although difficult to find they are pretty obvious once found.

In simple terms when automating Excel all objects need to be fully qualified, I’d managed to let one ‘Range(..)’ slip through from the recorder. I changed it to ‘ws.Range(..)’ and was back in business. The full story is here:


I then emailed my 3 bits of code to my fellow dev , he spent less than 5 mins integrating it and everything was working.

This mini project really brought home to me (again) just how powerful and flexible MS Office is. And also how easy it is to dip in and out of VB6. Another thing was how easy the integration was, I have always found multiple devs in Excel to be a real pain. (has anyone found a decent way to resolve this?)

I saw a post elsewhere that Outlook is the big reason people stay with MS Office, personally though I am convinced its VBA and the easy interoperation of all these technologies. What do you think?



8 Responses to “VB Access Excel”

  1. Harlan Grove Says:

    The big reason most business users stick with MSO is because that’s what they’re given. The majority of MSO users have no choice in the matter.

    The big reasons why IT shops stick with it have been existing ‘code base’ and retraining cost avoidance. MSO 2007 is going to put serious pressure on the second. As for the first, I have no idea what the proportion of macro-heavy Word documents and PPT presentations are of their respective total pools, but I suspect it’s LOTS LOWER than for Excel. As for Outlook, it only one part of the MSFT groupware platform. There’s also Exchange. From what I’ve seen, IT shops that spent big money on Lotus Notes in the 1980’s and 1990’s are still Notes shops with few if any Outlook clients. But they all run MSO. Clearly Outlook isn’t keeping them with MSO.

    That leaves home/hobby MSO users, but I suspect heavy VBA use is rather rare with them.

    If I had to guess, people stick with anything because of inertia and laziness.

  2. Will Riley Says:

    Like Harlan says, Outlook has nothing to do with why, for example, my company run Office. As Lotus Notes users (OK, only for a short while longer now) we had no interest in Outlook as a tool – in fact we actively discriminated against it for a while.

    Office, for us is/was all about Excel and the abiltiy for a compnay with limtied IT budget to leverage excellent MIS from source databases with a little bit of VBA.

    The new software platform we are rolling out consists of Exchange/Office (specifically Word/Sharepoint/Excel Services etc) – I’m not sure in 2 years time if I will be able to repeat the above para with the same conviction, given the interactivity we will inevitable come to rely on between the above software combination.

  3. Jon Peltier Says:

    I agree with Harlan and Will that Outlook is not a major factor in continued MSO use.

    But I will also agree with Simon that the easy interoperation of Excel and other apps with VBA is slowing the adoption of VSTO and .Net. These newer managed code technologies come with much higher overhead and greater development costs than their VBA equivalents.

  4. Ken Puls Says:

    Very well said, Jon.

    As far as Outlook goes, we use Novell’s Groupwise at work. While I have Outlook at home, it is essentially just a backup of my gmail inbox. Every time I accidentally open Oultook, it will download a copy of each of my gmail messages, just in case. I would have little use for Outlook at all if it weren’t for the fact that I can automate it to send a nice rich email and attach my Excel workbooks to it. (That and the fact that client’s sometimes want a job done in Outlook.)

    The thing keeping my company on MS Office is VBA, no question. I had a consutant call me the other day and tell me that I could save a bundle by switching to OO. (I love consultants who make a blanket statement like that without asking anything about your current business practices. )

    I think I derailed the poor guy when I asked him to estimate how much it would cost to port my VBA solutions to OO. (Based on what I’ve built over the past 5 years, my quick estimate is between 3 and 5 times the licensing costs.) Whatever it is, it would be a lot more than the software savings, and for what benefit? Automation in a different program?

  5. Dennis Wallentin Says:

    >>These newer managed code technologies come with much higher >>overhead and greater development costs than their VBA equivalents.

    I can agree on higher overhead in terms of ‘layers’ for .NET/VSTO’s solutions. The same was said when we ported us from DOS to Windows so it seems to be ‘true and valid’ for a limited ‘transition’ time.

    If a corporate move from pure VBA solutions to pure VSTO solutions then the development cost may (probably) increase. However, under other circumstances it’s not necessary that the costs actually will increase.

    Simon – You should by now have learned to release *all* variables in use ;-)

    Kind regards,

  6. Simon Says:

    Most of my clients use Lotus Notes, I can’t remember ever using outlook this decade. So yeah I’m pretty convinced that Excel/VBA is the lock-in.

    I’ll post about migration costs, thats an interesting point.

    The other big point slowing VSTO and .net adoption is its not included in the apps. VBA is free, it there, it pretty much always works. And anyone can chip in and have a go. That may have some negatives for sure, but its great for lock-in.

    Dennis – I’m normally pretty careful. If this is the only ‘error’ (it was fixed before test so it depends on your definition), I’ll be delighted. Believe me, a little Excel crash is nothing to some of my blunders! (there could be a post or two in that!)

  7. Marcus Says:

    From what I’ve experienced dealing primarily with business users, any change in development cost between VBA and VSTO is secondary to the loss of control as the IT department is more likely to become involved.

    Many business users like the fact that they can circumvent the time and dollars costs incurred in their interactions with the IT dept. VBA provides them immediately gratification and a sense of control. Having said that, I’ll also observe that many of these solutions are far better off in a more robust or secure environment.

    As for Outlook – I don’t thinks it’s a compelling reason for any company to stay with MSO per se. Most organisations I deal with use Outlook. Funny, I’ve only seen one Lotus Notes installation this decade.

    Whether the attraction to MSO is Excel based probably depends on the nature of the business. Many legal firms are just as dependent on Word as banks are on Excel.

  8. Ross Says:

    Talking about VSTO. I see that VSTA is getting a bit of a push now form MS. I’ve not actually been able to view any of the videos that have come out (hell this is MS we’re talking about, you can’t expect to go to a web site a just actually watch a f##king video, can you!).
    I don’t know much about VSTA, but I can’t see where it might fit, I guess there going to have to “cripple” it in some way to keep VSTO going? – I look forward to finding out more about it.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

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

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: