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!