vbalistbox-control

How to make Listbox.List keep type information?


If I affect a full array to a ListBox, using ListBox1.List = [{1;2;3;4}] for example, the Listbox's .List items keep their correct type (here numbers), but if I use ListBox1.AddItem 5 or Listbox1.List(5) = 6 to set an individual item the type is automatically changed to String.

Sample Code:

Private Sub UserForm_Initialize()
    ListBox1.List = [{1;2;3;4}]
    ListBox1.AddItem 5
    ListBox1.AddItem
    ListBox1.List(5) = 6
End Sub

enter image description here

Later on, when comparing values, I get wrong results because numbers are not equals to text (5 <>"5").

Is there any easy (1) way to ensure the type of the list items is not converted to String?

(1) I know I can explicitly make the conversion to String, but I rather keep my values as numbers instead of "numbers-strored-as-text" in the listbox


Solution

  • I guess that will be impossible when using AddItem. According to https://learn.microsoft.com/en-us/office/vba/api/access.listbox.additem, the first parameter is a string, so everything you pass will be converted to a string.

    Probably your best bet is to collect all items in an array and assign the array using ListBox1.List. Or you have to live with numbers stored as string...

    Update I mixed the pages up - link to the Access page was wrong.
    Anyhow, Documentation is rather poor. It says "valid object" but doesn't define what that means. At least it is not possible to add a object - that throws a type mismatch.

    Also, the documentation states that a Variant is returned, but it seems thatis not true - when I try to get the result, the compiler throws an error.

    As a conclusion, I assume that AddItem converts everything to a string (and throws an error if that fails). So I still assume that you have to build up an array and assign it to List if you want to have real numbers.