How to Use VBA Space Function in Excel (3 Examples)

In this tutorial, I will explain the details of the excel VBA Space function along with some examples. This function can only be used in VBA code. Usually, this function is useful for formatting data in the output. Besides, you can use VBA Space to clear data in fixed-length strings. So, let’s see some examples of using the function.


Download Practice Workbook

You can download the practice workbook that we have used to prepare this article.


Introduction to VBA Space Function

  • Description

VBA Space is a String function in excel. This function adds spaces to a string by supplying specific numbers. 

  • Syntax

Space(Number As Long)

  • Arguments

Argument Requirement Explanation
Number As Long Required The number of spaces you want to have in a given string

 

  • Return Value

VBA Space returns a string value along with specified spaces.

  • Available in Version

Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011; for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000.


3 Examples to Use VBA Space Function in Excel

1. Add Space to String Using VBA Space in Excel

For example, I have a string (Good Morning) containing two words. Now, using the VBA Space, I can add as many spaces in this string (between words). First, we will add seven (7) spaces in our specified string.

Steps:

  • Go to a blank worksheet in excel, right-click on the sheet name and click on the View Code option.

Add Space to String Using VBA Space in Excel

  • As a result, the VBA window will appear. Write the below code in the Module.
Sub AddSpace_Ex1()

Dim MyString As String
MyString = "Good" & Space(2) & "Morning"
MsgBox MyString

End Sub

Add Space to String Using VBA Space in Excel

  • Run the code by pressing the F5 key or clicking on the Run icon in the VBA window.

Add Space to String Using VBA Space in Excel

  • Upon running the code, in the message box, we will see seven (7) spaces added to our string.

Add Space to String Using VBA Space in Excel

  • If you want to change the number of spaces just change the code accordingly. For instance, I will add only two (2) spaces to my string.

  • As a consequence, we will get the following result.

Read More: How to Use VBA Str Function in Excel (4 Examples)


2. Use VBA Space Function to Create String with Five Spaces

Sometimes, if you want to create strings with a particular number of spaces, VBA Space can be used. For example, I will create a string with five spaces. You can modify the code if you want to have a different number of spaces.

Steps:

  • Go to a blank worksheet, right-click on the sheet name and click the View Code option to bring up the VBA window.
  • Type the below code in the Module.
'Create a string with 5 spaces
Sub VBA_Space_Ex2()

Dim iInput As Integer, Output As String
iInput = 5
Output = Space(iInput)
MsgBox "String with 5 spaces: <" & Output & ">", vbInformation, "VBA Space"

End Sub

Use VBA Space Function to Create String with Five Spaces

  • Run the code by pressing F5 and we will get the below message box. A string with 5 spaces is created as we have wanted.

Read More: How to Use VBA StrComp in Excel (5 Common Examples)


Similar Readings:


3. Create String with Zero Space Applying Excel VBA Space

Likewise in Example 2, now I will show how to create a string with zero space.

Steps:

  • Go to a blank sheet and right-click on the sheet name and click on the View Code option. Then the VBA window will appear.
  • Write the following code in the Module.
'Create a string with zero space
Sub VBA_Space_Ex3()

Dim iInput As Integer, Output As String
iInput = 0
Output = Space(iInput)
MsgBox "String with 0 space: <" & Output & ">", vbInformation, "VBA Space"

End Sub

Create String with Zero Space Applying Excel VBA Space

  • Press the key F5 to run the above code and the below message box will appear with a string with zero space.

Related Content: How to Use IsNull Function in Excel VBA (5 Examples)


Common Errors of Using VBA Space Function in Excel

While working with the VBA Space function, we face some common errors. Let’s have a look at the reason for those errors.

➤ If you enter a negative number as the number of spaces in VBA, it will return the Run-time error ‘5’ on running the code. For example, we will try to run the code in the below screenshot.

Common Errors of Using VBA Space Function in Excel

Unfortunately, upon pressing the F5 key, we will see the below error (Run-time error ‘5’).

Common Errors of Using VBA Space Function in Excel

➤ Again, if the supplied space number is given NULL, the code will return Run-time error ‘94’. For instance, I will try to run the following code now (see screenshot).

Common Errors of Using VBA Space Function in Excel

Now if you run the code, sadly, the below error (Run-time error ‘94’) will show up.


Conclusion

In the above article, I have tried to discuss several examples to use the VBA Space function in excel elaborately. Hopefully, these examples and explanations will be enough to solve your problems. Please let me know if you have any queries.


Related Articles

Hosne Ara

Hosne Ara

Hi, This is Hosne Ara. Currently, I do write for ExcelDemy. I have a long experience working with different industries and I have seen how vast the scope of Microsoft Excel is. So, eventually, I started to write articles on Excel and VBA. Basically, my articles are targeted to help people who are working in Excel. By profession, I am an Engineer. Materials and Metallurgical Engineering is my major. Besides, I am a certified Project Manager (PMP) too. I have worked with Power Plant and IT industry earlier. As a person, I am detail-oriented and love doing research. Establishing a greener world is one of my mottos.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo