如何在Excel中自动创建命名引用？我想要每行的第一个字段作为我的参考

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.

我的电子表格的第一列是一个唯一的密钥(数据明智,与excel无关)。我的另一列包含指向其他行的链接(指向该行的唯一键)。当我插入这些超链接时,我需要指向“已定义的名称”,因为行顺序会随着时间的推移而变化。

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.

2 个解决方案

#1

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

您应该查看Workbook.Names或Worksheet.Names(如果您希望将定义的名称限制为工作表)。

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.

上面链接中显示的示例非常好。在您的情况下,您可能希望使用单元格中的Range.Value或Range.Text作为Names.Add()方法的'Name'参数传入的字符串。

Using VBA it might look something like this:

使用VBA可能看起来像这样:

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.

以上将列A的定义名称设置为在标题单元格A1中找到的值(文本)。

Hope this helps!

希望这可以帮助!

Mike

#2

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

得到它了。 Mike R.让我走上正轨。谢谢。

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

#1