背景:
- Excel 中數字格式的儲存格將第 15 位以上的數字變更為零(原因)。此行為會影響可能超過 15 位數字的信用卡號碼的記錄。 Excel 中解決此問題的方法是將包含信用卡號的儲存格(在輸入資料之前)格式化為字串而不是數字。
- 為了以更用戶友好的格式顯示此字串,我添加了一個輔助列,用於獲取該字串並將其分塊。
- 我透過測試字串的長度來做到這一點,如果它是16 個字符,我將其分塊為4 4 4 4(加拿大VISA/MC),如果它是15 位數字,我將其分塊為3 6 5(加拿大Amex)。
問題:
您能否調整我的 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
是個事件每當工作表中發生變化時就會觸發。在此程式碼中,我們首先確保信用卡號碼列中的某些內容發生了確切的更改,只有在這種情況下,我們才開始檢查長度並修改值。