VBA - Function for Creating an n by m Matrix

Question

I am currently working with arrays and loops, and am trying to write a function that will output an n by m array (a matrix) with the numbers {1, 2, 3, ... , n*m}

I am trying to learn some basic VBA code, this is purely for educational purposes.

This is what I have come up with:

Function createMatrix(n, m)    
Dim matrix(1 To n, 1 To m) As Integer    
x = 1    
For i = 1 To n
    For j = 1 To m        
        matrix(i, j) = x            
        x = (x + 1)            
    Next j
Next i   
createMatrix = matrix    
End Function

It returns #VALUE. I cannot understand why.

I got it to work at one point (creating a 3x3 matrix) by making it a function that did not take any variables and then initializing the matrix array by

Dim matrix(1 to 3, 1 to 3) As Integer

replacing n and m in the for loops with 3s.

So I guess the variables n and m are causing the problems, but don't know why.

Solution 1

Array declarations must be static (where the bounds are defined by a hardcoded value); however you can resize them dynamically using the ReDim statement.

' Declare an array.
' If you want to size it based on variables, do NOT define bounds.
Dim matrix() As Integer
' Resize dynamically.
ReDim maxtrix(n, m)

Note that when you ReDim, all values will be lost. If you had values in matrix that you wanted to keep, you can add the Preserve keyword:

ReDim Preserve matrix(n, m) ' Keep any existing values in their respective indexes.

Solution 2

You first need to declare array as dynamic array and then redim it to your dimension.

Function createMatrix(n, m)
   Dim matrix() As Integer
   ReDim matrix(1 To n, 1 To m) As Integer
   x = 1

    For i = 1 To n
       For j = 1 To m
            matrix(i, j) = x
            x = (x + 1)
        Next j
    Next i

    createMatrix = matrix
End Function