Learn VBA to be Lean

Posted on by Dick Kusleika

Lean Office with Excel and VBA

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 that could easily be done by the software. Unfortunately, we simply don’t know how to do it.

and later…

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!

Awesome. I like to see encouragement for people to learn Excel from blogs that are not Excel blogs (i.e. normal people). Also, I’ve been studying the Toyota Production System, so seeing an Excel post on a lean blog is like worlds colliding for me.

I like the simplicity of the message. It doesn’t say “go be an expert” or “go start a VBA blog”. It simply suggests that you teach yourself some VBA over the next couple of weeks. How much VBA can you learn in a couple of weeks? In my opinion, it doesn’t matter. I’d say it’s quite likely that you’d learn something that significantly improves the quality or efficiency of your work. But even if you forget what you learn, there is still value.

Before my first two weeks learning VBA, I knew nothing. VBA so intrigued me, however, that I continued to learn it. Had I abandoned it, I would have learned something else and followed a different path. You can find a path too. If you never try VBA, you’ll never know if that was the path for you. So try it. If you don’t like it, go learn HTML or Javascript or belly dancing. You’ve already mastered Solitaire and The Sims, so move on.

When you’re ready, I recommend Excel VBA Programming for Dummies. I’ve read it several times (because I tech edited it) and I can say that is a great book for beginning VBA programmers. It reminds me of my freshman Calculus professor. She could make the most foreign topics seems easy and natural. The article mentions Microsoft Office Excel 2007 Visual Basic for Applications: Step by Step, which I haven’t read. I have read other ‘step-by-step’ titles and I’ve generally liked them.

I officially dub this week “Recruit a new VBA programmer” week.

Posted in Uncategorized

29 thoughts on “Learn VBA to be Lean”

  2. Dick, I was forced into using VBA as a job requirement. I was working for a consulting firm that forecasted eletricity prices using an Excel/VBA tool. When I started with the firm, I inherited the tool from its builders. I had to understand how the tool simulated market clearing prices for electricity on an hourly basis over 15 years. Daunting to say the least. I was not a programmer at all.

    I then became intrigued with the math in the simluation which then led me down the encryption path (I read “Music of the Primes”). I then became really interested in optimizing my string manipulations and started looking at Regular Expressions. Then I decided that I would take a stab at VB.Net and jump head first into Visual Studio Express 20005/2008. I then needed to understand how to store date which led me down the path of creating log files in Text Files. This later neeeded to be optimized which introduced me to XML and XML literals. Then I needed to learn how to query the XML and pass content to elements using LINQ. I am now realizing that I may want to start exporting my XML data to a database which means I will have to understand SQL. I am also realizing that I my decide to start looking at WPF controls (instead of Windows Forms) which will likely be easier as the XAML will not be as intimidating given my familiarity with XML.

    For me, VBA was simply an introduction/entry drug! It is great to learn as it sits in Excel which just happens to stare me in the face every day. I often write things in VBA with the sole purpose of translating it into VB later on when I get home.

    And resources like your blog keep the addiction going!

    ExcelMonkey

  4. I use the Step By Step book on the Excel VBA course I teach to UK undergraduates and I mostly like it. I augment it with Ch 3 from Professional Excel Development tho, must have some good practice!

  5. I learned VBA by reading Bill Jelen’s (Mr. Excel) book VBA and Macros for Microsoft Excel. It is the first and only computer book I have read cover to cover. It was written in an easy to understand style. I recommend it!

    http://bit.ly/1dJC4q

    There is also a version for 2007.

    http://bit.ly/3BCAV2

  6. Can I volunteer to be recruited? Thanks to this blog, and all the fabulous contributors, this unemployed actuarial analyst developed an addiction to vba. This is a terrible (U.S.) job market regardless of skill. Gives me more time to develop, though. Watching the computer work for you is a wonderful feeling!

    Brett

  7. My first VBA book was a really old Step By Step edition, and I liked its style. I’ve recommended the series to many people who want to get started with Excel programming.

  9. Dick, totally agree !!!

    CT, that Holy Crap moments are not only exclusive for VBA, but for all things in life, and yes, you’re absolutely right, it makes you feel goooooood !!!

    Like the chinese saying: “give a man a fish, and you’ll be feeding him a day; teach him woh to fish, and you’ll be feeding him for life”.

    I consider myself an evangelist within my work and my groups, and every once in a while I got one of those. and thanks to this post, and Debra’s, and Chandoo’s, and Jon’s, and so on… I sometimes feel on the other side, saying “HOLY CRAP !”.

    “Knowledge is one of the origins of happiness”.

  10. My job is process improvement in an automated biotech lab. When I studied the workflow I realized the rate limiting steps was not with the robotics but dataflow. The same old cut/paste/copy over and over and over…. I read Excel VBA for Dummies cover to cover. My first macro reduced a four hour job down to 20 seconds. Biotech produces a firehose of data. IT doesn’t understand the needs of the scientist and the scientist don’t know what Excel and Access can do. I have found a niche that makes me very valuable. And yes, it is addictive. It is both technical and creative.

  11. I just stumbled on your site – it’s refreshing to see an excel site that doesn’t look like its last design update was in 1994. I’ve been an excel junkie for a few years now, and have started learning VBA. I’m too lazy NOT to learn VBA. I’ll check out the Dummies book and keep the good stuff coming.

    ps – I really enjoyed Wayne Winston’s books on using excel for business analytics.

  12. I learned it by writing PUP v1. I got a beta copy of Excel 5 hand-delivered by Microsoft folks. Back in those days, Microsoft people actually came to my HOUSE to demo their stuff. I was writing spreadsheet reviews for magazines, and they wanted to make sure I understood their product so I could write accurate reviews.

    In any case, I was floored by VBA. I never really got into that XLM macro language, but VBA was something that actually made sense. So I had an idea to write a set of Excel utilities in VBA, modeled after the Baarns Utilities (written is XLM), which is still available:

    http://archive.baarns.com/excel/products/bu.asp

    Current PUP users will notice lots of similarities.

    PUP v1 was a disaster, and I sold about 20 copies. But I learned a lot, and kept on learning. Eventually I learned enough to write a few books about it.

  14. I used to do file transfer stuff in Quick Basic and VBA is just an extension of that

    Quick with the “Holy Crap” moments
    I Think Oprah has just Copyrighted “Aha Moments”

  15. Martin –

    The way I heard it is:

    Give a man a fish, and he’ll eat for a day.
    Teach a man to fish, and he’ll sit in a boat and drink beer all day.
    Teach a man to write code, and he’ll sit in a cubical and swear at you all day.

    (I made up that last line.)

  16. More likley he is swearing at the screen Jon, most coders I know don’t have the social skills to mix with real people -:)

  17. Thanks for that link, Dick – that will be getting forwarded around the office and posted on the intranet wiki on Monday morning. I’m currently working with a large aerospace engineering company, and Lean is something they get, and do quite well. Terms such as value stream mapping, five-s, and kanban are in common use (the signs in the employee kitchen and break areas encourage you to ‘5-S your mess’).

    However, there’s still some of the legacy of a period under public ownership, and some things are still done the long and complicated way because “that’s the way it’s always been done”. In general, people are positive and up for improvment, but I’ve heard it likened to steering an oil tanker with a hand fan.

    This is fertile ground, though – there’s a lot of work to do, and people with skills and enthusiasm for automating mandrolic processes are very much in demand. I don’t have anywhere near the experience or ability of DDOE’s contributors, but I still find I get phone calls and emails from people across the company that I’ve never met, usually starting “I’ve heard you’re a bit of a whizz on Excel” (it’s all relative, I suppose).

    Seeing the reaction of someone whose monotonous eight-hour task has just been automated to complete in less than a minute gives me a warm, fuzzy feeling inside every time. And it’s even more fun teaching them to do it themselves…

  18. I celebrated by showing somebody how to use the macro recorder today. When I mentioned removing Selection/Selection statements their eyes started to glaze over, so I may have blown the recruitment.

  19. I am encouraged to learn that our post has become a topic of discussion and may have spawned (renewed) interest in how VBA and Excel are (or could be) used in the lean manufacturing sector.

    I also highly recommend the VBA Programming for Dummies book and recently purchased the updated 2007 edition. My favorite book to date is Excel 2003 Power Programming with VBA by John Walkenbach. The writing style and presentation of information make for easy reading and learning.

    Lean practictioners tend to focus on production operations and seem to give little consideration for opportunities that may be found elsewhere … like the front office.
    How is it that someone who can type 80 words per minute is percieved as an expert on the computer? Fast isn’t necessarily efficient or effective.

    The breaking point to write the post came when I learned that one of our reports was being generated manually from text files created by our MRP system. This process required up to 30 minutes or more of face time with a spreadsheet every day. After writing some relatively simple VBA, the time to generate the report was reduced to less than a minute.

    Now, what to do with the time we’ve saved. Perhaps read a little more and see what other opportunities can be found. Thank you for visiting.

  20. I like to think I know my limitations. I know how much time and effort I’m willing to invest and I’ll push the envelope if there is a payback on the end.

    I did HTML, leading to a brief dabble with JavaScript then to web VBScript, ASP, Excel VBA along the way (recorded macros to edited macros to direct coding), batch files, standlone VBScripts (Windows Script Host). Word macros and a dip into Outlook macros. After a while you start to learn important skills that cross-over the technologies.

    To be completely honest there was a second motivation for the Excel VBA. A guy at work who, though not exactly the ‘go to’ guy, was known as someone who got things done. I started to suspect there was more brute force trial and error than skillful insight at play in his solutions. They worked, but he didn’t know exactly why or how, but was happy to let people think he knew. That didn’t sit well with me and I reckoned I could do better, and I would then know if his rep was well founded or not.

    Sad but true.

  21. I’ve been reading up around the TPS (Toyota Production System). Specifically a U.K. based guy called John Seddon (http://www.systemsthinking.co.uk/home.asp) who has taken the principles of TPS and applied them to the public services (Local Authorities, Police etc).

    I bought his book “Systems Thinking in the Public Sector” after reading about his work on the popular U.K. “Inspector Gadget” police blog (http://inspectorgadget.wordpress.com/). In Britain everything seems to be run the same way, and it’s NOT the TPS way!.

    The alternatives, though straightforward and logical, have fallen from living memory and from the cultural landscape. Everyone seemingly accepting that there isn’t a better way to do things.

    He makes some painful (eye-watering!) observations about how systems are simply set up and designed to fail and be more costly, usually in the name of efficiency savings. I found it difficult to challenge his logic or evidence.

    But the alternatives are maybe more scary than doing the ‘wrong thing for the right reasons’?

  22. John Walkenbach, I taught myself VBA from your book Excel 2000 Power Programming with VBA. I’m a CS guy, and was able to take old macros and re-engineer them from run times of twenty minutes down to 3 to 5 seconds! I was amazed at the power of VBA. Just so you know, your book really helped me. Are you coming out with another? My understanding is VBA for Excel 2007 is syntactically different. Also, does anyone know how to access an excel file (not Sharepoint) on a web server through VBA code? (sorry if this is the wrong place to ask, I’m a newbie here)

  23. Funny, I ran a VBA training course yesterday just like Ken did :-)

    J-Walk got me interested in learning more about Excel after visiting his site. Then I saw this blog and decided to really go for it.

    I read the Dummies book, then I read Power Programming. I recommend both, the latter is in my desk drawer and I refer to it often.

    Absolutely agree the best way to learn is hands on, there’s heaps of resources to help get started.

    Here’s to a new generation of Excel dudes!

  24. @Redge Semplonius: too true, it’s surprising that there isn’t more focus on a lean office. Most of our lean practitioners are office-based; you’d think they would want to make their own lives easier as well as others’!

    @gruff999: John Seddon should be knighted! Cheers for the link, I know his work but I’ve not visited his site. Looks like a good resource.

  26. I took an online VBA course a while back, but I need to practice so I can understand it more. I really liked it and have applied some of it to my work. I’d love to see what these lessons are and to use it so I can learn more of it.

    Thanks!

  28. I have job that required me to get Excel data into Google Earth® and started by finding bits of code and then modifying them (with various degrees of success!).

    I am not a programmer.

    I had managed to “do the job” after a fashion but had not properly understood how I got there.

    Metaphorically I found myself halfway up a mountain when my programming skills would barely see me through the foothills in reality.

    Your page has helped and encouraged me tremendously – Everest here I come!

    Thank you

  29. Steven Hackman, if you email me (antony dot boyle hat gmail dot company) then I’ll send you my code for exporting data into google earth. it is full of green comments so you should be able to follow it. I have got it to do the following:

    draw pushpins with description boxes
    draw lines/paths
    draw polygons
    draw circles of a set radius around a pushpin

    my biggest achievement with goodle earth is plotting data in a colour changing line, where the colour related to some data value in excel.

    here’s a few snippets for writing KML in VB (assuming you have a file open with a handle #intKMLFile)

    ‘set up KML Header
        Print #intKMLFile, “<?xml version=”“1.0”” encoding=”“UTF-8”“?>”
        Print #intKMLFile, “<kml xmlns=”“http://earth.google.com/kml/2.0”“>”
        Print #intKMLFile, “<Document>”
        Print #intKMLFile, “<name>” & Format(Date(), “dd/mm/yyyy”) & ” GPS/GSM Quality Data</name>”
        Print #intKMLFile, “<open>1</open>” ‘folder open by default
        ‘folder radio buttons…
        Print #intKMLFile, “<Style><ListStyle> <listItemType>radioFolder</listItemType> </ListStyle></Style>”
        Print #intKMLFile,
       
        ‘set up line styles
        Print #intKMLFile, “<Style id=”“Qual00”“> <LineStyle> <color>ff0000ff</color> <width>4</width> </LineStyle> </Style>”
        Print #intKMLFile, “<Style id=”“Qual02”“> <LineStyle> <color>ff0066ff</color> <width>4</width> </LineStyle> </Style>”
        Print #intKMLFile, “<Style id=”“Qual04”“> <LineStyle> <color>ff00ccff</color> <width>4</width> </LineStyle> </Style>”
        Print #intKMLFile, “<Style id=”“Qual06”“> <LineStyle> <color>ff00ffcc</color> <width>4</width> </LineStyle> </Style>”
        Print #intKMLFile, “<Style id=”“Qual08”“> <LineStyle> <color>ff00ff66</color> <width>4</width> </LineStyle> </Style>”
        Print #intKMLFile, “<Style id=”“Qual10”“> <LineStyle> <color>ff00ff00</color> <width>4</width> </LineStyle> </Style>”
        Print #intKMLFile, “<Style id=”“BadGPS”“> <IconStyle> <color>ff0000ff</color> <scale>1.5</scale> <Icon><href>http://maps.google.com/mapfiles/kml/shapes/shaded_dot.png</href></Icon&gt; </IconStyle> <LabelStyle><scale>0.5</scale></LabelStyle> </Style>”
        Print #intKMLFile,

        ‘make folder
        Print #intKMLFile, “<Folder>”
        Print #intKMLFile, “<name>” & strFolderName & “</name>”
        Print #intKMLFile, “<open>0</open>” ‘folder collapsed by default
        Print #intKMLFile, “<Style><ListStyle> <listItemType>checkHideChildren</listItemType> </ListStyle></Style>” ‘individual line segments hidden by default
        Print #intKMLFile, “<Placemark> <styleUrl>#” & strLinetype & “</styleUrl> <LineString> <name>” & strLineName & “</name> <coordinates>”
        Print #intKMLFile, Format(dblLon1, “0.0######”) & “,” & Format(dblLat1, “0.0######”) & ” “
        Print #intKMLFile, Format(dblLon2, “0.0######”) & “,” & Format(dblLat2, “0.0######”) & ” “
        Print #intKMLFile, Format(dblLon3, “0.0######”) & “,” & Format(dblLat3, “0.0######”) & ” “
        Print #intKMLFile, “</coordinates> </LineString> </Placemark>”
        Print #intKMLFile, “</Folder>”
        Print #intKMLFile,

        ‘end KML document
        Print #intKMLFile, “</Document>”
        Print #intKMLFile, “</kml>”

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.