Range Property Not Returning Correct Value for Relative Named Range in VBA Excel 365

Question

I've named a Range as ID and set its address as =Materiais!$B3. it is fixed for column but for the row, it will depend on the ActivateCell current position.

when I click on the G8 cell, for instance, the Name Manager identifies the correct cell value, as you can see on the image below. in this case, the letter e.

when I try to get this value via VBA code, it doesn't bring any value.

this is the VBA code that I'm using:

Sub ValorRange()
    MsgBox Range("Materiais!ID").Value
End Sub

it was supposed to bring the letter e, as Name Manager does.

I think it was not happening on Excel 2010 but I cannot do this test now. I'm using Excel 365 version.

thanks in advance.

I'd appreciate your help.

GSerg, ws.Range("ID") returns B1 cell value, as you can see below:

Answer 1

This new code should work. I added 'rngLimit' and VBA's Intersec method to check if 'rng' is contained in 'rngLimit'.

Sub ValorRange()

    Dim ws As Worksheet                     ' Worksheet object
    Dim rng As Range                        ' Range object
    Dim rngLimit As Range                   ' Range object
    Set ws = Sheets("Materiais")            ' Sets ws to worsheet
    Set rng = ws.Names("ID").RefersToRange  ' Gets range referenced by named cell
    Set rngLimit = ws.Range("B3:B100")      ' Gets limited range
    
    ' If intersection between rng and B3:B100 exists, shows rng value
    If Not Intersect(rng, rngLimit) Is Nothing Then
        MsgBox rng.Value
    Else
        ' Set default value or throws an error
        MsgBox "Error or default value"
    End If

End Sub

In a more generic version, you can use 'ws', 'rng' and 'rngLimit' as parameters of 'ValorRange' function.