检查数组元素是否等于空格时出现意外类型不匹配错误

Question

为了清楚起见，我把我的全部代码和它使用的所有函数贴在下面。这个宏的目标是读取如下所示的数据:XY05-09 XY37-49，并根据单元格中的范围生成标签。在这种情况下，它会产生XY05, XY06, XY07等。我的问题是

这行

If ArrOfChar(element) = " " Then

返回类型不匹配。我已经使用MSgBox来输出每个元素，因为他们正在创建，它看起来存储空间作为一个元素，但由于某种原因，我不能比较元素的值"。我挖了一段时间，但没有运气找到一个解释为什么会这样。任何帮助都是感激的!

Sub Label_Generator_Button1_Click()
Dim InitialString As String
Dim Prefix As String
Dim FirstNum As Integer
Dim LastNum As Integer
Dim IsLastNum As Boolean
Dim InsertZero As Boolean
Dim ArrOfChar() As String
Prefix = ""
FirstNum = 0
LastNum = 0
IsLastNum = False
InsertZero = False
InitialString = Range("A1")

ReDim ArrOfChar(Len(InitialString) - 1)
For i = 1 To Len(InitialString)
ArrOfChar(i - 1) = Mid$(InitialString, i, 1)
'MsgBox ArrOfChar(i - 1)
Next

For Each element In ArrOfChar

If ArrOfChar(element) = " " Then 'If space, reset all to zero, new number WHY IS THIS TYPE MISMATCH
For i = FirstNum To LastNum
Range("B2").Value = Range("B2").Value & Prefix
If InsertZero = True And i < 10 Then
Range("B2").Value = Range("B2").Value & "0"
End If
Range("B2").Value = Range("B2").Value & i & " "
Next i
Prefix = ""
FirstNum = 0
LastNum = 0
InsertZeroFirst = False
InsertZeroLast = False
IsLastNum = False
GoTo NextIteration
End If

If ArrOfChar(element) = "-" Then 'If a dash is used, flag the system to switch to last number and make insert zero false
IsLastNum = True
GoTo NextIteration
End If

If IsLetters(ArrOfChar(element)) = True Then 'If is a letter add to prefix
Prefix = Prefix & ArrOfChar(element)
GoTo NextIteration
End If

If ArrOfChar(element) = "0" And FirstNum = 0 Then ' If is 0 and the first number, set flag to insert leading 0
InsertZero = True
GoTo NextIteration
End If

If IsNumbers(ArrOfChar(element)) = True Then
If IsLastNum = False Then
FirstNum = CInt(ArrOfChar(element))
GoTo NextIteration
End If
If IsLastNum = True Then
LastNum = CInt(ArrOfChar(element))
GoTo NextIteration
End If
End If
If ArrOfChar(element) = "0" And FirstNum > 0 Then 'If is 0 and not the first number, multiply first number by 10. If we are given a 5 then the next is zero, multuplying by 10 gives us our aditional place
FirstNum = FirstNum * 10
GoTo NextIteration
End If
NextIteration:
Next element
End Sub

Function IsLetters(Str As String) As Boolean
Dim i As Integer
For i = 1 To Len(Str)
Select Case Asc(Mid(Str, i, 1))
Case 65 To 90, 97 To 122
IsLetters = True
Case Else
IsLetters = False
Exit For
End Select
Next i
End Function

Function IsNumbers(Str As String) As Boolean
Dim i As Integer
For i = 1 To Len(Str)
Select Case Asc(Mid(Str, i, 1))
Case 49 To 57
IsNumbers = True
Case Else
IsNumbers = False
Exit For
End Select
Next i
End Function

Answer 1

Because ofFor Each element In ArrOfCharyourelementIS数组的元素本身，而不是索引

因此ArrOfChar(element - 1)失败，因为element不是一个数字!

要么使用element = " "访问元素，要么将循环更改为索引循环，如:

Dim idx As Long
For idx = LBound(ArrOfChar) To UBound(ArrOfChar)
Debug.Print idx, ArrOfChar(idx)  ' print idx and value of the element
Next idx

这将从第一个元素循环到最后一个元素，idx是元素的索引。

确保使用Option Explicit并正确声明所有变量。我建议始终激活Option Explicit:在VBA编辑器中转到工具›选项›要求变量声明。

Answer 2

您得到错误的原因是因为element是一个字符，而不是整数。VBA无法计算element - 1

要循环，您需要将element更改为如下内容。For element = 1 To Len(InitialString)