Visual Basic for Applications (VBA) is a programming language that comes bundled with Microsoft Office. Using some VBA code, we could easily extend any Office applications (e.g. Excel, Word, PowerPoint, Outlook, Access) to achieve a quantum leap in work productivity!
Picture this. Would it be possible to build an integrated and automated solution to query data from a Access database, import the data into Excel pivot tables to construct graphs, copy these graphs into a Word or PowerPoint report and finally, email the report via Outlook at a specified future date and time? Yes! This entire solution can be implemented using just VBA. Imagine the amount of time saving that could be achieved with this solution!
VBA is indeed the undisputed Productivity Gem for Office automation!
Below is a simple demo of VBA automation. The objective is to reformat an un-pivotable report to become pivotable (see image below).
Write a VBA macro ReformatData() to perform the following 3 tasks (code listing shown below) –
- Remove all rows containing the word ‘Total’
- Populate all blank cells in columns ‘Country’ and ‘Customer’
- Transpose all the year and revenue data into two columns ‘Year’ and ‘Revenue’
It will take a long time to do the above 3 tasks manually for a report containing thousands of rows. The macro ReformatData() completes the 3 tasks in mere seconds!
IntegrateDots offers consulting services and training on VBA. Click here for participant’s feedback on our VBA workshops.
For more information on VBA consulting or training, please email info@integratedots.com.