0

I am trying to write VBA code where I use the dim command on an unknown number of matrices. The number of these matrices is an input. That is...

Dim mat1() as variant, mat2() as variant, mat3() as variant...

But it could end at mat2 or go to mat300 depending on what the user inputs (actually it is the number of columns in the input matrix).

So, for instance. If I am passed

8 2

12 7

I need to pass two matrices along.

8

12

and

2

7

It seems that there must be a way to do this.

thanks

njbadger
  • 1
  • 2
  • 2
    This sounds exactly like what a 2D array would do for you – Taelsin Jan 21 '16 at 22:36
  • Then use a 2 dimensional array. The you would only need one; `mat1 = Sheets("Sheet").Range("A1:AZ3000")` – Scott Craner Jan 21 '16 at 22:36
  • @njbadger, are you asking how to pass undetermined number of parameters into a function/sub? If yes use ParramArray. http://stackoverflow.com/questions/20783170/pass-array-to-paramarray – Jules Jan 21 '16 at 22:55
  • I don't think these answers are quite what i'm looking for. Let me try again. I am passed an MxN matrix; i know neither M or N beforehand. Because of our upload protocol, i must change the MxN matrix into N M x 1 matrices. I must then pass the N matrices to another program. I understand the process except for how to dim() the N matrices when I don't know N. – njbadger Jan 22 '16 at 00:10
  • This may work `M = UBound(MxN_Matrix)` and `N = UBound(Transpose(MxN_Matrix))`. This is Excel VBA we are dealing with? And please Edit the post instead of using comment to clarify. – PatricK Jan 22 '16 at 01:47
  • Thanks PatricK. Didn't mean to break protocol, and hope I am not now. Yes, we are talking about Excel VBA. I know how to find M and N. I don't know how to turn that knowledge into N matrices. I can create 1 matrix or 10 matrices, but I don't know how to do N matrices. – njbadger Jan 22 '16 at 02:18
  • So basically, you want to transform MxN array to NxM array? EDIT: and you want the array as array(n)(m) instead of array(n,m)? – Jules Jan 22 '16 at 02:26
  • No. I need to turn an MxN array into N column matrices (Mx1). I can only upload data one column at a time. I don't know how to use Dim() for a variable amount (N) of matrices. – njbadger Jan 22 '16 at 02:37
  • Not sure how you want the output is. Perhaps you can use .NET's System Collections ArrayList https://msdn.microsoft.com/en-us/library/system.collections.arraylist.aspx ? Dump the transpose of the input matrix to a temp worksheet, then add each row's value to the ArrayList? – PatricK Jan 22 '16 at 02:46
  • So you want the array(m,n) to be array(n)array(m)? for example the value of array(1,2) is the same value as array(2)(1)? – Jules Jan 22 '16 at 02:48
  • I tried to clarify in the original question. – njbadger Jan 22 '16 at 03:01
  • Still unclear 8 12 is array(8,12) and 12 7 is array(12,7)? – Jules Jan 22 '16 at 03:02
  • No. Sorry, I am really mishandling this. I am originally passed a 2x2 matrix. I need to turn that into two 2X1 matrices and pass them along separately to the next program. Except, I don't know the number of columns. – njbadger Jan 22 '16 at 03:06
  • OK :). so you want array(2,2) to be array(2)(2). – Jules Jan 22 '16 at 03:08
  • I am not familiar with your notation, so I can't answer. – njbadger Jan 22 '16 at 03:10

1 Answers1

1

Assuming you want array(M,N) to be transformed in to Array(N)Array(M).

Dim a(4, 5) As Variant '4,5 can be any integer value 
Dim i, j
Dim r() As Variant

For i = 0 To UBound(a, 1)
    For j = 0 To UBound(a, 2)
        a(i, j) = CStr(i) & CStr(j)
    Next
Next

r = TransformArray(a)

Debug.Print "Initial"
For i = 0 To UBound(a, 1)
    For j = 0 To UBound(a, 2) 
        Debug.Print "(" & i & "," & j & ")", a(i, j) '<- array(x,y)
    Next
Next

Debug.Print "Transformed"
For i = 0 To UBound(r)
    For j = 0 To UBound(r(i))
        Debug.Print "(" & i & ")(" & j & ")", r(i)(j) '<- array(y)(x)
    Next
Next

Public Function TransformArray(a() As Variant) As Variant()
  Dim r() As Variant
  Dim s() As Variant

  Dim i, j
  ReDim r(UBound(a, 2))
  ReDim s(UBound(a, 1))

  For i = 0 To UBound(r)
    For j = 0 To UBound(s)
        s(j) = a(j, i)
    Next
    r(i) = s
  Next
  TransformArray = r
End Function
Jules
  • 1,423
  • 13
  • 22
  • Yes. Thank you. The bottom third of your code is what I was missing. – njbadger Jan 22 '16 at 03:16
  • @njbadger, please press `Accept` for the answer since it worked for you. – Smandoli Jan 22 '16 at 04:01
  • @Smandoli, I noticed that vba tag posters rarely give up vote and accept. From 8 of my answers, none get an acceptance even though I think they are correct (but I could be mistaken) :) – Jules Jan 22 '16 at 11:03
  • vba tag posters are a discourteous, selfish bunch. However your answers are improving their lives and I expect there will be a reward some day. Hang in there! – Smandoli Jan 22 '16 at 13:53