Most metallurgists are wizards in Excel. And rightly so, it can be a powerful tool to keep mineral process plants running smoothly. With Excel VBA macros you can automate all kinds of repetitive tasks like data logging, production reports, and metal accounting.
VBA is versatile and powerful, but it does have significant limitations when it comes to data visibility, data reliability, and data accuracy. These three are essential if you want to make informed decisions about your plant based on up-to-date data. That’s also why Excel is non-compliant with the AMIRA P754 Code of Practice for metal accounting.
In 2024, there are better ways to handle data so you can free up valuable time for your team and find new ways to optimise your plant.
So what are the three reasons why leaning too heavily on Excel macros could be holding you back?
1. Data visibility
Why does data visibility matter? In a word, trust.
If you can see and trace how the data is being manipulated, you can trust it. And that’s where many metallurgy and operations teams run into problems with Excel.
If you don’t trust the numbers in a report created with a macro, tracing the calculations to check your numbers is difficult or impossible. Complex VBA macros have multiple subroutines, so data can be buried many pages deep.
Macro creators can hide certain actions from users, or lock out some of the sheets so it can’t be modified. This makes users completely blind to the calculations and workflow, making troubleshooting impossible.
Excel macros are notoriously difficult to audit, especially when multiple team members have access and can modify the code without leaving a clear audit trail. It’s hard to track who made changes, and when, or why. If a formula is accidentally changed, identifying the root cause can be extremely difficult.
If you use specialised software instead of Excel, your plant operation team can maintain clear audit trails, with changes and annotations tracked in one place. This fosters better communication and creates more collaboration and faster issue resolution.
“If you have the right software, it’s pretty easy just to click through, follow the workflow and find it,” explains Drew.
“It will tell you what instruments are being used in this calculation and what that tag name is. Whereas, to find those tag names in the VBA code in Excel, you’ve either got to hope that it’s in the cell formula, or if it’s buried in the VBA code, good luck.”
The risk of “cute data”
There’s another big reason you need data visibility: to find deliberate mistakes, or ‘cute data’. Sometimes, people feel incentivised to make the numbers balance and look correct rather than submit a report with errors.
“If you can’t see the actions being performed, then you’re assuming your VBA code is doing what you’ve been told it does, or shown it does. But, in some cases we’ve seen examples where the macros have been coded to hide actions and make the numbers balance,” he says.
“When it comes to the visibility of your plant and making good decisions, the key here is trust. If I can see and trace how my data is being manipulated in an easy-to-read format, then I’ll trust it.”
Without clear visibility of who changed what, accuracy and accountability suffer. Errors can go unnoticed for months or years.
2. Data accuracy
Accuracy is non-negotiable in plant operations. Without accurate data you can’t make good decisions to fix faults and make improvements.
For accuracy, Excel macros are an excellent tool. They will accurately repeat the same actions and print the report the same way every day.
Problems develop when the data put into the macro isn’t accurate. The people inputting the data can, and do, make mistakes. It’s common to miss a decimal place, accidentally insert a new column, or even receive erroneous lab data.
When this happens, your macro will probably still run and generate the report, but there are no checks in place to flag numbers that are out of range. So your reports could be completely inaccurate, and you wouldn’t even know. Excel doesn’t have debugging capabilities, or a way to tell you if an error has occurred.
Which takes us back to the visibility problem. Once you realise there’s a mistake, finding it can be an immense task.
“If you start moving a row or a location where data is actually retrieved from, that macro breaks down. Or, worse still — it still works — but now it’s got the wrong data in the process,” explains Drew.
This is the key challenge with an Excel-based met accounting system. It isn’t adaptive. It can’t tell when you’ve added a column, taken a column away or included a new row.
Small mistakes can propagate through the entire system and distort the reliability of the data you’re using to make decisions.
Accuracy can be much higher using software tools designed for process plant operations. When set up correctly, users get an error notification when they enter a number outside of the normal range. And the same checks are run on the final numbers in reports, flagging the likely sources of the errors.
3. Data reliability
To make good use of your data, the numbers must be reliable. When set up correctly, an Excel macro will reliably give you accurate results.
But, they start falling apart when you encounter unplanned ‘what if’ statements.
For example:
- What if I turn this circuit off?
- What if I change and recycle this stream back around?
- What if we’re only running one scalping screen instead of two?
That’s when you need more complex VBA code.
“As it becomes more complex, it becomes less robust,” says Drew.
Plants change all the time. There will nearly always be something offline, or getting replaced, or not operating within normal ranges.
Spreadsheets are built based on the current state of the plant. When something changes or gets added, it can quickly break. “It can execute code to the letter, but it has no flexibility or resilience to changing conditions,” he explains.
How do you accommodate that? You need to write code with logic branches. I.e., ‘if this is turned off, then this needs to have a trigger, then that sets off this next reaction’.
“You’ll need some pretty fancy logic pathways for it with triggers. But that’s not a common Excel coding skill for most metallurgists,” shares Drew.
In comparison, well-designed process plant management software is set up with complex what-if statements and logic pathways so it can automatically adapt to changes as they happen.
The legacy tool problem
Metallurgists often create and maintain these macros, which are then used for years or even decades. Then, at some point, they leave the business, leaving behind complicated, poorly documented code that someone else will need to modify as the plant changes.
All too often, employees forget a password for a protected sheet or leave the business for a new role and take the document passwords with them. Some operations don’t take data security seriously enough, not realising that a simple lost password could interrupt essential day-to-day activities.
So, what’s the alternative?
If Excel and VBA aren’t reliable for running a modern process plant, what should replace them?
Firstly, there are plenty of situations where Excel is the simplest, most straightforward option. But, if you are repeatedly running into data problems caused by complex macros, it may be time to start looking for better ways to do things.
If you have an existing spreadsheet using VBA you could take the process to the next level and automate it with software. This will give you more transparency and integration into your production reporting, analytics and visualisation tools.
A good starting point is to use software to automate and standardise the things you are already doing. If you aren’t using one already, setting up a data historian such as AVEVA Historian will give you a database of all process, alarm, and event history data.
Next, add downtime tracking software to track downtime events and production losses.
With these set up, you will be able to produce better daily production reports and also be ready to look at introducing a reconciliation engine for met accounting.
There are a range of software tools on the market that pull data directly from sensors, PLCs and control systems to give you real-time visibility without human intervention. Some are more flexible than others.
“You want to look for one that’s adaptable to different sources. And make sure it’s able to import the data from a wide variety of places,” says Drew.
Better analytics can uncover new optimisation opportunities
With modern process plant software, you eliminate many of the visibility, accuracy and reliability challenges created by relying on Excel macros.
You also gain new, actionable insights from detailed analytics software.
One tool specifically built for process plants is Mipac’s MPA software suite.
“If you run the data into MPA, you can access highly insightful analytics. You get new insights into your business that start unlocking more value because you can start seeing things that you couldn’t see before,” says Drew.
“These tools allow the optimisation team to come in, use the analytics, and help your plant. If it’s reliability data, they can ask valuable questions like ‘What is the mean time between failures on these pumps and what is the strategy around managing it?’” he explains.
It offers up new opportunities for improvements when reliability teams can ask things like:
- What is the meantime between failures on these pumps?
- What Trigger Action Response Plan are we going to put in to manage that?
- Is there a metallurgical solution for this that we haven’t thought of?
- Is there a digital way to manage that by changing the control loop or control strategy?
Don’t underestimate the value of being able to visualise your data in new ways. The software tools available now provide more powerful visual graphics than a spreadsheet can produce.
Is it time to address data visibility, accuracy, and reliability in your plant?
Excel macros may have served your process plant well for years, but as the complexity of mineral processing operations increases, their limitations become more apparent.
They are susceptible to human error, which can create inaccurate, unreliable data that is hard to audit and analyse.
When you adopt specialised software tools, you can boost efficiency and production in your plant while simplifying your reporting. And free up time for your team to work on more important things.
Read more about Mipac's software products
As technology continues to evolve at an unprecedented pace, mining companies are increasingly starting to adopt to innovative software solutions to enhance …
In the mineral processing industry, where every tonne counts and operational efficiency determines profitability, Operational Equipment Effectiveness (OEE) emerges as a key metric.
Any complex operation in the mining industry will know the importance of metallurgical accounting. For operations managers and superintendents, accurate tracking of …
