HC&TS and the power of VBA

hc&tsLeave a comment

If you are the person responsible for your company’s monthly/quarterly (finance, progress, workload, etc.) reports and work with Excel maybe HC&TS can help you.

You know your way around Excel and have built your own spreadsheets with formulas, graphics, the whole shebang.

When the time comes; you block your calendar for a day or three and start collecting all the data you need to prepare the reports; collect all (text, csv) files, spreadsheets (from other departments or companies), data from other sources, you name it.

Once all the data is there manual routines like importing text (csv) files; spreadsheets; maybe a recorded macro or two that you need to edit before you can run them to help you with your tasks…

After one or more days, your report is complete, you save it under a new name, protect the worksheet(s); maybe even the file; make it available to the necessary departments, mail one or more specific worksheets as an attachment; create a compiled Word file with all the relevant data for that month or quarter and… you’re done.

Boss, colleague(s) take a look, (accidentally) click here or there and … @#$%@xx!! … one or more of the used formulas are gone! UNDO button doesn’t do the trick; maybe there’s a backup file (which I always advice) so so the damage isn’t all that great.

Breaking down all the costs for these reports based on your salary by the hour; you might find out that this is quite a burden on the company’s monthly expense account.

Here is where HC&TS can lend a helping hand; using your own file(s) design and write a module that does all the tasks at the click of a button; if you prefer to have non-macro embedded files, no problem, a separate Excel application can do the job. A custom-built application can replace all your manual tasks; you just enter  the settings and requirements (sourcefile location, run a SQL script,  retrieve data from other sources, create pdf files (to be mailed as attachments) and even compile the basic Word file with the data for the requested period where you only have to edit/add your own story for that period.

Programming with VBA is time-consuming and costly but  once the application is up-and-running the process will probably take no more than a few minutes; it’s a one-time expense with the benefits of support; all the written code is made available with a detailed explanation. An in-house programmer for fine-tuning is advisable so that he/she can carry out the in-house maintenance, HC&TS can assist, train and guide the programmer to take it over.

VBA is a very powerful tool; it’s only limited by the programmer’s knowledge of the syntax and imagination designing routines and scenarios. The most important ingredient is time; lots of it; troubleshooting takes up a lot of it!

Next to VBA there are Add-In tools like Power Query and Power Pivot; Power BI for reporting.

Hans Hallebeek
HC&TS, 2020
“IT” Always crosses your path…

HC&TS and the power of VBA