VBA ListBox variable/object type

[Dmitry_D_Onishchenko]

Hi all,

I have a UserForm with a ComboBox and a ListBox on it; their respective names are "ComboBox_Field" and "ListBox_Value". Their list values are currently strings, but might be dates or integers as well.

Now, I have a function to count the number of selected items in a ListBox:

Function ListBoxSelectionCount(LB As ListBox) As Long
    Dim x As Long, count As Long

    For x = 0 To (LB.ListCount - 1)
        If LB.Selected(x) Then count = count + 1
    Next x

    ListBoxSelectionCount = count
End Function

For a reason unknown to me, I can't pass my ListBox to ListBoxSelectionCount() due to variable type mismatch:

Debug.Print VarType(ListBox_Value) ' returns 1 as for vbNull data type

However, if I redefine the function and pass the argument as Variant instead of ListBox

Function ListBoxSelectionCount(LB As Variant) As Long
' exactly the same code as above
End Function

, everything works fine.

My question is: Why is the result of VarType(ListBox_Value) equal to vbNull? (I would expect this to be identical to my Combobox properties - I have VarType(ComboBox_Field)=8 as the ComboBox is populated with strings.)

Thanks in advance,
Dmitry

 

[dmt32]

Hi,
try making change to the parameter as shown & see if this resolves your issue

Function ListBoxSelectionCount(ByVal LB As MSForms.ListBox) As Long
    Dim x As Long, count As Long

    For x = 0 To (LB.ListCount - 1)
        If LB.Selected(x) Then count = count + 1
    Next x

    ListBoxSelectionCount = count
End Function

Dave

 

[Dmitry_D_Onishchenko]

Thanks Dave, pretty impressive. It works perfectly. But... why? Why is it not the same as with ComboBoxes?

Debug.Print TypeOf ComboBox_Field Is ComboBox            ' True
Debug.Print TypeOf ComboBox_Field Is MSForms.ComboBox    ' True
Debug.Print TypeOf ListBox_Value Is ListBox              ' False
Debug.Print TypeOf ListBox_Value Is MSForms.ListBox      ' True

 

[RoryA]

The Excel library has a Listbox object and it takes precedence. (It doesn't have a combobox object as it calls that a Dropdown)

 

[dmt32]

@RoryA kindly answered your question.

Dave

 

[Dmitry_D_Onishchenko]

The Excel library has a Listbox object and it takes precedence. (It doesn't have a combobox object as it calls that a Dropdown)

Thank you Rory, it's clear. (However it's completely new to me that there exists two sets of object names with mismatches; I'll need to read a bit about the matter.)

The initial issue is resolved completely, thanks a lot guys!

 

[RoryA]

Different object libraries can have objects with the same name. For example, both Word and Excel have Range objects, so if you're automating Word from Excel (or vice versa) you should always explicitly declare variables as either Word.Range or Excel.Range.

 

[Jaafar Tribak]

Alternatively, you could declare ListBox_Value as ListBox2 (Hidden member) which is unique to the MSForms Library.

Debug.Print TypeOf ListBox_Value Is ListBox2               ' TRUE