VBA Associative Arrays (How to Index)

Question

I'm a newbie to VBA so please forgive my lack of experience.

Im using excel VBA and trying to figure out how to index an array. I'm importing a CSV and using the split function. I need to access each individual items of the items split into the array(s). The best way to explain what I need is an example like this from Actionscript:

var a:Array = [];
a[1] = "Hello";
a[2] = "World";
(Except that what I have is a dynamic array created by the SPLIT function)
Where I could access "Hello" with the var a[1]

Here is what I have so far:

Sub getTxtfile()
FilePath = Application.GetOpenFilename _
(Title:="Please choose a file to open", _
FileFilter:="CSV Files *.csv* (*.csv*),")
Open FilePath For Input As #1
row_number = 0
Do Until EOF(1)
    Line Input #1, LineFromFile
    LineItems = Split(LineFromFile, ",")
    'ActiveCell.Offset(row_number, 0).Value = LineItems(1)
    'ActiveCell.Offset(row_number, 1).Value = LineItems(0)
    row_number = row_number + 1
    'Debug.Print LineItems(0) & ": " & LineItems(1)
    Loop
Close #1
End Sub

I now have 2 arrays (LineItems(0) & LineItems(1)) but how do I index what is inside of them at this point?

Thanks for any and all help, it is greatly appreciated.

Mike

The CSV I'm using is formatted to use with other applications SolidWorks, python, etc.) besides Excel. I need to access only certain elements within the array to populate certain cells. As it is...I can pull the entire array into columns but I don't want to do that, just the ones I need. Here is a sample of the CSV:

0,.200 p,1.0709 q,1.167 r,1.177 s,1.216 t,1.570 u,1.5843 v,1.6883 w,1.9079 e,.2645

What I want to do is reference the letter in the first element and have the second element inserted in a certain cell: Reference "t" through an index and have "1.570" inserted.

The elements in my arrays are LineItems(0) and LineItems(1). So ideally I'm looking to reference each indexed item in an element as LineItems(1)(a) / LineItems(1-a) or something similar to that.

You already have an array with 2 items. To see what's inside you can do something like Debug.Print LineItems(0) for the first item or (1) for the second. You can iterate items by using a For Loop, like: `For i = LBound(LineItems) to UBound(LineItems)` — Ryan Wildry, Oct 03 '16 at 14:36
You don't have 2 arrays - you have *one* array with two *elements*. — Comintern, Oct 03 '16 at 14:36
There are no associative array(s) in VBA ... All indexes are numerical. — salih0vicX, Oct 03 '16 at 14:38
@salih0vicX dictionaries are associative arrays, although that isn't what Mike is talking about. (You could quibble that dictionaries are not part of VBA per se, but in practice they are used as a native data structure). — John Coleman, Oct 03 '16 at 14:43
Have you thought about using a dictionary? See the question here: http://stackoverflow.com/questions/17656580/create-dictionary-of-lists-in-vba — Preston, Oct 03 '16 at 14:43
looking at what you are doing, you can just use the array back to the worksheet. No need to index, just transpose? — Nathan_Sav, Oct 03 '16 at 14:45
You want a separate array for each line? You could declare `LineItems` to be a collection and then have `LineItems.Add Split(LineFromFile, ",")` — John Coleman, Oct 03 '16 at 14:48
Just noticed the edit. If the `1.570` is associated with the `t`, this isn't really what I'd call a csv file at all. Is the data in the edit a direct copy/paste? It actually looks like it might be *space* delimited. — Comintern, Oct 03 '16 at 22:00

cxw · Answer 1 · 2016-10-03T14:50:07.697

0

I think the commented-out lines in your code should actually work, at least as far as array access is concerned. (However, I may not fully understand what you are trying to accomplish. Would you please edit your question to clarify?) I do recommend adding

Option Explicit
Option Base 0

at the top of your file, and

Dim LineItems as Variant

before the Split call. That way the compiler will help you find errors.

However, If what you really want is to open a CSV, please allow me to suggest:

Dim wb as Workbook
Workbooks.OpenText Filename:="<filename>", DataType:=xlDelimited, Comma:=True
Set wb = Workbooks(Workbooks.Count)

which will give you a new workbook wb with the CSV parsed and ready to be accessed just like any other worksheet (docs on MSDN).

edited Oct 03 '16 at 14:50

answered Oct 03 '16 at 14:45

cxw

16,685
2
45
81

The CSV I'm using is formatted to use with other applications besides Excel. I need to access only certain elements within the array to populate certain cells. As it is...I can pull the entire array into columns but I don't want to do that, just the ones I need. Here is a sample of the CSV: – Mike Oct 03 '16 at 15:56
The CSV I'm using is formatted to use with other applications SolidWorks, python, etc.) besides Excel. I need to access only certain elements within the array to populate certain cells. As it is...I can pull the entire array into columns but I don't want to do that, just the ones I need. Here is a sample of the CSV: 0,.200 p,1.0709 q,1.167 r,1.177 s,1.216 t,1.570 u,1.5843 v,1.6883 w,1.9079 e,.2645 What I want to do is reference the letter in the first element and have the second element inserted in a certain cell: Reference "t" through an index and have "1.570" inserted. – Mike Oct 03 '16 at 16:04
@Mike thanks for the additional information! Would you please [edit your question](http://stackoverflow.com/posts/39834124/edit) to add the information there? That way it will be easier to read, and it will be easier for someone else to add another answer. **By the way**, welcome to the site! Check out the [tour](https://stackoverflow.com/tour) for more information on asking questions that will attract quality answers (and another badge!). – cxw Oct 03 '16 at 16:44

score 0 · Answer 2 · edited Sep 21 '17 at 21:52

You can have associative arrays in VBA with Scripting.Dictionary object or the .NET System.Collections.HashTable, but that seems a bit overkill.

You can use Jagged Arrays (Arrays of Arrays) like this:

Line = "0,.200 p,1.0709 q,1.167 r,1.177 s,1.216 t,1.570 u,1.5843 v,1.6883 w,1.9079 e,.2645"
LineItems = Split(Line, ",")

Dim LineSubItems()      ' has to be Variant or Variant() array
ReDim LineSubItems(0 To UBound(LineItems))

For i = 0 To UBound(LineItems)
    LineSubItems(i) = Split(LineItems(i), " ")
Next

Debug.Print LineSubItems(1)(1) ' "p"

VBA Associative Arrays (How to Index)

2 Answers2