VBA Code Behind Report

Once the theory and controls, as described in the previous paragraphs, are understood, the only thing that remains is to examine the code itself, which resides in the Report's code module. The code is designed to use a module-level variable to maintain the running balance. At points throughout the report execution, the variable is either incremented or displayed.

In this example, all of the procedures for this code are displayed as one segment. It may appear overwhelming to a new developer, but understand that this is one of the code views that can be selected when developing in the VBA environment. (The other view displays only one procedure at a time.)

The example procedures are arranged top-down in the order in which they are executed after the report is opened. This is not required, but has been done to add to assist with reading comprehension. VBA is an event-driven language and does not require any specific order to the procedures. (Some procedural languages require that called procedure occur prior to the calling procedure.)

The final benefit of viewing this code in its entirety is that it demonstrates the importance of having clean, readable, and simple code. It is important to understand that programming does not stop when the functionality is complete. At some point, either you or another programming will be required to read, enhance, or worse, fix your code. As you view the code example, take note of the subtle difference that it makes when techniques such as a naming convention, indentations, and readable comments are included.

'Create a variable to hold the running balance. Dim mdblBalance As Double

Private Function GetStartingBalance() 'Get Starting Balance

'Could be obtained via other dynamic methods GetStartingBalance = 1234.56

End Function

Private Sub Report_Open(Cancel As Integer)

'Initialize the running balance to previous balance mdblBalance = GetStartingBalance

'Set the starting balance in the Report Header txtBalance_Starting = mdblBalance

End Sub

Private Sub Detail_Print(Cancel As Integer, _ PrintCount As Integer)

'Increase the balance by the current transaction amount mdblBalance = mdblBalance + txtAmount

'Set the balance value to the new running balance amount txtBalance = mdblBalance

End Sub

Private Sub ReportFooter_Print(Cancel As Integer, _ PrintCount As Integer)

'Set the ending balance to the final balance amount txtBalance_Ending = mdblBalance

End Sub

In reviewing this code, the first thing that you should notice is that there are no more than 10 lines of actual code that you would have to write. All of the procedure headers and footers are created for you, and without the inclusion of developer comments, the coding effort is minimal. It is important to note that many facets of VBA do not require hundreds of lines of code, when designed correctly. In this case, the mixture of a few controls with a little bit of code offers a significant amount of functionality. If you find that you are coding with significant more effort than this, then you may want to rethink your methodology.

The code in the above example starts by declaring a module-level variable to hold the running balance. The variable must remain in scope throughout the life of the entire report, or else the running value would be lost. There is no need to declare the balance variable as Public (or Global) unless you need to refer to it outside of the report.

GetStartingBalance() is a procedure that is exposed to all other procedures within the report module. It is there to retrieve the previous ending balance from perhaps the last month. There are multiple paradigms that could be used for implementation, but for this example, a hard-coded value is supplied. In practice, you could store the previous balance in a table, or calculate it at the time the report is executed. It really depends on what functionality is supplied to the user when executing the report. For example, can they select a date range to print the report, or is it hard-coded by month?

The Report_Open() procedure executes one time when the report opens. In this example, the balance variable is initialized during this procedure, as it only needs to be initialized one time. You may have an example where a running balance needs to be reset at certain times throughout the execution of the report. If this is the case, then the variable initialization would need to occur in an event procedure that is executed multiple times throughout the generation of the report. An example of this would be a group header or footer. Also, the starting balance text box is populated with the initial value.

The processes of incrementing the running balance and displaying it on the report are both executed from the Detail section Print event procedure. Both of these steps are performed here, because at the time the value is computed, it needs to be printed. Separating these two steps between other event procedures can lead to the balance being increased at undesired intervals, and cause the wrong value to be written on the report.

The last section that is printed for a report is the Report Footer. This is commonly used for printing grand totals. For example, a sum of the Amount field would net the sum of the detail items for only the current report. In the example, the ending balance is displayed in the footer. This could be as a convenience to the user, or by their request.

0 0

Post a comment