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:
3
I try to convince myself that I know how ranges work in Excel-VBA, but this is actually something I had not encountered before. What you have here is indeed a relative named range which will update based on the active cell when you use it inside an Excel formula.
For the reason that they change based on the active cell, you should probably avoid using them inside VBA code since it could introduce unpredictability in the behavior of your code or force you to change the selected cell multiple times to make your calculations (obligatory mention of How to avoid using Select in Excel VBA).
Your named range is defined as RC2 in R1C1 notation and $B1 in A1 notation (with respect to cell A1). To evaluate this value, Excel needs a starting cell to convert the relative position into an absolute position. However, the VBA Range method doesn’t use the active cell, it always uses A1 which is why you always get the value in B1 when using the Range method.
Nelson’s answer works because it makes use of RefersToRange instead which does pass the active cell along to calulate the position on the sheet. This is why it works the way you are used to when using the named range inside a cell.
4
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.
8