Excel VBA - Variables : Strings

[gikitiki]

---

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

'  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

'  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

'  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

Error Checking

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.

Comments

[steemitboard]

Congratulations @gikitiki! You have completed the following achievement on the Steem blockchain and have been rewarded with new badge(s) :

You distributed more than 43000 upvotes. Your next target is to reach 44000 upvotes.

_{You can view your badges on your Steem Board and compare to others on the Steem Ranking
If you no longer want to receive notifications, reply to this comment with the word STOP}

Vote for @Steemitboard as a witness to get one more award and increased upvotes!