When working with Microsoft Excel across different versions, from Excel 2000 all the way to the latest Office 365, incorporating spaces within text strings is a common task for formatting. As someone who frequently uses Excel’s Visual Basic for Applications (VBA) to automate tasks, I find the VBA SPACE function incredibly handy. It’s quite straightforward: I just need to specify the number of spaces required, and the function generates a string containing exactly that many spaces.

Whether I’m working on Excel 2010 or experimenting with Excel 2019, the function behaves consistently, which is essential for developing portable VBA code. In my scripts designed for Excel 2013 or even Excel 2011 for Mac, I use the SPACE function to format outputs neatly or to ensure that my text aligns perfectly within fixed-length strings. This functionality continues to save time and headaches, no matter the version of Excel I’m using.

Syntax

Space(Number)
  • Number: The number of spaces I want in a string.

Arguments

  • Number: Numeric value determining how many spaces I create.
  • Negative Number: Not allowed, it’ll cause an error.
  • String Result: I get a string comprising the spaces specified by Number.

Example

Sub example_SPACE()
    Dim myString As String
    myString = "Hello" & Space(5) & "World"
    Range("A1").Value = myString
End Sub

In this VBA code:

  • myString is initialized with content.
  • Space(5) inserts five fixed-length spaces.
  • The concatenated string is placed in cell A1.

To run:

  1. Open VBA editor (Alt + F11).
  2. Insert a new module.
  3. Copy and paste the macro code.
  4. Press F5 to execute.

Notes

  • Returns: Space function gives back a string of spaces.
  • Output: The result is only spaces; no other characters.
  • Formatting: Handy for spacing in strings.
  • Clear: Use for clearing data in strings of fixed length.
  • Empty String: If the number is zero, you get an empty string.
  • Run-time Error: Negative numbers cause an error (run-time 5).

Remarks:

  • No spaces are added beyond specified count.
  • Positive numbers are necessary for proper function.
  • Feedback is not a feature of this function.

Similar Posts