Visual Basic for Applications or VBA is an event driven programming language which enhances, the applications of the Microsoft Office suite of products. It’s also an object-oriented programming language with an associated integrated development environment. It is most often used to create macros which automate routine tasks in MS office applications. However, VBA is most commonly used in MS Excel. You can code in VBA which will interact with or output to your excel spreadsheet.
Strings are an important part of any programming language. A string is basically any kind of saved text. You can do a number of operations on strings including concatenation, reversal, sorting, finding string length, deriving substrings, searching for specific character in a string and more. Today, we introduce you to the concept of VBA string length. We assume that you know the basic concepts of Excel VBA and strings. If you are not familiar with these concepts we suggest that you go through our tutorial on basics of VBA.
What is a String?
A string is an array of characters. String length is the number of characters in a string. The variable which stores a string is declared as string data type. It is then assigned a value. Take a look at the example given here:
Dim MyName as String MyName=" John Smith"
The string type has built-in functions that help you perform several manipulations on a string. To understand the example on string length you need to have a basic knowledge of programming. The best way to learn more about strings and string manipulation would be to take a basic course on C programming. Yes, C programming. You’ll find most other courses just brush through strings, or assume you already know about them. C being a basic programming language, most C courses will cover all data types in depth, including strings.
VBA Function to Calculate the Length of a String
MS Len function calculates the length of a string and returns it as a value. The syntax of this function is as follows:
Len( text )
Len() function works in the versions starting from Excel 2000 to Excel 2013. Take a look at the example below:
Dim LResult As Long LResult = Len ("http://www.shutterstock.com/")
In case you’d like to, you can check out this tutorial on strings in C, and see how they compare.
Determining Whether a String is a Fixed Length String or Resizable
In VBA, strings are resizable. The string functions of VBA can be used to set or retrieve parts of strings which have variable length. However, there are times when you require fixed length strings.
Dim A as String *10
In the above statement, A is declared as string of 10 characters. The drawback of this method is that the length of the string is permanently fixed at 10. The size of the string cannot be resized at run time. You cannot increase or decrease the length of the string. It is a must to know the length of the string at the time of coding. At times you may need to find out whether a string variable is a fixed length string or a resizable string. It is not possible to do this with a function call as VBA converts fixed length strings to resizable strings while passing the string variable to a function. Therefore you have to write code within the function in which string is declared to determine whether string is fixed or resizable.
Example1: Program to Test for Fixed Length Strings
Dim A As String Dim B As String * 10 Dim Orig_Len As Long Dim Orig_Val As String B = "ABC" A = "DEF" Orig_Len = Len(B) Orig_Val = B B = B & " " If Orig_Len = Len(B) Then Debug.Print "B is a fixed length string" Else Debug.Print "B is a sizable string" B = OrigVal End If Orig_Len = Len(A) OrigVal = AA = A & " " If Orig_Len = Len(A) Then Debug.Print "A is a fixed length string" Else Debug.Print "A is a sizable string" A = OrigVal End If
In this program the variables are declared as type string. The length of the String variable is calculated using the Len(string length) function. Then the variable is concatenated with a space character. The string length is recalculated. The first and second string length value is compared. If both are same the string is of type fixed length string. If both are different then the string is of type resizable string. To understand more about strings and VBA macros, you can try out this course on Excel VBA.
Example 2: Convert a String’s Original Length to a Specified Length
Let’s take another example. Here’s a VBA Program to return a string variable containing the specified text either on the right or left, padded with PadChar to make a string of a specified length.
Public Enum Size_StringSide Text_Left = 1 Text_Right = 2 End Enum Public Function SizeString(Text1 As String, Length1 As Long, _ Optional ByVal TextSide As Size_StringSide = Text_Left, _ Optional PadChar As String = " ") As String Dim sPadChar As String If Len(Text1) >= Length1 Then SizeString = Left(Text1, Length1) Exit Function End If If Len(PadChar) = 0 Then sPadChar = " " Else sPadChar = Left(PadChar, 1) End If If (TextSide <> Text_Left) And (TextSide <> Text_Right) Then TextSide = Text_Left End If If TextSide = Text_Left Then SizeString = Text1 & String(Length1 - Len(Text1), sPadChar) Else SizeString = String(Length1 - Len(Text1), sPadChar) & Text1 End If End Function
Text1 stands for the original string. Length1 stands for the length of the result string. Textside indicates whether text should come on the left in which case the result is padded on the right using PadChar. In case text should appear on the right, the string is padded on the left. A space is used if padChar is omitted. If PadChar is longer than one character we use the left most character of padChar. If TextSide is neither Text_Left nor Text_Right by default, the procedure uses Text_left.
Example 3: Find Tab Character in a String
Dim tab_Str as String Dim char as String Dim length_i As Integer Dim xCntr_i As Integer tab_Str = "good" & vbTab & "morning" length_i = Len(tab_Str) char = Left(tab_Str, 1) For xCntr_i = 0 To length_i - 1 tabString = Right(tab_Str, Len(tab_Str) - 1) If char = Chr(9) Then MsgBox "Index number " & xCntr_i & " is a tab in the String." End If char = Left(tab_Str, 1) Next xCntr_i
In this program, the variables tab_str, char are declared as data type string. length_i and xCntr_i are declared as integers. Variable tab_Str is assigned value string which contains a tab character. The length of the string contained in tab_Str is calculated using the len() function. Then we loop through each character in the string and check for the tab character. In the end, we display the position of the tab character in the string using MsgBox() function.
Mastering programming requires that you put in the effort to create your own unique programs. Though reading through existing code helps, what works better is experimenting with the code and trying out different stuff on your own. Hope this tutorial helped you on your way to become a proficient VBA programmer. Once you’re ready to take it to the next level, you can move on to our advanced Excel course!