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.

**Table of Contents**hide

## 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.

- 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
```

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

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

- 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
```

- 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:**

**How to Use the VBA Environ Function (4 Examples)****Use VBA IsEmpty Function (5 Relevant Examples)****How to Use VBA And Function in Excel (4 Examples)****Use VBA Mod Operator (9 Examples)****How to Use VBA IsNumeric Function (9 Examples)**

### 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
```

- 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.

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

➤ 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).

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.