Excel VBA Checking if valid Cell referenc, strange behavior if run from Chart-Sheet

[Anders W]

Hi!
I wonder if anyone can help me understand this in my opinion very strange VBA behavior (or if I miss something obvious). I have a Macro where I check if the content in an Excel cell can be interpreted as a cell reference. If so I do something if not I do something Else. A simple test example is submitted below.
As long as the active sheet when I execute the macro is a "normal" sheet it's no problem and the "If IsCellRef() Then" part of the code is executed. However if I execute the macro from a Chart-sheet the "Else" part of the code is executed even if (and this is the part I can't understand) the debugger tells the If-condition is True??

When running TestCellRef below from a Chart-Sheet and a break-point at the bold line it stops at the break-point and the debugger shows:
Watch : : IsCellRef(CellToTest) : True : Boolean : Module1.TestCellRef

Code example Excel 2013, Windows 7:

Sub TestCellRef()
Set WsList = ThisWorkbook.Sheets("Sheet2")
Dim CellToTest As String
CellToTest = WsList.Range("A1").Value

If IsCellRef(CellToTest) Then  'Cell contains CellRef
    ' Do Something. In this simple example I just write OK in the cell referenced
    WsList.Range(CellToTest).Value = "OK"
Else    ' Cell is not a valid ref
    ' In this case I write NOK.
    ' Of course writing that using the not valid reference does not work if it's really not valid.
    'WsList.Range("B1").Value = "NOK"
    WsList.Range(CellToTest).Value = "NOK"
    
    a = "Just for Breakepoint"
        
End If
End Sub

Function IsCellRef(CellRef As String) As Boolean
Dim TestRange As Range
On Error Resume Next
Set TestRange = Range(CellRef) 'Try to use the string as a referenced range, is address valid?
If Err.Number > 0 Then
    IsCellRef = False
Else
    IsCellRef = True
End If
End Function

Strange or ??

 

[RoryA]

Welcome to the forum.

The watch is causing your issue, as it's executing the function again but appears to be doing it in some other context. If you assign the result of IsCellRef(CellToTest) to a variable and put a watch on the variable, you should see that it is False when you are on a chart sheet.

 

[Anders W]

Thanks Rory!
That at least partly explains it, still strange behavior of the debugger I think. If I just execute:
CellToTest = WsList.Range("A1").Value
IsCellRefResult = IsCellRef(CellToTest)
BreakePoint

The debugger shows:
Watch : : CellToTest : "B1" : String : Module1.TestCellRef
Watch : : IsCellRef(CellToTest) : True : Boolean : Module1.TestCellRef
Watch : : IsCellRefResult : False : Variant/Boolean : Module1.TestCellRef

Which is like saying True = False

 

[RoryA]

Not really, as they are two separate evaluations. If you add a debug.print to your function, you should see that it gets called twice if you have the breakpoint - the second time is due to the watch. What is admittedly odd is that the watch clearly calculates the function in a different context from the initial evaluation.

 

[Anders W]

Thanks again Rory!
Ok, I see your point and I have got at least a little bit better understanding. I have no problem finding a workaround for the problem but what you points out as odd sure made the initial debugging harder.