HC&TS and the power of VBA

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 the text (csv) files, spreadsheets (from other departments or companies), data from other sources, you name it.

You sit down and start the all manual routines like importing text (csv) files, spreadsheets, maybe you have several recorded macros that you edit before you can run them to help you with your tasks.

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

Well, your boss, colleague or colleagues take a look, click here or there and … @#$%@xx!! … one or more formulas are gone, and the UNDO button doesn’t do the trick; maybe you have a backup file (which I always advice) and the damage is not that bad.

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; the entire application will do just what you do manually, you enter all the settings like where the source files can be found, maybe even run SQL scripts to retrieve data from other sources, create pdf files for those worksheets you want to mail 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.

Of course, programming with VBA is time-consuming and costly but  once the application is up-and-running the process will probably take no more that several minutes and it’s a one-time expense with the benefits of support and all the written code is also made available with a detailed explanation; my advice is to have someone inside your company that would be interested in VBA programming 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

Taking it a step at a time

Step 1:

My intentions are share VBA knowledge, especially with Excel and answer any questions anyone working with VBA for Applications may have.

I hope that I can make this work.

Time is the essence and imagination is what will limit you.

“IT” Always crosses your path…

 

Taking it a step at a time