I’ve always been interested in computers, technology and automating things. But I have no formal education to prove this, I just play around and learn as I go.
Here’s a little background to my (limited) programming background and how I have begun to progress and where I will go from here. This will likely become a series as I progress further into the world of programming.
My Programming So Far….
In recent years my job role has changed and I have found myself encountering many Excel based problems. Knowing the power of Excel, I know that it is possible to automate a lot of tasks. And that is often the key, not knowing how something can be done, but instead knowing that it can be done! And then striving to make it happen.
With Excel I start with an idea and a bit of paper, scribbling boxes and buttons and a little pseudocode. Next comes the basic template, then diving into the VBA.
Showing someone your VBA code always seems to impress them. “You wrote all of that?” they say.
Well in truth, no of course I didn’t write it all. Well I didn’t actually type it! The term ‘write code’ is very loose.
The bulk of VBA is often ‘written’ using the Macro Recorder. This is a great little tool that is built into Excel and records all of your clicks and keystrokes. Sounds easy? But there is an element of skill involved.
After recoding the code, you have to go and tidy up everything, there are often multiple selections, clicks and scrolls that are irrelevant and need to be removed. There is also the need to stitch various sections together.
There are also many things that just can’t be achieved using the macro recorder. For this, Google is your friend. I use Google so often to get tips and to find blocks of code that kind people have shared. There are many Excel forums that offer advice and share code and examples. I find that MRExcel.com and ExcelForum.com very useful resources for VBA and formulae.
As is the case in many coding languages – it’s not the language and learning that’s important, but the library and resources you use and knowing where to find them.
Why waste time recreating something that has already been done before?
Adapting VBA to The Required Application
This is where it gets a little more in depth and for me it is a lot of trial and error. Using various snippets and resources, I remove bits that I don’t need, add in bits that I do need and join together multiple parts to make amazing spreadsheets that always impress people at work.
In my work as a Production Planner & Stock Controller I work with a lot of spreadsheets and have also inherited a lot of different spreadsheets over the years. I always aim to simplify and automate as much as I can, and this is probably one of the most enjoyable parts of my job. A bit geeky, I know! But I do love a spreadsheet!
Over recent years I have simplified, improved and automated a number of tasks. Here are just a few:
- Stock Control Spreadsheet – pulling data from databases, sorting stock, keeping historic data, dynamic charts. I use this every day.
- Stock Checks – comparing data, identifying discrepancies, suggesting discrepancy reasons based on a set of rules and criteria.
- Purchasing – Tracking what is bought from where and when, automated email sending. This is used every day by people in my office.
- Water Consumption Recording – recording paper mill water consumption with dynamic charts. A 30-45 minute task each month when I took it over, now it takes me about 30 seconds a month!!
- My Any Year Calendar – I love this, I made it a few years ago. Select the year and will calculate not only the days of each month, but also bank holidays, such as Christmas, New Year, May Day and even Easter! As I created it for work it also has the ability to include our shut days. Because I’m nice I am giving this away as a Free Download to readers of my blog. I hope it is of some use to you! See link at bottom of page.
- Holiday/Time and Attendance Tracker – This was for another company and keeps track of employees’ attendance as well as remaining holiday entitlement.
- Complaints Register – again for another company. It separates out complaints by various criteria, keeps history and keeps items to be actions easily visible.
- Daily Update – This is something that I inherited from someone else at work. Previously it was not sent every day, wasn’t always current and often had irrelevant or redundant information. Now I have it completely automated with all data coming from databases and other sources. It even emails out to all employees automatically without input. All information and data is accurate to the minute of sending. Used daily (obviously), but with zero input!
- And so many more. And I have a long list of things to do!
Back to School
So it’s about time I learned something properly and structured, but where to start?
I am quite lucky to have a Lynda.com paid for by my employer so this seemed like a great place to start. I had already completed a number of other courses, mainly on Excel, Photoshop, SEO, and various other subjects. It really is a great resource.
I have recently completed my first programming course – Foundations of Programming: Fundamentals with Simon Allardice and what an informative course it was. Simon Allardice really does explain things well and I learnt a lot!
Well I am continuing to progress through courses on Lynda.com and hope to get more in depth with various languages as I go on and aim to start putting my new knowledge to work as often as I can. Hopefully it will open some new doors for me.
My aim is to continue writing blogs on my progress throughout this journey.
This is a very useful spreadsheet that calculates calendar date for any given year. It also highlights UK Bank Holidays (even Easter!).
I have also added a functionality to highlight specific dates or blocks or dates – ideal for adding your holidays or in my case production shut days.