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