Lean Office with Excel and VBA

Have you ever wondered what people do at their computers all day?  How can they possibly be that busy working on their day to day tasks?  The answer may surprise you!  Computers were supposed to make our jobs easier and give us more time to do other things.  The reality is quite the opposite.

We have noted that many people are not as adept at using the software we have placed at their finger tips as we would like to think.  As a result, we have found that one of the significant losses in the front office is time wasted doing work that could easily be done by the software.  Unfortunately, we simply don’t know how to do it.

Time is wasted entering, retrieving, editing, and manipulating data to consolidate and prepare reports that only require further manipulation and editing so an effective analysis can be performed.  This is particularly true for spreadsheet applications.

Pivot Tables are an excellent example of the point we are making here.  Some people are aware of them and even fewer know how to use them.  Pivot Tables make organizing data a relatively simple chore.  Once created, it is a simple matter to refresh the data and regenerate new reports.

We have also observed that many of the tasks being performed using spreadsheets can be easily and readily automated resulting in considerable time savings.  When we discuss these opportunities with the current users, we find that most people only use a fraction of the capabilities that are available to them simply because they are not aware of them or simply have have not been trained.

There is something about our current work methodology that supports learning  just enough to get the job done rather than learning all we can to perform the job efficiently and effectively.

Resources:

There are many venues available including online training and packaged offsite training programs however, we still prefer learning by the “book”.  Many books include a companion CD that not only includes working examples and tutorials from the book, but often times a copy of the book itself is offered in digital form.

If purchasing books seems to be more of a burden than you bargained for, visit your local library.  They often carry many books that are better borrowed than purchased, especially if you are starting at the most basic level.  In a short period of time, your experience will outweigh the content of the introductory texts.

The next time you find yourself entering even more data and formatting spreadsheets and reports, consider teaching yourself some VBA over the next few weeks to see how much you can do to help yourself by putting your software to work!

Trusted Web Sites

If Microsoft Help will take you online to visit the archives of Microsoft’s help system, it only seems inevitable that you will find yourself searching for an online “solution” as well at some point in the near future.  Rather than list the sites here in this post, we recommend that you look at the Web Sites Page under “Books and Resources” menu or click here.

Visual Basic for Excel

Microsoft Office Excel 2007 Visual Basic for Applications:  Step by Step

We are presently reviewing Microsoft Office Excel 2007 Visual Basic for Applications:  Step by Step, by Reed Jacobson, published by Microsoft.  The book requires a minimal degree of familiarity with the Excel and VBA environments and offers a solid interactive approach to familiarize the reader / student with Macros and VBA.

While this may be a little more than just coincidence, the examples developed in the first few chapters of the book were very similar to an actual situation we encountered only a month ago.  Although we already developed our solution, it was interesting to note that the “HOW TO DO IT” was already out there, bound in a book and sitting on a shelf.

The ERP system would generate a text file that was manually loaded into a spreadsheet, manipulated, formulas added, data edited, and finally formatted to create the final report.  This task would require anywhere from 30 to 45 minutes per day.  Using the macros created through the first few chapters allowed this work to be completed in less than a minute.

While books may not make for a great gift, the time you can save to do other things is the best gift you can receive.  It is interesting how often we hear “I don’t have time”.

Next Steps

We are more than impressed by companies that support in-house libraries where books and other resources are available for their employees.  If you don’t have one where you work, this may make for a great suggestion where everyone wins.

Until Next Time – STAY Lean!

Vergence Analytics

Advertisements

7 thoughts on “Lean Office with Excel and VBA”

  1. In past editions, I have not found the Excel VBA Step-by-Step book to be the best introduction to VBA, although that may have changed in the 2007 edition; the example were trivial, and the pace was glacial. I was not some sort of VBA expert when I started with it, but it was still aimed far too low to be of any real use. I would recommend either of the John Walkenbach books (he writes one titled “Excel Bible” and one “Power Programming in Excel with VBA”) for the appropriate Excel version, or the applicable version of “Excel 2002 VBA Programmer’s Reference” or “Excel 2007 VBA Programmer’s Reference” by Bullen, Bovey, Green (with a different fourth author for each book) – note that “Excel 2003 VBA Programmer’s Reference” was published by the same publisher as an update to the 2002 version, but the original authors state that this work was prepared without involving any of the original four authors. Unfortunately, the update introduced numerous errors and failed to cover some of the most important enhancements for Excel 2003… and it is probably best avoided.

    In order of level of targeted user, the books increase from “Bible” to “Power Programming” to “Programmer’s Reference.” A user who is reasonably proficient in Excel and wishes to develop personal or workgroup solutions using Excel and VBA could get there with the Walkenbach “Power Programming” book – but once they are at or close to that level, the “Programmer’s Reference” will allow them to make their applications more robust and useful for wide, reliable deployment – say where hands-on support may be infeasible. For many users (as opposed to developers) whose main interest is to get work done the “Power Programming” is a very good place to start and probably end. Users should also be aware that the “Programmer’s Reference” series assumes a pretty high level of Excel knowledge walking in.

    I hope those suggestions help,

    Deaner

  2. Hi. VBA drives me mad. I’ve been on thousands of websites on the subject and none of them can explain how I can use an If Then Else piece of code in it’s related worksheet. Whatever I do nothing happens. I’ve just about to give up and go back to the manual way of doing things. It may take longer but it works!! VBA doesn’t. Stuart

    1. As was suggested by our post, we recommend purchasing a good book such as the one we reviewed. I would also recommend John Walkenbach’s Power Programming with VBA for Applications. You can also visit our recommended links from our Books and Resources page.

      Attempting to jump into the middle of a problem with a VBA solution is not necessarily the recommended approach to getting VBA to work. It should however be enough to create the desire to want to learn more.

      Your efforts will be rewarded.

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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