In excel, there is a function named PROPER, which takes text as an argument and formats it so that the first character of each word in the text is capitalised, it works like this:-
=PROPER(“this is my example text”)
and the result is:-
It’s a pretty useful function. I wanted to carry out similar text formatting in a VBA routine I’d written in Access, but didn’t want to reference Excel and then use Application.WorksheetFunction to call it. That’s where the incredibly useful StrConv function in VBA steps in.
StrConv looks like this:-
StrConv(string to convert, conversion type)
The conversion type argument is one of a selection of constants which carry out various text manipulations. Here’s a list of them and what they do:-
vbUpperCase – converts the entire string to uppercase
vbLowerCase – converts the entire string to lowercase
vbProperCase – capitalises the first letter of every word in the text string
There are some others, but I’m not going to discuss them as I’ve never used them.
So..to convert our text string using VBA, it would look something like this:-