Named Range RefersTo input not the same as output

user_5172630 0
Viewed 60

I have a dynamic cascading dropdown (The dropdown values change based on what is selected in the cell next to it) that i have set up on the worksheet "(T) Mechanical".

I want the same data validation list dropdown to work on other sheets, i.e "(T) Lines".

To do this, i'm opting to use VBA to swap out the sheet name in the named range when the worksheet is activated. (The dropdowns use exactly the same cells) -

The below code almost works except that the output of RefersTo is not the same as what gets input, and i cant figure out why its changing between the steps:

Private Sub Worksheet_Activate()

    Dim rng As String
    Dim xWb As Workbook
    Dim xNameString As String 'Existing Name of Named Range
    Dim xName As Name 'Name
    Set xWb = Application.ActiveWorkbook

    xNameString = "Dyn_oSystem"
    rng = "='(T) Lines'!$D2"

    Debug.Print (rng)

    Set xName = xWb.Names.Item(xNameString)

    With xName
        .RefersTo = rng
    End With

    Debug.Print (xName.RefersTo)

End Sub

Output of Debugs

='(T) Lines'!$D2
='(T) Lines'!$D3

What is causing the value shift?

vba excel
1 Answers

TL;DR: change $D2 to $D$2 in the line rng = "='(T) Lines'!$D2", if you want to create an absolute reference. If you are looking for a relative reference that starts at $D2 (implied R[1]C4 in reference to A1), your method is sound. Just make sure that your active cell is in row 1, if you want to print the reference as $D2 as well.

Unintended effects of relative referencing:

The answer as to why your reference is shifting, can be found here:

Although named ranges are absolute by default, they can also be relative. A relative named range refers to a range that is relative to the position of the active cell at the time the range is created.

The misunderstanding concerns line rng = "='(T) Lines'!$D2". The row here is relative, so that the resulting reference will be an offset to a certain other reference. Interestingly, within a macro it appears that the offset will always be relative to A1 (R1C1), thus creating a relative reference R[1]C4, regardless of the active cell. E.g. if your code is printing a resulting reference to $D3, this simply means that your active cell was in row 2, when reading the reference.

E.g. if you were to check like this:

    For i = 1 To 5:
        ActiveSheet.Cells(i, 1).Select
        Debug.Print (xName.RefersTo)
    Next i

Your prints would look like this:

='(T) Lines'!$D2
='(T) Lines'!$D3
='(T) Lines'!$D4
='(T) Lines'!$D5
='(T) Lines'!$D6

So, the row offset remains 1 throughout. Again, within a macro the position of your active cell does not influence the offset, at the moment of creation.

When you create such a reference manually (in the Name Manager), this works a little differently. In this case, Excel will take the active cell as the reference. This means that the resulting offset depends on your position in the sheet. E.g. If you are in A1, then the relative reference $D2 will be interpreted as R[1]C4 (row offset 1: row 2 - row 1), just like in the macro. However, if you are in A3, it will be interpreted as R[-1]C4 (row offset -1: row 2 - row 3!).

A common thing that happens, is that users update a relative reference, without taking into account how the update will affect the offset based on the position of the active cell. Just to clarify a bit further: compare the following situations. We want to create a reference to $D2,3,4. If our active cell is in row 1, this will give us the correct row offset (1), producing the expected result (in A1,2,3):

expected result offset

However, if we set the reference for the Named Range to $D2, while our active cell is in row 3, we end up with an unintended result:

unexpected result offset

Now, the offset will be -1. This causes the reference for row 1 to be pushed all the way to the end of column D (row 1048576).

user_184706920
Related