VBA中混合数据类型变量引发运行时错误‘13’类型不匹配问题

在Excel文件中，我给单元格的验证数据列表设置了24个变量，其中会员编号是数字加字母的混合格式，其余变量多为纯数字类型。运行VBA宏时，把member_num声明为Long类型后，纯数字编号（比如589）能正常执行，但遇到像463A这类混合格式的编号时，会在member_num = Sheet1.Range("I2")这一行触发运行时错误‘13’：类型不匹配。请问是不是需要更换变量类型，或者写其他代码来处理这种混合数据类型的变量？

Sub RecordofInvoices()

Dim member_num As Long
Dim invoice_num As Long
Dim name As String
Dim invoice_date As Date
Dim due_date As Date
Dim amount_due As Currency
Dim next_invoice As Range

member_num = Sheet1.Range("I2")
invoice_num = Sheet1.Range("I3")
name = Sheet1.Range("B11")
invoice_date = Sheet1.Range("I4")
due_date = Sheet1.Range("I6")
amount_due = Sheet1.Range("I26")

Set next_invoice = Sheet3.Range("A1048576").End(xlUp).Offset(1, 0)

next_invoice = member_num
next_invoice.Offset(0, 1) = invoice_num
next_invoice.Offset(0, 2) = name
next_invoice.Offset(0, 3) = invoice_date
next_invoice.Offset(0, 4) = due_date
next_invoice.Offset(0, 5) = amount_due

End Sub

最简单的处理：更换变量类型 #

直接把member_num的类型从Long改成String，因为混合格式的会员编号本质是文本类型，Long只能存储纯数字，无法兼容带字母的内容。修改后的代码片段：

Dim member_num As String
' 后续赋值和写入逻辑不变
member_num = Sheet1.Range("I2")

这样不管是纯数字还是字母数字混合的会员编号，都能正常读取和写入，不会触发类型错误。

可选：兼容数字与文本的判断处理 #

如果需要区分纯数字编号和混合格式编号（比如对纯数字做额外处理），可以用IsNumeric函数做判断，把变量声明为Variant类型：

Dim member_num As Variant
If IsNumeric(Sheet1.Range("I2").Value) Then
    member_num = CLng(Sheet1.Range("I2").Value) ' 纯数字转Long
Else
    member_num = Sheet1.Range("I2").Value ' 混合格式保留文本
End If

不过这种方法没必要，因为Excel中纯数字字符串和数字的显示差异极小，直接用String类型能更简单地兼容所有场景。

内容的提问来源于stack exchange，提问作者Winston Smith