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

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



"qpg" <(E-Mail Removed)> wrote in message
news:d90f0b89-e288-4560-9ea3-(E-Mail Removed)...
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

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

--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"qpg" wrote:

> 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
>

On Jul 30, 9:11*am, "Bernie Deitrick" <deitbe @ consumer dot org>
wrote:
> 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
>
> "qpg" <quadroprope...@gmail.com> wrote in message
>
> news:d90f0b89-e288-4560-9ea3-(E-Mail Removed)...
> 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.