why is my redim preserve causing type mismatch error?

0
votes

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?

2
excelvba
did you redim the array prior to the loop: Redim arrBlankFields(1 to 1) As Variant - Scott Craner
@ScottCraner ok so that worked, but now getting a subscript out of range error on arrBlankFields(x) part of arrBlankFields(x) = icontrol.Name - NidenK
make sure x=1 prior to the loop also. - Scott Craner
Or make your array start at 0 like a normal programmer. - HackSlash
@HackSlash that depends on what one is doing with the array later. If the OP is going to load it into the sheet or compare it to an array bulk loaded from the sheet it is easier to maintain the 1 base. - Scott Craner

2 Answers

1
votes

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/

0
votes

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