CODEWITHSUNDEEP
arraysexcelmultidimensional-arrayexcel-2010vba
makerofthings7
makerofthings7

Reputation: 61463

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

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.

Upvotes: 2

Views: 3383

Answers (2)

Jeremy Thompson
Jeremy Thompson

Reputation: 65554

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

Upvotes: 2

chris neilsen
chris neilsen

Reputation: 53126

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

Upvotes: 2

Related Questions