VBScript excel dropdown list

Question

I am using the code from the answer of this question: how to add a drop down list in excel using vbs

to create a dropdown list in excel.
I am using an array instead of a range to fill the list.

.Add 3, 1, 1 "" & cStr(array(1)) & "" &  cStr(array(2)) & "" etc

But there is a limitation.

"If you type the items into the data validation dialog box (a delimited list), the limit is 256 characters, including the separators." - google

Is there a way to bypass this limitations or an alternative to have more items in my dropdown list?

Answer 1

There are limits to the number of items that will show in a data validation drop down list:

• The list can show up to show 32,767 items from a list on the worksheet.
• If you type the items into the data validation dialog box (a delimited list), the limit is 256 characters, including the separators.

If you type the items into the data validation dialog box (a delimited list), the limit is 256 characters, including the separators.

If you need more items than that, you could create a dependent drop down list, broken down by category. There is a sample file here: Dependent Drop Down from Sorted List

What I am understanding is that you need more elements... you can reach 32767 items, but if it is a manual introduction, then 256 characters. If you are taking the values from one cell, then you have the usual Excel limitation of 32767 characters.