Search code examples
excelvbaexcel-365

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

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.

enter image description here

I'd appreciate your help.

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

enter image description here

Solution

  • 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.