How can I automate the creation of named references in Excel? I want the first field of every row to be my reference.

Question

The first column of my spreadsheet is a unique key (data wise, nothing to do with excel). Another of my columns contains links to other rows (to that row's unique key). When I insert these hyperlinks, I need to point to "defined names" since the row order will change over time.

I don't want to have to create the defined name every time I insert a new row. Is there a way to automatically define a column's text as the "defined name"?

I hope this is clear.

Many thanks.

Answer 1

You should look into the Workbook.Names or Worksheet.Names (if you wish to restrict the defined name to the worksheet).

The examples shown in the links above are pretty good. In your case, you would want to use the Range.Value or the Range.Text found in the cell to be used as the string passed in as the 'Name' argument for the Names.Add() method.

Using VBA it might look something like this:

ThisWorkbook.Names.Add _
    Name:=Sheet1.Range("A1").Text, _
    RefersTo:=Sheet1.Range("A:A"), _
    Visible:=True

The above sets the defined name for the column A to be the value (text) found in the header cell A1.

Hope this helps!

Mike

Answer 2

Got it. Mike R. put me on the right track. Thanks.

Note the need to remove non alphanumeric characters for names. Also, duplicating a name just overwrites the old reference.

Private Sub Worksheet_Change(ByVal Target As Range)
    If (Target.Column = 1 And Target.Text <> "" And Target.Cells.Count = 1) Then
        ThisWorkbook.Names.Add _
            Name:=StripChar(Target.Text), _
            RefersTo:=Target, _
            Visible:=True
    End If
End Sub

Function StripChar(s As String) As String
    With CreateObject("vbscript.regexp")
        .Global = True
        .ignorecase = True
        .Pattern = "[^\dA-Z]"
        StripChar = .Replace(s, "")
    End With
End Function