Чтобы объединить массив Variant в Excel VBA, вы можете использовать несколько методов. Вот несколько примеров кода:

1. Использование цикла:

   Function JoinArray(arr As Variant, Optional delimiter As String = ",") As String
   Dim result As String
   Dim i As Long
   
   For i = LBound(arr) To UBound(arr)
       result = result & arr(i) & delimiter
   Next i
   
   If Len(result) > 0 Then
       result = Left(result, Len(result) - Len(delimiter))
   End If
   
   JoinArray = result
   End Function

   Пример использования:

   Sub TestJoinArray()
   Dim myArray As Variant
   myArray = Array("Apple", "Banana", "Orange")
   
   Dim joinedString As String
   joinedString = JoinArray(myArray, "-")
   
   MsgBox joinedString ' Output: "Apple-Banana-Orange"
   End Sub

2. Использование функции соединения:

   Function JoinArray(arr As Variant, Optional delimiter As String = ",") As String
   JoinArray = Join(arr, delimiter)
   End Function

   Пример использования:

   Sub TestJoinArray()
   Dim myArray As Variant
   myArray = Array("Apple", "Banana", "Orange")
   
   Dim joinedString As String
   joinedString = JoinArray(myArray, "-")
   
   MsgBox joinedString ' Output: "Apple-Banana-Orange"
   End Sub

3. Использование метода WorksheetFunction.TextJoin (доступно в Excel 2019 и более поздних версиях):

   Function JoinArray(arr As Variant, Optional delimiter As String = ",") As String
   Dim ws As Worksheet
   Set ws = ThisWorkbook.Worksheets.Add
   
   ws.Range("A1").Resize(UBound(arr) + 1, 1).Value = Application.Transpose(arr)
   JoinArray = ws.Evaluate("=TEXTJOIN(""" & delimiter & """,TRUE,A:A)")
   
   Application.DisplayAlerts = False
   ws.Delete
   Application.DisplayAlerts = True
   End Function

   Пример использования:

   Sub TestJoinArray()
   Dim myArray As Variant
   myArray = Array("Apple", "Banana", "Orange")
   
   Dim joinedString As String
   joinedString = JoinArray(myArray, "-")
   
   MsgBox joinedString ' Output: "Apple-Banana-Orange"
   End Sub