Definition and use of multi-dimensional variables

[Arthur Negus via OfficeKB.com]

I want to define and use multi-dimensional variables.

I can define variables of type variant (i.e. Dim field() as variant), and
then refine them by using: Redim field(1 to i); to build i variables (field(1)
, field(2) ... field(i)) in variant field.

What I want to do now is define the variables field(1), field(2) etc. to ...
field(i), such that each 'field(i)' variable contains ~4 sub-variables. i.e:

field -> field(1) -> (fldRange as Range
Name As String
Title As String
index As Integer, etc.

field(2) -> fldRange as Range
Name As String
Title As String
index As Integer,

etc; to ..................

field(i) -> fldRange as Range
Name As String
Title As String
index As Integer

Is this possible, and can anyone explain how I can do this?

 

[Sandy]

Try using a multi-dimensional array... a quick example would be:

Sub test()
Dim myarray(1 To 10, 1 To 3) As Variant
Dim i As Integer, x As Integer, j As Integer
For i = 1 To 10 Step 1
myarray(i, 1) = "My Name" & i
myarray(i, 2) = "My Title" & i
myarray(i, 3) = i
Next i
For x = 1 To 10 Step 1
For j = 1 To 3 Step 1
Debug.Print myarray(x, j)
Next j
Next x
End Sub

Try it out (make sure that the immediate window is visible)

HTH
Sandy

 

[Zack Barresse]

Hi there,

You can create your own Type variable, here is an example...



Option Explicit

Public Type Field
Range As Range
Name As String
Title As String
Index As Long
End Type

Sub TestMyType()

'Declare variables
Dim myField1 As Field, myField2 As Field, myField3 As Field, v

'Set first field variable
myField1.Index = 1
myField1.Name = "My First Variable Name"
myField1.Range = Range("A1")
myField1.Title = "VAR TITLE1"

'Set second field variable
myField2.Index = 2
myField2.Name = "My Second Variable Name"
myField2.Range = Range("A2")
myField2.Title = "VAR TITLE2"

'Set third field variable
myField3.Index = 3
myField3.Name = "My Third Variable Name"
myField3.Range = Range("A3")
myField3.Title = "VAR TITLE3"

End Sub



The other option could be to just make a two-dimensional array...


Dim arrField(1 to i, 1 to 4)


HTH

 

[Sandy]

Also, I'm not sure how high your field(i) is going to go but you might
want to look at Chip Pearson's website (link below) regarding Class
modules, it has a great introduction to them and has an
example/walk-through of what I think you are trying to accomplish (it
also gives a brief description of Type Variables that ZB posted about),
if nothing else, it's a great link for lot of excel/vba tricks and
tutorials...and advancing your knowledge of VBA!

http://www.cpearson.com/excel/ClassModules.htm
or
http://www.cpearson.com/excel.htm
if you're not interested in Class Modules


Sandy