Passing Arrays to Array Merge Function and getting Syntax Error

[rkaczano]

I am getting a syntax error in my function ArrayUnion below. Clearly I am passing Vector1 and Vector2 incorrectly to the function. What am I doing wrong?

Option Explicit

Sub Convert()
Dim Vector1
Dim Vector2
Dim Vector3

Dim k As Integer

Vector1 = Create_Vector(Sheets("Sheet1").Range("A4:D8"))
Vector2 = Create_Vector(Sheets("Sheet1").Range("A10:D14"))

Vector3 = ArrayUnion(Vector1, Vector2)

End Sub


Function ArrayUnion(va1, va2 )
    Dim i As Long, Upper As Long
    If TypeName(va1) = "Empty" Then
        va1 = va2
    Else
        Upper = UBound(va1)
        If LBound(va2) = 0 Then Upper = Upper + 1
        ReDim Preserve va1(LBound(va1) To UBound(va1) + UBound(va2) – LBound(va2) + 1)
        For i = LBound(va2) To UBound(va2)
            va1(Upper + i) = va2(i)
        Next i
    End If
    ArrayUnion = va1
End Function

 

[Fluff]

What is the code for Create_Vector?
Also what is the exact error you get & what is highlighted?

 

[JLGWhiz]

I am not so sure that Excel 2013 will handle the Create_Vector function. It was a C++ function but not in the vba libraries. But I could be wrong.

 

[Norie]

Where did you get the code from?

I think there might be some 'invisible' characters in there that are causing the problem.

When I retype the problem line it no longer causes a syntax error.

Function ArrayUnion(va1, va2)
    Dim i As Long, Upper As Long
    If TypeName(va1) = "Empty" Then
        va1 = va2
    Else
        Upper = UBound(va1)
        If LBound(va2) = 0 Then Upper = Upper + 1
        ReDim Preserve va1(LBound(va1) To UBound(va1) + UBound(va2) - LBound(va2) + 1)
        For i = LBound(va2) To UBound(va2)
            va1(Upper + i) = va2(i)
        Next i
    End If
    ArrayUnion = va1
End Function

 

[JLGWhiz]

The space after the va2 would most likely cause a hiccup.

 

[rkaczano]

Sorry I did forget to paste the Create Vector sub.

Full code below. It think there we hidden characters in the ArrayUntion code. It was from a snippet in Daily Dose of Excel.

Option Explicit

Sub Convert()
Dim Vector1
Dim Vector2
Dim Vector3

Dim k As Integer

Vector1 = Create_Vector(Sheets("Sheet1").Range("A4:D8"))
Vector2 = Create_Vector(Sheets("Sheet1").Range("A10:D14"))

Vector3 = ArrayUnion(Vector1, Vector2)

For k = 1 To UBound(Vector3)
        Sheets("Sheet1").Range("B20").Offset(k, 1).Value = Vector3(k)
Next k
End Sub
Function Create_Vector(Matrix_Range As Range) As Variant
Dim No_of_Cols As Integer, No_Of_Rows As Integer
Dim i As Integer
Dim j As Integer
Dim Cell
No_of_Cols = Matrix_Range.Columns.Count
No_Of_Rows = Matrix_Range.Rows.Count
ReDim Temp_Array(No_of_Cols * No_Of_Rows)
'Eliminate NULL Conditions
If Matrix_Range Is Nothing Then Exit Function
If No_of_Cols = 0 Then Exit Function
If No_Of_Rows = 0 Then Exit Function


For j = 0 To No_Of_Rows - 1
    For i = 1 To No_of_Cols
    Temp_Array((j * No_of_Cols) + i) = Matrix_Range.Cells(j + 1, i)
    Next i
Next j

Create_Vector = Temp_Array
End Function
Function ArrayUnion(va1, va2)
    Dim i As Long, Upper As Long
    If TypeName(va1) = "Empty" Then
        va1 = va2
    Else
        Upper = UBound(va1)
        If LBound(va2) = 0 Then Upper = Upper + 1
        ReDim Preserve va1(LBound(va1) To UBound(va1) + UBound(va2) - LBound(va2) + 1)
        For i = LBound(va2) To UBound(va2)
            va1(Upper + i) = va2(i)
        Next i
    End If
    ArrayUnion = va1
End Function

Regards

RK

 

[Fluff]

Does your code now work?

 

[rkaczano]

Yes sorry it all works now!

 

[Fluff]

Glad we could help & thanks for the feedback.