How to create data matrix

I want to create a data matrix with following known parameters:
n: count of columns
min_int(n): min value for each column
max_int(n): max values for each clumn

for example:
n=3
min_int(1,2,3)=(1,1,1)
max-int(1,2,3)=(3,2,4)

I want to got the matrix as below:
col(1)    col(2)   col(3)
-------------------------
1          1         1
1          1         2
1          1         3
1          1         4
1          2         1
1          2         2
...
...
...
3          2         3
3          2         4
----------------------

Can anyone give me sample code?
LVL 1
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

progCommented:
You need to us a 2dimensional array

for a 4 x 5 matrix example

dim matrix(4,5)
matrix(1,1) = 1
matrix(1,2) = 4
...
...

etc.

process the matrix using  for next loops.

If you're learning VB then I'd strongly recommend you to think about the problem yourself and try to get somewhere with it, then show us your code if you get stuck.

Good Luck!

0
Commented:
It's easy, if the number of columns is a constant value

Private Type mRow
ColValue(1 to 3) As Integer
End Type

Dim mRows() As mRow

Sub InitMatrix(min1,max1,min2,max2,min3,max3)

Dim rowsNeeded As Long
Dim actRow As Long
Dim i As Long
Dim j As Long
Dim k As Long

rowsNeeded = (max1-min1+1) * (max2-min2+1) * (max3-min3+1)

Redim mRows(1 to rowsNeeded)
actRow = 1

For i = min1 To max1
For j = min2 to max2
For  k = min3 to max3
mRows(actRow).ColValue(1) = i
mRows(actRow).ColValue(2) = j
mRows(actRow).ColValue(3) = k
actRow = actRow + 1
Next k
Next j
Next i

End Sub

Let me know if you need an example with variable number of columns, meaning that matrix parameters are set at runtime and may differ each time
0
Author Commented:
Yes I want to know the answer with variable number of columns. That is the question.
0
Commented:
If you don't need maximum performance just use an MsFlexGrid to store your data.

Here is a piece of code that accepts variable info

Option Explicit

Private Type mRow
ColValue() As Integer
End Type

Private Type Minmax
minVal As Integer
maxVal As Integer
End Type

Private Type fPara
n As Long
mm() As Minmax
End Type

Dim iPara As fPara
Dim mRows() As mRow

Sub Start()

Dim n As Long

n = 3

ReDim iPara.mm(1 To n)

iPara.n = n
iPara.mm(1).minVal = 1
iPara.mm(1).maxVal = 3
iPara.mm(2).minVal = 1
iPara.mm(2).maxVal = 2
iPara.mm(3).minVal = 1
iPara.mm(3).maxVal = 4

InitMatrix iPara
PrintMatrix
End Sub
Private Sub InitMatrix(para As fPara)

Dim rowsNeeded As Long
Dim actRow As Long
Dim i As Long
Dim j As Long
Dim k As Long
Dim iVal As Long
Dim iCol As Long
Dim iRow As Long
Dim vRepeatCount As Long
Dim numColValues As Long
Dim nRepeats As Long
Dim minColVal As Long
Dim maxColVal As Long
Dim iRep As Long

rowsNeeded = 1
For i = 1 To para.n
iVal = (para.mm(i).maxVal - para.mm(i).minVal + 1)
rowsNeeded = rowsNeeded * iVal
Next i

ReDim mRows(1 To rowsNeeded)
For i = LBound(mRows) To UBound(mRows)
ReDim mRows(i).ColValue(1 To para.n)
Next i

vRepeatCount = 1

For iCol = para.n To 1 Step -1

maxColVal = para.mm(iCol).maxVal
minColVal = para.mm(iCol).minVal
numColValues = (maxColVal - minColVal + 1)
nRepeats = rowsNeeded / vRepeatCount / numColValues

actRow = 1
For iRep = 1 To nRepeats
For j = minColVal To maxColVal
For k = 1 To vRepeatCount
mRows(actRow).ColValue(iCol) = j
actRow = actRow + 1
Next k
Next j
Next iRep

vRepeatCount = vRepeatCount * numColValues
Next iCol

End Sub

Private Sub PrintMatrix()

Dim i As Long
Dim j As Long
Dim mLine As String

For i = 1 To UBound(mRows)
mLine = vbNullString
For j = 1 To UBound(mRows(i).ColValue)
mLine = mLine & mRows(i).ColValue(j) & "  "
Next j
Debug.Print mLine
Next i

End Sub
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
Sorry to bother you again.

above code you provide is enough to create the data matrix, but one problem is when n become pretty big, like 50 or above, the row count will reach the limitation of LONG data type. for example, n=50, all columns cycle between 1 to 5, the total row number will be 5^50= 8.881784197001e+34. It's terrible.

So I think we need a new solution. My idea is that not store all rows in data array, just get 1 row, use it for some function, just throw it away. then next row. Would you pls help me again on it?

Many thank and happy new year!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.