How to Use VBA StrConv Function (5 Examples)

Get FREE Advanced Excel Exercises with Solutions!

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.

StrConv function overview-VBA StrConv


Excel VBA StrConv Function: Syntax and Arguments

🔄 Function Objective:

 To return any given string in a specified format.

 🔄 Syntax:

StrConv(String, Conversion As VbStrConv, [LocaleID As Long])

StrConv function syntax-VBA StrConv

🔄 Arguments Explanation:

Argument Required/Optional Explanation
 String Required any String Expression we want to convert.
 Conversion Required integers or formats specifying types of conversion have to be done.
 LCID Optional local id. The function replicates the system id by default.

🔄 Conversion Types

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

Argument Required/Optional Explanation
vbUpperCase 1 Transform the given string into upper case letters.
vbLowerCase 2 Transform the given string into lower case letters.
vbProperCase 3 Transform the first character of each word to uppercase and the rest of the characters to lowercase.
vbWide 4 Transform narrow-spaced characters into wide-spaced characters
vbNarrow 8 Transform wide-spaced characters into narrow-spaced characters
vbKatakana 16 The Hiragana characters get converted into the Katakana characters
vbHiragana 32 Katakana characters get converted into Hiragana characters
vbUnicode 64 The string gets converted into Unicode
vbFromUnicode 128 Any strings from Unicode get converted into the default code of the system

🔄 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.

developer option-VBA StrConv

🔼 Using Worksheet Tab

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

Context Menu-VBA StrConv


🔄 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.

module insertion-VBA StrConv

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

Module Insertion-VBA StrConv


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

uppercase macro code

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.

Uppercase 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

Lowercase macro code

  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.

Lowercase result

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

propercase macro code-VBA StrConv

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.

Proper case outcome

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


Similar Readings


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

Unicode macro code-VBA StrConv

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.

Unicode result-VBA StrConv

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.

Cell reference

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

Cell reference macro code

 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.

Cell reference result


⧭ 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

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Maruf Islam
Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

2 Comments
  1. https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/strconv-function
    Microsoft doc says that Unicode functions of Strconv are not available on Mac.
    vbNarrow/Wide functions change the code unit size (bytes per character) – nothing to do with spacing.

    • Hello, Nick! Right now we’re not working with Mac so unfortunately we’re unable to provide you with the exact solutions.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo