Microsoft Excel allows us to estimate different data types and carry out monetary, mathematical, and statistical computations. When it comes to augmenting output at work or gaining time, macros are your ultimate guide. Using VBA, we can automate any process, no matter how small or large. In this article, I will show you how to use Excel VBA to reverse a string. Let’s get started!
How to Launch the VBA Editor in Excel
Before going to methods, in this part, we will show you how to open the Visual Basic tab. This is the environment where we write our code. To open the Visual Basic tab, follow the below steps.
📌 Steps:
- First, go to the Developer tab >> select Visual Basic.
- Or, you can just press ALT+F11 on your keyboard to open Visual Basic.
- After that, in the Visual Basic tab, click Insert and select the Module option.
- As a result, a coding module will appear.
Note:
If you can’t find the Developer tab in the Excel ribbon or if you are working for the very first time with VBA macros, don’t panic; you can get the Developer tab easily in your ribbon. It is not displayed on the ribbon by default.
How to Use Excel VBA to Reverse String: 5 Suitable Examples
In order to demonstrate how you can reverse a string in Excel using VBA, I have taken a dataset of 11 rows and 3 columns which are Employee Name, Job Title, and Age. In this article, we are going to use the Excel 365 version. You can use any other version according to your convenience.
Now, let’s go through the following part.
1. Using the StrReverse Function to Reverse a String in Excel with VBA
The StrReverse function is Excel’s built-in String/Text function. This function returns a string in reverse character order. We can use Excel’s built-in StrReverse function in VBA code to reverse a string. In this part, I am going to show you how to reverse a string in Excel with VBA using the StrReverse function.
Now follow the below steps.
📌 Steps:
- Insert the following code in the code editor and press F5 to run the entire code.
Sub ReverseString()
Dim originalString As String
Dim reversedString As String
originalString = Range("B17").Value
reversedString = StrReverse(originalString)
Range("C17").Value = reversedString
End Sub🔎 Code Breakdown
Here,
Dim originalString As String
Dim reversedString As StringThese lines declare two variables to hold the original and reversed strings: originalString as String and reversedString as String.
originalString = Range("B17").Value- Getting the original string from cell B17 on the active worksheet.
reversedString = StrReverse(originalString)- Using the built-in StrReverse function to reverse the original string.
Range("C17").Value = reversedString- Outputting the reversed string to cell C17 on the active worksheet.
Here is the final output image.
Read More: How to Reverse Names in Excel
2. Using VBA StrReverse Function In Active Cell Range to Reverse a String in Excel
You can use the StrReverse function in the active cell range to reverse a string. In this way, the active cell string will be inversed, and the output will be shown in the next cell.
Now, insert a new module and copy and paste the following code. Press F5 to run the entire code, and your work will be done.
Sub ReverseString()
Dim inputString As String
Dim reversedString As String
inputString = ActiveCell.Value
reversedString = StrReverse(inputString)
ActiveCell.Offset(0, 1).Value = reversedString
End Sub🔎 Code Breakdown
Here,
Dim inputString As String
Dim reversedString As StringThese lines declare two variables to hold the input and reversed strings: inputString As String and reversedString As String.
inputString = ActiveCell.Value- Getting the input string from the active cell.
reversedString = StrReverse(inputString)- Using the built-in StrReverse function to reverse the input string.
Offset(0, 1).Value = reversedString- Outputting the reversed string to the cell immediately to the right of the active cell.
This code retrieves a string from the active cell, uses the StrReverse function to reverse the string, and then outputs the reversed string to the cell immediately to the right of the active cell.
Here is the final output image after running this VBA code successfully.
3. Reverse a String Using For Loop in Excel VBA
Suppose you want to reverse a string using the for loop. In Excel VBA, you can also do that. With a For loop, you can ask the user for the string to be reversed and reverse it. To help you understand, I have included a video demonstration.
Now, insert another new module and copy and paste the following code. Press F5 to run the entire code.
Sub ReverseString()
Dim inputString As String
Dim reversedString As String
Dim i As Integer
inputString = InputBox("Enter a string to reverse:")
For i = Len(inputString) To 1 Step -1
reversedString = reversedString & Mid(inputString, i, 1)
Next i
MsgBox reversedString
End Sub🔎 Code Breakdown
Here,
Dim inputString As String
Dim reversedString As String
Dim i As IntegerThese lines declare three variables to hold the input and reversed strings, and the loop index: inputString As String, reversedString As String and i As Integer.
inputString = InputBox("Enter a string to reverse:")- Getting the input string from an input box.
For i = Len(inputString) To 1 Step -1
reversedString = reversedString & Mid(inputString, i, 1)
Next iThis For Loop goes through each character in the input string, starting from the end and moving backward. It adds the current character to the reversed string.
MsgBox reversedString- Displaying the reversed string in a message box.
This code prompts the user to input a string using an input box, loops through each character in the string from the end to the beginning, adds each character to a new string to create a reversed version of the original string, and finally displays the reversed string in a message box.
Here is the final output image after running the VBA macro.
4. Using An Array to Reverse the String With LBound and UBound Functions
You can also reverse a string using an array with LBound or UBound in Excel VBA. LBound or UBound are two functions used in Excel VBA to determine the lower and upper bounds of an array. LBound function returns the lower bound of an array, which is the index of the first element in the array. UBound function returns the upper bound of an array, which is the index of the last element in the array.
Now, insert a new module and copy and paste the following code. Press F5 to run the entire code.
Sub ReverseString()
Dim inputString As String
Dim reversedString As String
Dim i As Integer
inputString = InputBox("Enter a string to reverse:")
Dim charArray() As String
ReDim charArray(Len(inputString) - 1)
For i = 1 To Len(inputString)
charArray(i - 1) = Mid(inputString, i, 1)
Next i
For i = UBound(charArray) To LBound(charArray) Step -1
reversedString = reversedString & charArray(i)
Next i
MsgBox reversedString
End Sub🔎 Code Breakdown
Here,
Dim inputString As String
Dim reversedString As String
Dim i As IntegerThese lines declare three variables to hold the input and reversed strings and the loop index.
inputString = InputBox("Enter a string to reverse:")- Getting the input string from an input box.
Dim charArray() As String- Declaring an array to hold the characters of the input string.
ReDim charArray(Len(inputString) - 1)- Resizing the character array to the length of the input string.
For i = 1 To Len(inputString)
charArray(i - 1) = Mid(inputString, i, 1)
Next iThis For Loop goes through each character in the input string and adds it to the character array.
For i = UBound(charArray) To LBound(charArray) Step -1
reversedString = reversedString & charArray(i)
Next iThis For Loop goes through the character array backward and adds each character to the reversed string.
MsgBox reversedString- Displaying the reversed string in a message box.
Here is the final output image after running the VBA macro.
5. Creating a User-Defined Function to Reverse String with Excel VBA
In this part, I will show how to reverse a string by creating a user-defined function. Suppose we want to reverse the string “Director” under the Job Title column. We want to show the result in cell C17. Follow the attached video to understand clearly.
Now, insert a new module and copy and paste the following code. Press F5 to run the entire code, and you will get your output.
Function ReverseStringByUserDefinedFunction(inputString _
As String) As String
ReverseStringByUserDefinedFunction = _
StrReverse(inputString)
End Function🔎 Code Breakdown
Here,
Function ReverseStringByUserDefinedFunction(inputString _
As String) As StringThis function takes an input string and returns a reversed version of the string. The implementation of the function simply calls the built-in StrReverse function and returns its result.
ReverseStringByUserDefinedFunction = StrReverse(inputString)- Using the built-in StrReverse function to reverse the input string.
This code defines a user-defined function named ReverseStringByUserDefinedFunction which takes an input string and returns a reversed version of the string. The implementation of the function simply calls the built-in StrReverse function and returns its result.
Here is the final output result.
How to Reverse String in Excel Without VBA
We have already seen some easy ways of reversing a string in Excel with VBA. Suppose you want to reverse a string in Excel without using the VBA macro. Excel allows you to do that too. Let’s say we want to reverse the Name “Alice Mehta” without using a VBA macro. In Excel, we can do that by applying formulas.
Now, insert the following formula in cell C17 and press Enter.
=CONCAT(MID(B17,SEQUENCE(1,LEN(B17),LEN(B17),-1),1))Here, B17 is the cell containing the original string we want to reverse. We have used the MID, SEQUENCE, and CONCAT functions in this formula to extract and concatenate the characters of the original string in reverse order, resulting in the reversed string.
🔎 Formula Breakdown
- LEN(B17) returns the length of the string in cell B17.
- SEQUENCE(1,LEN(B17),LEN(B17),-1) → generates an array of sequential numbers from the length of the string to 1, in reverse order. This sequence is used as the second argument of the MID function to extract the characters of the string from right to left.
- MID(B17,SEQUENCE(1,LEN(B17),LEN(B17),-1),1) → extracts each character of the string from right to left, using the MID function. The first argument of the MID function is the original string, the second argument is the sequence of numbers generated by the SEQUENCE function, and the third argument is 1, indicating that we want to extract one character at a time.
- CONCAT(MID(B17,SEQUENCE(1,LEN(B17),LEN(B17),-1),1)) → concatenates the characters extracted by the MID function into a single string, using the CONCAT function. The result is the reversed string.
Here is the final output image.
Read More: How to Reverse a String in Excel
How to Reverse an Array With Excel VBA
With the help of an Excel VBA Macro, you can easily reverse an array. Let’s assume that in an array we have all employee names (B5:B14). Now we will reverse the array with the help of a VBA macro and show the output in cell range C5:C14 which is the Reversed Employee Name column.
Now insert a new module and copy and paste the following code. Press F5 to run the code.
Sub ReverseArray()
Dim inputArray As Variant
Dim reversedArray As Variant
Dim i As Long, j As Long
inputArray = Range("B5:B14").Value
ReDim reversedArray(LBound(inputArray, 1) To UBound(inputArray, 1), 1 To 1)
j = UBound(inputArray, 1)
For i = LBound(inputArray, 1) To UBound(inputArray, 1)
reversedArray(i, 1) = inputArray(j, 1)
j = j - 1
Next i
Range("C5:C14").Value = reversedArray
End Sub🔎 Code Breakdown
Here,
Dim inputArray As Variant
Dim reversedArray As Variant
Dim i As Long, j As LongThese lines declare three variables to hold the input, reversed arrays, and loop indices.
inputArray = Range("B5:B14").Value- Getting the input array from a range of cells.
ReDim reversedArray(LBound(inputArray, 1) To UBound(inputArray, 1), 1 To 1)- Creating a new array with the same dimensions as the input array.
j = UBound(inputArray, 1)- Setting the loop index to the last row of the input array.
For i = LBound(inputArray, 1) To UBound(inputArray, 1)
reversedArray(i, 1) = inputArray(j, 1)
j = j - 1
Next iThis For Loop goes through each row of the input array and adds the corresponding value to the reversed array. Then it decrements the loop index to move to the next row in reverse order.
Range("C5:C14").Value = reversedArray- Setting the reversed array to a range of cells.
This code gets an input array from a range of cells, creates a new array with the same dimensions as the input array, loops through each row of the input array in reverse order and adds the corresponding value to the reversed array, and finally sets the reversed array to a range of cells.
Here is the final output image after running the VBA code successfully.
Read More: How to Reverse Rows in Excel
Key Takeaways from This Article
- In this article, I have shown how to launch a VBA editor in Excel.
- Chosen real-life dataset for better understanding.
- Focusing on how to reverse a string with VBA code in Excel.
- Explained different approaches with VBA code.
- Showed how to reverse an array using VBA code.
- Provide solutions to frequently asked questions from readers.
- Overall, we focused on using VBA code to reverse a string in Excel.
Download Practice Workbook
You can download the practice workbook here:
Conclusion
In this article, I have tried my best to explain how to use Excel VBA to reverse a string. I hope this article was informative and useful for you. Feel free to leave any questions, comments, or recommendations in the comments section.
