VBA Part 2 - Variables in Automated Reports

Introduction 

In the last post we looked at some Visual Basic code that found the last row of data in our Garden Bird Tracker and activated it. In this post, we're going to use declarations and variables to create a snapshot report of the week's most common visitor, and then add some code to activate text-to-speech and give us a commentary of the report. I'm also going to introduce the Visual Basic Editor (VBE) Immediate Window - an exceptionally useful feature when writing code.

The Report

The report we're going to create is initially going to be partially constructed in the REPORTS worksheet. This is what it will look like:

The new report appears to the right of our existing chart, and tells us which was the most common visiting species during the current week. The green title bar is simple text, whereas the grey text containing the information is a formula that we've used before, and is written into cell M15:

The result of this dynamic formula will change from week to week depending on the number of each visiting species, so we need a way of automatically updating the picture with VBA code. The first step is to add some additional information in our DATA sheet - an abbreviation of each species name, which we are going to use as a look-up reference for the picture of each species:

All of the images in my Garden Bird Tracker are stored in a dedicated folder and have a naming convention of two letters, and a .png format - i.e. WP.png is the WOOD PIGEON, as seen in the report. Using INDEX, MATCH and MAX, we can find the most common visitor in the right column, and return the relevant picture's name in the left column. It's important to ensure all of the pictures are in the same format,( .jpeg, .png, etc.) as the file format will be used in our code. Once we have the picture lookup references added, and the pictures stored, we can open up the VBE and start constructing the code to insert and format the correct picture. 

Declaring and Setting the Variable Values

When writing VBA code, we need to declare - or specify - exactly what object of information a variable is - is it text, a number, a picture, a chart, the value in a cell, etc.? Declaring the variable incorrectly will often result in a "type mismatch" error when the code is run; for instance, if you declare MyPicture to be the value of cell A1 and then try to reference MyPicture and change its properties, it will throw up and error. We are going to use two types of variable, a picture and some text: we set up the variables by declaring their Dimension, which is abbreviated to DIM. An example would be to declare MyName to have the dimension of text: DIM MyName as STRING; another would be to decalre MyPic as a Picture: DIM MyPic as PICTURE. You can use any descriptive name or abbreviation for a variable, as long as it's not a reserved word used in the application. So after our opening SUB INSERT_PIC() module name, we add four lines of code:

Line 1 stops the screen updating until the code has executed and stops screen flicker (this line is optional)

Line 2 defines BI as a Picture (BI stands for Bird Image). This will be the most common visitor.

Lines 3 and 4 define PS and TT as strings of text. (PS will be the two letter picture name returned by a formula; TT  is the value of a second formula that we will use later.

Having given these variables a dimension, we then need to state what value they have. We know that BI is the picture of the most common visitor, so BI's value will be the result of the lookup formula to find the pictures name. The formula is written in VBA format below; the syntax is Application.WorksheetFunction.FunctionName, for each element of the formula:

Highlighted in yellow I've set BI to insert the picture "PS" in the active sheet, and specified the files location. Note the quotation marks ("") around the first part of file path, the break and ampersand to incorporate the variable PS, and then another ampersand to connect the file format in quotation marks. The value of PS can change to any of the species' pictures, but the correct picture will always be inserted.

The final piece of this part of the code renames BI as BS; whatever picture is inserted can immediately be referred to as BS. Before we go any further, we can test run the code by pressing F5 or the run button in the VBE. What we find is that the code has inserted the correct picture, but in the original size and at the default location:

We are going to correct this by using the Immediate Window in the VBE to find the picture's properties, and then use that information in our code.

The Immediate Window

The Immediate Window (IM) is an extremely useful feature of the VBE, and allows strings of code to be run without being written into a module, and for information about the active sheet to be retrieved. In the VBE, press Ctrl+G or select View and then Immediate Window, and a new window appears below the main coding window:

To use the IM to execute commands, enter the code and press enter. In the following example,  I’ve entered these four lines; the commands select cell N23, puts a value of 10 in the cell, gives it a red interior fill, removes the value and then removes the interior fill. I’ve placed the cursor at the start of line 1 and pressed enter; the cursor moves to the start of line 2, and I’ve pressed enter again:

 Looking at the REPORTS sheet, we see this:

Back in the IM, with the cursor now at the start of line 3, I’ve pressed enter twice; back in the REPORTS sheet, cell N23 is as it originally was.

The IM is great for getting information about objects in a worksheet – for example, a count of the number of pictures in the worksheet, or the number of items in a pivot table field. The following example returns the formula in cell M15 in R1C1 format. Note that the line is preceded by a question mark (?) to define that it is a query and not a command: 

Before we use the IM any further, we’re going to go into our REPORTS sheet, select the picture we’ve inserted, and then we're going to drag it into the size and position that we want in our report. Once it’s in position, we can go back to the VBE and then use the IM to find its exact position by entering the following queries, one at a time, and pressing enter each time:

We now have the exact top and left positions, and the exact height and width for our picture, which we have named BS at the insertion stage. In the main coding window, we can select BS and then say “with this selection, the top is here, left is here…..” Here’s the code, using the positions returned in our IM queries:

Note that a WITH must have an END WITH after the block of code for the selection. In the REPORTS sheet, delete the existing picture, then test run the code again; the result is the right picture in the right place and the right size.

Talking Text

One final thing we’re going to do is make a couple of variable declarations, both of which are strings of text:

With these new variables, we are going to use a line of code to execute text-to-speech to read out the result of the formula in cell M15, the words “HERE IS A PICTURE OF THE” and then the value of TT. The code used is at the end of the module, all of which is shown here:

Now when we run the code, we get a commentary and a picture, both of which are dynamic.

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

In the next post, we’re going to incorporate the whole report into our code so that it only appears when requested!