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

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

因为For Each element In ArrOfChar您的 element 是 数组的元素本身，而不是 索引 的一个元素。
因此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 编辑器中转到工具 › 选项 › Require Variable Declaration .