Passing Arrays to Array Merge Function and getting Syntax Error

rkaczano

Board Regular
Joined
Jul 24, 2013
Messages
84
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?

VBA Code:
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
 
Last edited by a moderator:

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,383
Office Version
  1. 365
Platform
  1. Windows
What is the code for Create_Vector?
Also what is the exact error you get & what is highlighted?
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,303
Office Version
  1. 365
Platform
  1. Windows
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.
VBA Code:
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

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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

rkaczano

Board Regular
Joined
Jul 24, 2013
Messages
84
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.


VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,383
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Does your code now work?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,383
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Forum statistics

Threads
1,141,302
Messages
5,705,587
Members
421,400
Latest member
chakam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top