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
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
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.