Space Function

Returns a String consisting of the specified number of spaces.

Syntax

Space(number)

Parameters

number - Required. Long or any valid numeric expression specifying the number of spaces to return.

Return Value

Returns a String containing the specified number of space characters (ASCII 32).

Remarks

The Space function is useful for creating strings with a specific number of spaces, commonly used for: - Formatting output in fixed-width columns - Creating indentation in text - Padding strings to specific lengths - Aligning text in reports or displays - Creating blank lines or spacing in file output

Key characteristics: - Returns a string of space characters (ASCII value 32) - If number is 0, returns an empty string ("") - If number is negative, generates Error 5 (Invalid procedure call or argument) - Non-integer values are rounded to the nearest integer - Maximum practical limit is system memory for string storage

The Space function is related to other string generation functions: - Space(n): Creates n space characters - String(n, character): Creates n repetitions of any character - String(n, charcode): Creates n repetitions of character with given ASCII code

Typical Uses

Column Formatting: Align text in fixed-width columns
Indentation: Create indented text structures
Padding: Pad strings to specific widths
Separation: Add spacing between elements
Report Generation: Format reports with proper alignment
Text Files: Create formatted text file output
Display Alignment: Align data in list boxes or text boxes
Table Creation: Build ASCII tables with proper spacing

Basic Examples

' Example 1: Create a string of 5 spaces
Dim spaces As String
spaces = Space(5)
' Returns "     " (5 spaces)

' Example 2: Pad a string to 20 characters
Dim name As String
Dim paddedName As String
name = "John"
paddedName = name & Space(20 - Len(name))
' Returns "John                " (16 trailing spaces)

' Example 3: Create indented text
Dim level As Integer
Dim text As String
level = 3
text = Space(level * 4) & "Indented text"
' Returns "            Indented text" (12 spaces for 3 levels of 4-space indent)

' Example 4: Format columns in output
Dim item As String
Dim price As String
item = "Apple"
price = "$1.99"
Debug.Print item & Space(20 - Len(item)) & price
' Outputs: "Apple               $1.99"

Common Patterns

Pattern 1: `PadRight`

Pad string to specified width (right padding)

Function PadRight(text As String, totalWidth As Integer) As String
Dim currentLen As Integer
currentLen = Len(text)

If currentLen >= totalWidth Then
PadRight = text
Else
PadRight = text & Space(totalWidth - currentLen)
End If
End Function

Pattern 2: `PadLeft`

Pad string to specified width (left padding)

Function PadLeft(text As String, totalWidth As Integer) As String
Dim currentLen As Integer
currentLen = Len(text)

If currentLen >= totalWidth Then
PadLeft = text
Else
PadLeft = Space(totalWidth - currentLen) & text
End If
End Function

Pattern 3: Center

Center text within specified width

Function Center(text As String, totalWidth As Integer) As String
Dim currentLen As Integer
Dim leftPadding As Integer
Dim rightPadding As Integer

currentLen = Len(text)

If currentLen >= totalWidth Then
Center = text
Exit Function
End If

leftPadding = (totalWidth - currentLen) \ 2
rightPadding = totalWidth - currentLen - leftPadding

Center = Space(leftPadding) & text & Space(rightPadding)
End Function

Pattern 4: `CreateIndent`

Create indentation for nested structures

Function CreateIndent(level As Integer, Optional spacesPerLevel As Integer = 4) As String
If level <= 0 Then
CreateIndent = ""
Else
CreateIndent = Space(level * spacesPerLevel)
End If
End Function

Pattern 5: `FormatColumn`

Format text in fixed-width column

Function FormatColumn(text As String, width As Integer, _
Optional alignment As String = "LEFT") As String
Select Case UCase(alignment)
Case "LEFT"
FormatColumn = PadRight(text, width)
Case "RIGHT"
FormatColumn = PadLeft(text, width)
Case "CENTER"
FormatColumn = Center(text, width)
Case Else
FormatColumn = text
End Select
End Function

Pattern 6: `CreateSeparator`

Create separator line with spaces

Function CreateSeparator(leftText As String, rightText As String, _
totalWidth As Integer, _
Optional separator As String = " ") As String
Dim leftLen As Integer
Dim rightLen As Integer
Dim middleSpaces As Integer

leftLen = Len(leftText)
rightLen = Len(rightText)
middleSpaces = totalWidth - leftLen - rightLen

If middleSpaces < 0 Then middleSpaces = 0

CreateSeparator = leftText & String(middleSpaces, separator) & rightText
End Function

Pattern 7: `BuildTableRow`

Build formatted table row

Function BuildTableRow(columns() As String, widths() As Integer) As String
Dim row As String
Dim i As Integer

row = ""
For i = LBound(columns) To UBound(columns)
If i > LBound(columns) Then row = row & " | "
row = row & PadRight(columns(i), widths(i))
Next i

BuildTableRow = row
End Function

Pattern 8: `IndentMultiline`

Indent all lines in multiline text

Function IndentMultiline(text As String, spaces As Integer) As String
Dim lines() As String
Dim result As String
Dim i As Integer
Dim indent As String

lines = Split(text, vbCrLf)
indent = Space(spaces)
result = ""

For i = LBound(lines) To UBound(lines)
If i > LBound(lines) Then result = result & vbCrLf
result = result & indent & lines(i)
Next i

IndentMultiline = result
End Function

Pattern 9: `CreateBlankLine`

Create blank line with specific spacing

Function CreateBlankLine(width As Integer) As String
CreateBlankLine = Space(width)
End Function

Pattern 10: `AlignNumber`

Right-align number in field

Function AlignNumber(value As Variant, width As Integer, _
Optional decimals As Integer = 2) As String
Dim formatted As String

formatted = Format(value, "0." & String(decimals, "0"))
AlignNumber = PadLeft(formatted, width)
End Function

Advanced Usage

Example 1: `TableFormatter` Class

Format data in ASCII tables

' Class: TableFormatter
Private m_columnWidths() As Integer
Private m_columnCount As Integer
Private m_alignment() As String

Private Sub Class_Initialize()
m_columnCount = 0
End Sub

Public Sub SetColumns(widths() As Integer, Optional alignments As Variant)
Dim i As Integer

m_columnCount = UBound(widths) - LBound(widths) + 1
ReDim m_columnWidths(LBound(widths) To UBound(widths))
ReDim m_alignment(LBound(widths) To UBound(widths))

For i = LBound(widths) To UBound(widths)
m_columnWidths(i) = widths(i)
If IsMissing(alignments) Then
m_alignment(i) = "LEFT"
Else
m_alignment(i) = alignments(i)
End If
Next i
End Sub

Public Function FormatRow(values() As String) As String
Dim row As String
Dim i As Integer
Dim formattedValue As String

row = ""
For i = LBound(values) To UBound(values)
If i > LBound(values) Then row = row & " | "

formattedValue = FormatCell(values(i), m_columnWidths(i), m_alignment(i))
row = row & formattedValue
Next i

FormatRow = row
End Function

Private Function FormatCell(value As String, width As Integer, _
alignment As String) As String
Dim currentLen As Integer
Dim padding As Integer

currentLen = Len(value)

If currentLen >= width Then
FormatCell = Left(value, width)
Exit Function
End If

padding = width - currentLen

Select Case UCase(alignment)
Case "LEFT"
FormatCell = value & Space(padding)
Case "RIGHT"
FormatCell = Space(padding) & value
Case "CENTER"
Dim leftPad As Integer
Dim rightPad As Integer
leftPad = padding \ 2
rightPad = padding - leftPad
FormatCell = Space(leftPad) & value & Space(rightPad)
Case Else
FormatCell = value & Space(padding)
End Select
End Function

Public Function CreateHeader(headers() As String) As String
Dim header As String
Dim separator As String
Dim i As Integer

header = FormatRow(headers)
separator = ""

For i = LBound(m_columnWidths) To UBound(m_columnWidths)
If i > LBound(m_columnWidths) Then separator = separator & "-+-"
separator = separator & String(m_columnWidths(i), "-")
Next i

CreateHeader = header & vbCrLf & separator
End Function

Public Function GetTotalWidth() As Integer
Dim total As Integer
Dim i As Integer

total = 0
For i = LBound(m_columnWidths) To UBound(m_columnWidths)
total = total + m_columnWidths(i)
Next i

' Add separator widths
total = total + (m_columnCount - 1) * 3  ' " | " between columns

GetTotalWidth = total
End Function

Example 2: `ReportGenerator` Module

Generate formatted text reports

' Module: ReportGenerator

Public Function GenerateReport(title As String, data() As Variant, _
columnHeaders() As String, _
columnWidths() As Integer) As String
Dim report As String
Dim i As Integer
Dim j As Integer
Dim totalWidth As Integer
Dim titleLine As String

' Calculate total width
totalWidth = 0
For i = LBound(columnWidths) To UBound(columnWidths)
totalWidth = totalWidth + columnWidths(i)
Next i
totalWidth = totalWidth + (UBound(columnWidths) - LBound(columnWidths)) * 3

' Center title
titleLine = CenterText(title, totalWidth)
report = titleLine & vbCrLf
report = report & String(totalWidth, "=") & vbCrLf

' Add header
For i = LBound(columnHeaders) To UBound(columnHeaders)
If i > LBound(columnHeaders) Then report = report & " | "
report = report & PadRight(columnHeaders(i), columnWidths(i))
Next i
report = report & vbCrLf

' Add separator
For i = LBound(columnWidths) To UBound(columnWidths)
If i > LBound(columnWidths) Then report = report & "-+-"
report = report & String(columnWidths(i), "-")
Next i
report = report & vbCrLf

' Add data rows
For i = LBound(data, 1) To UBound(data, 1)
For j = LBound(data, 2) To UBound(data, 2)
If j > LBound(data, 2) Then report = report & " | "
report = report & PadRight(CStr(data(i, j)), columnWidths(j))
Next j
report = report & vbCrLf
Next i

GenerateReport = report
End Function

Private Function CenterText(text As String, width As Integer) As String
Dim textLen As Integer
Dim leftPad As Integer
Dim rightPad As Integer

textLen = Len(text)

If textLen >= width Then
CenterText = text
Exit Function
End If

leftPad = (width - textLen) \ 2
rightPad = width - textLen - leftPad

CenterText = Space(leftPad) & text & Space(rightPad)
End Function

Private Function PadRight(text As String, width As Integer) As String
If Len(text) >= width Then
PadRight = Left(text, width)
Else
PadRight = text & Space(width - Len(text))
End If
End Function

Public Function CreateSummaryLine(label As String, value As String, _
totalWidth As Integer) As String
Dim labelLen As Integer
Dim valueLen As Integer
Dim spacesNeeded As Integer

labelLen = Len(label)
valueLen = Len(value)
spacesNeeded = totalWidth - labelLen - valueLen

If spacesNeeded < 1 Then spacesNeeded = 1

CreateSummaryLine = label & Space(spacesNeeded) & value
End Function

Example 3: `CodeFormatter` Class

Format source code with indentation

' Class: CodeFormatter
Private m_indentLevel As Integer
Private m_spacesPerIndent As Integer
Private m_output As String

Private Sub Class_Initialize()
m_indentLevel = 0
m_spacesPerIndent = 4
m_output = ""
End Sub

Public Property Let SpacesPerIndent(value As Integer)
If value > 0 Then m_spacesPerIndent = value
End Property

Public Sub IncreaseIndent()
m_indentLevel = m_indentLevel + 1
End Sub

Public Sub DecreaseIndent()
If m_indentLevel > 0 Then
m_indentLevel = m_indentLevel - 1
End If
End Sub

Public Sub AddLine(text As String)
Dim indent As String
indent = Space(m_indentLevel * m_spacesPerIndent)

If m_output <> "" Then m_output = m_output & vbCrLf
m_output = m_output & indent & text
End Sub

Public Sub AddBlankLine()
If m_output <> "" Then m_output = m_output & vbCrLf
End Sub

Public Function GetOutput() As String
GetOutput = m_output
End Function

Public Sub Clear()
m_output = ""
m_indentLevel = 0
End Sub

Public Sub AddBlock(blockStart As String, blockEnd As String, _
lines() As String)
Dim i As Integer

AddLine blockStart
IncreaseIndent

For i = LBound(lines) To UBound(lines)
AddLine lines(i)
Next i

DecreaseIndent
AddLine blockEnd
End Sub

Example 4: `ListBoxFormatter` Module

Format items for list box display

' Module: ListBoxFormatter

Public Function FormatListItem(item As String, value As String, _
totalWidth As Integer, _
Optional separator As String = " ") As String
Dim itemLen As Integer
Dim valueLen As Integer
Dim separatorLen As Integer
Dim spacesNeeded As Integer

itemLen = Len(item)
valueLen = Len(value)
separatorLen = Len(separator)

spacesNeeded = totalWidth - itemLen - valueLen

If spacesNeeded < 1 Then spacesNeeded = 1

FormatListItem = item & Space(spacesNeeded) & value
End Function

Public Sub PopulateFormattedList(lst As ListBox, items() As String, _
values() As String, width As Integer)
Dim i As Integer

lst.Clear

For i = LBound(items) To UBound(items)
lst.AddItem FormatListItem(items(i), values(i), width)
Next i
End Sub

Public Function CreateTreeItem(text As String, level As Integer, _
Optional expandSymbol As String = "+") As String
Dim indent As String

indent = Space(level * 2)

If level > 0 Then
CreateTreeItem = indent & expandSymbol & " " & text
Else
CreateTreeItem = text
End If
End Function

Public Function AlignCurrency(amount As Double, width As Integer) As String
Dim formatted As String

formatted = FormatCurrency(amount, 2)
AlignCurrency = Space(width - Len(formatted)) & formatted
End Function

Error Handling

The Space function can generate the following errors:

Error 5 (Invalid procedure call or argument): If number is negative
Error 6 (Overflow): If number exceeds Long range
Error 7 (Out of memory): If resulting string exceeds available memory
Error 13 (Type mismatch): If number is not numeric

Always validate inputs:

On Error Resume Next
result = Space(count)
If Err.Number <> 0 Then
MsgBox "Error creating spaces: " & Err.Description
End If

Performance Considerations

Very fast for small to moderate space counts (< 1000)
For large space counts, consider if you really need that many
String concatenation in loops can be slow; build once when possible
Space function is more efficient than repeated string concatenation
Consider caching commonly used space strings

Best Practices

Validate Count: Ensure space count is non-negative
Use Constants: Define column widths as constants for consistency
Avoid Magic Numbers: Use named constants instead of literal numbers
Handle Edge Cases: Check for zero or negative values
Consider Alternatives: For very large strings, evaluate necessity
Cache Results: Store frequently used space strings
Document Width: Comment expected column widths in code
Test Alignment: Verify output with different data lengths
Use Monospace: Ensure font is monospace for proper alignment
Combine with Format: Use with Format function for numeric alignment

Function	Purpose	Example	Result
Space(n)	n spaces	Space(5)	" "
String(n, " ")	n of any character	String(5, " ")	" "
String(n, 32)	n of ASCII char	String(5, 32)	" "
String(n, "*")	n asterisks	String(5, "*")	"*"

Platform Considerations

Available in VB6, VBA (all versions)
Part of core string functions
Consistent behavior across platforms
Subject to system memory limits
Maximum string length: approximately 2 billion characters (limited by available memory)

Limitations

Cannot create negative number of spaces (generates error)
Limited by available system memory
Non-integer values are rounded (e.g., Space(3.7) = Space(4))
Returns empty string for Space(0)
Not suitable for creating non-breaking spaces (use Chr(160) for HTML/Unicode)

String: Creates a string of repeated characters
SPC: Positions output in Print # statements
Tab: Positions output at specific column in Print # statements
LSet: Left-aligns string within string variable
RSet: Right-aligns string within string variable

Space Function

Syntax

Parameters

Return Value

Remarks

Typical Uses

Basic Examples

Common Patterns

Pattern 1: PadRight

Pattern 2: PadLeft

Pattern 3: Center

Pattern 4: CreateIndent

Pattern 5: FormatColumn

Pattern 6: CreateSeparator

Pattern 7: BuildTableRow

Pattern 8: IndentMultiline

Pattern 9: CreateBlankLine

Pattern 10: AlignNumber