One thing I often found frustrating with VBA is the lack of any easy way to see the values in a range object.
I often use Debug.Print when debugging my VBA and I'd love to just be able to Debug.Print a range like you can with other variables.
But because a range is an object, not a variable, you can't do that.
One way to see what's in a range is to step through your code (pressing F8) and when the range is set, you can check the values in the range in the Locals window.
Our range looks like this
How To Open The Locals Window
In the VBA editor go to the View menu and click on Locals Window.
Each time F8 is pressed one line of VBA is executed. When the range MyRange is set, you can see that values appear for it in the Locals window.
By examining the MyRange object you can drill down to see the values in it.
You can also set a Watch on the MyRange object to examine it in the Watch window, but this is essentially doing the same thing as examining it in the Locals window.
Using both Locals and Watch windows require you to step though code and examine values as the code executes.
If you want to just let the VBA run and see the values in the range printed out to the Immediate window, you need to write some code to do this.
How To Open The Immediate Window
In the VBA editor press CTRL+G, or go to the View menu and click on Immediate Window.
Print Values in Range One by One (Unformatted)
The code shown below will go through each cell in the range and print its value to the Immediate window.
However because we are printing one value at a time, it doesn't really give you a feel for the structure of the range. That is in this case, that there are 4 values per row/line.
Print Values in Range Row by Row (Formatted)
This next sub stores each value in a row to an array, and then prints out all of these values using the JOIN function to create a string with values separated by a comma.
More on VBA String Functions
This formatted output gives a better representation of the actual structure of your range.
In this format, you can also use the output to write data to a CSV file.
Download Sample Workbook
Enter your email address below to download the workbook containing all the code from this post.
All the code in this post can be downloaded in this workbook.
Re. Sub PrintRangeValues(), terminate Debug.Print with comma or semicolon to print all on one line:
In “zones” (separated by space characters):
Debug.Print MyCell.Value,
Separated by N space characters:
Debug.Print MyCell.Value; Spc(N);
Separated by comma:
Debug.Print MyCell.Value; “,”;
Great tips, thanks.
Great, for personal use I’ve changed “Debug.Print MyRow.Row, Join(Values, “,”)”
Thanks a lot, always interesting to learn from Gurus
Glad you found this useful 🙂