Are poorly written Excel macros slowing you down? Probably!

If your macros are taking longer than they should, you’re not alone. We’ve seen processes that should complete in seconds, but instead, take minutes – and even up to 96 hours in extreme cases! With the right optimisations, we reduced those 96-hour routines to under a minute.

Why are your macros so slow?

Culprit 1: Excessive read/write operations

Every time you read from or write to an Excel sheet, it slows things down. While tricks like disabling calculations, screen updating and events help the issue, the real issue is frequent read/write operations for every single cell. This can grind your process to a halt.

Solution: Use arrays! Load your data into an array, process it in memory, and write everything back to the sheet in one go. This speeds up your process from minutes (or hours) to milliseconds.

Culprit 2: Inefficient code design

Poorly written code often involves unnecessary loops. The most common mistake is looping through one dataset for every item in another. While this might seem fine for small datasets, it becomes unmanageable with large ones, making processes much slower than they could be.

Solution: Use dictionaries! Store values that need to be accessed quickly in a dictionary, eliminating the need for multiple loops.

For more expert help with optimising your Excel macros and VBA routines, get in touch today.

 

Build custom business applications in Excel with UserForms

Person using an Excel UserForm on computer

UserForms are an incredibly powerful data capture tool.They are basically boxes which help you ensure that anyone putting data into your spreadsheets understands exactly what is needed in a simple, easy-to-use format.

This protects your data integrity by ensuring that the information that people supply is:

  • Put into the correct cells 
  • In the correct format
  • The full set of data required for the spreadsheet. 

UserForms also reduce the risk of inexperienced users accidentally damaging the functionality of the spreadsheet. 

In short, they help you control, protect and manage your data. A well thought out implementation, will enable you to achieve sophisticated, database-type functionality, from within an Excel workbook.

How UserForms make Excel more user-friendly

Unless you’re an experienced user,  spreadsheets can be daunting and it can take time to get to grips with how they work.

Even more experienced users can find complex workbooks confusing and this can lead to mistakes that can totally undermine the integrity of your data and lead to serious errors. UserForms provide an interface between the user and the spreadsheet which helps remove that risk. 

UserForms are incredibly versatile – allowing you to create interfaces that are really easy to use. They can feature drop-down menus, responsive functions, command buttons and a host of other features to make them simpler and more pleasant to use than entering data directly onto a spreadsheet. There’s a wide range of styles and designs to choose from to improve the user experience and you can even use pictures, when necessary, to make the process even easier for the user to understand. 

Designing effective userforms in Excel

a real art to doing this well. UserForms can be as simple or as sophisticated as you need them to be and some good VBA code behind the scenes will do all the heavy lifting for your users, making business administration a breeze.

Key factors to consider will be:

  • Data structure – your underlying data will still be sitting in a spreadsheet, so getting this well laid out will make it easier to link together and report on.
  • Data validation – or checking the form for input errors and giving users clear feedback on what they need to correct before the form saves data
  • Use cases – considering what tasks a user needs to complete will instruct the different components you need to build into the form
  • Optimisation for speed – if you’re working with large data, making your forms quickly searchable will pay for itself many times over

So what’s the catch?

There isn’t an obvious one. UserForms can deliver an extremely cost effective answer to the data management needs of many SMEs. They can drive efficiencies and mitigate risks. They will make you and your employees more confident about the data you are handling. 

If you need multiple users to  access your spreadsheet at the same time, then you might be stretching the bounds of what can reasonably be accomplished with an Excel tool and it could be time to consider alternatives, like a web application. 

Excel UserForms have the power to transform the way you use spreadsheets

Well-designed UserForms can effectively turbo-charge the way you operate your spreadsheets and, ultimately, your business. As we start to exit from lockdown, digitisation and automation will be key factors in developing more efficient business practices. 

Embracing them, will help you take advantage of the return to normal and deal with the associated upturn in operational demands. 

We have worked with a number of clients to develop sophisticated UserForms and created software and algorithms that empower them to use the data collected to maximum effect. We’d be more than happy to chat with you about the difference UserForms could make to the way you run your business. Check out some of our case studies  or get in touch for discuss your needs.