VBA Array Join Function in Excel. The Join function helps us to join sub strings in an array and returns a single string. It is an opposite of VBA Array Split function. We can also use ‘&’ operator to concatenate strings. It performs same like the join function.
Syntax of the Join Function
Here is the Syntax of the Join Function in Excel VBA.
Join(SourceArray, [Delimiter]) As String
Where SourceArray: It is a mandatory argument. The SourceArray argument represents the array of sub-strings which we want to join together. It should be one dimensional array.
Delimiter: It is an optional argument. The Delimiter argument represents the delimiter which is to be used to concatenate strings. It can be anything like space, comma, etc. The Space is a common delimiter.
Example1: Concatenate Sub-strings using Join Function without Delimiter
Let us see the example VBA macro code using array Join function in Excel. In the below example we have specified an array with sub-strings. We are joining sub-strings without delimiter. Default it considers space as a delimiter.
'VBA Array Join Function without Delimiter Sub VBA_Array_Join_Function_Without_Delimiter() 'Declare Variables Dim sResult As String Dim aSubstrings(1) As String 'Define an Array values aSubstrings(0) = "FName" aSubstrings(1) = "LName" 'Concatenate Substrings sResult = Join(aSubstrings) 'Display Output on the Screen MsgBox "Add Substrings (FName and LName) without Delimiter: " & vbCrLf & sResult, vbInformation, "VBAF1" End Sub
Here is the screenshot of above VBA macro code.
Example2: Concatenate Sub-strings using Join Function with Delimiter
Here is the example vba macro code using array Join function in Excel.We are joining sub-strings with delimiter.
'VBA Array Join Function with using Delimiter '-'. Sub VBA_Array_Join_Function_With_Delimite() 'Declare Variables Dim sResult As String Dim aSubstrings(1) As String 'Define an Array values aSubstrings(0) = "FName" aSubstrings(1) = "LName" 'Concatenate Substrings sResult = Join(aSubstrings, "-") 'Display Output on the Screen MsgBox "Add Substrings (FName and LName) with Delimiter: " & vbCrLf & sResult, vbInformation, "VBAF1" End Sub
Let us see the screenshot of above macro code.
Instructions to Run VBA Macro Code or Procedure:
You can refer the following link for the step by step instructions.
Instructions to run VBA Macro Code
Other Useful Resources:
Click on the following links of the useful resources. These helps to learn and gain more knowledge.
VBA Tutorial VBA Functions List VBA Arrays VBA Text Files VBA Tables
VBA Editor Keyboard Shortcut Keys List VBA Interview Questions & Answers Blog
3 thoughts on “VBA Array Join Function in Excel”
hello this pst very interesting
Hello! I could have sworn I’ve been to this blog before but after
browsing through some of the post I realized it’s new to me.
Nonetheless, I’m definitely happy I found it and I’ll
be book-marking and checking back often!