如何在Excel中将ActiveCell地址存储到动态VBA数组中

Question

我使用单元格作为"按钮"，将它们超链接到VBA函数：

=HYPERLINK("#vba_function()", "jump_text")

当单击"按钮"时，我希望将单元格地址保存到某个数组clicked_cells中，这样就不能再单击了。伪码：

if ActiveCell.Address in clicked_cells then MsgBox("That cell already selected. Choose another.")

如何初始化这样的数组，以及如何在vba_function中更新它？

Answer 1

Public clicked_cells As Scripting.Dictionary
Function vba_function()
Dim addr
If clicked_cells Is Nothing Then
Set clicked_cells = New Scripting.Dictionary
End If
addr = Selection.Address
If clicked_cells.Exists(addr) Then
MsgBox "Already clicked that " & (Timer - clicked_cells(addr)) & " seconds ago"
Else
clicked_cells.Add addr, Timer
End If
Set vba_function = Selection
End Function