Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to print two dimensional array in Immediate window in VBA?

Tags:

How to print two dimensional array in Immediate window in VBA ? Does it exist any generic method for doing this ? Some method for ploting one row of array per line in Immediate window could solve this problem, because then only thing to do is to loop this code for each line of array.

like image 266
Qbik Avatar asked Jan 11 '13 09:01

Qbik


People also ask

How do I create a two dimensional array in Excel VBA?

To run my example you will need to fill columns A and B in Sheet1 with some values. Then run test(). It will read first two rows and add the values to the BigArr. Then it will check how many rows of data you have and read them all, from the place it has stopped reading, i.e., 3rd row.

How do I get the immediate window in VBA?

From the View menu, choose Immediate window (CTRL+G).

How do you print a multi-dimensional array?

To print a multi-dimensional array, you instead need to call the Arrays. deepToString() method and pass the multi-dimensional array as an argument.


2 Answers

If this is for debugging purposes, it's not convenient to write a macro to view the contents of the array during program execution. Might even cause problems.

For debugging during program execution, you'll want a code-free method you can use across all VB projects, to spy the contents of your array.

  1. In VBA IDE, click View menu > Locals Window
  2. In Local pane, find the array-name. enter image description here
  3. Expand the nodes to find your values. The nodes will differ, depending on the type of array.

In this example, "aLookList" is a variant array. The values under "Value2" come from a range.

enter image description here

enter image description here

Another answer here suggests using the Watch pane. This is similar to my answer, but poster did not explain that you can spy the entire array (all cells) by simply adding the array name to Watch. Then , drill down nodes. The advantage of the Locals Window over the Watch Window, is that with Locals pane, you do not have to manually add the array to the pane, it's already there. So it's a bit less effort.

like image 164
johny why Avatar answered Oct 18 '22 15:10

johny why


I made a simple loop to do this for anybody's reference:

Sub WriteArrayToImmediateWindow(arrSubA As Variant)  Dim rowString As String Dim iSubA As Long Dim jSubA As Long  rowString = ""  Debug.Print Debug.Print Debug.Print "The array is: " For iSubA = 1 To UBound(arrSubA, 1)     rowString = arrSubA(iSubA, 1)     For jSubA = 2 To UBound(arrSubA, 2)         rowString = rowString & "," & arrSubA(iSubA, jSubA)     Next jSubA     Debug.Print rowString Next iSubA  End Sub 
like image 25
user3706920 Avatar answered Oct 18 '22 15:10

user3706920