VBA: String indices in multi-dimensional arrays

Question

From what I've read, it doesn't look possible to use string indices in arrays. However, the topic seems to summarize what I am trying to do.

I have a table showing student and course information in Excel, and from that table I need to create something like

x(StudentID, CourseID) (equals 1 if student is taking the course, 0 otherwise)

I've been Googling for two days now but I'm lost. I've come across with collections and dictionaries but I don't know which one is more suitable or if they are suitable at all. What would you recommend?

Thanks in advance.

http://stackoverflow.com/questions/915317/does-vba-have-dictionary-structure — Mitch Wheat, Apr 14 '13 at 13:50
Thanks for your comments. Though I couldn't figure out how to use multiple keys for a dictionary. Should I use multiple dictionaries? — stgath, Apr 14 '13 at 15:03

score 0 · Answer 1 · answered May 05 '13 at 11:59

I agree with the other comments but believe there cam only be one unique key in a dictionary and as such suggest you fashion one by using the student and course.

Dim s
Set s = CreateObject("Scripting.Dictionary")
s.Add "student a-course a", "1"
s.Add "student b-course b", "1"
s.Add "student c-course c", "1"

If s.Exists("student c-course c") Then
    MsgBox s("student c-course c") 'displays 1
End If

If s.Exists("student c-course a") Then
    a.add "student c-course a", "1"
End If

Two points; the value of 1 is pointless as the s.exists(...) method exists, and as the dictionaries are used you can create a loop to add all your student and course information.

VBA: String indices in multi-dimensional arrays

1 Answers1