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

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.

Change Positive Numeric Value to String with VBA Str Function

  • This will open up the visual basic window.
  • Now, go to Insert and select Module from the drop-down menu.

Change Positive Numeric Value to String with VBA Str Function

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

Change Positive Numeric Value to String with VBA Str Function

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.

Turn Negative Numeric Value to String with VBA Str Function

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

Turn Negative Numeric Value to String with VBA Str Function

Output:

Turn Negative Numeric Value to String with VBA Str Function

Read More: Excel Formula to Generate Random Number (5 examples)


Similar Readings:


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.

Turn Negative Numeric Value to String with VBA Str Function

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.

Excel Convert a Numeric Value to String in a Sheet Cell

  • 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:

VBA str function

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!


Related Articles

Sabrina Ayon

Sabrina Ayon

Hi there! This is Sabrina Ayon. I'm really excited to welcome you to my profile. Currently, I'm working in SOFTEKO as a Excel and VBA Content Developer. I'm a student of United International University and my program is Computer Science and Engineering. I love working with computers and solving problems. I’ve always been interested in research and development. So, here I will be posting articles related to Microsoft Excel. Hoped this may help you. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo