WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA JOIN

VBA JOIN

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

Excel VBA JOIN Function

As the name itself suggests, the VBA JOIN function is used to join together an array of substrings with the specified delimiter. If we do not specify any delimiter, then it takes ‘space’ as a default delimiter character. It does the same work as Concatenate function does in Excel except we have to specify delimiter character once only whereas, in Concatenate function, we have to specify delimiter character every time in 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 give reference to an array of substrings that are to be joined.
  2. Delimiter: The delimiter is used to separate each of the substrings when creating up the resultant string. As this is an optional argument, if we omit it, the delimiter is set to be a space ” “.

The VBA SPLIT function is an exact opposite function of the VBA JOIN function.

VBA Join

Examples of VBA Join Function

Below are the examples of 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:

  • First, we need to open the visual basic editor. We can do the same by clicking on ‘Visual Basic’ command in the ‘Code’ group under the ‘Developer’ tab excel or we can use the excel shortcut key Alt+F11.

Developer Tab

  • Insert the module by right-clicking on a “sheet 1” and choosing ‘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 ARRAY function to provide SourceArray to the JOIN function and skipped to specify the delimiter character so ‘space’ would be 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.

Popular Course in this category
Sale
VBA Training (3 Courses, 12+ Projects)
4.6 (247 ratings)
3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
View Course

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

  • Open the Visual Basic Editor using shortcut key Alt+F11.
  • Right click on ‘Sheet1′ (Example 2)’ sheet to open the contextual menu and click on ‘Insert’ to insert a VBA ‘Module’ in the VBA project.

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 ‘Microsoft Scripting Runtime’ object library using 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 FileSystemObject. 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 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

  • To count the total number of columns in range, we will define the following statement.

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 will count the total number of columns in the current region.

  • We will write the following statements for assigning the variable ‘FolPath’ a path using VBA ENVIRON function and Concatenation Operator.

Code:

FolPath = Environ("UserProfile") & "\Desktop\Items_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 B column one by one to ‘Items_Sold’ We have used ‘OFFSET function’ to get the reference of cell in 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 ‘Items_Sold’ variable in one by one in appending mode (the new values will be appended at last).

Code:

Set ts = FSO.OpenTextFile(FolPath & "\" & Items_Sold & ".xls", ForAppending, True)

Example 2-11

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

  • We need to keep in mind that VBA JOIN function takes an only 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 a number of columns in the range.

As delimiter, we have used ‘vbTab’ keyword so that values would be filled in different cells.

  • As we have stored the processed value of JOIN function into ‘fs’ variable, we will write the fs’s values into new lines of VBA created excel files for every row in our original file from row number 2 to the last row (in our case it is 350th row).

Example 2-13

  • Before ending the loop, we will close the file opened. 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") & "\Desktop\Items_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

Now to execute the code, we will press F5 then, we can see that a folder named ‘Items_Sold’ has been created 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 and we can find out 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. We cannot refer to an individual cell, as this will create multiple multi-dimensional arrays.
  • If we specify a zero-length string (“”) as a delimiter, all items in the array are concatenated with no delimiters.

Recommended Articles

This has been a guide to VBA Join. Here we learn how to use VBA Join Function to join together an array of substrings with the specified delimiter along with examples & downloadable templates. Below are some useful articles related to VBA –

  • VBA Operators
  • VBA COUNTIF
  • VBA Now
  • VBA End
4 Shares
Share
Tweet
Share
VBA Training (3 Courses, 12+ Projects)
  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download VBA Join Function Excel Template

New Year Offer - VBA Training Course (6 courses, 35+ hours video) View More