VBA Player Piano: Pt II

Our staff accountants have to account for specific types of revenue every month. This involves 9 separate tabs, each pulling data from different sources (other spreadsheets, database dumps etc), each with variable amounts of data. Luckily pivot tables and a variety of secondary sheets and formulas were set in place help provide an architecture and dataset to filter . There was still a large manual process involved with aggregating these tabs of data, cleaning them further and preparing them for journal entry upload.

I didn't know what these spreadsheets meant exactly, but I did see the forest over the trees. I asked questions about the end result the team wanted. I tried to look 5 steps ahead and anticipate what would be needed then backtrack and map things out. Before I knew it I was hacking away at some code and design for formatting details.

My first instinct was to lay the groundwork in the spreadsheets so anything that could remain as a default template would be pre-loaded, thus never having to write code.

Accounting had one too many permutations of classifications and encodings so I made isolate what we needed. One of the nine tabs had the least amount of average monthly data so I used it as a test case to play around with my code. The scaffolding was as follows:

- declare row counts and worksheets
- assign the current file name based on dynamic criteria
- check if upload template file is open; if not open it
- set last row variables at crucial points to exit the loops precisely
- fill in relevant line descriptions based on criteria checkboxes

All of this code in VBA was simply "taking the tools out of the toolbox" prep-work necessary to have the the rest go smoothly.

Since this project involved the data spreadsheet and an upload spreadsheet I wanted to be able to interact between the two as soon as the data was gathered. I found a nifty custom function on the excel forums which would check if our template file was already open.

Function IsFileOpen(FileName As String)
    Dim iFilenum As Long
    Dim iErr As Long

    On Error Resume Next
    iFilenum = FreeFile()
    Open FileName For Input Lock Read As #iFilenum
    Close iFilenum
    iErr = Err
    On Error GoTo 0

    Select Case iErr
    Case 0:    IsFileOpen = False
    Case 70:   IsFileOpen = True
    Case Else: Error iErr
    End Select

End Function

I tested it a few times as in my experience, when multiple files are involved, things can get confusing fast. I didn't want the end-user to have to think about anything. Just sit back, relax and go home on time.

With this function in place, I could make the call to skip ahead to the relevant code, otherwise open the file and continue.

As I had 9 tabs with uniform formatting, I didn't want to replicate this code nine times and put a button for each tab. So I looped through every sheet and ran the same formatting so the before/after looked indistinguishable from the manual process.

There was still some junk data after filling in line descriptions and criteria, so I had to identify all the exceptions and delete them before adding to the template.

Formatting 9 tabs in a 15MB excel file wasn't bad . The next step was to open the upload template (with its own set of internal macros that I couldn't touch), dynamically resize the rows to account for the desired entries, copy the values and paste them into the upload template and then sum the values dynamically.

Initially I tested it for each tab and added a template button that would clear all the rows except for one so I could begin the process over for the next tab. This proved frustrating as the upload template had previous formatting that made dynamic resizing difficult. Yet I couldn't modify this template file further because it interacted with our internal database based on the formatting our consultant created. Luckily I got the OK to be able to upload all 9 tabs at the same time into this template, meaning I only had to resize the rows and populate the necessary data once, thus preventing the need to have to delete rows and resize more than in one instance.

After countless iterations of testing (I didn't find the logic too difficult to implement, most of the frustration came from little formatting errors that I felt I was playing whack-a-mole with), I had a functional code set that allowed me to:

1 - Manipulate 9 tabs' worth of accounting data
2 - format and filter them based on requested criteria
3 - aggregate the values of all 9 tabs to a master data dump sheet
4 - copy those values into the upload template, dynamically resizing the parameters as necessary

It felt good creating the whole thing from scratch. When my colleague mentioned that one of those tabs has to be uploaded alone, I split the module into an 8-tab and 1-tab version where the split occurred only when calling which sheet to modify. The code thus remained the same, I just added an extra button, and 2 sub modules for the buttons to refer to the main module separately

From there all the accountant would have to do is save their desired upload template where they wished and then proceed to upload their journal entries.

After all was said and done, a 1-2 business day process was reduced to less than an hour. I haven't heard any complaints since so I'm assuming everything is going smoothly.

With that, I landed back in proverbial hangar to continue reading my newspaper. The next day my immediate colleagues got jealous about my month end automation for a different department. But that story's for another day...