vba 如何通过VBA获取/设置Excel中单元格的唯一ID

How to get/set unique id for cell in Excel via VBA

提问by Chris Kimpton

I want to have/define a unique id for each data row in my Excel data sheet - such that I can use it when passing the data onwards and it stays the same when rows are added/deleted above it.

我想为我的 Excel 数据表中的每个数据行定义一个唯一的 id - 这样我就可以在传递数据时使用它，并且在它上面添加/删除行时它保持不变。

My thoughts are to use the ID attribute of Range (msdn link)

我的想法是使用 Range 的 ID 属性（msdn 链接）

So, I have a user defined function (UDF) which I place in each row that gets/sets the ID as follows:

因此，我有一个用户定义的函数 (UDF)，我将其放置在获取/设置 ID 的每一行中，如下所示：

Dim gNextUniqueId As Integer

Public Function rbGetId(ticker As String)
    On Error GoTo rbGetId_Error
    Dim currCell As Range
    'tried using Application.Caller direct, but gives same error
    Set currCell = Range(Application.Caller.Address)
    If currCell.id = "" Then
        gNextUniqueId = gNextUniqueId + 1
        'this line fails no matter what value I set it to.
        currCell.id = Str(gNextUniqueId)
    End If
    rbGetId = ticker & currCell.id
    Exit Function

    rbGetId_Error:
    rbGetId = "!ERROR:" & Err.Description
End Function

But this fails at the line mentioned with

但这在提到的行失败

"Application-defined or object-defined error"

“应用程序定义或对象定义的错误”

I thought perhaps its one of those limitations of UDFs, but I also get the same error if I try it from code triggered from a ribbon button...

我认为这可能是 UDF 的这些限制之一，但是如果我从功能区按钮触发的代码中尝试它，我也会遇到同样的错误......

Any other suggestions on how to keep consistent ids - perhaps I should populate the cells via my ribbon button, finding cells without IDs and generating/setting the cell value of those...

关于如何保持一致 id 的任何其他建议 - 也许我应该通过我的功能区按钮填充单元格，查找没有 ID 的单元格并生成/设置这些单元格的值...

EDIT: As Ant thought, I have the sheet protected, but even in an unlocked cell it still fails. Unprotecting the sheet fixes the problem.... but I have used "Protect UserInterFaceOnly:=True" which should allow me to do this. If I manually allow "Edit Objects" when I protect the sheet it also works, but I don't see a programmatic option for that - and I need to call the Protect function in AutoOpen to enable the UserInterfaceOnly feature...

编辑：正如 Ant 所想的那样，我保护了工作表，但即使在未锁定的单元格中它仍然失败。取消保护工作表解决了这个问题......但我已经使用了“Protect UserInterFaceOnly:=True”，这应该允许我这样做。如果我在保护工作表时手动允许“编辑对象”，它也可以工作，但我没有看到相应的编程选项 - 我需要在 AutoOpen 中调用 Protect 函数以启用 UserInterfaceOnly 功能...

I guess I need to turn off/on protect around my ID setting - assuming that can be done in a UDF... which it seems it cannot, as that does not work - neither ActiveSheet.unprotect nor ActiveWorkbook.unprotect :(

我想我需要在我的 ID 设置周围关闭/打开保护 - 假设可以在 UDF 中完成......这似乎不能，因为这不起作用 - ActiveSheet.unprotect 和 ActiveWorkbook.unprotect :(

Thanks in advance. Chris

提前致谢。克里斯

采纳答案by Chris Kimpton

I have found that if I protect the sheet with "Protect DrawingObjects:=False", the UDF can set the Id. Strange.

我发现如果我使用“Protect DrawingObjects:=False”保护工作表，UDF 可以设置 Id。奇怪的。

Thanks for all the help with this.

感谢您对此提供的所有帮助。

回答by Joel Goodwin

Okay...

好的...

It does appear that if the sheet is locked, macros do not have write access to low-level information such as ID.

看起来，如果工作表被锁定，宏就没有对 ID 等低级信息的写访问权限。

However, I do not think it is possible to unprotect the sheet within a UDF. By design, UDFs are heavily restricted; I think having a cell formula control the sheet protection would break the formula paradigm that a cell formula affects a cell only. See this pageon the Microsoft website for more details.

但是，我认为在 UDF 中取消保护工作表是不可能的。按照设计，UDF 受到严格限制；我认为让单元格公式控制工作表保护会打破单元格公式仅影响单元格的公式范式。有关更多详细信息，请参阅Microsoft 网站上的此页面。

I think this limits your options. You must either:

我认为这限制了您的选择。您必须：

• give up sheet protection
• give up the UDF, use a Worksheet_Change event to capture cell changes and write to ID there
• use a UDF that writes the ID into the cell value, rather than save to ID

• 放弃床单保护
• 放弃 UDF，使用 Worksheet_Change 事件来捕获单元格更改并写入那里的 ID
• 使用将 ID 写入单元格值的 UDF，而不是保存到 ID

The UDF approach is fraught with problems as you are trying to use something designed for calculation of a cell to make a permanent mark on the sheet.

UDF 方法充满了问题，因为您试图使用设计用于计算单元格的东西在工作表上制作永久标记。

Nonetheless, here's an example of a UDF you can use to stamp a "permanent" value onto a cell, which works on unlocked cells of a protected sheet. This one only works for single cells (although it could be adapted for an array formula).

尽管如此，这里有一个 UDF 示例，您可以使用它在单元格上标记“永久”值，该值适用于受保护工作表的未锁定单元格。这仅适用于单个单元格（尽管它可以适用于数组公式）。

Public Function CellMark()

    Dim currCell As Range
    Set currCell = Range(Application.Caller.Address)

    Dim myId As String
    ' must be text; using .value will cause the formula to be called again
    ' and create a circular reference
    myId = currCell.Text

    If (Trim(myId) = "" Or Trim(myId) = "0") Then
       myId = "ID-" & Format(CStr(gNextUniqueId), "00000")
       gNextUniqueId = gNextUniqueId + 1
    End If

    CellMark = myId

End Function

This is quite flawed though. Using copy or the fillbox will, however, retain the previous copied value. Only by explicitly setting cells to be a new formula will it work. But if you enter in the formula into the cell again (just click it, hit ENTER) a new value is calculated - which is standard cell behaviour.

不过，这是相当有缺陷的。但是，使用复制或填充框将保留先前复制的值。只有明确地将单元格设置为新公式才能工作。但是，如果您再次在单元格中输入公式（只需单击它，按 ENTER），就会计算出一个新值 - 这是标准的单元格行为。

I think the Worksheet_Change event is the way to go, which has much more latitude. Here's a simple example that updates the ID of any cell changes. It could be tailored to your particular scenario. This function would need to be added to every Worksheet the ID setting behaviour is required on.

我认为 Worksheet_Change 事件是要走的路，它有更多的自由度。这是一个更新任何单元格更改的 ID 的简单示例。它可以根据您的特定场景量身定制。需要将此功能添加到需要 ID 设置行为的每个工作表中。

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim currCell As Range
    Set currCell = Target.Cells(1, 1)

    Dim currId As String
    currId = currCell.ID

    If Trim(currCell.ID) = "" Then
        Target.Parent.Unprotect
        currCell.ID = CStr(gNextUniqueId)
        Target.Parent.Protect
        gNextUniqueId = gNextUniqueId + 1
    End If

End Sub

Last note; in all cases, your ID counter will be reset if you re-open the worksheet (at least under the limited details presented in your example).

最后一点；在所有情况下，如果您重新打开工作表（至少在您的示例中提供的有限详细信息下），您的 ID 计数器将被重置。

Hope this helps.

希望这可以帮助。

回答by mandroid

The problem is with Application.Caller.

问题出在 Application.Caller 上。

Since you are calling it from a user defined function it is going to pass you an error description. Here is the remark in the Help file.

由于您是从用户定义的函数中调用它，因此它将向您传递错误描述。这是帮助文件中的注释。

Remarks

评论

This property returns information about how Visual Basic was called, as shown in the following table.

此属性返回有关如何调用 Visual Basic 的信息，如下表所示。

Caller - Return value

调用者 - 返回值

• A custom function entered in a single cell - A Range object specifying that cell
• A custom function that is part of an array formula in a range of cells - A Range object specifying that range of cells
• An Auto_Open, Auto_Close, Auto_Activate, or Auto_Deactivate macro - The name of the document as text
• A macro set by either the OnDoubleClick or OnEntry property - The name of the chart object identifier or cell reference (if applicable) to which the macro applies
• The Macro dialog box (Tools menu), or any caller not described above- The #REF! error value

• 在单个单元格中输入的自定义函数 - 指定该单元格的 Range 对象
• 作为单元格范围内数组公式一部分的自定义函数 - 指定该单元格范围的 Range 对象
• Auto_Open、Auto_Close、Auto_Activate 或 Auto_Deactivate 宏 - 文本形式的文档名称
• 由 OnDoubleClick 或 OnEntry 属性设置的宏 - 宏应用到的图表对象标识符或单元格引用（如果适用）的名称
• “宏”对话框（“工具”菜单）或上述未描述的任何调用方- #REF！误差值

Since you are calling it from a user defined function, what is happening is Application.Caller is returning a String of an error code to your range variable curCell. It is NOT causing an error which your error handler would pick up. What happens after that is you reference curCell, it's not actually a range anymore. On my machine it tries setting curCell = Range("Error 2023"). Whatever that object is, it might not have an ID attribute anymore and when you try to set it, it's throwing you that object error.

由于您是从用户定义的函数调用它，因此发生的事情是 Application.Caller 将错误代码的字符串返回到您的范围变量 curCell。它不会导致您的错误处理程序会接收到的错误。之后会发生什么是你引用了 curCell，它实际上不再是一个范围了。在我的机器上，它尝试设置 curCell = Range("Error 2023")。不管那个对象是什么，它可能不再有 ID 属性，当您尝试设置它时，它会向您抛出该对象错误。

Here's what I would try...

这就是我要尝试的...

1. Try removing your error handler and see if VBA throws up any exceptions on Range(Application.Caller.Address). This won't fix it, but it could point you in the right direction.

2. Either through logic or Application.ActiveCell or however you want to do it, reference the cell directly. For example Range("A1") or Cells(1,1). Application.Caller.Address just doesn't seem like a good option to use.

3. Try using Option Explicit. This might make the line where you set curCell throw up an error since Range(Application.Caller.Address) doesn't look like it's passing a range back, which is curCell's datatype.

1. 尝试删除您的错误处理程序，看看 VBA 是否在 Range(Application.Caller.Address) 上抛出任何异常。这不会解决它，但它可以为您指明正确的方向。

2. 通过逻辑或 Application.ActiveCell 或您想要的方式，直接引用单元格。例如 Range("A1") 或 Cells(1,1)。Application.Caller.Address 似乎不是一个好的选择。

3. 尝试使用 Option Explicit。这可能会使设置 curCell 的行抛出错误，因为 Range(Application.Caller.Address) 看起来不像是传回一个范围，这是 curCell 的数据类型。

回答by Joel Goodwin

Concur with Ant - your code works fine here on Excel 2003 SP3.

同意 Ant - 您的代码在 Excel 2003 SP3 上运行良好。

I've also been able to use:

我还可以使用：

Set currCell = Application.Caller
If Application.Caller.ID = "" Then
    gNextUniqueId = gNextUniqueId + 1
    'this line fails no matter what value I set it to.
    currCell.ID = Str(gNextUniqueId)
End If

Aha! I think I have it.

啊哈！我想我有。

I think you're calling this from an array formula, and it only gets called ONCE with the full range. You can't obtain an ID for a range - only a single cell. This explains why Application.Caller.ID fails for you, because Range("A1:B9").ID generates an Application-defined or object-defined error.

我认为您是从数组公式中调用它的，并且它只会在整个范围内被调用一次。您无法获得某个范围的 ID - 只能获得一个单元格。这解释了为什么 Application.Caller.ID 对您失败，因为 Range("A1:B9").ID 生成Application-defined or object-defined error.

When you use Range(Application.Caller.Address)to get the "cell" you just defer this error down to the currCell.IDline.

当您Range(Application.Caller.Address)用来获取“单元格”时，您只需将此错误推迟到该currCell.ID行即可。

回答by Oorang

I think we may have a few issues going on here, but I think they are testing issues, not problems with the code itself. First, if you call the function from anything other than a Cell, like the immediate window, other code, etc. Application.Caller will not be set. This is what is generating your object not found errors. Second, if you copy/paste the cell that has the function, they you will by copy/pasting the ID too. So wherever you paste it to, the output will stay the same. But if you just copy the text (instead of the cell), and then paste then this will work fine. (Including your original use of Application.Caller.)

我认为我们在这里可能会遇到一些问题，但我认为它们是测试问题，而不是代码本身的问题。首先，如果您从 Cell 以外的任何地方调用该函数，例如直接窗口、其他代码等，则不会设置 Application.Caller。这就是生成您的对象未找到错误的原因。其次，如果您复制/粘贴具有该功能的单元格，他们也会复制/粘贴 ID。因此，无论您将其粘贴到何处，输出都将保持不变。但是如果你只是复制文本（而不是单元格），然后粘贴，那么这将工作正常。（包括您最初使用 Application.Caller。）