If you think about almost everything you do in Excel, it involves some form of manipulating numbers or text. The same is true with VBA.
In this post, I want to show you some simple manipulations of text in VBA .
When folks are asked to type text, there is usually no way to know how long or short the text is going to be. If we really want to, we can put rules and restrictions to control the text. But, there is only one variable type.
For all text, we will use the variable type = String
How to name your variables
In many institutions, it takes as long to determine what to call your variables as it does to write your code.
For this post, I am going to use a simple modification to camelCase
Since we are only going to use types String, Decimal and Long, the first character of the variableName will be lowercase s, d, or l (string, decimal or Long). Followed by the name of your variable, starting with an uppercase for every word.
i.e. is you are creating a variable to hold a string containing someones first name, it would be sFirstName
Some built-in functions that are useful
' Mid(string, start, [ length ]) ' You pass a string to the function, then specify which character you want to start at then optionally how many characters you want to have returned. ' examples ' Mid("farting",2,3) will return "art" ' Mid("farting",1,3) will return "far" ' Mid("farting",5,2) will return "in"
' UCase(string) ' You pass a string to the function, it will return the same string with every character in UPPERCASE. ' examples ' UCase("steemit") will return "STEEMIT" ' UCase("bob") will return "BOB"
' LCase(string) ' You pass a string to the function, it will return the same string with every character in lowercase. ' examples ' lCase("Steemit") will return "steemit" ' lCase("BOB") will return "bob"
Time to write a little code
Open a blank Workbook in Excel
Open the VBA Editor and create a new Module
If you copy and paste this block of code into your VBA Editor, the colour coding will make it more readable.
' This line will tell VBA to check your work ' It will force you to always declare your variables Option Explicit ' A Function is used when you are going to pass it a value (or multiple values) ' The function is going to do something with the values you passed to it. ' The function is then going to return a result based on whatever you programmed it to do. ' You can call functions from within Excel Cells Function ProperCase(sInput As String) As String 'This function will take the value you pass as sInput, make the first letter uppercase ' and make the remaining letters lowercase. 'Declare a variable to hold the first letter of sInput Dim sFirstLetter As String 'Declare a variable to hold the remaining letter of sInput Dim sRemaining As String 'Declare a variable to hold what will become your result Dim sResult As String ''''' 'Take the first letter of sInput and put it in sFirstLetter ''''' 'start from the first character, and grab one character of sInput sFirstLetter = Mid(sInput, 1, 1) 'Convert the First Letter to UPPERCASE sFirstLetter = UCase(sFirstLetter) ''''' 'Take the remaining letters of sInput and put them in sRemaining ''''' 'start from the second character, and grab all remaining characters of sInput sRemaining = Mid(sInput, 2) 'convert the remaining letters to lowercase sRemaining = LCase(sRemaining) ''''' 'Combine sFirstLetter with sRemaining into sResult ''''' ' To join two string variables, use the & character sResult = sFirstLetter & sRemaining ' Have the function return sResult to whatever called it ProperCase = sResult End Function
Try it Out
In Excel, create some dummy words with mixtures of upper and lower case.
Then, use the function ProperCase to make them proper
The Built-in Function in Excel is PROPER
For those that had never used it, the function that you just created already exists and is called PROPER
The code above is just scratching the surface. If you ever shared this with folks at work, within 5 minutes or so, someone would break it.
- What if there is only one letter?
- What if there are no letters?
- What is there are more than one word passed?
- What if the user passes a range instead of a single cell?
- etc etc
That's all for now
I hope you find this useful, or that it gives you some ideas for a script of your own.