Sabrina Ayon
In Excel, we can use various functions to work quickly, and also we utilize VBA to do the work fast. In Excel VBA, there are so many functions that we can use in our codes, the VBA Str function is one of them. This article will give an overview of the Excel VBA Str Function with examples and some of the errors.
Download Practice Workbook
You can download the workbook and practice with them.
VBA Str Function: Syntax & Arguments
The excel Str function is one of the most basic functions pre-owned by VBA.
➧ Syntax
The syntax for the VBA Str function is:
Str(number)
➧ Arguments
| Argument | Required/Optional | Explanation |
|---|---|---|
| number | Required | This number is a numeric value that is converted as string data. |
➧ Return Value
String / Text value.
➧ Type
VBA Function (String / Text Functions).
➧ Applies to
Excel office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011, Excel 2010, Excel 2007, Excel 2003.
4 Different Examples of Using VBA Str Function
There are countless build-in functions in excel. Excel VBA has also a list of different categorical functions. VBA Str function is a string/text function that turns the numerical data into string data. Let’s have a look at some examples of Excel VBA Str Function.
1. Change Positive Numeric Value to String with VBA Str Function
In this example, we will convert a positive integer value into string data.
- Firstly, go to the Developer tab on the ribbon.
- Secondly, click on Visual Basic.
- This will open up the visual basic window.
- Now, go to Insert and select Module from the drop-down menu.
- Or, we can simply right-click on the sheet and select View Code.
- In the VBA Code, we input an integer 70 as iIn and want to view the output as a string, we name the output as sOut. Now we want to see the result in a Msgbox.
- Now, write down the VBA Code below.
VBA Code:
Sub VBA_Str_func_Positive()
Dim iIn As Integer, sOut As String
iIn = 70
sOut = Str(iIn)
MsgBox "Positive Number to String : " & sOut, vbInformation, "VBA Str Function"
End Sub- Now, run the code by pressing F5 or clicking the Run Sub button.
Output:
Read More: How to Use VBA Val Function in Excel (7 Examples)
2. Turn Negative Numeric Value to String
In this example, we will see the VBA Str function can also convert the negative value to a string.
- In the beginning, go to the Developer tab > Visual Basic > Insert > Module.
- Or, right-clicking on the worksheet will open up a window. From there go to the View Code.
- This will take you to the Visual Basic Editor field, where we can write VBA Macros.
- Likewise, in the previous example, we take an input then will see the output in the MsgBox.
- Similarly as an earlier example, now, write down the VBA Code to convert the negative numeric data (-70) to string data.
VBA Code:
Sub VBA_Str_func_Negative()
Dim iIn As Integer, sOut As String
iIn = -70
sOut = Str(iIn)
MsgBox "Negative Number to String : " & sOut, vbInformation, "VBA Str Function"
End Sub- Now, run the code by pressing F5 or clicking the Run Sub button.
Output:
Read More: Excel Formula to Generate Random Number (5 examples)
Similar Readings:
- How to Use VBA InstRev Function (7 Suitable Examples)
- Use the Left Function in VBA in Excel (2 Examples)
- How to Return a Value in VBA Function (Both Array and Non-Array Values)
- Use VBA DIR Function in Excel (7 Examples)
- How to Use MsgBox Function in Excel VBA (A Complete Guideline)
3. VBA Str Function to Transform Number Zero to String
The number zero (0) also can be converted as string data.
- By the same token, right-click on the sheet, then select View Code.
- This will appear in the Visual Basic Editor.
- Now, copy and paste the VBA code below.
VBA Code:
Sub VBA_Str_func_Zero()
Dim iIn As Integer, sOut As String
iIn = 0
sOut = Str(iIn)
MsgBox "Zero to a String : " & sOut, vbInformation, "VBA Str Function"
End Sub- Now, press the F5 key or click on the Run Sub button to run the code.
Output:
Read More: How to Use VBA Str Function in Excel (4 Examples)
4. Convert a Numeric Value to String in a Cell in Excel Sheet
In earlier examples, we saw the conversion in a MsgBox. Now we will see the result in our excel sheet. For this, we are going to use the dataset below. The dataset contains some numeric values. In column B we can see that there are positive values, negative values as well as zero. Let’s convert them to string data.
- As same as above examples, first, right-click on the sheet then select View Code.
- After that, write down the code below.
VBA Code:
Sub VBA_Str_func()
Range("C5").Value = str(Range("B5"))
Range("C6").Value = str(Range("B6"))
Range("C7").Value = str(Range("B7"))
Range("C8").Value = str(Range("B8"))
End Sub- Then, run the code by clicking on the Run Sub button or using the keyboard shortcut F5 key.
Output:
Read More: How to Use VBA StrConv Function (5 Examples)
Errors with Str Function in Excel VBA
While writing macros on Visual Basic Editor, there would be many errors shown after running the code. VBA Str function will show some errors if we made some mistakes in code.
1. Type Mismatch
As we know VBA Str function has a parameter which is a number. So if we write a text value or string it will show an error. So, let’s demonstrate it in practice.
- At first, open the visual basic editor by right-clicking on the sheet from the sheet bar and then go to View Code.
- Next, write down the VBA code here.
VBA Code:
Sub VBA_Str_func_Mismatch()
Dim st As String
st = str("ExcelDemy")
Cells(1, 1).Value = st
End Sub- Finally, Run the code or press the keyboard shortcut F5 key to run the code.
- As we take a string value to the Str function parameter. It will show an error Type mismatch.
Related Content: How to Use VBA IsError Function (6 Examples)
2. Invalid Use of Null
The VBA Str function can’t take the Null value as a parameter. It will show an error. Let’s have a look at the example below.
- In the same way, first, right-click on the worksheet. Then, go to View Code.
- Next, write down the VBA code below.
VBA Code:
Sub VBA_Str_func_Null()
Dim iIn As Integer, sOut As String
iIn = Null
sOut = Str(iIn)
MsgBox "Number to String : " & sOut, vbInformation, "VBA Str Function"
End Sub- After that, run the code by pressing the F5 key. Also, we can run by clicking on the Run Sub button on the Visual Basic Editor.
- As a result, we will see an error message “Invalid use of Null”, as we already know that the string functions only converts the numeric value to string value.
Read More: How to Use IsNull Function in Excel VBA (5 Examples)
Things to Remember
- While converting a string the Str function inserts a leading space for the sign of the numeric value.
- VBA will produce the run-time 13 error if the value given is not a number or a number that can’t be recognized as a number as shown above Type mismatch error will appear.
- VBA will assemble the run-time 94 error if the value is invalid or Null.
Conclusion
The above examples are an overview of the Str function in Excel VBA. Hope this will help you! If you have any questions, suggestions, or feedback please let us know in the comment section. Or you can have a glance at our other articles in the ExcelDemy.com blog!
