Names RefersToRange property - VBA RT error

[Jon von der Heyden]

I'm baffled...

Both customer and I are on Excel 32bit. I am on 64bit Win7, customer is on 32bit.

Taking an excerpt of the code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngTarget As Excel.Range
    Dim rngCell As Excel.Range


    Set rngTarget = Target.Resize(1, 1)


    With Application
        .ScreenUpdating = False
        .Calculation = xlManual
        .EnableEvents = False
    End With


    With Me
        '-- View name change; populate the view description if the view already exists
        If rngTarget.Address = .Range(mstrREF_NAME).Address Then
            .Range(mstrREF_DESCRIPTION).Value = Replace$(CStr(Application.VLookup(.ViewName, ThisWorkbook.names(mstrREF_VIEWS).RefersToRange, 2, False)), _
                                                         "Error 2042", _
                                                         "")
        End If

Customer gets "Application or object defined error". No problems on my side.

mstrREF_NAME is a string that refers to worksheet scoped name. The name refers to a single cell.
mstrREF_DESCRIPTION is a string that refers to worksheet scoped name. The name refers to a single cell.
mstrREF_VIEWS is a string that refers to a workbook scoped name. This is a dynamic named range that refers to a 10R x 4C grid in a different worksheet

Any ideas people?

 

[Jon von der Heyden]

Last update!

PROBLEM SOLVED!

RefersToRange is definitely the culprit here! It appears that the work around is to use the Evaluate Method.

Old:

ThisWorkbook.names(mstrREF_VIEWS).RefersToRange

New

Evaluate(ThisWorkbook.names(mstrREF_VIEWS).RefersTo)

So now I wonder; what if I was programming an addin? What alternative would I be able to use then? Evaluate used in this context in an addin will also produce a RT error.

 

[ZVI]

New

Evaluate(ThisWorkbook.names(mstrREF_VIEWS).RefersTo)

So now I wonder; what if I was programming an addin? What alternative would I be able to use then? Evaluate used in this context in an addin will also produce a RT error.

Hi Jon,
I'm using this way of referencing to the range of Add-In:

  ' Code in Add-In
  Dim Rng As Range
  ' In the line below sheet is required in the reference!
  Set Rng = ThisWorkbook.Sheets(1).Range("MyRangeInAddIn")

Range MyRangeInAddIn can be dynamic as well.

Vlad

 

[Jon von der Heyden]

I can't believe I overlooked that Vlad. I tend to use names for a lot more than just ranges which is why I believe my auto-pilot mode is to either evaluate or use RefersToRange. One thing is certain; I'm going to steer well clear of RefersToRange in future!

 

[RoryA]

Idle curiosity - why can't you use Evaluate in the context of an add-in?

 

[ZVI]

Idle curiosity - why can't you use Evaluate in the context of an add-in?

Hi Rory,

Do you mean Evaluate(ThisWorkbook.Names("MyName").RefersTo) where MyRange is in AddIn?
It refers to the range of the sheet of active workbook, if sheet with the same name as of MyName's parent is present in active workbook.

Testing code:

' Code in AddIn
Sub Test()
  Debug.Print Evaluate(ThisWorkbook.Names("MyRange").RefersTo).Address(External:=True)
End Sub

BTW, the same happens with MyRange & code in one workbook (not AddIn) if another workbook is active.

Vlad

 

[RoryA]

But if you use worksheet.evaluate rather than application.evaluate you can specify the context, or does that not work from an addin for some reason?

 

[ZVI]

But if you use worksheet.evaluate rather than application.evaluate you can specify the context, or does that not work from an addin for some reason?

Yea, this works: Set Rng = ThisWorkbook.Sheets(1).Evaluate(ThisWorkbook.Names("MyRangeInAddIn").RefersTo)
but the direct Set Rng = ThisWorkbook.Sheets(1).Range("MyRangeInAddIn") is easier & clearer, for me at least

 

[Jon von der Heyden]

But if you use worksheet.evaluate rather than application.evaluate you can specify the context, or does that not work from an addin for some reason?

I just didn't think to use that!

 

[Jon von der Heyden]

but the direct Set Rng = ThisWorkbook.Sheets(1).Range("MyRangeInAddIn") is easier & clearer, for me at least

Agreed! And in the context of this discussion I agree it's the way forward. The Evaluate method just goes to show how I can become too familar with one method and completely fail to recognise/consider alternatives.

But sometimes the evaluate becomes necessary if the name doesn't return a range.

 

[justinrylie]

Hi everyone. It's still happening in excel 365--this error. We're both in 32 bit.

Unsure if geography has anything to do with it but I'm in NZ and he's in Germany.

I have this code:

dim nm,submitrange as string

For Each nm In ActiveWorkbook.Names
If nm.RefersToRange.Parent.Name = ActiveSheet.Name Then
msgbox("hurray")
end
next nm

And he gets "Application or object defined error" around "If nm.refersToRange.Parent.Name" line, particularly with RefersToRange. It just doesn't work with him but works perfectly in mine.

Any other findings on this? Did Evaluate() work?