How to Use VBA StrConv Function (5 Examples)

VBA StrConv refers to String Conversion; is a String Function used in Microsoft Visual Basic macros. This function changes a given string into a specified format, especially in VBA macros. In this article, we discuss the VBA StrConv function and its usage with multiple conversion formats.

Excel VBA StrConv Function: Syntax and Arguments

🔄 Function Objective:

 To return any given string in a specified format.

 🔄 Syntax:

🔄 Arguments Explanation:

🔄 Conversion Types

There are multiple conversion types for VBA StrConv function. In the table below, we illustrate them with explanations.

🔄 Return Parameter:

 The given string is in the specified format.

🔄 Applies To:

 Microsoft Excel Version 2003, Excel 2011 for Mac and Onwards.

⧭ Opening and Inserting Module in Microsoft Visual Basic

Before proceeding to demonstrate any examples, it’s necessary to know the ways to open and insert a Module in Microsoft Visual Basic in Excel.

🔄 Opening Microsoft Visual Basic: There are mainly three ways to open the Microsoft Visual Basic window,

🔼 Using Keyboard Shortcuts

 Press ALT+F11 altogether to open the Microsoft Visual Basic window.

🔼 Using Developer Tab

 In an Excel worksheet, Go to Developer Tab > Select Visual Basic. The Microsoft Visual Basic window appears.

🔼 Using Worksheet Tab

Go to any worksheet, Right-Click on it > Choose View Code (from the Context Menu).

🔄 Inserting a Module in Microsoft Visual Basic: There are two ways to insert a Module in Microsoft Visual Basic window,

🔼 After opening the Microsoft Visual Basic window, Select a Worksheet > Right-Click on it > Select Insert (from the Context Menu) > then Choose Module.

🔼 You can also do it by Selecting Insert (from the Toolbar) > then Choosing Module.

1. Converting String to Uppercase Using VBA StrConv

From the syntax, we know that the VBA StrConv function converts text strings into specified formats as instructed in the Conversion Types section. In this case, we just convert a simple text string (i.e., “vba test string”) into an Uppercase text format. To do so, we have to write a simple VBA Macro Code with the StrConv function. A typical VBA Macro Code has a format to proceed with maintaining certain criteria depending on any project.

➤ Paste the following macro code in a Module of Microsoft Visual Basic window, 

Sub VBA_StrConv_Function()
Dim mString as String
Dim mResultString As String
mString = "vba test string"
mResultString = StrConv(mString, vbUpperCase)
MsgBox "The delivered string to Uppercase is :" & mResultString, vbInformation, "VBA_StrConv_Function_Uppercase"
End Sub

➤ in the code,

1 – start the macro procedure by declaring the Sub name. You can assign any name to the code.

2 – define the variable mString and mResultString as String. We can also declare the variable type as Long. Because both String and Long variables define texts.

3 – assign the mString and mResultString to text (i.e., “vba test string”) and function (i.e., StrConv) respectively.

4 – show the outcome in a message box with a specified format.

➤ Press F5 to run the Macro and it brings out a message box depicting the function’s outcome.

Read More: How to Use VBA StrComp in Excel (5 Common Examples)

2. Converting String to Lowercase with the Help of the VBA StrConv Function

Similar to the Uppercase example, we can display a string in Lowercase. In order to do so, we have to execute the following sequences.

➤ Write the following code in a Module of Microsoft Visual Basic window.

Sub VBA_StrConv_Function()
Dim mString as String
Dim mResultString As String
mString = "vba test string"
mResultString = StrConv(mString, vbLowerCase)
MsgBox "The delivered string to Lowercase is :" & mResultString, vbInformation, "VBA_StrConv_Function_Lowercase"
End Sub

 ➤ From the above image, in the sections,

1 – begin the macro code declaring the Sub name.

2 – declare the variables sString and mResultString as String. As we assign it to the resultant value of StrConv function, it must be a text String.

3 – provide the values to the variables. Variable mString refers to the text (i.e., “vba test string”) and mResultString the StrConv function value.

4 – the formatted text shows up in a message box.

➤ Hit F5 to run the Macro and it displays a message box with the formatted text string in it.

Read More: How to Use VBA Str Function in Excel (4 Examples)

3. Changing String to Proper Case Using VBA StrConv Function

Proper Case is one of the Conversion Types available in the StrConv function. We can reduce the hassle of changing the first letters in each word in any writing such as essays, product intros, etc.

➤ Use the below code in any Module in the Microsoft Visual Basic window.

Sub VBA_StrConv_Function_Propercase()
Dim mString as String
Dim mResultString As String
mString = "vba test string"
mResultString = StrConv(mString, vbProperCase)
MsgBox "The delivered string to Proper Case is:" & mResultString, vbInformation, "VBA_StrConv_Function_Propercase"
End Sub

➤ The code’s sections,

1 – initiate the macro procedure declaring the Sub name.

2 – declare the variable sString and mResultString as String. As we mentioned earlier, you can declare the variables as Long also.

3 – assign the values to variables. Where mString = “vba test string”, mResultString = StrConv(mString, vbProperCase).  mResultString is the resultant value that the latter message box will show with format.

4 – fetch the formatted outcome in a message box.

➤ Use F5 to run the macro and it brings out a message box with the given text formatted.

Related Content: VBA Format Function in Excel (8 Uses with Examples)

Similar Readings

• How to Use Log Function in Excel VBA (5 Suitable Examples)
• Use VBA Abs Function in Excel (9 Examples)
• How to Use VBA Randomize Function in Excel (5 Examples)
• Use VBA FileDateTime Function in Excel (3 Uses)
• How to Use VBA MkDir Function in Excel (6 Examples)

4. Converting String to Unicode

Unicode is an international standard for handling, and representing text. We can convert a string into Unicode standard just using the VBA StrConv function. And Unicode is one of the Conversion types referred to in the function’s syntax.

➤ Type the following code in a Module of Microsoft Visual Basic window.  

Sub VBA_StrConv_Function_Unicode()
Dim mString As String, mResultString As String
Dim iCunt As Integer
Dim fString() As Byte
mString = "vba test string"
fString = StrConv(mString, vbUnicode)
For iCunt = 0 To UBound(fString)
mResultString = mResultString & fString(iCunt)
Next
MsgBox "The delivered string in Unicode is: " & mResultString, vbInformation, "VBA_StrConv_Function_Unicode"
End Sub

➤ From the above image, the code’s sections,

1 – take forward the macro by setting the Sub name.

2 – declares the variables mString, mResultString as String. Also iCunt as Integer, fString() as Byte. We declare fString() as Byte because we store Unicode characters here.

3 – set mString equals to the String (i.e., “vba test string”). The fString equals the StrConv function. For Loop iCunt equals 0 to UBound function which returns an array length of fString. In the end, mResultString is assigned to equal mResultString & fString(iCunt).

4 – display the Unicode characters in a message box.

➤ Press F5 to run the Macro and it brings out a message box depicting the Unicode characters.

Related Content: How to Use VBA Case Statement (13 Examples)

5. Converting String from Reference Cell

For earlier examples, we get results in message boxes. What if we want to assign the texts from a cell reference and make them appear Uppercase in certain cells? We can achieve it using cell references in formulas. You can convert texts into other conversion types as you wish. However, in this method, we only show Uppercase to be the formatted text version.

➤ Let’s say we have texts in cells (i.e., B3 and B4) as shown in the image below.

➤ Paste the following code in a Module.  

Option Explicit
Sub VBA_StrConv_CellReference()
Range("C3").Value = StrConv(Range("B3"), vbUpperCase)
Range("C4").Value = StrConv(Range("B4"), vbUpperCase)
End Sub

 ➤ The code is marked in parts,

1 – begin the macro by setting the Sub name.

2 – allow the formula to fetch text from cells (i.e., B3 and B4) then convert it as instructed (i.e., Uppercase) by the formula.

➤ Push the F5 key to run the Macro and it makes the converted texts appear in destined cells.

⧭ Things to Keep in Mind

🔄 VBA StrConv is only applicable for texts.

🔄 We can use Both String and Long variables to define any text.

🔄 Must save the Excel workbook as Macro Enabled Excel format to use the macros in the future.

🔄 StrConv function formats in macros such as Lowercase, Uppercase, or Proper Case work in similar ways to the same functions in Excel.

Download Practice Workbook

Conclusion

In this article, we discuss and demonstrate the uses of the VBA StrConv function. We try to clear the basics and cover typical uses of this function. I hope the above-described uses of the VBA StrConv function intrigue you to use the function more efficiently. If you have further queries or feedback, please let me know in the comment section. See you in my next article.

Related Articles

• Excel VBA ASC() Function – Get ASCII Value of Character
• How to Create a Body Mass Index (BMI) Calculator in Excel Using VBA
• Use TRIM Function in VBA in Excel (Definition + VBA Code)
• How to Use VBA SPLIT Function in Excel (5 Examples)
• Use LCase Function in VBA in Excel (With 4 Examples)
• How to Use VBA RTrim Function (5 Suitable Examples)
• How to Use VBA InStrRev Function (7 Suitable Examples)