VBA full code reporting

Introduction

In the last post we created a weekly report of the most common visiting bird species for the current week, and wrote the VBA code to insert the correct picture in the right place and at the right size. The rest of the report and its formatting already existed in the REPORTS sheet of our Garden Bird Tracker. In this post, we're going to code these elements, and add a means of creating the report, and then clearing it.

Report Properties

The first step in creating our code is to use the Immediate Window (IM) to get the formatting and text values of the report as it currently exists in the REPORTS tab. We have three main elements: the header, the footer, and the border, each of which has a range of properties and or values:

Header: text; font name, size and color; background color; initial cell; merged cell range; vertical and horizontal alignment.

Footer: formula; font name, size and color; background color; initial cell; merged cell range; vertical and horizontal alignment.

Border: position; style; weight; color.

In the IM, I've entered the following queries, one at a time, pressing enter at the end of each line:

Note the reference to cell L7, and not the merged range.

At this point, you can leave the results in the IM window, or paste them into a document and clear the window (I often store pieces of code in Word to free up some space in the coding window). The next stage is to retrieve the footer and border information in the IM:

Note that I haven't queried the font color, as this is the same as the interior color of the header.

The last thing we need is merge ranges for the header and footer: L7:P8  and M15:O19 respectively. 

Adding the VBA code

In the VBE we can now edit the code we built in the last post. For the header and footer, I've added this code directly after the insertion of the bird species picture:

The formula in the footer is too long to fully display in the IM, so I've pasted it into a Word document to make it clear. Note the whole formula is contained in quotation marks, and that the text portions are surrounded by double quotation marks:

Note that immediately after the formula is entered in cell M15, the next line of code converts it to the value of its result. This removes the formula and leaves the static text and ensures that the text does not dynamically update if new data is entered into the tracker, which would lead to a mismatch between the text and the picture unless the code is run again.

Each block of code contains the merge and alignment commands directly after the text formatting.

At this point, we can delete the existing report in the REPORTS tab, and then run the code; this is what we find:

On Action Assignment

Now that we have the report fully coded, we can add some controls to run and clear the report. The first step is to create a new VBA module that will delete any existing report when it is run. It is effectively a reversal of our report module, and will remove each element. I've called this module

Sub CLEAR_WEEKLY_REPORT(), and this is the basic code:

We now have a creation module, and a deletion module, so we're going to use the onAction command to make them work together by assigning each to its own control. I've added this picture, which I've named "IN", to my REPORTS tab 

Then I've right-clicked it and assigned the INSERT_PIC macro to the image; this will now create the report when clicked. I've then created another image named "OUT" with slightly different text and added that to the REPORTS tab:

I haven't assigned any module to this image at this point; instead, I've used the IM to find the exact position of each of these images, and added this code to the INSET_PIC module: 

This code inserts the picture "OUT" in the precise position above the report, and then, using the onAction command assigns the CLEAR_WEEKLY_REPORT module to the image; it then deletes the "IN" picture (which triggered the INSERT_PIC module). Finally, cell V1 is selected to move away from the main report area.

I've then added the following code to the CLEAR_WEEKLY_REPORT module, which works in reverse:

This code will put the "IN" picture back in its original position, reassign the INSERT_PIC module and delete the "OUT" picture. The final result looks like this, starting with no report, then with the report generated, and then after the report is cleared:

I hope you enjoyed this post - if you have any questions or need any help, let me know.

In the next post: working with Text in Visual Basic!