Many offices use Microsoft Excel not just for number crunching, but as a database, scheduler, project management tool, and even a word processor.

Its ubiquitous use can unknowingly be a bottleneck to business processes, despite its apparent usefulness. Excel excels (haha) at summarizing data - that's the main purpose of spreadsheets; however, Excel is often used for every other office process because it tends to be one of the first applications office workers learn to use, and it's easy to adopt to other purposes other than number crunching.

A common problem Bulletproof Solutions solves is automating business practices; that is, something that was done manually, taking hours or days, can be automated down to minutes. The overuse of Excel is a prime example.

Almost anything done manually using Excel can be automated. Anything.

All Microsoft products support a macro language called VBA - Visual Basic for Applications - since 1993. It's an intermediate language with some object-oriented features that can be used to automate most functions of Microsoft Office products. It can be used to customize an Office product such that you wouldn't recognize it as an Office product.

For example, say part of your monthly tasks is to import a CSV file into Excel, which contains empty rows. Those rows need to be deleted before processing the spreadsheet further. The following VBA code will delete all empty rows in your spreadsheet (there are multiple ways to accomplish this):

Sub DeleteEmptyRows()
Dim i As Long
For i = UsedRange.Rows.Count To 1 Step -1
If WorksheetFunction.CountA(Rows(i)) = 0 Then
Rows(i).EntireRow.Delete
End If
Next i
End Sub

An Excel Add-in is a special spreadsheet that includes VBA code that can automate most Excel functions. It can be configured to start when Excel starts, creating buttons or controls to access custom functions specialized for certain tasks.

For example, a button or menu item could be created to access the DeleteEmptyRows function above with a click of the mouse.

Bulletproof Solutions has developed custom solutions to automate Excel (and other Microsoft Office products) tasks, creating add-ins or macros that save effort and time. We have also evaluated business needs of clients, often recommending and developing solutions to move customers away from using Excel if it's appropiate.

Future posts here will demonstrate Excel VBA tips and tricks. Stay tuned!