代码之家 › 专栏 › 技术社区 › Chris Kimpton

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

excel-vba vba excel

Chris Kimpton · 技术社区 · 15 年前

我希望为Excel数据表中的每个数据行都有/定义一个唯一的id,这样我可以在向前传递数据时使用它,并且在上面添加/删除行时它保持不变。

我的想法是使用Range的ID属性( msdn link )

因此,我有一个用户定义函数(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

但这在前面提到的行中失败了

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

正如Ant所想,我保护了床单,但即使在一个未锁定的单元格中,它仍然失败。取消对工作表的保护可修复此问题。。。。但是我使用了“Protect UserInterFaceOnly:=True”,这应该允许我这样做。如果我在保护工作表时手动允许“编辑对象”,它也可以工作,但我看不到用于此的编程选项-我需要在AutoOpen中调用protect函数以启用UserInterfaceOnly功能。。。

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

提前谢谢。克里斯

5 回复 | 直到 15 年前

Joel Goodwin 15 年前

如果工作表被锁定,宏似乎没有对低级信息(如ID)的写入权限。

但是,我认为不可能在UDF中取消对工作表的保护。根据设计,自定义项受到严格限制;我认为让一个细胞公式控制床单保护将打破一个细胞公式只影响一个细胞的公式范式。看见 this page

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

放弃床单保护
放弃UDF,使用工作表_Change事件捕获单元格更改并写入其中的ID
使用将ID写入单元格值的自定义项,而不是保存到ID

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

尽管如此,这里有一个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

但这是有缺陷的。但是,使用“复制”或“填充框”将保留先前复制的值。只有将单元格显式设置为新公式,它才能工作。但是,如果您再次在单元格中输入公式(只需单击它,然后按enter键),将计算一个新值-这是标准单元格行为。

我认为工作表变更事件是一个方向,它有更多的自由度。下面是一个简单的示例,用于更新任何单元格更改的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

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

希望这有帮助。

Joel Goodwin 15 年前

同意Ant-您的代码在Excel2003SP3上运行良好。

我还能够使用:

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

我想你是从一个数组公式中调用它的,它在整个范围内只被调用一次。无法获取某个区域的ID-只能获取单个单元格。这就解释了为什么Application.Caller.ID会失败,因为Range(“A1:B9”).ID会生成 Application-defined or object-defined error .

当你使用 Range(Application.Caller.Address) 要获得“单元格”,只需将此错误推迟到 currCell.ID 线

Oorang 15 年前

mandroid 15 年前

问题在于Application.Caller。

因为您是从用户定义的函数调用它,所以它将向您传递一个错误描述。这是帮助文件中的备注。

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

在单个单元格中输入的自定义函数-指定该单元格的范围对象
作为单元格区域中数组公式一部分的自定义函数-指定该单元格区域的范围对象
自动打开、自动关闭、自动激活或自动停用宏—文档的文本名称
“宏”对话框(工具菜单)或上面未描述的任何调用程序 -裁判!误差值

因为您是从用户定义的函数调用它,所以发生的是Application.Caller向您的范围变量curCell返回一个错误代码字符串。它不会导致错误处理程序将拾取的错误。之后发生的是引用curCell,它实际上不再是一个范围。在我的机器上,它尝试设置curCell=Range(“错误2023”)。无论该对象是什么,它可能不再具有ID属性,当您尝试设置它时,它会向您抛出该对象错误。

这是我要尝试的。。。

尝试删除错误处理程序,并查看VBA是否在范围(Application.Caller.Address)上引发任何异常。这不会解决问题,但它可以为您指明正确的方向。
可以通过logic或Application.ActiveCell,也可以直接引用单元格。例如,范围(“A1”)或单元格(1,1)。Application.Caller.Address似乎不是一个好的选择。
尝试使用optionexplicit。这可能会使设置curCell的行抛出错误,因为Range(Application.Caller.Address)看起来不像是在传回一个Range,即curCell的数据类型。

Chris Kimpton 15 年前

我发现,如果我使用“protect DrawingObjects:=False”保护该表,UDF可以设置Id.Strange。

谢谢你在这方面的帮助。