excel VBA (not VBScript) 101: How do I create and read a multidimensional array?

Question

I'm using Excel 2010 and I have a macro that needs to load OData results into an in-memory array. The only part I'm missing is how to actually create that array in Excel.

How do I create a multi-dimensional array (or an array of a class) in Excel 2010?

The only constraint I have is that anything I build must be self contained in the XLSX. So that means dependencies on PowerPivot, addins, etc, are out. I think that leaves me with just VBScript macros.

I've searched MSDN, StackOverflow, and Google for hours and can't find a clear-cut example of how to do this.

To be clear, the macro language in Excel is VBA. VBScript is a much more limited language than VBA, so your answers would be very different asking about one versus the other. — Cheran Shunmugavel
– Cheran Shunmugavel, Commented Sep 19, 2011 at 6:06

chris neilsen · Accepted Answer · 2011-09-19 05:38:12Z

2

As an extension to @Jeremy 's answer:

you CAN redim multi dimensional arrays.

DIM arr() as Variant 'or object or class or whatever.  
Redim arr(1 To 1, 1 To 1) 'works 
Redim arr(1 To 3, 1 To 3) 'works, but deletes any existing data
Redim Preserve arr(1 To 3, 1 To 10) 'works and keeps any data
Redim Preserve arr(1 To 10, 1 To 10) 'fails, you can only redm preserve the last dimension

Whether Arrays, Collections (or Dictionaries for that matter) are best for your app depends on the details of your application

answered Sep 19, 2011 at 5:38

chris neilsen

53.3k10 gold badges91 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jeremy Thompson · Accepted Answer · 2012-10-24 21:43:06Z

2

Open Excel

Press Alt + F11

Right click on VBAProject > Insert > Class

Specify the Name for the VBA class "Person" in the left hand pane, properties dialog

Give the Person class a property eg firstname

Public FirstName As String

Create a second class or module file and here is how to create/access an array of the People class:

Public colOfPeople As New Collection

Public Function MakePeople() As String

Dim clsP As New clsPerson
clsP.FirstName = "Jeremy"

colOfPeople.Add (clsP)

End Function

Solution 1: To make this multi-dimensional, I've made the collection an array:

Public multiColOfPeople() As New Collection

Public Function MakeMultiPeople() As String

ReDim Preserve colOfPeople(1) 'dimension multi-array collection
Dim clsP As New clsPerson
clsP.FirstName = "Jeremy"
colOfPeople(0).Add (clsP)

Dim clsP1 As New clsPerson
clsP1.FirstName = "Lisa"
colOfPeople(1).Add (clsP1)

End Function

Solution 2: Using a multi-dimensional array (no collection)

Public multiArray(3, 3) As New clsPerson

Dim clsP As New clsPerson
'store
multiArray(0, 1) = clsP
'retrieve
clsP = multiArray(0, 1)

Edit *

To use the second solution, see chris neilsen's answer for info on ReDim'ing multidimensional arrays

edited Oct 24, 2012 at 21:43

answered Sep 19, 2011 at 3:43

Jeremy Thompson

66.3k38 gold badges225 silver badges344 bronze badges

7 Comments

makerofthings7 Over a year ago

I'm unable to rename the left hand class in excel. Right clicking, saving, and clicking once on the name and waiting produce no results. Not to mention I am a little unsure what to name them. I'm guessing clsPerson?

makerofthings7 Over a year ago

aaah... it's in the properties dialog box. I'm able to rename my class only in that screen. I'll see if I can enumerate and create my array.

makerofthings7 Over a year ago

Which solution above supports dynamic growth? My proof of concept code uses ReDim Preserve myArray(UBound(myArray) + 1)

makerofthings7 Over a year ago

Doesn't work for me... assiging a string or a variable returns: Function call on left-hand side of assignment must return Variant or Object. Then I changed the property from String to object and got this error: Object variable or With block variable not set. Perhaps I need to do something different with clsPerson with regard to Excel 2010?

Jeremy Thompson Over a year ago

@makerofthings7 - please see edit, sorry for leading you up the garden path

|

Collectives™ on Stack Overflow

excel VBA (not VBScript) 101: How do I create and read a multidimensional array?

2 Answers 2

Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related