Strings in VBA – Fixed and Variable Length

In the previous article, we learned the use of variants. Today we would learn about another interesting topic. Strings are one of the frequently used data types in any programming language. VBA provides two types of strings to be used. We would now learn about strings.

So let us begin learning.

What are Strings and their Types?

A text string is a combination of alphabets, numbers, or special characters.

The String is one of the important data types that can store a text string. The text string is always enclosed in double quotes during the assignment of the string. A string variable can store a fixed-length string or a variable-length string.

When we specify the length of the string variable at the time of its declaration then it becomes a fixed-length string. Otherwise, it could be of any length.

We can specify the fixed length of a string to be 65400 as the maximum number of characters.

Download the practice workbook to follow along.

Declaring a Text String in VBA

The syntax to declare a variable-size string is as follows.

Dim <string_name> As String

We can assign a string of any length to this variable. For instance:-

Dim Name as String

The name is the <string_name>

When we specify the size of the string then it would be a fixed size string.

Dim <string_name> As String * <size_of_string>

Dim Name As String * 20

We would use the * operator followed by the length of the string ( maximum number of characters in the string ).

Variable Sized String vs Fixed Size String

In a fixed size string, there is a possibility that we assign a string whose length is less than the maximum limit of the string. In this case, there are blank spaces at the end of that string.

Sub example()

'declaring name1 and name2 string variable

Dim name1 As String * 20
Dim name2 As String

'assigning text string to the string type variables

name1 = "Chetna Dua"
name2 = "Chetna Dua"

'printing the string variables

MsgBox name1 + name2      'concatinating the two strings in name1 and name2

End Sub

When we run this sub-procedure, we see that there would be blank spaces at the end of name1 that make its length to be of 20 characters long.

name1 variable has a fixed size of 20 characters long but the value assigned to it “Chetna Dua” is 10 characters long, which is why 10 blank characters are automatically appended at the end.

Assigning Values from Excel Workbook

We can also assign text string to a string variable from any cell in our excel workbook.

We can assign the value of the active cell in our workbook in any string variable or vice versa.

Sub Example2()

'declaring two string type variables

Dim var1 As String, var2 As String

'value of var1 would be equal to the value in active cell of workbook

var1 = ActiveCell.Value
MsgBox var1   'print value of active cell using var1

'assigning a text string to var2

var2 = "New string in active cell"

'the var2 would be printed in active cell of workbook

ActiveCell.Value = var2
End Sub

Now to run this macro, follow the steps.

• Paste the above code into a new module of the VBA editor and then close the VBA Editor.
• Go to cell A1, which is our active cell (currently selected cell), and type your name into it.

• Hold the Alt key and press the F8 key. This opens the list of available macros. Select Example2 and click on Run.

• A message box appears displaying the value of active cell A1 containing the name you just entered.

• Thereafter, when you click Ok, the value of active cell A1 would change to the text string we assigned to var2 in our macro code.

This is how we work with strings in VBA code.

Concatenation of two Strings

We can concatenate or join two strings in VBA with the help of & (ampersand) or + (addition works as a concatenation operation) operator.

Sub example3()

'declaring the text string

Dim first_name As String
Dim last_name As String

'assigning values to first_name and last_name

first_name = "Chetna"
last_name = "Dua"

'concatenating two strings using + opertator

MsgBox first_name + last_name

' you can also use Msgbox first_name & last_name

End Sub

When we run this sub procedure, we get the value of first_name and last_name to be concatenated into “ChetnaDua”.

Since there is no space between the first name and last name, you can add space while concatenating the two strings and add a white space character within double quotes like this.

Msgbox first_name & " " & last_name

This brought us to end.

Thank you for reading.