Writing Custom VBA Reports in Microsoft Excel

Screen-Shot-2018-11-25-at-9.02.22-pm.png

  • VBA Reports returns a macro-enabled Microsoft Excel file containing Sale, Purchase, Payment, ExpenseTransfer, Category and Time Tracking data 
  • Once downloaded, the data can be manipulated using Excel's inbuilt functions to generate custom formatted reports
  • This feature is only for users familiar with VBA and advanced Microsoft Excel features - we will not document these as they are extensively documented and tutorialised elsewhere online.  Here's a link to a starting point on what VBA can do and how to use it if you are unfamiliar.
  • The reporting data is populated into the following named sheets:
    • Sheet kbPaymentsData
    • Sheet kbExpensesData
    • Sheet kbSalesData
    • Sheet kbPurchasesData
    • Sheet kbReversedSalesData - where reversedDate in range
    • Sheet kbReversedPurchasesData - where reversedDate in range
    • Sheet kbRelatedPaymentsData - Payments for kbSalesData sales in the given date range, regardless of the paymentDate
    • Sheet kbRelatedExpensesData - Expenses for kbPurchasesData purchases in the given date range, regardless of the paymentDate or completion status
    • Sheet kbRelatedRefundExpensesData - Refund Expenses for kbSalesData sales in the given date range, regardless of the paymentDate or completion status
    • Sheet kbTransfersData
    • Sheet kbStockAdjustmentsData
    • Sheet kbFloatAdjustmentsData
    • Sheet kbBankAdjustmentsData
    • Sheet kbTimeTrackingData
    • Sheet kbCategoriesData
  • Two additional sheets exist which also include INCOMPLETE Sales and Purchases.  These are intended for use in  reporting on upcoming orders or orders due, not for reporting actual sales or sale amounts
    • Sheet kbSalesIncIncompleteData
    • Sheet kbPurchasesIncIncompleteData
  • It's possible to attach a Template excel file to the report containing pre-written Excel macros, so that generated reports contain those macros when they are exported, rather than having to add the macros to each exported report.
  • Your own sheets should not be named as above to prevent them being overwritten
koalaVbaFunctions.bas
  • We have written a number of custom reports for clients and have gathered some helpful functions to speed up the process.  We provide these functions here to help you get started with your own custom reports - see the sidebar to the left of the screen for a download link
  • Import or copy/paste the contents of this file into your own project as a module to make it available for your use
Tips
  • Referring to data by column letter/number is strongly advised against - lookup the column by it's header name - as column position may change in a later version, breaking your references.  koalaVbaFunctions includes helper functions for looking up columns by the header name in the first row
  • Macros included with your VBA report template will be retained in the exported version, but any layout elements (colours, backgrounds, buttons etc) will be purged.  If you wish to include these in your report, they must be added programatically in the VBA macro so that they can be created after the report has generated.  This is due to the complexity in handling legacy Excel layout elements.
  • In sheets such as kbSalesData and kbPurchasesData, a sale or purchase row will repeat for each additional tax or discount row after the first tax and discount rows - that is, each additional tax or discount row will include a copy of it's parent.  This is to allow multiple tax and discount row records to be included in the flat excel format.  Our helper functions include functions to de-duplicate data - usually by it's UUID column.
  • Reversed Expenses/Sales/Payments/Purchases are included in records - nothing is excluded
  • Date range filters by Payment/Expense date, not the date of the creation of the record.  Sales/Purchases are included by their completion date, not their sale/purchase date.
Back to top