CODEWITHSUNDEEP
excelvba
NidenK
NidenK

Reputation: 369

why is my redim preserve causing type mismatch error?

Trying to add in control names from a userform into an array to be spat out into a msgbox at the end. arrBlank Fields is supposed to look at controls and see if they're blank or not. if they are, redim preserve each name and then keep looping through to find all blank fields.

    Dim icontrol As Control
    Dim arrBlankFields As Variant
    Dim i As Long, x As Long

'for loop

Case icontrol.Name Like "txt*"
    If icontrol.Value = "" Then
        ReDim Preserve arrBlankFields(1 To UBound(arrBlankFields))
        arrBlankFields(x) = icontrol.Name
        x = x + 1
End If

'end loop

getting a type mismatch at 1 to UBound(arrBlankFields). I've also tried just doing ReDim Preserve arrBlankFields(x) because it's just a 1-dimensional array, but no luck.

Any ideas?

Upvotes: 0

Views: 1161

Answers (2)

Julio Gadioli Soares
Julio Gadioli Soares

Reputation: 336

1º Break line all variables (no change nothing but is better) 2º You need define first your arrBlankFields size, like this (example, make for you):

ReDim arrBlankFields(1000) ' No need preserve before increment, in this moment

or sample for you

Set dbUser = ThisWorkbook.Worksheets("Usuários")
QntUsers = dbUser.Cells(rows.count, 1).End(xlUp).Row
ReDim arrBlankFields(QntGrupoUsers2)

3º you need start of zero your array:

    On Error GoTo err_control    
    Dim icontrol As Control
    Dim arrBlankFields As Variant
    Dim i As Long
    Dim x As Long
    x=0
    
    'Case icontrol.Name Like "txt*" ' error  ... no have end select 
        If icontrol.Value = "" Then
        ReDim Preserve arrBlankFields(1 To UBound(arrBlankFields)) 'REMOVE THIS LINE FROM HERE.
            arrBlankFields(x) = icontrol.Name
            x = x + 1
    End If
    ReDim Preserve arrBlankFields(x)

   Err_Control:
   If Err.Number <> 0 Then
       MsgBox "Error Number: " & Err.Number & Chr(13) & "Error Description: " & 
   Err.Description
   End If

4º after you read and create your array, now you can do that, above.

Good Luck

Upvotes: 1

HackSlash
HackSlash

Reputation: 5805

Only use arrays when you know the size ahead of time. Use a collection when you don't. You never have to ReDim an array ever again.

Dim icontrol As Control
Dim arrBlankFields As New Collection
Dim i As Long

'for loop

Case icontrol.Name Like "txt*"
    If icontrol.Value = "" Then            
        arrBlankFields.Add icontrol.Name
    End If

'end loop

Additional reading here: https://excelmacromastery.com/excel-vba-collections/

Upvotes: 1

Related Questions