Create a VBA Array from Comma-Separated Values in Excel Without a Loop
Question details
Create a VBA array from comma-separated values without assigning each element in a For loop.

- Product
- Microsoft Excel VBA
- Device & OS
- not provided
- Scenario
- An Excel user wants to store values such as 12, 10, 5, 18, and 5 in a VBA array.
- Observed behavior
- The desired result is an array populated in a single assignment, with accessible elements and known index bounds.
Open the workbook in a desktop version of Excel and make sure you can access the Visual Basic Editor. Decide whether the array elements need to be numeric or whether text values are sufficient.
Use Split to Create an Array from a Text String
Use Split when the values are stored in a comma-separated string.
Split returns a zero-based array of strings. If the input contains spaces after commas, those spaces remain in the resulting elements.
In Excel, open the Developer tab and select Visual Basic. In the editor, choose Insert > Module.
Enter the following code in the module: Sub CreateArray() Dim Nbr As Variant Nbr = Split("12,10,5,18,5", ",") Debug.Print Nbr(0), Nbr(4) End Sub
Run CreateArray and open View > Immediate Window in the VBA editor. The printed values are 12 and 5; the array indexes run from 0 through 4.

Use VBA.Array to Create an Array of Numeric Values
Use VBA.Array when the values are already known and should be numeric rather than strings.
Work with Excel Spreadsheets in WPS Office
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and Microsoft Office format compatibility for an easier transition. Excel VBA macro support depends on the WPS edition and environment, so check compatibility before relying on an existing macro.

Frequently Asked Questions
Does VBA Split return numbers or text?
Split returns an array of strings. For example, Split("12,10", ",")(0) represents the text value "12"; use a conversion function such as CLng when you need a numeric value.
What indexes does the Split array use?
Split returns a zero-based array. For five comma-separated values, use indexes 0 through 4, or check the bounds with LBound(Nbr) and UBound(Nbr).
How do I create a numeric array without a loop?
For values known in advance, use Dim Nbr As Variant followed by Nbr = VBA.Array(12, 10, 5, 18, 5). The elements are numeric, and the array starts at index 0.
What happens if the string has spaces after the commas?
Split keeps those spaces in the resulting strings. For example, Split("12, 10", ",")(1) contains a leading space; use Trim on an element when you need to remove it.




