VBA Array Join Function in Excel

VBA Array Join function

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.

VBA Join Function

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.

VBA Array Join Function

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”

  1. 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!

Leave a Comment