Manipulating text with VBA

Introduction

In the last post we added more code to our basic report module to fully automate it, created a second module to delete the report, and then added custom images to call each module. In this post, we're going to look at manipulating text using VBA.

Spreadsheet Text

Working with text in Excel presents some limitations on the format of strings contained in cells or ranges of merged cells. For instance, if we look at the example of the text in our weekly report:

I am able to change the entire text appearance using the Font group in the Home tab of the ribbon, but not an individual character or number of characters. I've used the Immediate Window (IM) in the Visual Basic Editor (VBE) to change the species name to bold font:

Here's the line of code in the IM, after which I've pressed enter:

Character 40 denotes the start character number, and 9 the number of characters to apply the format to.

Dynamic Text Manipulation

 As our report is dynamic and could include any species with any number of characters in its name, we need to make the selection dynamic, and add it to our report creation module. We can achieve this by using the LEN function and a variable we have already set up - the INDEX,MATCH and MAX formula to retrieve the correct species name, which is TT and has the dimension of a text string:

It's essential that the code to format the text comes after the conversion of the formula in cell M15 to the value of the result, so I have grouped the new variable declaration and value directly afterwards:

The variable L has the dimension of an integer, and its value is the LEN (length) of TT, which is the dynamic look-up of the species name. Whatever species is the most common visitor, TT will select the correct name, and L will count the number of characters in the name.

The last line of code selects Cell M15, and the string of characters it contains. The start of the species name will always be static at position 40; the number of characters to format is then determined by the variable L. After adding the code, I've tested the report by updating the species count data and running the code again, this time getting a different result and correctly formatted:

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

In the next post - VBA and Pivot Tables!

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!

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!

Introducing VBA Coding

Introduction

In previous posts we have concentrated on spreadsheet functions and formulas, charts and pivot tables to calculate and present the data we have collected about our garden bird visitors. In this post we're going to begin some Visual Basic for Applications (VBA) coding to automate aspects of our Garden Bird Tracker. 

VBA and the VB Editor

VBA , or Visual Basic as it's widely known, is a programming language developed by Microsoft based on Basic language. It uses a combination of graphical and script elements, and is relatively user friendly. In many cases, it is possible to use the built in Macro recorder in Microsoft applications to build the VBA code for a task, and then save it for future use. Before we look at a recorded Macro, we need to add the Developer Tab to our Excel ribbon, and then look at the Visual Basic Editor (VBE).

In your open spreadsheet, click on file and then select Options from the drop-down list. From the sub-menu select Customise Ribbon and then tick the checkbox for Developer under the Main Tabs option:

Returning to the spreadsheet, we now see that there is a Developer Tab available:

We now have access to the VBE (first option on the left), and direct access to a list of any available Macros (second from left). Opening the Visual Basic option takes us into the VBE:

The right section of the window shows what modules of code (individual macros) are available; the large left are is where code can be written, edited or viewed. If you record a Macro, the underlying VBA code is generated for you; alternatively, code can be built from scratch in the VBE. For example here's a Macro recorded to select cell A1 of a spreadsheet and apply a red fill, step by step:

1 Start recording Macro

2 Name the Macro

3 Fill cell A1 in red, stop recording and then run macro again

4 View generated code

Opening the VBE and selecting the recorded Macro shows us the code that has been generated. Some of the code that has been generated is not necessary, so I've edited it in the coding window to the absolute minimum:

The Macro recorder can be very helpful if you have a complex formula that you need to convert into its coded row number and column number (R1C1) format (for instance our multiple look-up array formula); this what the spreadsheet function and coded forms look like:

As you can see, attempting to write this formula in R1C1 format would be complicated and prone to errors.

However, there are many instances where direct coding needs to be used, as in the example we are going to look at next.

Last Row

In our spreadsheet we are going to use some VBA code to go to the next available line with one click. The first thing to do is open the VBE, and then insert a new, blank module:

In the coding window, we can start to build our VBA by naming the module as Sub GO_TO_LAST_LINE()

 The name defines this a Subroutine that can apply to any spreadsheet.There can be no spaces, so the words are separated with underscores, and the name always ends with (). The code starts directly below the name and is broken down as follows:

DIM LAST AS INTEGER. DIM is an abbreviation of Dimension, and defines what type of information LAST is; we are stating that LAST is an INTEGER (whole number), because it is going to equal the number of the last cell in  column C of our spreadsheet that contain values. 

LAST = ActiveSheet.Range("C50").End(xlUp).Row This line assigns a value to LAST by stating that LAST equals the number of the last row in column C that contains data by starting at cell C50 and going up to where the data ends.

MSGBOX LAST This line will display a message box showing the last row number (the integer value of LAST).

END SUB is automatically generated and must be the last line of code.

Running the code by selecting the Macros tab in the ribbon, and then our module name and Run, we will see this:

Now that we know our last row, we can add some more code to select the last row plus 1 and add a value to that cell. The value we will use is the text "CURSOR JUMPS TO LAST LINE". Here's the code:

 And when the code is run, this is what we see:

Two last things to do - assign the macro to a trigger, which is the picture of the bird with a call-out saying "CLICK ME FOR LAST LINE". To do this, insert the picture and then right click inside it. From the drop-down list, select Assign Macro, and then select the named module we created and click OK. When you click the picture, the code will run.

And finally remove the lines of code in green below for the message box, and the value assigned to the row after the last:

The code will now select the last row without a message or anything being written into the cell. Note that in this example, the LAST range is set at C50; increase this to a value that you don't think your spreadsheet will reach, i.e. 5000 or 10000 so that the count doesn't stop in the middle of your existing data.

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

In the next post we're going to look at some VBA that will insert a picture into a report based on a LOOKUP function!

ARRAYS Part 4 - Dynamic INDEXING

Introduction

In the last post we looked at data validation drop-down lists to provide criteria for a multiple INDEX and MATCH look-up array formula, and then formatting the output as a string of text. In this post, we're going to reverse the look-up criteria to an unknown variable - what and when was the most common species visiting in a week - using the MAX function in an INDEX array formula.

Table 1

In table 1 of the DATA tab, we have a series of values for species by day of the week, which is arranged as a two dimensional array (or range), i.e. down a range of rows and across a range of columns:

We are going to find the day of the most common visit by one species, and the name of the species. The first thing to determine is the maximum value in the range B2:H10; using the MAX function across the entire range, we get a value of 77:

We can see that this is the Robin on Thursday in the table above, with the value in row 8 and column E. Note that for clarity, I've populated the table with random values using the RANDBETWEEN function; the real data has many duplicate values and makes these examples less clear to demonstrate.

Dynamic INDEXING

In the last post, we used match to provide row or column numbers for the INDEX function; for this example, where the maximum value could be in any row or column of our table, and will also change as more data is added, we need to use the ROW and COLUMN functions to pass values to INDEX. In the example below, we are going to find the species that corresponds to the maximum value in the whole table:

The INDEX defines the result range as being in column A (A2:A10). To find the correct row, we are going to use the same technique to coerce a numeric result that we used in the last post. Bearing in mind that we are using the IF logical test to ask if any of the data range equals the maximum value in the same range, and that the virtual array will return a 1 (TRUE) or 0 (FALSE) result, we can use the MAX function prior to the logical test to pinpoint the highest value. This works because the maximum value in the virtual array will be 1, and all the other values will calculate to 0. We then use two ROW functions to return the correct row number for the INDEX function. By subtracting the ROW at the top left of the numeric range (B2) from the ROW of the entire numeric range (B2:H10) and then adding 1, we define the virtual range that the array formula will return a value from. 

To find the day of the week, we create a horizontal index of the days of the week (B1:H1): 

We then use the same MAX and IF logical tests as before, and this time subtract the first COLUMN from the entire range of columns and add 1. We now have all three elements of an extended array formula, which we can use with strings of text in a formatted output. Here's the result, which is dynamic and the result will change when more data is entered in the table:

I hope you enjoyed this post - if you have any questions or need any help, please get in touch

In the next post, an introduction to Visual Basic with a quick way to navigate to the next row in a our active tracker!