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

narendra

Board Regular
Joined
Apr 15, 2008
Messages
87
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?

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

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

ZVI

MrExcel MVP
Joined
Apr 9, 2008
Messages
3,817
Office Version
  1. 2016
  2. 2010
  3. 2007
Platform
  1. Windows
Hi,
This replacement of ProperUnion() function works in Excel formulas as well as in VBA
VBA Code:
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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,129,478
Messages
5,636,566
Members
416,924
Latest member
cmlacerna

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