What is Excel VBA Join Function?
The Excel VBA JOIN function is used to join an array of substrings with a specified delimiter and returns a single string as the result. It is listed under the Array category of VBA functions and can combine multiple strings into one string, just like the Concatenate worksheet function.
Let’s see a simple example of VBA Join ArrayList:
Create a subroutine to declare an array of strings (names in this case) and then perform VBA Join collection of these strings with the separation value deemed as ‘,.’ Print the output in a Message Box.
Table of Contents
Key Takeaways
- The VBA Join function is used to join an array of substrings with a specified delimiter and returns a single string in the result.
- The syntax of the function is Join(SourceArray, [Delimiter]), where SourceArray is an array of values that you want to join as a new string, and [Delimiter] is a delimiter that you want to use to separate each of the substrings when making up the new string.
- There is no built-in function to concatenate two arrays for VBA Join Arraylist.
- To merge two arrays, you can use the Join function to convert each array to a string, concatenate the strings, and then use the Split function to convert the concatenated string back to an array.
- To join two tables in VBA, you can use macros that combine data and tables using criteria, VLOOKUP or INDEX MATCH formulas, or Power Query or Merge Tables Wizard.
Syntax
The syntax for VBA Join is as shown below:
Join(SourceArray, [Delimiter])
Where,
- SourceArray is an array of values that you want to join as a new string.
- [Delimiter] is a delimiter that you want to use to separate each of the substrings when making up the new string.
Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)
If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.
How to Use Excel VBA JOIN Function?
To correctly use VBA Join columns, follow the steps below.
Step 1: Open the Excel Workbook. In the toolbar, select “Developer.”
In Developer, on the far-left corner, select “Visual Basic.”
It opens the VBA Editor.
In the Editor, select the “Insert” button in the toolbar, and in the dropdown, select “Module.”
Start with creating subroutines for your workbook.
Step 2: Start with defining the name of the subroutine.
Step 3: Initialize an array of range 0-4.
It is important to declare the array as a String datatype since the VBA Join function will throw an error for other datatypes (Integer or Variant).
Step 4: Define the values of the array in VBA with the corresponding cell values.
The values mentioned are predefined earlier in the worksheet.
Step 5: Perform VBA Join collection on the given array values and print it in cell “A7.”
Since there is no delimiter mentioned, the default separation value is space.
Code:
Sub example_JOIN()
Dim myAry(0 To 4) As String
myAry(0) = Range(“A1”)
myAry(1) = Range(“A2”)
myAry(2) = Range(“A3”)
myAry(3) = Range(“A4”)
myAry(4) = Range(“A5”)
Range(“A7”).Value = Join(myAry)
End Sub
Step 6: Click “F5” or the “Run” icon on the activity bar in the Excel VBA Module to run the program.
Now that we know how to use the Join function in Excel VBA, let us view some exciting examples below.
Examples
Here, we can view the different ways in which VBA join range can be used.
Example #1
Given a list of emails, you want to club them together and display them in a message box. It can be done by using VBA Join.
Step 1: Start with initializing the name of the subroutine to join emails and display them all together.
Step 2: Initialize a Range variable to hold in the range of emails we’ll be checking in the workbook.
Step 3: Declare the range of the emails you want to check.
Step 4: Define a string array in VBA.
Step 5: Declare the size of the string array declared in Step 4 to the number of cells in the range.
It is subtracted with one since the Excel range goes from 1-5, but an array’s range starts from 0. Hence, you only need the size 0-4, which has five values.
Step 6: Initialize an iterative variable to use in a FOR-loop.
Step 7: Create a FOR-loop to add the cell values containing the emails to the string array.
Since i starts from 0, we add one while looping it to find the cell values in Excel.
Step 8: Initialize a string variable to hold the concatenated values after performing VBA Join collection.
Step 9: Perform VBA Join with the string array, with the separation value defined as “;”.
Code:
Sub email_JOIN()
Dim emailList As Range
Set emailList = Range(“A1:A5”)
Dim emails() As String
ReDim emails(emailList.Cells.Count – 1)
Dim i As Integer
For i = 0 To emailList.Cells.Count – 1
emails(i) = emailList.Cells(i + 1).Value
Next i
Dim emailString As String
emailString = Join(emails, “; “)
MsgBox emailString
End Sub
Step 10: Press “F5” to run the code. A Message Box pops up to show the concatenated values.
Example #2
Here, we need to concatenate all available directories in a given range and perform VBA Join Range. The table is given below.
With this, you can join all the cell values by following the steps below.
Step 1: Start with naming the sub-procedure to concatenate all directories.
Step 2: Define a range variable to store the range of cells we want to perform VBA Join upon.
Step 3: Set the range variable with your desired range.
Step 4: Initialize a string array and calculate its size by counting the range of cells declared and subtracting it by 1 to get the correct size.
It is done to prevent “Out of Range” errors in VBA due to variable-workbook range mismatch.
Step 5: Initialize an iterative variable to run through the string array.
Step 6: Initialize the values to be added to the current day, that is, the number of days and number of hours.
Step 7: Declare a FOR loop to add the directories in cells to the array.
Step 8: Define a string variable to store in the concatenated values after the VBA Join range.
Step 9: Perform VBA Join on the string array and then print the values in the Immediate tab.
Declare the delimiter/ separation value as vbCrLf.
This is a constant in VBA used to declare a new line.
Code:
Sub dir_JOIN()
Dim pathList As Range
Set pathList = Range(“A1:A10”)
Dim paths() As String
ReDim paths(pathList.Cells.Count – 1)
Dim i As Integer
For i = 0 To pathList.Cells.Count – 1
paths(i) = pathList.Cells(i + 1).Value
Next i
Dim pathString As String
pathString = Join(paths, vbCrLf)
Debug.Print pathString
End Sub
Step 9: Click the green arrow button when you want to run the code. This will print all the values in the Immediate tab.
Important Things To Note
- The JOIN function doesn’t work if the array is declared as a date or variant VBA data type.
- To use the JOIN function, you need to declare an array of values that you want to join and then pass it as an argument to the JOIN function.
- You can use a loop to populate the array with values from a range of cells in an Excel worksheet.
- Make sure to test your code thoroughly to ensure that it produces the desired result.
- If the delimiter is omitted, the default delimiter is a space ” “.
- Before joining tables, make sure that they have a common field or column that can be used to match the data.
Frequently Asked Questions (FAQs)
● To concatenate two strings using a VBA code, you need to use the ampersand. You can use an ampersand in between two strings to combine them and then assign that new value to a cell, variable, or range.
● There are two concatenation operators in VBA, + and &, both carry out the basic concatenation operation.
● You can use a loop to concatenate values from a range of cells in an Excel worksheet.
To perform the VBA Join Array list, you can refer to the steps below:
● To merge two arrays, you can use the Join function to convert each array to a string, concatenate the strings, and then use the Split function to convert the concatenated string back to an array.
● You can also use a loop to copy the elements of one array to another array and then copy the elements of the second array to the end of the first array.
To perform VBA join range for two tables:
● We can use the VLOOKUP and INDEX MATCH function combination to merge tables.
● To use VLOOKUP or INDEX MATCH, you need to have a common column in both tables that can be used to match the data.
● In the first table, you must add a column that contains the common column from the second table.
● You can then use VLOOKUP or INDEX MATCH to look up the values from the second table and return them to the first table.
● Once the tables are merged, you can use the JOIN function to join the values from the merged table into a single string with a specified delimiter.
● The code may be contained inside an automatically running subroutine, such as an Auto_Open or Auto_Close subroutine, which may not function correctly when you open or close your workbook.
● The JOIN function may not work if the array is declared as a date data type or variant data type.
● The code may not be contained in a Visual Basic module, but “behind” a worksheet or the workbook itself.
● The tables may not have a common field or column that can be used to match the data.
Download Template
This article must be helpful to understand the VBA JOIN. with its formula and examples. You can download the template here to use it instantly.
Recommended Articles
This has been a guide to VBA JOIN. Here we learn how to use Excel VBA Join Function and syntax along with examples & download excel template. You can learn more from the following articles –
Leave a Reply