These are projects posted by the students of Dr. Gove Allen at Brigham Young University. These students have taken one semester-long course on VBA and generally have had no prior programming experience

Thursday, December 12, 2013

Quickbooks Report Merger

Executive Summary
A company has two separate subsidiaries that they want to compare side to side, viewing 5 different financial statement reports generated from Quickbooks. The problem is that the two companies are in separate Quickbooks files so users previously had to manually create the side by side reports. I have written the code very descriptively so that it can be changed easily to fit another set of companies trying to accomplish the same thing. The people modifying the code would likely not be experienced in VBA, but will be able to use a written process I create to update the code.

I used plenty of message boxes, file selecting, and file saving procedures to make sure everything would flow smoothly. If the code ever threw an error, it is likely that the user would just throw it out and go back to the manual process rather than try to fix the code, even if it was their own fault.
I changed the names of the sheets and headers to match the appropriate dates and reports. I looped through each of the worksheets pulling in the data for each line item that matched between the source data and the final report.

One of the challenges I had was getting the code to skip lines that I wanted to have formulas in. I wanted to have check figures after the code ran to make sure everything pulled over correctly. I also thought about automatically adding lines that were in the source data and not in the report template, but then thought it would be better for the user to add the lines to make sure the formulas were updated correctly as well and so they knew to update the report template for future application runs. One major challenge was the use of identical names for line items. Usually this happens with sub-section headings and subtotals. Finally, I had to work around the fact that the account titles were never in the same column. Each title is indented a certain degree based on its place in the hierarchy.

Jason Orvin

No comments:

Post a Comment

Blog Archive