vba 如何在excel中使用单元格存储额外的（隐藏）信息

Question

提问by Colophi

I'm writing an application for work which compiles a whole mess of different spreadsheets into reports for a specific work site. I have quite a few worksheets with a whole bunch of metrics on each. Each metric on each sheet might need to be compiled from a different worksheet and would be found on that worksheet by looking up a keyword.

我正在编写一个工作应用程序，它将一大堆不同的电子表格编译成特定工作站点的报告。我有很多工作表，每个工作表都有一大堆指标。每个工作表上的每个指标可能需要从不同的工作表编译，并且可以通过查找关键字在该工作表上找到。

Another problem is that some of the wording on these spreadsheets gets changed periodically to better reflect industry standards so I can't just hardwire in keywords to search for.

另一个问题是，这些电子表格上的一些措辞会定期更改以更好地反映行业标准，因此我不能只是硬连接要搜索的关键字。

So I'm looking for a way to store metadata along with a cell that I can hide from users so they don't accidentally delete it but can access easily from VBA in order to change if needed (I would write a procedure to do this if needed).

所以我正在寻找一种方法来存储元数据以及一个我可以对用户隐藏的单元格，这样他们就不会意外删除它，但可以从 VBA 轻松访问以便在需要时进行更改（我会编写一个程序来执行此操作如果需要的话）。

Research said I could use comments (though I'm not sure if I can access these from vba, and I would like them to be hidden) or a hidden worksheet to mirror each of the worksheets I use with information in the given cell on that worksheet. I would probably go with the latter but it's not perfect.

研究表明我可以使用注释（虽然我不确定我是否可以从 vba 访问这些，我希望它们被隐藏）或隐藏的工作表来反映我使用的每个工作表和给定单元格中的信息工作表。我可能会选择后者，但它并不完美。

Any other ideas?

还有其他想法吗？

Edit for clarity: I need a string associated with a single cell which will point me at how to find the appropriate data. Ex: "Look in Workbook 1 -%- Search for this text".

为清楚起见进行编辑：我需要一个与单个单元格关联的字符串，它将指向我如何找到适当的数据。例如：“在工作簿 1 中查找 -%- 搜索此文本”。

The VeryHidden Attribute mentioned by @Andrew might be the best way to go as I don't think that there is a way to attach a variable to a cell.

@Andrew 提到的非常隐藏的属性可能是最好的方法，因为我认为没有办法将变量附加到单元格。

Answer 1

回答by Jesse

One of my favourite tricks is to use the N()function.

我最喜欢的技巧之一是使用该N()功能。

N()converts a non number to a number but if you feed it text it always returns 0. When I need to add an in cell note to myself I'll add something like:

N()将一个非数字转换为一个数字，但如果你给它输入文本，它总是返回 0。当我需要给自己添加一个单元格注释时，我会添加如下内容：

=..... +N("This is to calculate blah blah blah")

As long as adding 0 to the value won't hurt it works well.

只要将 0 添加到值不会伤害它就可以正常工作。

Answer 2

回答by Andrew Leach

What I have done in the past when needing to store data for the application but which should not be accessible by users is to use a sheet with visibility set to VeryHidden.

我过去在需要为应用程序存储数据但用户不应访问时所做的是使用可见性设置为VeryHidden.

There may also be the option of creating an Excel Add-in (XLA) which can be independent of user data and so can be updated separately if rules need to change — although it is possible to use VBA in an "update" workbook to replace entire VBA modules in the "data" workbook. I'll have to see if I can find the code I wrote to do that.

还可以选择创建一个 Excel 插件 (XLA)，它可以独立于用户数据，因此如果规则需要更改，可以单独更新 - 尽管可以在“更新”工作簿中使用 VBA 来替换“数据”工作簿中的整个 VBA 模块。我必须看看我是否能找到我写的代码来做到这一点。

Answer 3

回答by Willwillpower

You can use a similar trick for Text.

您可以对 Text 使用类似的技巧。

You can use:

您可以使用：

="Display Value" & left("This is to calculate...",0)

OR:

或者：

=CHOOSE(1,"Display Value","This is to calculate...")

Answer 4

回答by ian0411

Every spreadsheet should have labels or headers, or at least some description fields. If this is true, there is a trick that you can hide a value in one of those cells and nobody will find out. Here is how you do it.

每个电子表格都应该有标签或标题，或者至少有一些描述字段。如果这是真的，那么有一种技巧可以在其中一个单元格中隐藏一个值，而没有人会发现。这是你如何做到的。

Enter the value in the cell with =N(). For example:

用输入单元格中的值=N()。例如：

=N("Apple, Google, Facebook, Microsoft").

Go to Format Cells > Custom: Enter the text you want to display for the header/label/description. For example:

转到Format Cells > Custom：输入要为标题/标签/说明显示的文本。例如：

"Header Name".

As you can see the attached image that I was able to enter text in the cell but shows something different. And as a bonus, you can use this syntax below to get the formula/text back from VBA for your program:

如您所见，我可以在单元格中输入文本但显示不同的内容。作为奖励，您可以使用下面的语法从 VBA 为您的程序获取公式/文本：

Range("A1").Formula

Hope this helps.

希望这可以帮助。

Answer 5

回答by Jonas Glesaaen

I encountered this exact problem and found a couple of possible solutions depending on need.

我遇到了这个确切的问题，并根据需要找到了几个可能的解决方案。

Save data in `Hyperlink`

将数据保存在 `Hyperlink`

You can store data in hyperlinks for example in the link itself or in the tooltip for the link. The advantage of this method is that the data is tied to the cell itself, and not to the address. Meaning that if e.g. someone sorts the cells the hidden data follows. You could also catch the HyperlinkClick event if you wished, but none of these hyperlinks go anywhere, so it probably doesn't matter.

您可以将数据存储在超链接中，例如在链接本身或链接的工具提示中。这种方法的优点是数据与单元格本身相关，而不是与地址相关联。这意味着如果例如有人对单元格进行排序，则隐藏数据如下。如果您愿意，您也可以捕获 HyperlinkClick 事件，但是这些超链接都不会去任何地方，所以这可能无关紧要。

Using `ScreenTip`

使用 `ScreenTip`

' Write hidden data to a cell
Public Sub WriteData(ByVal Cell As Range, ByVal Data As String)

    Cell.Hyperlinks.Delete
    Cell.Hyperlinks.Add Cell, Address:="#", ScreenTip:=Data

    ' Set standard formatting
    With Cell.Font
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With

End Sub

' Read hidden data from a cell
Public Function ReadData(ByVal Cell As Range) As String

    If Not CellContainsData(Cell) Then Exit Function

    ReadData = Cell.Hyperlinks(1).ScreenTip

End Function

Private Function CellContainsData(ByVal Cell As Range) As Boolean

    CellContainsData = Cell.Hyperlinks.Count > 0

End Function

Using `Address`

使用 `Address`

This probably has some restrictions on the kind of string you can save as they have to somewhat be valid links. For example it cannot have any spaces in them, but you could of course use a code (like \20) to represent a space and decode it later.

这可能对您可以保存的字符串类型有一些限制，因为它们必须是有效的链接。例如，其中不能有任何空格，但您当然可以使用代码（如\20）来表示空格并稍后对其进行解码。

Disclaimer: I am actually not entirely sure what this does, I was trying to find a way to create a valid link that didn't go anywhere and this is what I came up with. It somewhat breaks excel as you can no longer edit the hyperlink with the Excel GUI, and clicking it doesn't trigger a FollowHyperlinkevent. If you simply set ?Dataas the address then Excel will clear it the next time anyone clicks it.

免责声明：我实际上并不完全确定这是做什么的，我试图找到一种方法来创建一个没有去任何地方的有效链接，这就是我想出的。它在某种程度上破坏了 excel，因为您无法再使用 Excel GUI 编辑超链接，并且单击它不会触发FollowHyperlink事件。如果您只是将?Data地址设置为地址，那么下次有人单击它时，Excel 将清除它。

' Write hidden data to a cell
Public Sub WriteData(ByVal Cell As Range, ByVal Data As String)

    Cell.Hyperlinks.Delete
    Cell.Hyperlinks.Add Cell, Address:="//?" & Data, ScreenTip:="Nothing suspicious here."

    ' Set standard formatting
    With Cell.Font
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With

End Sub

' Read hidden data from a cell
Public Function ReadData(ByVal Cell As Range) As String

    If Not CellContainsData(Cell) Then Exit Function

    Dim Data As String
    Data = Cell.Hyperlinks(1).Address    

    ReadData = Right$(Data, Len(Data) - 3)

End Function

Private Function CellContainsData(ByVal Cell As Range) As Boolean

    If Cell.Hyperlinks.Count < 1 Then Exit Function

    Dim Data As String
    Data = Cell.Hyperlinks(1).Address

    ' Check that the cell has the correct prefix
    If Len$(Data) < 3 Then Exit Function
    If Left$(Data, 3) <> "\?" Then Exit Function

    CellContainsData = True

End Function

Save data in `Validation`

将数据保存在 `Validation`

It is also possible to store data in the validation of a cell. This however does not work if e.g. someone sorts the cells.

还可以在单元格的验证中存储数据。但是，如果有人对单元格进行排序，这将不起作用。

' Write hidden data to a cell
Public Sub WriteData(ByVal Cell As Range, ByVal Data As String)

    With Cell.Validation
        ' Remove previous validation
        .Delete

        ' Write data on a specific format so you know it was you.
        .Add xlValidateCustom, Formula1:="""@" & Data & """"

        ' Hide it as well as possible
        .InCellDropdown = False
        .ShowInput = False
        .ShowError = False
    End With

End Sub

' Read hidden data from a cell
Public Function ReadData(ByVal Cell As Range) As String

    If Not CellContainsData(Cell) Then Exit Function

    Dim Data As String
    Data = Cell.Validation.Formula1

    ReadData = Mid$(Data, 3, Len(Data) - 3)

End Function

Private Function CellContainsData(ByVal Cell As Range) As Boolean

    On Error GoTo InvalidValidation

    If Cell.Validation.Type <> xlValidateCustom Then Exit Function

    Dim Data As String
    Data = Cell.Validation.Formula1

    ' Check that the data is on your specific format
    If Left$(Data, 2) <> """@" Then Exit Function
    If Right$(Data, 1) <> """" Then Exit Function

    CellContainsData = True

InvalidValidation:
    Exit Function

End Function

vba 如何在excel中使用单元格存储额外的（隐藏）信息

提问by Colophi

回答by Jesse

回答by Andrew Leach

回答by Willwillpower

回答by ian0411

回答by Jonas Glesaaen

Save data in `Hyperlink`

将数据保存在 `Hyperlink`

Using `ScreenTip`

使用 `ScreenTip`

Using `Address`

使用 `Address`

Save data in `Validation`

将数据保存在 `Validation`

相关推荐

最近更新

标签

vba 如何在excel中使用单元格存储额外的（隐藏）信息

提问by Colophi

回答by Jesse

回答by Andrew Leach

回答by Willwillpower

回答by ian0411

回答by Jonas Glesaaen

Save data in Hyperlink

将数据保存在 Hyperlink

Using ScreenTip

使用 ScreenTip

Using Address

使用 Address

Save data in Validation

将数据保存在 Validation

相关推荐

在 VBA 中返回，不符合我的预期

vba Excel 数据验证下拉列表中的空白值

类型不匹配 VBA

vba 如何将范围设置为没有第一行 + 另一列的 UsedRange 的交集？

相关推荐

最近更新

标签

Save data in `Hyperlink`

将数据保存在 `Hyperlink`

Using `ScreenTip`

使用 `ScreenTip`

Using `Address`

使用 `Address`

Save data in `Validation`

将数据保存在 `Validation`