Named Manager formula not recognised in VBA

[OGRZ]

Hi all,

I have been re-using some macros for a while but I know very little about them. I recently changed laptops and now I am using Excel 2016 (I was using 2019 before) and macros are returning a Run time error 1004 for application defined or object defined error.
The error happens in this extract:

Set rngNames = ThisWorkbook.Names("listNamedRanges").RefersToRange

It seems like the listNamedRanges formula, which is defined in the Name Manager as

=OFFSET(DevSettings!$G$101;1;0;DevSettings!$E$184;1)

is not recognised as a range in VBA but as an Integer; see the Watch window schreenshot below:

I already tried to modify the offset, currently used to extend the range when list is extended, so that it takes the range instead of using OFFSET but code is pretty heavy and it results in other errors.
I wonder if there is a way to tell the macro these formulas defined in the Name Manager are Ranges and not Integers. I will otherwise try in a different laptop with later versions of Excel.

Thanks!

 

[Jon Peltier]

I recreated this in a worksheet. In the Immediate Window I checked:

? TypeName(ThisWorkbook.Names("listNamedRanges").RefersToRange)
Range
? ThisWorkbook.Names("listNamedRanges").RefersToRange.Address
$G$102:$G$111

How have you declared rngNames? This snippet has no errors and selects the correct range:

  Dim rngNames As Range
  Set rngNames = ThisWorkbook.Names("listNamedRanges").RefersToRange
  rngNames.Select

 

[OGRZ]

I recreated this in a worksheet. In the Immediate Window I checked:

? TypeName(ThisWorkbook.Names("listNamedRanges").RefersToRange)
Range
? ThisWorkbook.Names("listNamedRanges").RefersToRange.Address
$G$102:$G$111

How have you declared rngNames? This snippet has no errors and selects the correct range:

  Dim rngNames As Range
  Set rngNames = ThisWorkbook.Names("listNamedRanges").RefersToRange
  rngNames.Select

Hi Jon,

My Sub is as it follows, with the error in red. You can see there how rngNames has been defined:

Sub ListNamedRanges()

Dim countNames As Long
Dim countStore As Long
Dim rngNames As Range
Dim strNameLoc As String
Dim strNameLocTrim As String
Dim strNameFormulaRows As String
Dim strNameFormulaCols As String
Dim calcManual As Boolean
Dim countErr As Long

Dim i As Long
Dim strName As Name

If Application.Calculation = xlCalculationManual Then
    calcManual = True
Else
    calcManual = False
    Application.Calculation = xlCalculationManual
End If

Application.Calculate

[COLOR=rgb(226, 80, 65)][B]Set rngNames = ThisWorkbook.Names("listNamedRanges").RefersToRange[/B][/COLOR]

countStore = rngNames.Count

If countStore > 1 Then
    Range(rngNames.Item(2, 1), rngNames(countStore, 1)).EntireRow.Delete
End If

countNames = ThisWorkbook.Names.Count
rngNames.Item(1, 1).EntireRow.Copy
Range(rngNames, rngNames.Offset(countNames - 2, 0)).EntireRow.Insert

'Application.Calculation = xlCalculationAutomatic

Set rngNames = ThisWorkbook.Names("listNamedRanges").RefersToRange

countErr = 0
i = 1

For Each strName In ThisWorkbook.Names

If (strName.Name Like "*_xlfn*") Or (InStr(strName.Name, "!") > 0) Then
    countErr = countErr + 1
    GoTo Skip:
End If

strNameLoc = strName.RefersToLocal
strNameLocTrim = VBA.Right(strName.RefersToLocal, VBA.Len(strName.RefersToLocal) - 1)
strNameFormulaRows = "=ROWS(" & strNameLocTrim & ")"
strNameFormulaCols = "=COLUMNS(" & strNameLocTrim & ")"

    With rngNames.Item(i, 1)
        .ClearContents
        .Value = strName.Name
        .Offset(0, 1).ClearContents
        .Offset(0, 1).Value = strNameLoc
        .Offset(0, 3).Value = strNameFormulaRows
        .Offset(0, 4).Value = strNameFormulaCols
    End With

i = i + 1

Skip:

Next

If countErr <> 0 Then
    countErr = countErr - 1
    Range(rngNames.Item(countNames, 1), rngNames.Item(countNames - countErr, 1)).EntireRow.Delete
End If

If calcManual = True Then
    Application.Calculation = xlCalculationManual
End If

End Sub

When I change the OFFSET formula in the Name Manager and instead select the range, the error is removed but another one appear in the section referring to .Offset(0, 1).Value = strNameLoc

 

[Jon Peltier]

When you type listNamedRanges in the Name box, is the correct range selected?

What are the actual errors you are seeing? Descriptions, not just numbers.

FWIW, if I put zero into the cell that tells the OFFSET name definition how many rows high the Name is, I get a run-time error 1004, "Application-defined or object-defined error" when I try to use MyName.RefersToRange.