Training and Consultancy Services for Business
with Naturally Excel

[15/11/2018]

I'm delighted to announce that I have joined the team at Naturally Excel to add Training to their existing range of services. Naturally Excel is a small team of highly experienced and dedicated Excel professionals offering a wide range of consultancy services to business clients. I have been training Excel and Excel VBA since 1995, helping people get the best out of this excellent program. Working with Naturally Excel we are offering half-day training courses and workshops for Excel users to learn about or hone their skills on a wide range of topics.

I introduced my popular half-day training courses in Excel, Access and VBA in response to an increasing demand from businesses who were dissatisfied with off-the-shelf courses offered by training companies. The courses come at a fixed price regardless of the number of participants and take place on the customers' own premises. The convenient half-day timetable means colleagues don't have to be away from their desks for a whole day, making it easier for many more people to attend. The wide range of topics means you can spend a morning or afternoon learning about exactly what you need in far more detail than an out-of-the-box course can provide.

You can view a list of Excel Short Courses here, and Excel VBA Short Courses here. For prices and details of how to book visit Naturally Excel's Excel Training page.

Modifying Recorded Macros: A Universal Sort Macro for Excel

[2/11/2018]

This question turned up in my Inbox a few days ago. The writer had recorded a macro that included sorting a range of data. When run on the original data the macro worked fine but when run on different data (in this case the number of rows varied) the macro didn't work correctly. How could it be fixed?

Excel's Macro Recorder is a great tool for creating simple macros and for helping you learn VBA programming. After more than 20 years as a VBA developer I still use it occasionally to remind me how to perform a particular action, to show me how Excel performs a task I've never had to automate before, or simply to save time writing the code myself. But the real skill comes with modifying the recorded code to work in different, often unpredictable circumstances.

At this point you might be wondering what is the point of having a macro to sort data. After all, it only takes a couple of clicks for the user to do it manually. But you might want to include a sort into a larger, more complex macro, in which case it has to be able to work with whatever circumstances prevail at the time.

If you want to see how I dealt with the problem take a look at my latest Mailbag item Modifying Recorded Macros: A Universal Sort Macro for Excel.

New Access Forms Masterclass
#6 A Push-Button Filter for Your Access Forms

[5 October 2018]

When building a database the task I enjoy most is creating the user interface, what used to be called the UI until Microsoft coined the term UX or User Experience. Good and thoughtful work here can not only help maintain the integrity of your data, avoiding user errors and preventing junk from getting in, but it can also go a long way to create user satisfaction. My aim is always to ensure my users have a good day and enjoy working on my databases. Tools that are awkward and frustrating to use just end up annoying and frustrating their users. I want the tools I create to be intuitive and, above all, useful. You can add as many bells-and-whistles as you want but unless they make sense to the user they might as well not be there.

The topics I have included in my series of Access Forms Masterclasses are all drawn from my personal experience of over 20 years of building Access databases for my business clients. Many of them are included in all the databases I create. In my latest Masterclass I show you how to build a simple Push-Button Filter. The idea isn't my own, in fact it was one of the things that inspired me to get "under the hood" and start being creative with Access 2 way back in the mists of time. I wish I could remember where I read it or the author's name but much thanks to them anyway. The original tutorial used Access Macros but I have made use of VBA, taking advantage of the added flexibility and creativity that this offers.

As usual, the Masterclass includes step-by-step instructions, plenty of screenshots and code that you can copy and paste into your own project and, most importantly, explanations of how everything works.

Build a Back-End Link Checker for Your Access Database

[30 September 2018]

It wasn't until I started building databases for other people (way back in the mists of time!) that I began to realize the benefits of splitting a database. With a split database you have two database files: the back-end file containing the tables, and the front-end file containing everything else. A single copy of the back-end file is located in a shared location and each user can have their own (tailored if appropriate) copy of the front-end file. The benefits of splitting a database are many but for the developer perhaps the most useful is the ability to implement updates, improvements and bug-fixes simply by supplying the client with a new copy of the front-end.

All the users have to do is reconnect the new front-end with their existing back-end file... and that's where the problems arise. Do the users know how to link the files? Can they be trusted to do it correctly and, anyway, it it fair to ask them to perform this task? Is is going to require a site visit from the developer to install the new front-ends and link them? To solve this I built a tool that seamlessly checks the links to the back-end file each time the front-end is opened. If the link to the back end is broken all the user has to do is specify where the back-end file is located. Even if it does require a site visit from you, of if the client's IT guy has to do it, the process only takes a moment. Once linked the job doesn't have to be repeated unless a new front-end file is required or if the path from front-end to back-end is changed. If the back-end can't be located the tool will let the user know and offer to fix it.

I incorporate this tool in all my split databases and now you can do the same thanks to my latest tutorial Build a Back-End Link Checker for Your Access Database. If you aren't familiar with splitting and re-connecting databases I have included a section describing how to split a database and how manual re-linking is performed. There is a fully working sample database to download plus a print-friendly copy of the tutorial in PDF format.

Handling Errors in Your Access Database
#2 Add an Error Log to Your Database

[25 September 2018]

Hopefully, should an error occur in your database, whether anticipated or not, the user will note the details and report it so that action can be taken to rectify the problem. Unfortunately, this seldom happens. Usually there is a message like “the database keeps crashing” or “I keep getting an error message”. More savvy users will note the details or even take a screenshot of the error message, which is very helpful, but diagnosing the problem will often require more information. Additional information would be helpful, such as: Does this only happen to a particular user? Has it happened before and if so how often? Does it always happen on the same computer? What was the user doing at the time? To answer some of these questions and to make the reporting of errors easier, I always include an Error Log in my databases. I have even discovered that users have failed to report an error, later saying something like “Oh yes, it does that…” as if they expected things to go wrong occasionally when, had I known about the problem, I could have fixed it.

The solution is to create a system to record as much information as possible when an error occurs. My Error Log does exactly that, and includes additional reporting features such as the facility to email you a copy of the error log. My latest tutorial Add an Error Log to Your Database tells you everything you need to know and shows you how to do it.

Handling Errors in Your Access Database
#1 Errors 101

[20 September 2018]

Nobody writes perfect code. The aim of every developer is to write code that is "bulletproof" but it's so easy to leave something out, make a mistake, or simply get it wrong. We try to anticipate every unexpected, irrational or simply crazy thing the user might do and we test our code rigorously before releasing it to the world. But unfortunately, despite our best efforts, errors happen. That's when Error Handling comes to the rescue. Good error handling can stop an error from becoming a crisis or worse, a disaster! When your code hits a bump in the road a well-written error handler can help it safely come to a halt. I am frequently surprised, even horrified, to see code written by professional developers (well, folks who take money for it) that either lacks any sort of error handler or simply relies on a cursory On Error Resume Next to deal with whatever might happen. My latest tutorial Handling Errors in Your Access Database #1 Errors 101 tells you what you need to know about adding safe, secure error handling to your Access databases. If you work with macros in Excel, Word or any of the multitude of Microsoft applications that can be programmed with VBA then much of what you will see in this tutorial will be relevant to these programs too.

Coming Soon: Handling Errors in Your Access Database #2 Build an Error Log for Your Access Database.

New Resources Page

[16 April 2018]

Those of you who have been fortunate enough to attend one of my training courses will know that I have scant regard for many of the handbooks supplied by training companies. These weighty tomes usually end up on the shelf and gather dust until they are eventually thrown away unread. Instead I prefer to write my own documantation which is well illustrated and easy to read. The examples in my handouts will often be the same as you will have done in class but if you haven't been to one of my classes, or would just like to run through some of the exercises again, many are accompanied by sample files.

All my handouts are free for anyone to download. They are all in PDF format and are fully printable with no restrictions. You can find them on my new Training Resources Page. As I write this the content is mostly Excel and VBA but I will be adding more as they become available so stop by regularly for the latest content.

FREE eBOOKS

[25 July 2017]

Until now my existing eBooks on The Visual Basic Editor, VBA Message and Input Boxes, and Recording Excel Macros have been available for purchase. From today all of these ebooks are totally FREE for you to download. Each PDF is unrestricted so you can copy and paste content and print out as many copies as you like. No strings! Just follow the links above or go to my eBooks page and follow the download links.

Why free? I belong to a generation that remembers life before the Internet. So many people enjoy its benefits but never make any contribution themselves. I decided it was time for me to give something back.

If you would like to say thanks and make a small donation to help support this site please click the PayPal Donate button below. You can donate any amount in any currency you choose. It's easy and only takes a moment. Thank you.

Pip pip!