VBA JOIN

Updated on January 1, 2024
Article byBabita Sehdev
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

Similar to what we have in a worksheet, as Concatenate function and the & command, one may join two or more two strings together. In VBA, we use the Join command to do so. In Join, we take the data source in an array, and similar to the concatenation, we use a delimiter to join them.

Excel VBA JOIN Function

As the name suggests, one may use the VBA JOIN function to combine an array of substrings with the specified delimiter. If we do not specify any delimiter, it takes ‘space’ as a default delimiter character. It does the same work as the Concatenate function in Excel, except we only have to specify the delimiter character once. In the Concatenate functionConcatenate FunctionThe CONCATENATE function in Excel helps the user concatenate or join two or more cell values which may be in the form of characters, strings or numbers.read more, we have to specify the delimiter character every time between every two strings.

The syntax of the function is

VBA Join Syntax

As we can see, the function takes two arguments and returns a string. Arguments are:

  1. SourceArray: We need to specify or reference an array of substrings joined.
  2. Delimiter: The delimiter is used to separate each substring when creating the resultant string. As this is an optional argument, if we omit it, the delimiter is set to be a space ” “.

The VBA SPLIT functionVBA SPLIT FunctionSplit function in VBA is used to split strings into multiple substrings based on a delimiter provided to the function and a comparison method. Unlike other string functions, split function can split a string into more than one substrings.read more is the opposite of the VBA JOIN function.

VBA JOIN

You are free to use this image on your website, templates, etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: VBA JOIN (wallstreetmojo.com)

–>> If you want to learn Excel VBA professionally, then our VBA Basic Course (16+ hours) is the perfect solution. In our Basic Excel VBA course you learn the skill of automating tasks using Variables, Data Types, Ranges, and Cells in VBA. Master Control Structures, including Conditional Statements and Loops, and discover techniques for manipulating data through sorting, filtering, and formatting. By the end of the course, you will be able to apply your acquired skills to real projects, culminating in an Excel VBA project which will solidify your ability to create efficient, automated solutions.

Examples of VBA Join Function

Below are examples of the Join function in Excel VBA.

You can download this VBA Join Function Excel Template here – VBA Join Function Excel Template

VBA Join – Example #1

Suppose we want to join the first (Ramesh), middle (Kumar), and last name (Mishra).

VBA join Example 1

Steps would be:

Developer Tab
  • Insert the module by right-clicking on “sheet 1” and choosing the ‘Insert’ command from the contextual menu, and then choose ‘Module’ to insert.
VBA join Example 1-1
  • Create a subroutine named ‘JoiningName’.

Code:

Sub JoiningName()

End Sub
VBA join Example 1-2
  • Use the JOIN function as follows.

Code:

Sub JoiningName()

Range("D2").Value = Join(Array("Ramesh", "Kumar", "Mishra"))

End Sub
VBA join Example 1-3

We can see that we have used the ARRAY function to provide SourceArray to the JOIN function and skipped to specify the delimiter character, so ‘space’ would be the default character. The processed value of the JOIN function will be written in cell D2 when we execute this code using the F5 key or manually.

VBA Join Example 1-4

VBA Join – Example #2

Suppose we want to create various Excel files with the item name containing sales only for that item.

VBA join Example 2
VBA join Example 2-1
  • Define a subroutine named ‘CreateItemSoldFiles’.

Code:

Sub CreateItemSoldFiles()

End Sub
VBA join Example 2-2
  • We need to set a reference to the ‘Microsoft Scripting Runtime’ object library using the Tools menu -> References… command, as we will use some code (objects), which will not work if we do not include this object library.
VBA join Example 2-3
VBA join Example 2-4
  • Now, we will declare all the variables.

Code:

Dim FSO As New Scripting.FileSystemObject
VBA join Example 2-5

The above FSO variable gives access to the VBA FileSystemObjectVBA FileSystemObjectVBA FileSystemObject (FSO) is similar to FileDialog in that it is used to access other files on the computer we are working on. We can also edit these files, which means we can read or write to them.read more. After binding, we can use functions like BuildPath, CopyFile, CreateTextFile, etc.

  • The next statement creates a TextStream object. Through the TextStream object, we can read from or append to the original file.

Code:

Dim FSO As New Scripting.FileSystemObject
Dim ts As Scripting.TextStream
VBA join Example 2-6.png
  • We will declare more variables. ‘r’ is for holding rows in the range, ‘fs’ is for storing the final joined string, ‘cols’ for storing numbers of columns in the range, ‘FolPath’ for storing the path of the folder so that we can save the files in the folder and ‘Items_Sold’ for storing various item names to create a file with these names.

Code:

Dim r As Range
Dim fs As String
Dim cols As Integer
Dim FolPath As String
Dim Items_Sold As String
VBA join Example 2-7
  • We will define the following statement to count the total number of columns in the range.

Code:

cols = Range("A1").CurrentRegion.Columns.Count
VBA join Example 2-8

This statement will first select the current region for cell A1 and then count the total number of columns in the current region.

Code:

FolPath = Environ("UserProfile") & "DesktopItems_Sold"
If Not FSO.FolderExists(FolPath) Then FSO.CreateFolder FolPath
VBA join Example 2-9

The second statement will create the folder if the folder does not exist in the same location.

  • This code will assign the values of the B column one by one to ‘Items_Sold.’ We have used the ‘OFFSET function’ to get the reference of the cell in the B column as the currently selected cell is in column A.

Code:

Items_Sold = r.Offset(0, 1).Value
VBA join Example 2-10
  • The following bordered statement will open the files with names stored in the ‘Items_Sold’ variable one by one in appending mode (it will append the new values at last).

Code:

Set ts = FSO.OpenTextFile(FolPath & "" & Items_Sold & ".xls", ForAppending, True)
Example 2-11

We have used the Concatenate operator with variables ‘FolPath’ and ‘Items_Sold’ and static values (“” and”.xls”) to create file names for excel files.

  • We need to remember that the VBA JOIN function takes only a one-dimensional array as SourceArray To convert the rows into a one-dimensional array, we need to use Application.Transpose method two times.

Code:

fs = Join(Application.Transpose(Application.Transpose(r.Resize(1, cols).Value)), vbTab)
Example 2-12

We have used the Resize method of range object to resize the range to the width of several columns in the range.

As a delimiter, we have used the ‘vbTab’ keyword so that it would fill values in different cells.

Example 2-13
  • Before ending the loop, we will close the file. The code would be as shown in the screenshot.
Example 2-14

We have written the full code now.

Code:

Sub CreateItemSoldFiles()

Dim FSO As New Scripting.FileSystemObject
Dim ts As Scripting.TextStream

Dim r As Range
Dim fs As String
Dim cols As Integer
Dim FolPath As String
Dim Items_Sold As String

cols = Range("A1").CurrentRegion.Columns.Count

FolPath = Environ("UserProfile") & "DesktopItems_Sold"
If Not FSO.FolderExists(FolPath) Then FSO.CreateFolder FolPath

For Each r In Range("A2", Range("A1").End(xlDown))

Items_Sold = r.Offset(0, 1).Value
Set ts = FSO.OpenTextFile(FolPath & "" & Items_Sold & ".xls", ForAppending, True)
fs = Join(Application.Transpose(Application.Transpose(r.Resize(1, cols).Value)), vbTab)
ts.WriteLine fs

ts.Close
Next r

End Sub

To execute the code, we will press F5. Then, we can see that it created a folder named ‘Items_Sold’ with the help of VBA code on the desktop.

Example 2-15

In the folder, there are 7 unique files created with the names of the item. Therefore, we can find details about only that particular item in files.

Example 2-16

Laptop.xls

Example 2-17

Things to Remember About VBA JOIN Function

  • The SourceArray must be a one-dimensional array. Therefore, we cannot refer to an individual cell, as this will create multiple multi-dimensional arrays.
  • Suppose we specify a zero-length string (“”) as a delimiter, all items in the array concatenated with no delimiters.

Recommended Articles

This article has been a guide to VBA Join. Here, we learn how to use the VBA Join function to combine an array of substrings with the specified delimiter, along with examples and downloadable templates. Below are some useful articles related to VBA: –