VBA Array Join function

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 substrings 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 Substrings using Join Function without Delimiter

Let us see the example vba macro code using array Join function inExcel. In the below example we have specified an array with substrings. We are joining substrings 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 Substrings using Join Function with Delimiter

Here is the example vba macro code using array Join function inExcel.We are joining substrings 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 use Macro

Here are the instructions to use above macro in Visual basic editor.

  • Open Visual Basic Editor(VBE) by clicking Alt +F11
  • Go to code window by clicking F7
  • Copy above specified macro or procedure
  • Paste above copied code in code window
  • Run macro by clicking F5 or Run command
  • You can see output on the screen
  • Find above specified output screenshot.

Other related VBA Arrays articles

You may also like the related VBA Array articles.

Back to VBA Arrays




Leave a Comment

Your email address will not be published. Required fields are marked *