January 22

Why I don’t like RUN buttons

7  comments

When I first started this blog, I thought to myself: VBA is wonderful and amazing—there’s nothing you can’t do with it. I still love VBA, don’t get me wrong, but my relationship with the scripting language has changed considerably over the last several years. On a recent Excel.TV episode, Rick rightly pointed out the irony in my relationship with VBA solutions. “VBA appears in the title of you blog!” he said. 

My issue isn’t with VBA itself, but rather with how we use it. This won’t be a cliché article complaining about how old VBA is or why I think another language is so much better. Blech. But I do want to discuss how I think it should be located in development and provide you some food for thought in this vein.

image

The “RUN” button perhaps best encapsulates my relationship with VBA. You know what the RUN button is because you’ve created it before. It’s the button that updates a worksheet’s values, creates a new report, inputs data into a model—and so on. It’s the button you press to begin automation. In a sense, it’s everything I used to think VBA was about.

But having done this for many years, I now hate RUN buttons. To some extent, they can be unavoidable given the underlying nature of the problem. But they are often more avoidable than we think. As followers of my blog know, I am keen to place as much functionality on the spreadsheet as possible. If I can do it with Excel and formulas, then I avoid needless VBA. This makes for tighter faster work, that’s easily scalable. (In my book, I call the concept “reusable components.”)

And RUN buttons are often filled with needless VBA. We can use formulas to automatically update data values so that what we’re looking at is always live data. Form controls can help us with this. RUN buttons are often used to create copies of new report tabs, where the layout of a template tab is copied, filled with the latest information, and then displayed. But wouldn’t one display tab that could use formulas or Power Pivot suffice? The RUN button symbolizes processes that are far more complex than they need be.

Look, I’m just complaining about a symbol of a true underlying problem. We make things more complex than they need to be. Our work should never be more complex than the underlying model.

But agree or disagree, we should think more about how we develop. Like I said, RUN buttons are sometimes unavoidable. But if you can limit the amount of code in your work, replacing iterative volatile actions with faster formulas, that’s something to shoot for. At least I think so.

Jordan Goldmeier
Latest posts by Jordan Goldmeier (see all)

Tags

optionexplicitvba


You may also like

April 9, 2011

How to Create a Rollover Effect in Excel: Execute a Macro When Your Mouse is over a Cell

October 26, 2014

Monte Carlo Simulation Formula in Excel – Tutorial and Download

September 17, 2012

The Excel Rollover Mini FAQ
    • I didn’t supply an example by design. I know my personality: if I attempted to create an example, it would subsume me and I wouldn’t complete this blog post. In a certain sense, my screed against complexity stems from struggling with it internally. I’m a complex character, if you will.
      So, without going into specifics, I’ll mention this. A while back, I was hired to correct the work of someone else. Basically, the client wanted a front-end input that would make updates to a project schedule that was on another tab. It could have been achieved with formulas, but the former contractor decided to have the program cycle through each cell on the new sheet and place formulas where they needed to go. It was slow and didn’t work all that well. It was iterative, each write to the screen is volatile, and the way it was coded was convoluted. I had been asked to fix something that was far more complex than the underlying problem itself. But if you treat VBA as the great hammer, than every problem is a nail.

      • I couldn’t disagree more. VBA (or other coding language which enables Excel to be manipulated) is a wonderful tool which opens up all sorts of possibilities which would otherwise be time consuming, cumbersome and generally in-efficient. What you are complaining about (in your above comment) is badly written VBA. If that routine was written properly, it would be almost instantaneous.
        Having ‘heavy’ worksheets with data, formulae and reports all mixed in together is immensely frustrating and usually unnecessarily slow. It is not automatically better to only use the native Excel UI.
        What we should all be promoting is better Excel which (in my opinion includes a good dose of well written VBA).

      • @PeterB,
        Well, I think Excel itself is a wonderful tool for those reasons. Obviously, VBA is an instrumental part of allowing Excel to transcend time consuming and cumbersome process. VBA in my work is largely unavoidable, so this isn’t a screed against VBA itself. And to be very clear, I’m not complaining about badly written VBA. Even well written VBA in a RUN button doesn’t fully compensate for the deficiencies of run buttons.
        I’m not just seeking speed, although that’s a part of it. I’m also pursuing connectivity throughout. The advantage to functions and formulas is that changes to the underlying data are ramified through the spreadsheet application. That means if I see an error on the front end, I can go one layer back (where I’m transforming the data in formulas) and see if the error originates there. If these formulas and results are wonky, I can move back to backend data to see if the underlying issue originates there. (If it doesn’t, I can easily deduce where the problem exists.) There’s something useful to errors being permeated through the entire application. This helps us find, diagnose and fix them. That’s so much harder to do with heavily iterative code. With code, it’s easy to diagnose a problem in the process of an algorithm, but it’s march harder to find data points that present problems for the algorithm. Move it all (or most of it) to formulas and functions and you can diagnose both data points and the process. If you limit volatile actions in your unavoidable VBA code, then you get the advantage of both connectivity and speed.
        The use of RUN buttons to generate reports disconnects changes in the backend data and the report. Nothing is live about the data being reported.. Moreover, it obfuscates important calculations into code. If I perform calculations on backend data, I can readily see these calculations in real time on the spreadsheet. In many cases, I can see these calculations on the data points themselves. All VBA can do in this regard is to describe the algorithm performed. It can display results of many of these calculations to the extent we allow it through debugging. But even msgbox’s and the immediate window can’t display the results of these calculations to the extent a spreadsheet can.

  • Even if I know I’m going to use VBA for a project, I still prototype it with Excel’s UI and formulas. It’s a really nice way to get your head around something and ferret out some problems before you write any code. And some of the time I never end up writing any code because building it with formulas showed that some of my assumptions were wrong and I didn’t need VBA after all.

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

    Free 24 Excel Tips Guide

    Discover the secrets to Excel efficiency with our free guide: '24 Proven Microsoft Excel Tips to Help You Save Time'

    A humble gift from us to you, to make your Excel journey smoother and more productive. Grab your copy now and start mastering Excel like never before!

    >