data structure help

[qpg]

I have some subroutines that take apart a selected matrix into x, y,
and value sets (each “record” representing an intersection on the
matrix). I am now trying to figure out the right data structure to put
it into so that I can pass it to another sub for more processing and
eventually to place back into excel as 3 columns

Is this a job for a 3 dimensional array? Or for a one dimensional
array of arrays each internal array holding one “record”? Or is there
a better way to do this? The data would only be accessed as records

I’m looking for examples how to both assign and access the 3
dimensional array. I’m only an occasional programmer and seem to be
having some problems with the 3D idea and not sure how to implement
others.

This seems to assign a 3D array:
outdata(i, j, x) = Array(Range(left)(i + 1, 1), Range(top)(1, j +
1), d.Value)

but then when I try to access it I get a type mismatch error
Debug.Print outdata(1, 1, 1)

Any help or pointing in the right direction is appreciated.

Thanks

 

[Bernie Deitrick]

qpg,

You don't need a 3D array: a 2D will work fine. Enter some values into A1:C10, then try the two
example subs below.

HTH,
Bernie
MS Excel MVP

Sub TryNow()
Dim i As Integer
Dim j As Integer
Dim OutData(1 To 10, 1 To 3) As Variant

For i = 1 To 10
For j = 1 To 3
OutData(i, j) = Cells(i, j).Value
Next j
Next i

For i = 1 To 10
For j = 1 To 3
MsgBox OutData(i, j)
Next j
Next i
End Sub


Sub TryNow2()
Dim i As Integer
Dim j As Integer

Dim OutData As Variant

Set OutData = Range("A1:C10")

For i = 1 To 10
For j = 1 To 3
MsgBox OutData(i, j)
Next j
Next i

End Sub



I have some subroutines that take apart a selected matrix into x, y,
and value sets (each “record” representing an intersection on the
matrix). I am now trying to figure out the right data structure to put
it into so that I can pass it to another sub for more processing and
eventually to place back into excel as 3 columns

Is this a job for a 3 dimensional array? Or for a one dimensional
array of arrays each internal array holding one “record”? Or is there
a better way to do this? The data would only be accessed as records

I’m looking for examples how to both assign and access the 3
dimensional array. I’m only an occasional programmer and seem to be
having some problems with the 3D idea and not sure how to implement
others.

This seems to assign a 3D array:
outdata(i, j, x) = Array(Range(left)(i + 1, 1), Range(top)(1, j +
1), d.Value)

but then when I try to access it I get a type mismatch error
Debug.Print outdata(1, 1, 1)

Any help or pointing in the right direction is appreciated.

Thanks

 

[John Bundy]

You just need a 2d array.
dim myArray(10000,3) '10000 is just a guess at how many rows, adjust as needed
myIndex=1
do or for loop
myArray(myIndex,1)=data1
myArray(myIndex,2)=data2
myArray(myIndex,3)=data3
myIndex=myIndex+1
loop or next

then to output you just reverse it like
myIndex=1
sheet1.Cells(1,1)=myArray(myIndex,1)
etc etc
loop
etc

 

[qpg]

qpg,

You don't need a 3D array: a 2D will work fine.  Enter some values intoA1:C10, then try the two
example subs below.

HTH,
Bernie
MS Excel MVP

Sub TryNow()
Dim i As Integer
Dim j As Integer
Dim OutData(1 To 10, 1 To 3) As Variant

For i = 1 To 10
   For j = 1 To 3
      OutData(i, j) = Cells(i, j).Value
   Next j
Next i

For i = 1 To 10
   For j = 1 To 3
      MsgBox OutData(i, j)
   Next j
Next i
End Sub

Sub TryNow2()
Dim i As Integer
Dim j As Integer

Dim OutData As Variant

Set OutData = Range("A1:C10")

For i = 1 To 10
   For j = 1 To 3
      MsgBox OutData(i, j)
   Next j
Next i

End Sub


I have some subroutines that take apart a selected matrix into x, y,
and value sets (each “record” representing an intersection on the
matrix). I am now trying to figure out the right data structure to put
it into so that I can pass it to another sub for more processing and
eventually to place back into excel as 3 columns

Is this a job for a 3 dimensional array? Or for a one dimensional
array of arrays each internal array holding one “record”? Or is there
a better way to do this? The data would only be accessed as records

I’m looking for examples how to both assign and access the 3
dimensional array. I’m only an occasional programmer and seem to be
having some problems with the 3D idea and not sure how to implement
others.

This seems to assign a 3D array:
     outdata(i, j, x) = Array(Range(left)(i + 1, 1), Range(top)(1, j +
1), d.Value)

but then when I try to access it I get a type mismatch error
Debug.Print outdata(1, 1, 1)

Any help or pointing in the right direction is appreciated.

Thanks

Of course. Thank you very much. I was mixed up.