Convert from ParamArray to cells/range-CodePudding

I'm working on the macro below, that will allow the user to feed it multiple ranges, and it will restore default for each cell in each range. The problem comes when I try to loop through each of the ranges, I need to use a variant "vI as variant", that I need to convert into a range to be used in the next function. How can I convert that vI variant to a range? (please keep in mind that ranges can be on different sheet)

Sub restoreDefaults_cellByCell(ParamArray targetRanges())
    
    Dim rI As Range, cellI As Range, vI As Variant
    Dim sName As String
    
    For Each vI In targetRanges
        'Set rI = vI.Range 'This returns an error
        For Each cellI In rI.cells
            sName = NamedRange_getCellNamedRange(cellI, False)
            If Not dNamesFromSelection.Exists(sName) Then
                dNamesFromSelection.add sName, ""
            End If
        Next
    Next
End sub

CodePudding user response：

Perhaps using a For...Next loop with LBound/Ubound to iterate:

Sub restoreDefaults_cellByCell(ParamArray targetRanges())
    
    Dim rI As Range, cellI As Range, i As Long
    Dim sName As String
    
    For i = LBound(targetRanges) to Ubound(targetRanges)
        If TypeOf targetRanges(i) Is Range Then
            Set rI = targetRanges(i)
            For Each cellI In rI.cells
                sName = NamedRange_getCellNamedRange(cellI, False)
                If Not dNamesFromSelection.Exists(sName) Then
                    dNamesFromSelection.add sName, ""
                End If
            Next
        End If
    Next
End Sub

Or just:

If TypeOf vI is Range Then
   Set rI = vI
End If

though I would suggest using a For...Next loop to iterate over arrays instead of a For Each loop.

CodePudding user response：

It seems I needed to loop 2 times, with v1 and v2, even though the tested named range was a named range containing 5 separate cells. I gues loop once for range, look twice for area, and only them I can loop through cells. Conversion is succesfull this way.

Sub restoreDefaults_cellByCell_MAIN_TEST()
    restoreDefaults_cellByCell_MAIN Range("'Global Inputs'!SID_lead_required")
End Sub    

Sub restoreDefaults_cellByCell_MAIN(ParamArray targetRanges())    
    setProgramAlertsOff
    restoreDefaults_cellByCell targetRanges
    setProgramAlertsOn
End Sub

Sub restoreDefaults_cellByCell(ParamArray targetRanges())
    
    Dim rI As Range, cellI As Range, v1, v2
    Dim sName As String, dName As Variant, sFormula As String
    Dim dNamesFromSelection As New Scripting.Dictionary
    Dim arrDefaults(), dHeaders As New Scripting.Dictionary, dDefaults As New Scripting.Dictionary
    Dim rowI As Variant
    Dim LO As ListObject
    
    For Each v1 In targetRanges
        For Each v2 In v1
            Set rI = v2
            For Each cellI In rI.cells
                sName = NamedRange_getCellNamedRange(cellI, False)
                If Not dNamesFromSelection.Exists(sName) Then
                    dNamesFromSelection.add sName, ""
                End If
            Next
        Next
    Next
End Sub