VBA Excel 代码根据字符串长度自动格式化文本(信用卡号)?

VBA Excel 代码根据字符串长度自动格式化文本(信用卡号)?

背景:

  1. Excel 中数字格式的单元格的第 15 位以外的数字均更改为零 (原因)。此行为会影响长度超过 15 位的信用卡号的记录。在 Excel 中解决此行为的方法是将包含信用卡号的单元格(在输入数据之前)格式化为字符串而不是数字。
  2. 为了以更加用户友好的格式显示该字符串,我添加了一个辅助列,用于获取字符串并将其分块。
  3. 我通过测试字符串的长度来做到这一点,如果它是 16 个字符,我将其分块为 4 4 4 4(加拿大 VISA/MC),如果它是 15 位数字,我将其分块为 3 6 5(加拿大 Amex)。

我的表格样本

我在辅助列中使用公式进行分块

问题:
您能否调整我的工作表代码以适应 VBA,从而每当数据发生变化时,将所需的格式动态地应用于包含我的信用卡字符串的列?

添加请求的 VBA 代码后的表格

答案1

首先,您肯定需要在列中使用文本格式B。否则,Excel 会在此代码之前完成其工作,因此代码无法可靠地完成工作。

然后,您必须打开 VBA 窗口并打开要自动更新的工作表模块。在此模块中粘贴以下代码:

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim c As Range
  Dim rIntersect As Range
  On Error GoTo errH
  Application.EnableEvents = False
  Set rIntersect = Intersect(Me.Range("Table1[Text]"), Target)
  If Not rIntersect Is Nothing Then
    For Each c In rIntersect
      If IsNumeric(c.Value) Then
        If Len(c.Value) = 14 Then
          c.Value = Format(c.Value, "@@@ @@@@@@ @@@@@")
        ElseIf Len(c.Value) = 16 Then
          c.Value = Format(c.Value, "@@@@ @@@@ @@@@ @@@@")
        End If
      End If
    Next
  End If
errH:
  Application.EnableEvents = True
End Sub

(谢谢,@罗恩,用于引导该Format函数。)

Table1第三,用您的实际表名更新代码中的表名。

Worksheet_Change是个事件每次工作表中发生更改时都会触发此事件。在此代码中,我们首先确保信用卡号列中确实发生了更改,并且只有在这种情况下我们才开始检查长度并修改值。

相关内容