How a ‘5 minutes a day’ Mentality Makes a Difference

Two or three minutes a day doesn’t sound like much, even when you consider that 5 minutes a day adds up to 3 days a year. It’s such a small time period that many people won’t try to save that much.  They’ll say it’s not worth it.

But it all adds up.  If you can save 5 minutes a day on your email (and you can) that’s 5 minutes you can use to do something else.  Maybe you can invest that 5 minutes into saving some more time, maybe the investment becomes catching the earlier train on Friday.  The savings is yours, you just need to be open to finding it.

I’ve found that looking for the small time savings becomes a habit.  It’s becomes a mindset.  Spend a bit of time making a macro – maybe the payoff is only for that one workbook – but if you get really comfortable doing macros they’ll get done faster.  This week I needed a macro to work around some data issues.  I probably won’t use that exact code again but it’s in my toolbox and I’m exercising my VBA muscles.

It’s like a vlookup in Excel, once you do a lot of them they become second nature and they’re no longer hard to do.  Record enough macros and you’ll remember all those funny bits of code and come back to reuse them.

Make enough Outlook Quick Steps and you’ll start making them for anything.  I made one for year end accruals, something I needed for 2 weeks.  It was faster to spend 3 minutes making a rule then having to process 3 emails in the normal manner.

When it’s faster to make it and then use it compared to the alternative you’re in the really productive zone.

So don’t say ‘I don’t have enough time’, in today’s fast paced world you need to make some time to save more time.

Advertisements

Mid-week Macro – editing VBA code (part 2), record then edit

Last week I went over how to access the Visual Basic editor and said that my usual to make macros was to record something then edit it.  This week we’re going to do just use that.

I’ll be honest – I’m no coder.  While I’ve done stuff beyond:

10 Print "I'm trapped in a Vic20"
20 goto 10

I haven’t really coded any programs beyond first year computers in school (what do you mean TurboPASCAL isn’t relevant anymore).  But I’m not afraid of code and you shouldn’t be either.

Today I needed to do some graphing of a few years’ worth of monthly data – easy right? In this case it wasn’t because in the data I had there was a variance column after each month.  The data went April, variance, May, variance, June, variance, July…  What I needed to do was delete the variance cells.

If I was going to do this manually it was going to take some time – I had 5 years of monthly data.  What I needed to do was something like this:

  • Delete the cell (assuming we’re starting on a variance value),
  • Shift the remaining cells to the left
  • Space over once to the right so I’m on the next variance cell
  • Repeat

What I’m going to show is how to record a macro that did this and then clean up some of the code in the VBA editor.

To start recording a macro go to the Developer tab and click Record Macro

Record a macro

That will bring up a dialog box where you can name the macro.

02 record macro dialog

I’ve named the macro as above – I don’t suggest leaving the suggest Macro1 name as you’ll get too many to keep track of eventually.  At this point Excel is recording whatever you do and translating it into VBA code.  Any superfluous clicks will be recorded (as we’ll see later) so be careful.

Now go ahead and click on the cell you want to delete, right click and select Delete.  You’ll get a dialog box like this:

if you right click, then select Delete this is the dialog box
if you right click, then select Delete this is the dialog box

In this case I do want to shift the cells to the left so I would click OK.

Then I used the arrow key to move to the next cell which was the variance cell I want to delete next.

To stop recording the macro go back up to the Developer tab and click on Stop Recording.  Don’t forget this step!

if you don't stop recording you'll end up recording a bunch of steps.  Don't worry we're going to learn how to delete things.
if you don’t stop recording you’ll end up recording a bunch of steps. Don’t worry we’re going to learn how to delete things.

Let’s look at the code we just created.  To get to the Visual Basic screen click on Visual Basic, more info is here.

look at the errors, we'll need to fix that
look at the errors, we’ll need to fix that

This isn’t quite what we want – the first two lines were caused by me being in the wrong workbook (I was in the PERSONAL.xlb macro workbook FWIW), the third line selects a specific cell which is incorrect, and the last line does the same thing in error.  If you paste in the code you’ll see what I mean.

What we need to do is edit out the bad code.  Go ahead and delete the two lines starting with ‘Windows’

06 edit VBA code highlighted

Then delete the two ‘Range’ lines.  You should get something like this:

07 edit VBA code deleted

As you can see this is the command that was generated by us deleting a cell.  You can see that we’re shifting the cells to the left.

Now in order to move the cursor to the next cell to the right we need to do something complex.  I complexly went to Google and typed in “excel vba move one cell right”.  That took me to mrexcel.com which is a great resource.

The first result showed that we needed to use the following code:

activecell.offset(r,c).select
where r = # of rows you want to move (-r if you want to move up)
c = # of columns to move (-r if you want to move left)
so in your example it would be
activecell.offset(0,1).select
Pasted from <http://www.mrexcel.com/forum/excel-questions/69473-move-active-cell-one-right-excel-2000-a.html&gt;

I pasted this into the VBA editor.  I’ll note that the actual code that needed is only the

activecell.offset(0,1).select

The rest was an explanation.  I’ve pasted in the explanation so I’ll remember how this works.  That means I need to start off those ‘comment’ lines with a “ ” as you can see here.

the final product, note that comments are in green
the final product, note that comments are in green, the real code is in black

Save the code and we’ve ‘written’ our macro.

Next week I’ll show you how to create a button so you can run a specific macro at the touch of said button.

Hopefully you get from this that macros can be easy.  It took me 4 minutes to create this macro and another 5 minutes to debug it (I got the rows/columns part backwards) and setup the button.  If I had done in manually I’d be looking at 60*45 seconds/60=45 minutes.  And that would likely have taken longer with some mistakes.

As I always say

If you need to do something more than 3 times write a macro.

The value of 300 seconds a day

As an accountant I like to put things in terms of dollars and cents. I’m also the sort of person that sees the upside on small improvements done multiple times. When the two intersect I get excited (well work excited at least).

If you can save 5 minutes a day – 5 days a week and 50 weeks a year you’ll earn back 21 hours (5*5*50/60 = 20.8333). That’s about $1200 dollars or more per person for most teams of professionals. If someone were handing out cheques for that amount we’d signup in a minute. So why don’t we do this at work if the benefit is time?

Five minutes a day isn’t hard to achieve – if you do something 100 times a day (looking at you email) you only need to do something 3 seconds faster each time.

The following Quick Step takes under 5 minutes to create and if you use it you’ll easily make that time up in the first day of use. I’m likely low balling as most people can’t drag an item from the inbox to the appropriate folder in under 10 seconds but you can go up and click a quick step in under 5. The difference is 40% or one full day more a year.

saving 3 seconds on each email is the same as getting 3 days of free time a year

Go to Quick Steps and start a new one (for a detailed view of doing this including screen shots look at this post). Select the move to folder option, then select a default folder. Name the quick step something like “current month”. As I wrote before I put all my inbox into one folder 0-current month, then move each month’s email into a separate folder.

current month

I’ve ordered my quick steps so that this one is in the top left corner (1st in the list). This way its always in the same place and muscle memory means I don’t need to think hard to use it. The alternative is to drag and drop and that option is fraught with the potential to drop the email into the wrong folder -you need to pay attention. This way is simple and so fast that you really don’t have an excuse not to use it.

Between this, my delegation/follow up steps and keeping my junk mail purged I’m routinely at zero items in my inbox.

You need to take the time to save the time in the long run

xkcd comic
How much effort should you take to improve a process?

This is a little comic that I found at XKCD.  It’s the only comic / funny posting that hangs in my office (I’m so over Dilbert).  When I’ve interviewed people I’ve pulled this comic out and become animated.

Simply put – if you can save 1 second on something you do 50 times a day – spending less than a day to shave off that time will pay for itself in five years.

Many people are going to say they don’t do many things 50 times a day but most office workers do.  Most of us in business complain bitterly of getting hundreds of emails a day.  If you could shave off 5 seconds on every one of a 100 emails per day, you could spend 2 days working on this and still have a payback under one year.