VBA first array item is always empty

Question

The code below I patched togheter from several examples I found online, I am no VBA expert.

But the first item on the clist array (and the first item on the dropdown) is always empty, I'm assuming it has something to do with the redims, but I couldn't figure it out.

What could be the issue?

Private Sub ComboBox1_Change()
    ReDim clist(0)
    'If any value is input
    If ComboBox1.Value <> "" Then
        Dim kword As Variant
        Dim product As Variant
        'For each product description in our sheet table
        For Each product In [Produtos[Descrição]].Rows
            'Keyword search
            For Each kword In Split(ComboBox1.Value, " ")
                If InStr(Replace(product.Value, "", " "), kword) And kword <> "" Then
                    'Issue most likely here
                    ReDim Preserve clist(UBound(clist) + 1) As Variant
                    clist(UBound(clist)) = product.Value
                    Exit For
                End If
            Next kword
        Next product
        ComboBox1.list = clist
        'If found something
        If UBound(clist) > 0 Then
            ComboBox1.DropDown
        End If
    'If no Input just show all products, here it doesn't show a blank item
    Else
        ComboBox1.list = [Produtos[Descrição]].Value2
    End If
End Sub

user4039065 · Accepted Answer · 2017-09-01 19:41:37Z

4

Try it as,

    ReDim clist(0)
    For Each product In [Produtos[Descrição]].Rows
        'Keyword search
        For Each kword In Split(ComboBox1.Value, " ")
            If InStr(Replace(product.Value, "", " "), kword) And kword <> "" Then
                'Issue most likely here
                clist(UBound(clist)) = product.Value
                ReDim Preserve clist(UBound(clist) + 1)
                Exit For
            End If
        Next kword
    Next product
    ReDim Preserve clist(UBound(clist) - 1)

answered Sep 1, 2017 at 19:41

user4039065

Sign up to request clarification or add additional context in comments.

1 Comment

Mojimi Over a year ago

This gives an error if clist is never increased in size

Quima · Accepted Answer · 2017-09-01 19:43:35Z

2

It is because you are increasing the size of your array and only then setting a value to the last index of it.

ReDim Preserve clist(UBound(clist) + 1) As Variant 'Increase array size by 1
clist(UBound(clist)) = product.Value 'Set a value to the higher index

This way you will never set a value to the index 0

Something like this should solve your problem:

if clist(Ubound(clist)) <> empty then
    ReDim Preserve clist(UBound(clist) + 1) As Variant
end if
clist(UBound(clist)) = product.Value

answered Sep 1, 2017 at 19:43

Quima

92411 silver badges21 bronze badges

Collectives™ on Stack Overflow

VBA first array item is always empty

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related