How to use Spc function in VBA?

In VBA (Visual Basic for Applications), the Spc function is used to insert a specified number of space characters in a print line. The Spc function thus works with the Print statement typically when printing to files or the Immediate Window in the VBA environment for formatting purposes.

Here is an example on how to use the Spc function in VBA:


Sub UseSpcFunction()
    Dim numSpaces As Integer
    numSpaces = 10 ' Number of spaces to insert

    ' The following will print "Hello" followed by 10 spaces and then "World" in the Immediate Window
    Debug.Print "Hello"; Spc(numSpaces); "World"
End Sub

When you run this subroutine, it should output Hello World in the Immediate Window (Ctrl + G in the VBE to view it) with 10 spaces between “Hello” and “World”.

Do note that Spc function may not be commonly used for user-facing applications, it’s more of a convenience during debugging or when dealing with legacy code that interacts with text-file output.

For better control over strings and spaces in a user-facing application, it’s recommended to use string manipulation functions such as Space (which generates a string with a specified number of spaces) or string concatenation with the & operator.


Sub UseSpaceFunction()
    ' The following achieves similar to the above but using Space function
    Dim spaceStr As String
    spaceStr = Space(10)
    Debug.Print "Hello" & spaceStr & "World"
End Sub

This method is more versatile since Space gives you a string and you can use it anywhere you can use a string, not only with Print.

Unlock Your Potential


Basic - Advanced


Access Basic - Advanced

Power BI

Power BI Basic - Advanced

Help us grow the project

Leave a Reply

Your email address will not be published. Required fields are marked *