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?

 

[shg]

What is the ViewName property, Jon? I can't find it in Help or the Object Browser.

 

[Jon von der Heyden]

Sorry I should have mentioned.

Public Property Get ViewName() As String
        ViewName = Me.Range(mstrREF_NAME).Value
End Property

 

[shg]

I would break the offending line into several lines and see which falls over on his system.

 

[Jon von der Heyden]

I think you're right Steve. I have a call scheduled with him tomorrow. Was just wondering (hoping) that it was something obvious I had overlooked...

Thanks for looking. Sorry to baffle with that property!

 

[shg]

You're welcome, good luck.

 

[Jon von der Heyden]

Turns out that on my customers machine it outright refuses to resolve RefersToRange for a dynamic range:

ThisWorkbook.names(mstrREF_VIEWS).RefersToRange

Application-defined or object-defined error. Strange because it is absolutely fine on my machine!

 

[shg]

How about using referstorange.address(external:=true)?

 

[Jon von der Heyden]

That's exactly what I tried When he wakes up I'm going to ask him to test:

Evaluate(ThisWorkbook.names(mstrREF_VIEWS).RefersTo)

I tend to avoid this method generally because it fails in an addin; but since this isn't an addin I'm hoping it will work.

 

[Jon von der Heyden]

Still plodding on with this one.

The workbook is for users in the Nordic countries. I have used a dynamic range name in the workbook:

=Views!$A$3:INDEX(Views!$D:$D,MATCH(BigText,Views!$A:$A,1),1)

What the user sees is:

=Views!$A$3:INDEX(Views!$D:$D;MATCH(BigText;Views!$A:$A;1);1)

Ok so that bit is fine; the name returns a valid range.

It seems that in VBA RefersToRange will not resolve it. Is anyone aware of this? I know we have local version of RefersTo and RefersToR1C1 (although I've never fully understood it).

MSDN Developers Reference doesn't make any suggestion of international issues with RefersToRange (as far as I can see)...