VBA & Macro Problems

Create a VBA Array from Comma-Separated Values in Excel Without a Loop

Khadija KhanKhadija Khan Nov 14, 2026 868 views

Question details

Create a VBA array from comma-separated values without assigning each element in a For loop.

Create a VBA Array from Comma-Separated Values in Excel Without a 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.
Before you start

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.

Solution 1Recommended

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.

1
Open the VBA editor

In Excel, open the Developer tab and select Visual Basic. In the editor, choose Insert > Module.

2
Assign the array

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

3
Check the values

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 Split to Create an Array from a Text String
The elements are strings: Split produces text elements, even when they look like numbers. Convert individual values with a function such as CLng(Nbr(0)) when a numeric value is needed.
Free Microsoft Office alternative

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.

Open and edit spreadsheets in a familiar interfaceWork with Microsoft Excel formats such as XLSX and XLSMReview and organize spreadsheet data alongside macro-enabled workbooksUse a free, lightweight Office alternative
microsoft office alternative - wps office

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.