VBA: Add Spaces to a String – Space()

October 6th, 2004 | Categories: Strings | Tags: ,

A common way to add spaces to a string with VBA is to use blank spaces between quotation marks. The following example adds ten spaces between the word “Hello” and “World”:

Sub AddSpaces()

Dim MyString As String

MyString = "Hello" & "          " & "World"
MsgBox MyString

End Sub

A more intuitive way to add spaces to a string is to use the Space() function. The following does the same as the previous code, however it uses the Space() function:

Sub AddSpaces()

Dim MyString As String

MyString = "Hello" & Space(10) & "World"
MsgBox MyString

End Sub

Sidenote: This works in VBA, however it doesn’t work in a spreadsheet formula.


Get LIVE Help
Custom Excel solutions & live Excel help!
► Go to www.ExcelAutomationHelp.com
► Email Now@ExcelAutomationHelp.com
  1. October 21st, 2004 at 21:02
    Reply | Quote | #1

    Want a worksheet formula? Check out the REPT function:

    =”Hello”&REPT(” “,10)&”World”

    - Jon

  2. Farhana
    July 20th, 2009 at 18:43
    Reply | Quote | #2

    i want to generate this loop which increse space like
    12345678910
    2345678910
    345678910
    45678910
    5678910
    678910
    78910
    8910
    910
    10

  3. Tuomo
    April 30th, 2010 at 09:11
    Reply | Quote | #3

    Can’t see any spaces…

    Farhana :i want to generate this loop which increse space like12345678910234567891034567891045678910567891067891078910891091010