What causes an entire ListBox column to be blank?

Question

I'm working on an Excel project where I have a VBA UserForm with two ListBox controls. I load them by assigning an array to the List property. All of the expected data shows up, except for the seventh column which is entirely blank.

Why is this entire column blank?

Additional details:
The array has around 15 columns and 25 rows.
The seventh column of the array is listed in Locals as type Variant/Decimal. It contains UPC values (up to 14 digits) stored in the database as DECIMAL(14,0).
None of the UPC values are NULL or blank. They DO show up in the array in the Locals window and they can be retrieved from the ListBox, just not seen.
The ListBox has ListStyle fmListStylePlain and MultiSelect fmMultiSelectMulti.
Although you cannot Dim (declare) a variable as Decimal, you can convert to Decimal with the CDec function: ?typename(CDec(4.5)) in the Immediate window.

Minimal Example:
Create a UserForm with a ListBox (Listbox1), two TextBox controls (TextBox1 and TextBox2), and two Command Buttons (CommandButton1 and CommandButton2).
In the Code Module of the UserForm, paste in the below code:

Private Sub CommandButton1_Click()
    Dim x(0 To 0, 0 To 0)

    x(0, 0) = CDec(TextBox1.Text)
    ListBox1.List = x

    TextBox2.Text = ListBox1.List(0, 0)
End Sub

Private Sub CommandButton2_Click()
    Dim x(0 To 0, 0 To 0)

    x(0, 0) = CDbl(TextBox1.Text)
    ListBox1.List = x

    TextBox2.Text = ListBox1.List(0, 0)
End Sub

Annotated Screenshot

Run the UserForm, type a number (with or without decimal places) in TextBox1, then click CommandButton1. ListBox1 remains blank, but Textbox2 shows a value.
Click CommandButton2. ListBox1 and TextBox2 show the value.

Does your array following the same indexing as the List property (i.e. starting from 0 rather than 1)? Also what happens if you try to get the value from the 17th column (i.e. in the Immediate Window type ?FormName.ListBoxName.List(0,13)). Does it return a value or nothing? This will indicate if the data is making it into the List — DavidN
UPC codes shouldn't be stored as numeric values... they're strings, much like a phone or credit card number is (e.g. 1234568901234 shows up as 1.23457E+13 in Excel, by default). If you inspect the array elements in the locals toolwindow, does it appear to contain the UPC codes? Do you have an example of one such UPC codes? Also, feel free to edit your question to embed a minimal reproducible example that reproduces the problem - makes it easier for answerers to help. — Mathieu Guindon
@DavidN Yes, my array uses the base index of 0. When I get the value from the list as in your sample, I DO get a value back (something like 6757583452). — Shadovv
@MathieuGuindon I understand some of the issues around storing UPC codes as numbers. At this point, the database structure is out of my control. The Locals window DOES show the UPC values such as 6757583452. I thought about including an example, but I don't think it would resemble "minimal" :) I'm not sure how I could "fake" the database part, without which I suspect my example would actually work as expected. — Shadovv

Shadovv Shadovv · Accepted Answer · 2019-01-24T20:10:53

Apparently, a VBA ListBox cannot handle Variant/Decimal values. I haven't been able to find an authoritative source to explain this, but I've found that converting the Decimal values to text using the Format function was effective.

Dim vData as Variant
Dim lRow as Long

'fill vData array from database here
...

'convert seventh column to zero-padded 14-character string in this loop:
For lRow = LBound(vData,1) to UBound(vData,1)
    vData(lRow, 6) = Format(vData(lRow, 6), "00000000000000")
Next lRow

What causes an entire ListBox column to be blank?

1 Answers