In my experience working with Excel and Visual Basic for Applications, also known as VBA, I’ve often come across the need to convert numerical data into a textual format. This is where the STR function in VBA comes in handy. It’s one of those simple yet powerful tools in a coder’s toolbox that makes data manipulation a breeze in Excel across various versions, from Excel 2003 right up the latest releases like Excel 2019.

I remember starting out with Excel 2007 and seeing the evolution of Excel versions over the years. Each version has brought new features, but some functions like the STR function in VBA have stayed consistently useful. It’s pretty straightforward: the function takes a number and turns it into a string, so you can work with it as text within your macro. It’s a neat way to ensure your numbers are stored just the way you want them, especially when sign matters—like ensuring there’s a space for positive numbers.

Syntax

Str(Number)

Arguments

  • Number: The numeric value I need to convert.
    • Long: Handling large integers
    • Numeric Expression: Could be a variable or direct input
    • Currency: To include monetary values
    • Dates: To handle calendar dates

Sign Handling:

  • Positive (+) or Negative (-): Recognizes and retains the sign
  • Leading Space: Allocated for sign of number, even for positive or zero

Function Usage:

  • Str(Number): Directly converts Number to string
  • CStr(Number): Another way to cast Number to a string value

Format Variations:

  • Format Function: Offers custom string representations
  • CStr vs. Str: CStr handles nulls or empty strings as errors, while Str does not.

Remember, when using Str, my numeric input is directly cast into a string with a leading space reserved for the sign, be it negative, positive or zero, without any additional formatting. When I need to compare numbers or deal with numbers in their string form, these arguments are what I tweak to get the desired string representation.

Example

In VBA, converting numbers to strings seamlessly can be a common task. For instance:

Sub example_STR()
    Range("B1").Value = Str(Range("A1"))    
End Sub

Here’s what’s happening:

  • Select the cell you want to read (A1).
  • Apply Str to convert its value to a string.
  • Insert the result into B1.

It’s a straightforward method to append text form of numeric values to other cells. Simple and handy!

Notes

  • When I run a macro that includes the VBA Str function, it always prefixes the numerical value with a space to indicate the sign.
  • Beware, if I mistakenly feed in non-numeric content or something unusable as a number, I’ll encounter a run-time 13 error.

Similar Posts