Project #4
VBA Scripts
Had to learn:
VBA (I knew a little beforehand, but really small stuff compared to what I achieved for them).
The project itself:
Basically had to rework from scratch relatively big scripts.
The scripts were used to lay out information in a way that then allows the user to print sheets of information.
They had something working, but the execution time would be between 15–30 seconds depending on the script that was called, in a local file on a laptop. On a network hosted virtual machine, it would take 3–5 minutes.
After optimisation, the execution time of all the scripts put together was between under a second to a second and a half, depending on which parts of the script you wanted to execute, on a very low power laptop. It was pretty much instantaneous on a virtual machine, which made it faster where it would usually be used.
The scripts had to be understandable by pretty much anyone working in their office (meaning lots of documentation on stuff you typically wouldn't document because straightforward to developers).
I also made them a better calling system, using a form, instead of the buttons they had lying around in the sheets, each had big “Run this one before running that one, else you'll break everything” warnings.
The form would also disable the use of anything that couldn't happen if something else was happening, preventing human mistakes. That was necessary since depending on what results we wanted, we had to run some scripts and not others, in a particular order.
Of course, as any VBA script made using “Record macro”, it would crash at random times, sometimes meaning you'd lose 10+ minutes of work. That's why I made sure my scripts had a 100% success rate (tested them hundreds of times).
I asked them a few months afterwards for a follow-up, they said the script had not failed a single time and that they were really happy about it.
The end product was:
- Faster
- More reliable
- More user-friendly
- And cleaner both in execution and code
I have to admit being particularly proud of what I managed to make, especially using a language so different from what I'm used to, and also considering everything I had to take into consideration for them.
More about this project
- Made using: VBA
- Visual Basic for Applications