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
KingSunAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
richtsteigConnect With a Mentor 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
using your example

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
 
deightonCommented:
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
 
richtsteigCommented:
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
 
KingSunAuthor Commented:
Yes I want to know the answer with variable number of columns. That is the question.
0
 
KingSunAuthor 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
All Courses

From novice to tech pro — start learning today.