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.
Once upon a time there was a system called Franklin Planner. Back in the 80’s before even PDAs were dreamed of except by brain-fevered science fiction authors, the concept of having Goals, Planning to meet Goals, and having Everything in One Place was thought of by Hyrum W. Smith, who developed the Franklin Planner (FP).
Put everything in here, he said. Keep your contacts, your calendar, your to-dos all here. Paste in or hole-punch your photos and documents. Proudly strike off things you’ve decided to Not Do. Assign everything a goal-based priority, and then stick to that priority.
One of the nice things about the FP system was the fact that you made notes right on the day pages. You wrote down your phone calls, checked off your to-dos, made meeting notes, scribbled character info for the novel you were (not so much) working on in your spare time……
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
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
That will bring up a dialog box where you can name the macro.
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:
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!
Let’s look at the code we just created. To get to the Visual Basic screen click on Visual Basic, more info is here.
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’
Then delete the two ‘Range’ lines. You should get something like this:
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:
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
I pasted this into the VBA editor. I’ll note that the actual code that needed is only the
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.
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.
BYOD is the trendy acronym for Bring Your Own Device, basically using your personal smart phone for work purposes. There are a whole host of technical and legal issues around information security. There are also tax concerns and what happens when you quit if the contract isn’t up. This blog entry will be looking at none of these. I’m going to tackle the issue of work/ life separation, a topic that I haven’t seen addressed in all the marketing fluff my inbox gets.
I hate having to carry a separate BlackBerry. With my Android smartphone, a work BlackBerry, keys, wallet and various other stuff I need a murse. So the idea of one less device is very appealing. Add in some cost benefit from work paying a part of the monthly fee and at first glance it’s a no brainer.
BUT do I want to be checking work email all the time? Is this a habit that my work should expect or even desire?
My BlackBerry is shut off earlier than my personal phone. On the weekend I can leave my work device on the kitchen counter – I’m an accountant after all not one of the IT team that may need reboot something. Let’s be honest – no one really needs an emergency journal entry.
I fear that if I went BYOD I’d get into the trap of reading my email constantly and treating it all as urgent.
As Dwight Eisenhower said
“What is important is seldom urgent and what is urgent is seldom important.”
That’s why I’m in no rush to hop on the BYOD bandwagon. I need a break from work and work needs me to take a break too.
Generation Z are born between 1994 and 2005 are beginning to enter the workforce. Are we ready for Generation Z or are we still trying to understand Millennials?
A quick Generation Z definition is that Generation Z are Ultra-Millennials. If you want to brush up on ‘What Is A Millennial’ I’ve discussed them often within this Blog, at conferences and on Television – so I’ll link a few resources at the end of this post.
Generation Z Are Ultra-Millennials
I jokingly call Generation Z Ultra-Millennials because they move at lightning speed and are even more comfortable with technology and social media than Millennials. Generation Z have seen more, want more and can produce more in less time.In addition, more than any generation before them, Generation Z do not feel limited by geography, proximity or time zones.
Diversity, choice and possibility are cultural norms for…
If you’re in a planning role chances are you’re always looking at what the buildup for your budget was/is. This sometimes occurs when you aren’t on the network. I’ve had to look items up while on the train, at someone’s desk and during a natural disaster.
While VPN and WiFi make much of this easier than before there is still some benefit in having the files locally on your laptop.
You can use the built in Microsoft offline files capability to keep an synchronized version of the folders you need locally. This doesn’t move the files outside of the corporate firewall like a Dropbox or SkyDrive does – it’s all done on your LAN. Assuming that your laptop has the relevant security there isn’t a potential data leakage issue with this process.
To turn this on navigate in Windows Explorer and right click on the folder you want to be available. Select ‘Always available offline’. That’s it.
I strongly suggest you do this at the end of the day and that you leave your laptop connected on the wired LAN. You’re going to be copying the entire folder so you want to do it the fastest way possible and not slow you down.