Unexpected Type Mismatch Error when Checking If Array Element Equals Space

Question

For clarity, I've posted my entire code below and all the functions it uses. The goal of this macro is to read data that looks like this: XY05-09 XY37-49 and generate labels based on the range in the cell. In this case it would make XY05, XY06, XY07 etc. My problem is with the line

If ArrOfChar(element) = " " Then

Which returns a type mismatch. I've used MSgBox to output each element as they are being created and it looks to store the space as an element but for some reason I can't compare the element's value to " ". I've dug around for awhile but had no luck fining an explanantion as to why this is. Any help is appreciated!

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

user_3219613 · Answer

Because of For Each element In ArrOfChar your element IS an element itself of the array and not the index of an element.

Therefore ArrOfChar(element - 1) fails because element is not a number!

Either use element = " " to access the element or change your loop to a indexed loop like:

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

This will loop from the first element to the last element and the idx is the index of the element.

Make sure you use Option Explicit and declare all your variables properly. I recommend always to activate Option Explicit: In the VBA editor go to Tools › Options › Require Variable Declaration.

user_16404872 · Answer

The reason you are getting the error is because element is a character, not an integer. VBA is not able to evaluate element - 1

To loop through, you will need to change element to something like this. For element = 1 To Len(InitialString)

Unexpected Type Mismatch Error when Checking If Array Element Equals Space

2 Answers