我想在Excel工作表中嵌入一个过程，该过程将检测单元格格式何时发生更改，例如从文本到数字。
但是我不知道如何获取单元格的格式类型。我尝试使用Worksheet_Change事件处理程序检查数据类型，如下所示:
Private Sub worksheet_change(ByVal Target As Range)
If Target.Address = "a1" Then
If VarType(Target) <> 5 Then
MsgBox "cell format has been changed"
End If
End If
End Sub
但是使用此代码后，如果我将单元格A1的数据类型从“数字”更改为“文本”，则不会触发
Worksheet_Change；仅当我更改单元格的内容时才调用事件处理程序。而且，该过程可以检测内容是否从数字改变为字母字符串，例如。从“35.12”到“abcd”，但不是从数字类型编号到文本类型编号；如果我将单元格B1设置为文本，然后输入“40”，然后将单元格B1的内容粘贴到单元格A1中，则
vartype()仍返回“5”，因此不会触发警报。无论内容类型是否已更改，如何检测格式已更改？
最佳答案
好问题!
如果您只是想在NumberFormat更改上触发事件(您似乎错误地将其称为数据格式，则NumberFormat是所需的属性)，下面是一个很好的示例。
我正在拦截所有选择更改事件，并检查是否更改了NumberFormat。
Option Explicit
'keep track of the previous
Public m_numberFormatDictionary As New dictionary
Public m_previousRange As Range
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'requires reference to Microsoft Scripting Runtime
Dim c As Variant
Dim wasChange As Boolean
Debug.Print "***********************"
'make sure you had a previous selection and it was initialized
If m_numberFormatDictionary.Count > 0 And Not m_previousRange Is Nothing Then
'Iterate through all your previous formattings and see if they are the same
For Each c In m_previousRange
Debug.Print "Found " & c.NumberFormat & " in " & c.Address
Debug.Print "Stored value is " & m_numberFormatDictionary(c.Address) & " in " & c.Address
'print out when they are different
If c.NumberFormat <> m_numberFormatDictionary(c.Address) Then
Debug.Print "~~~~~~ Different ~~~~~~"
wasChange = True
End If
Next c
End If
'clear previous values
m_numberFormatDictionary.RemoveAll
'Make sure you don't error out Excel by checking a million things
If Target.Cells.Count < 1000 Then
'Add each cell format back into the previous formatting
For Each c In Target
Debug.Print "Adding " & c.NumberFormat & " to " & c.Address
m_numberFormatDictionary.Add c.Address, c.NumberFormat
Next c
'reset the range to what you just selected
Set m_previousRange = Target
End If
'simple prompt now, not sure what your use case is
If wasChange Then
MsgBox "There was at least one change!"
End If
End Sub
我不确定您要寻找的内容，您必须适当地修改print/msgbox语句。根据您的用例，您可能需要稍作修改，但这在我的所有测试示例中都可以使用。