VBA for Excel 2007 tutorial-2D arrays to print a series of numbers


VBA 2D arrays to print a series of numbers

In this example, We use VBA two-dimensional array to print a table of numbers as shown below:

1 2 3 4 5
6 7 8 9 10
11 12 13 14 15
16 17 18 19 20
21 22 23 24 25



The table will fill with a series of numbers starting from 1 to 25. In each row there are five numbers.

To the run the example program, you will need one Form and one CommandButton.



VBA for Excel: using VBA 2D array to print table of series numbers

VBA code for this example:

Option Explicit
Private Sub CommandButton1_Click()
 
Dim Table(4, 4) As Integer
    Dim row, col As Integer
    'fill values in the table
    row = 0
    While row <= 4
        col = 0
        While col <= 4
            If row = 0 Then
                Table(row, col) = col + 1 'fill the first row
            ElseIf (row > 0) And (col = 0) Then
                Table(row, col) = Table(row - 1, 4) + 1 'fetching the last cell of the previous row to be placed in the first cell of the current row
               
            Else
                Table(row, col) = Table(row, col - 1) + 1 //fill subsequent cells
            End If
            col = col + 1
        Wend
        row = row + 1
    Wend
   
    'display the matrix in excel sheet
    For row = 0 To 4
        For col = 0 To 4
            Cells(row + 1, col + 1) = Table(row, col)
        Next
    Next
 
   
End Sub


Comments

r17158 comment

 r17158

"Dim row, col As Integer"
row is defined as Variant. You may assign e.g. text to it.
Correct would be: Dim row As Integer, col As Integer


2017-05-08




This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages:
C, C++, C#, Java, VB.NET, Python, VBA,PHP & Mysql, SQL, JSP, ASP.NET,HTML, CSS, JQuery, JavaScript and other applications such as MS Excel, MS Access, and MS Word. However, we don't guarantee all things of the web are accurate. If you find any error, please report it then we will take actions to correct it as soon as possible.