如何使用 VBA 为 Excel 单元格分配名称？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2280535/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to assign a name to an Excel cell using VBA?
提问by nimo
I need to assign a unique name to a cell which calls a particular user defined function.
我需要为调用特定用户定义函数的单元格分配一个唯一名称。
I tried
我试过
Dim r As Range
set r = Application.Caller
r.Name = "Unique"
回答by Alex P
The following code sets cell A1 to have the name 'MyUniqueName':
以下代码将单元格 A1 设置为具有名称“MyUniqueName”：
Private Sub NameCell()
Dim rng As Range
Set rng = Range("A1")
rng.Name = "MyUniqueName"
End Sub
Does that help?
这有帮助吗？
EDIT
编辑
I am not sure how to achieve what you need in a simple way, elegant way. I did manage this hack - see if this helps but you'd most likely want to augment my solution.
我不确定如何以简单、优雅的方式实现您的需求。我确实管理了这个 hack - 看看这是否有帮助，但你很可能想增加我的解决方案。
Suppose I have the following user defined function in VBA that I reference in a worksheet:
假设我在工作表中引用的 VBA 中有以下用户定义的函数：
Public Function MyCustomCalc(Input1 As Integer, Input2 As Integer, Input3 As Integer) As Integer
MyCustomCalc = (Input1 + Input2) - Input3
End Function
Each time I call this function I want the cell that called that function to be assigned a name. To achieve this, if you go to 'ThisWorkbook' in your VBA project and select the 'SheetChange' event then you can add the following:
每次调用此函数时，我都希望为调用该函数的单元格分配一个名称。为此，如果您转到 VBA 项目中的“ThisWorkbook”并选择“SheetChange”事件，则可以添加以下内容：
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Left$(Target.Formula, 13) = "=MyCustomCalc" Then
Target.Name = "MyUniqueName"
End If
End Sub
In short, this code checks to see if the calling range is using the user defined function and then assigns the range a name (MyUniqueName) in this instance.
简而言之，此代码检查调用范围是否正在使用用户定义的函数，然后在此实例中为该范围分配一个名称 (MyUniqueName)。
As I say, the above isn't great but it may give you a start. I couldn't find a way to embed code into the user defined function and set the range name directly e.g. using Application.Caller.Addressor Application.Caller.Cells(1,1)etc. I am certain there is a way but I'm afraid I am a shade rusty on VBA...
正如我所说，以上内容不是很好，但它可能会给你一个开始。我无法找到一个方法来嵌入代码到用户定义的功能，并设置范围名称直接使用，例如Application.Caller.Address或Application.Caller.Cells(1,1)等我敢肯定有一种方法，但我怕我在VBA阴影生锈...
回答by Tony Webster
I used this sub to work its way across the top row of a worksheet and if there is a value in the top row it sets that value as the name of that cell. It is VBA based so somewhat crude and simple, but it does the job!!
我使用这个子程序在工作表的顶行工作，如果顶行中有一个值，则将该值设置为该单元格的名称。它是基于 VBA 的，所以有点粗糙和简单，但它可以完成工作！！
Private Sub SortForContactsOutlookImport()
Dim ThisCell As Object
Dim NextCell As Object
Dim RangeName As String
Set ThisCell = ActiveCell
Set NextCell = ThisCell.Offset(0, 1)
Do
If ThisCell.Value <> "" Then
RangeName = ThisCell.Value
ActiveWorkbook.Names.Add Name:=RangeName, RefersTo:=ThisCell
Set ThisCell = NextCell
Set NextCell = ThisCell.Offset(0, 1)
End If
Loop Until ThisCell.Value = "Web Page"
End Sub
回答by Julio Galindo
I use this sub, without formal error handling:
我使用这个 sub，没有正式的错误处理：
Sub NameAdd()
Dim rng As Range
Dim nameString, rangeString, sheetString As String
On Error Resume Next
rangeString = "A5:B8"
nameString = "My_Name"
sheetString = "Sheet1"
Set rng = Worksheets(sheetString).Range(rangeString)
ThisWorkbook.Names.Add name:=nameString, RefersTo:=rng
End Sub
To Delete a Name:
删除名称：
Sub NameDelete()
Dim nm As name
For Each nm In ActiveWorkbook.Names
If nm.name = "My_Name" Then nm.Delete
Next
End Sub
回答by Omkar Salunke
You can proceed with this
Range("A1") = "Unique"
你可以继续这个
Range("A1") = "Unique"
