Error 424 when passing a VBA array to a function as a ParamArray input

[narendra]

I am using below UDF from Chip Pearson which accepts an input array as arguments (here ranges are entered as argument).
Before proceeding with the main code, this function checks if there are any arguments by using "Is Nothing".

As long as i use this udf directly on a worksheet, it works fine. For example: =COUNTA(ProperUnion(A1:A10)) works fine.
But, when i call this function from another vba code and pass my variant array as argument to this function, the "Is Nothing" check fails with Error 424: Object Required.
Note: the variant array i am passing to this function contains "Ranges" as elements.

(when I stepped into the code during debug, it stopped execution after testing with "Is Nothing". This line gave the error 424 when tested in the immediate window.)

Is this because the arguments are passed from one array v/s them being directly entered via. formula?
Can someone tell me what is the problem here?

Function ProperUnion(ParamArray MyRanges() As Variant) As Range
' Credits: Chip Pearson (cpearson.com)
If MyRanges(LBound(MyRanges)) Is Nothing Then '<-- Error 424 when calling this function from below Function
    ProperUnion = Nothing
    Exit Function
End If
'.
'Rest of the code
'.
End Function


'My Function calling the above code
Function RngToCellAddress(ParamArray MyRng() As Variant) As String
Dim Temp_MyRng() As Variant 'Used to Redim a ParamArray, which is Not directly allowed
'.
'. My Code
'.
Temp_MyRng = MyRng    '<-- checked (array was copied correctly)
For Each mycell In ProperUnion(Temp_MyRng).Cells '<-- Calling above function
                                                 '    and passing this variant
                                                 '    array as input.
' some code to loop thru each cell
Next mycell
'.
'.

 

[ZVI]

Hi,
This replacement of ProperUnion() function works in Excel formulas as well as in VBA

Function ProperUnion(ParamArray Ranges() As Variant) As Range
' ZVI:2020-08-11 https://www.mrexcel.com/board/threads/.1142559/post-5536689
' Replacement of the ProperUnion() function from http://www.cpearson.com/excel/BetterUnion.aspx for Excel/Vba
  Dim aRanges(), a
  Dim rRes As Range, rArea As Range, rCell As Range
  aRanges() = Ranges()
  For Each a In aRanges()
    If TypeOf a Is Excel.Range Then
      If Not a Is Nothing Then
        For Each rArea In a.Areas
          If Not rArea Is Nothing Then
            If rRes Is Nothing Then
              Set rRes = rArea
            ElseIf Intersect(rRes, rArea) Is Nothing Then
              Set rRes = Union(rRes, rArea)
            Else
              For Each rCell In rArea.Cells
                If Intersect(rRes, rCell) Is Nothing Then
                  Set rRes = Union(rRes, rCell)
                End If
              Next
            End If
          End If
        Next
      End If
    End If
  Next
  Set ProperUnion = rRes
End Function

Sub Test_ProperUnion()

  Dim rRes As Range

  ' Test 1 Union range
  Set rRes = ProperUnion(Range("A1:C3,B3:D5"))
  Debug.Print "Test1", rRes.Address(0, 0), rRes.Cells.Count

  ' Test 2 Partially intersected ranges
  Set rRes = Nothing
  Set rRes = ProperUnion(Range("A1:C3"), Range("B3:D5"))
  Debug.Print "Test2", rRes.Address(0, 0), rRes.Cells.Count

  ' Test 3 Not intersected ranges
  Set rRes = Nothing
  Set rRes = ProperUnion(Range("A1:C3"), Range("E3:G5"))
  Debug.Print "Test3", rRes.Address(0, 0), , rRes.Cells.Count

  ' Test 4 Adjacent ranges
  Set rRes = Nothing
  Set rRes = ProperUnion(Range("A1:C3"), Range("D1:F3"))
  Debug.Print "Test4", rRes.Address(0, 0), , rRes.Cells.Count

End Sub