VBA StrConv

What is Excel VBA StrConv Function?

The Excel VBA StrConv function stands for “String Convert” and is a valuable tool for manipulating text within VBA macros. It allows you to perform various conversions and transformations on text strings, such as changing cases, converting between character encodings, and more.

Let us look at an example. Here, we start with an input string entirely in uppercase, reading ‘CONVERT ME TO LOWERCASE.’ We start by declaring two variables: inputString, which is initialized with the text “Convert me to lowercase,” and resultString, which serves as the container for the converted text.

In the third step, we utilize the VBA StrConv function. Specifically, we use the vbLowerCase constant as the conversion type, which instructs the function to change the case of inputString to lowercase.

Finally, upon executing the code, resultString contains “convert me to lowercase.” in lowercase letters.

How to Use Excel VBA StrConv Function?

To use the StrConv function in Excel VBA, follow these steps:

Step 1: In the Excel workbook, press ALT + F11 to open the Visual Basic for Applications (VBA) editor.

Step 2: Inside the VBA editor, go to Insert > Module to insert a new module where you can write your VBA code.

Step 3: Inside the newly created module, write your VBA code that includes the StrConv function.

Here’s the basic syntax of the StrConv function:

newString = StrConv(oldString, conversionType,)

oldString: This is the input string that you want to convert.

• conversionType: An integer representing the type of conversion you want to perform (e.g., changing case in excel, character encoding).

Step 4: To execute your VBA code, press ALT + F8, select your macro from the list, and click “Run.”

Step 5: The result of the VBA StrConv function will be stored in the variable you assigned it to (e.g., newString).

You can display the result using MsgBox in VBA, write it to a cell in the worksheet, or use it in other parts of your VBA code as needed.

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Examples

Here are some examples demonstrating the use of the VBA StrConv function for various conversions:

Example #1 – Convert to Uppercase

In this example, we convert the inputString to uppercase using vbUpperCase, resulting in “HELLO, WORLD!” displayed in a message box.

Step 1: We first establish a VBA subroutine called ConvertToUppercase in the new module.

Step 2: In this step, we introduce two variables: inputString to capture the original text (in this case, set as “hello, world!”), and resultString to hold the transformed text.

Step 3: Here, we employ the VBA StrConv function. It takes inputString as input and uses the vbUpperCase constant to convert the text to uppercase.

Note: vbUpperCase is a VBA constant utilized with the StrConv function to convert text to uppercase, ensuring that all characters in the string are in their uppercase form.

Step 4: We use the message box, and its content is the value stored in the resultString variable.

Step 5: When executing the code, resultString contains “HELLO, WORLD!” in uppercase. The converted text is then displayed in a message box, allowing us to observe the result of our conversion.

Here is the full code:

Sub ConvertToUppercase()

    Dim inputString As String

    inputString = “hello, world!”

    Dim resultString As String

    resultString = StrConv(inputString, vbUpperCase)

    MsgBox resultString ‘ Displays “HELLO, WORLD!”

End Sub

Example #2 – Convert to Proper Case

In this example, the VBA subroutine converts inputString to the proper case with VBA StrConv vbProperCase, leading to the message box displaying “This Is A Sentence.”

Step 1: In the new module, we introduce a new subroutine named ConvertToProperCase.

Step 2: In this line of code, we define and initialize two string variables: inputString, given the initial value “this is a sentence,” and resultString, set up to store the modified text.

Step 3: We apply the VBA StrConv function with the VBA StrConv vbProperCase constant to inputString. This action transforms the text into a proper case. Proper case means that the first letter of each word is capitalized, while all other letters are in lowercase. The resulting properly cased text is stored in the resultString variable for further use or display.

Step 4: In this step, we utilize the message box function to present the content stored within the resultString variable within the confines of the message box.

Step 5: Now save the macro and click on run.

Upon execution, resultString now contains “This Is A Sentence.” in proper case. The converted text is presented in a message box for us to see the outcome of the conversion.

Here is the full code:

Sub ConvertToProperCase()

    Dim inputString As String

    inputString = “this is a sentence.”

    Dim resultString As String

    resultString = StrConv(inputString, vbProperCase)

    MsgBox resultString ‘ Displays “This Is A Sentence.”

End Sub

Example #3 – Convert to ASCII Encoding

In this example, the VBA subroutine converts inputString to ASCII encoding using VBA StrConv Unicode, and the message box displays “ASCII” as the result.

Step 1: In the new module, we introduce the ConvertToASCII subroutine.

Step 2: In this step, we commence by establishing the inputString variable, assigned with the initial text “ASCII” for storing the original content, and we introduce the resultString variable, prepared to collect the transformed text

Step 3: In this line, we employ the VBA StrConv function to change the character encoding of the inputString from Unicode to another encoding, typically used when you want to convert text to a non-Unicode format. The modified text is then stored in the resultString variable.

Note: “vbFromUnicode” is a VBA constant that converts text from Unicode encoding to another encoding, such as ASCII, facilitating compatibility and data transformation.

Step 4: In this step, we utilize the message box function to present the content within the resultString variable.

Step 5: Save the VBA macro and click on run.

Once the code is executed, resultString now contains “ASCII.” The converted text is displayed in a message box, demonstrating the successful conversion from Unicode to ASCII.

Here is the full code:

Sub ConvertToASCII()

    Dim inputString As String

    inputString = “ASCII”

    Dim resultString As String

    resultString = StrConv(inputString, vbFromUnicode)

    MsgBox resultString ‘ Displays “ASCII”

End Sub

Important Things To Note

1. The conversion type parameter in VBA StrConv accepts various constants, such as VBA StrConv UTF-8, vbUpperCase, vbLowerCase, vbProperCase, vbUnicode, vbFromUnicode, and more. Be sure to use the appropriate constant for your desired conversion.
2. The [LCID] parameter, representing LocaleID, is optional. It affects specific conversions, especially when dealing with character encodings. If omitted, the system default LCID is used.
3. VBA StrConv can be used for character encoding conversions, like converting between VBA StrConv Unicode and VBA StrConv ASCII. However, consider using dedicated encoding/decoding functions or libraries for more complex encoding tasks.
4. Remember that strings in VBA are immutable, meaning that any changes to a string result in a new string being created. Assign the result of VBA StrConv to a new variable to store the converted string.
5. VBA StrConv UTF-8 encoding typically involves reading and writing text files with UTF-8 encoding, and it often requires handling byte-level operations for specific transformations.

Frequently Asked Questions (FAQs)

Recommended Articles

This has been a guide to VBA StrConv. Here we explain the how to use Excel VBA StrConv Function along with examples & downloadable excel template. You can learn more from the following articles –

• VBA StrComp
• VBA LCASE FUNCTION
• VBA String to Integer