VBA Str Function

The VBA Str function is a built-in function in Microsoft Excel and other Office applications that converts a number into a string, which is a set of characters. This function is very useful when working with numbers as strings instead of numerical values. It allows for easier handling and manipulation of numbers, as well as facilitating the concatenation of numbers and text.

VBA Str Function – Purpose, Syntax and Arguments

Syntax:

Str(Number)

Arguments:

• Number: This is the number that will be converted into a string. It can be a constant, variable, or expression.

Example:

Let’s say we have a variable ‘num’ with a value of 1234. Using the Str function, we can convert this number into a string as follows:

Dim num as Integer
num = 1234
MsgBox "The number is " & Str(num) 

The resulting message box will display “The number is 1234”.

Remarks:

The VBA Str function can be very useful when working with numbers in string format. It allows for more flexibility in manipulating numbers, as well as providing an easy way to concatenate numbers and text. When using this function, it is important to remember that the resulting string will include decimal points and scientific notation if the number is larger than the maximum for an Integer data type (32,767). Additionally, the Str function only works with numerical values and will return an error if a non-numeric value is passed as an argument.

Important Notes:

• The VBA Str function is case-insensitive. This means that it will recognize both lowercase and uppercase letters as the same.
• The function will always return a string, even if the argument passed is already a string or text type.
• If the argument passed is a null value, the function will return an empty string.
• If the argument passed is a 0 or a negative number, the function will still return a string with no negative sign (-).
• If the argument passed is a floating-point number, the resulting string will contain decimal points and scientific notation if the number is larger than 32,767.

The VBA Str function is a useful tool in converting numerical values into strings. Its easy-to-use syntax and flexibility make it a valuable function for handling and manipulating numbers in VBA. However, it is important to remember its limitations and use it accordingly to avoid any errors.

Understanding VBA Str Function with Examples

One of the most commonly used functions in VBA is the ‘Str’ function, which is used to convert a numeric value into a string. In this blog post, we will explore the ‘Str’ function in detail and provide multiple examples to help you understand its usage.

Example 1: Basic Usage of Str Function

Sub BasicStrExample()
Dim num As Integer
Dim str As String
num = 123
str = Str(num)
MsgBox str
End Sub

1. The ‘Dim’ statements are used to declare two variables – ‘num’ as an integer and ‘str’ as a string.
2. The variable ‘num’ is assigned a value of 123.
3. The ‘Str’ function is used to convert the numeric value to a string and is stored in the variable ‘str’.
4. The ‘MsgBox’ function is used to display the string value in a message box.

In this example, the ‘Str’ function simply converts the number 123 to a string “123” and is then displayed in a message box. This is the basic usage of the ‘Str’ function.

Example 2: Str Function on Decimal Numbers

Sub DecimalStrExample()
Dim num As Double
Dim str As String
num = 123.45
str = Str(num)
MsgBox str
End Sub

1. In this example, the variable ‘num’ is assigned a decimal value of 123.45.
2. The ‘Str’ function is used to convert the decimal value to a string and is stored in the variable ‘str’.
3. The ‘MsgBox’ function is used to display the string value in a message box.

The result displayed in the message box will be “123.45”. The ‘Str’ function is able to handle both integer and decimal values and convert them to strings.

Example 3: Handling Negative Numbers

Sub NegativeStrExample()
Dim num As Integer
Dim str As String
num = -123
str = Str(num)
MsgBox str
End Sub

1. In this example, the variable ‘num’ is assigned a negative integer value of -123.
2. The ‘Str’ function is used to convert the negative value to a string and is stored in the variable ‘str’.
3. The ‘MsgBox’ function is used to display the string value in a message box.

The result displayed in the message box will be “-123”. The ‘Str’ function is able to handle negative numbers and convert them to strings without any additional steps.

Example 4: Str Function on Date Values

Sub DateStrExample()
Dim dt As Date
Dim str As String
dt = DateSerial(2020, 9, 15)
str = Str(dt)
MsgBox str
End Sub

1. In this example, the variable ‘dt’ is assigned a date value using the ‘DateSerial’ function.
2. The ‘Str’ function is used to convert the date value to a string and is stored in the variable ‘str’.
3. The ‘MsgBox’ function is used to display the string value in a message box.

The result displayed in the message box will be “9/15/2020” depending on your system’s date format. The ‘Str’ function is not limited to only numeric values, it can also handle date values and convert them to strings.

Example 5: Str Function on Null Values

Sub NullStrExample()
Dim nullVar As Variant
Dim str As String
str = Str(nullVar)
MsgBox str
End Sub

1. In this example, a variable ‘nullVar’ is declared but not assigned any value.
2. The ‘Str’ function is used to convert the null value to a string and is stored in the variable ‘str’.
3. The ‘MsgBox’ function is used to display the string value in a message box.

The result displayed in the message box will be an empty string, as the ‘Str’ function is not able to convert null values to strings. It is important to handle null values before using the ‘Str’ function to avoid any errors in your code.

Example 6: Str Function on String Values

Sub StringStrExample()
Dim str1 As String
Dim str2 As String
str1 = "Hello"
str2 = Str(str1)
MsgBox str2
End Sub

1. In this example, the variable ‘str1’ is assigned a string value “Hello”.
2. The ‘Str’ function is used to convert the string value to a string again and is stored in the variable ‘str2’.
3. The ‘MsgBox’ function is used to display the string value in a message box.

The result displayed in the message box will be “Hello”. The ‘Str’ function can also be used on strings, but it will not change the value of the string. It will simply return the same string value.

Conclusion

In summary, the VBA Str function is useful for converting numeric, date, and string values to strings. It can also handle negative values but cannot convert null values to strings. It is important to correctly handle the data type of the input value before using the ‘Str’ function to avoid any errors in your code.
Hopefully, this blog post has helped you understand the ‘Str’ function in VBA better. With the knowledge of this function, you can now efficiently convert different types of data to strings in your VBA projects.