33. VBA Tips - Output Array without looping.

Nick's picture

A common approach to outputting an array is to loop through each element of the array, and write each element to an Excel cell.

This methodology however is very inefficient. If you have a big array, you will really notice that performance is awful, especially if calculation is on automatic.

Here's a code sample that picks up the values of a range, and writes them back:

Sub ReadAndWriteBackArray()
 
    Dim myArray As Variant
 
    myArray = Range("A1:D4").Value
    Range("A1:D4").Value = myArray ' NOTE - NO LOOPING

End Sub

When you look at myArray in the watch window, you will notice how it is configured

- If you wish to replicate the functionality with arrays not taken from the sheet, you need to ensure that they are dimensioned the same way..

The equivalent code with looping is:

Sub ReadAndWriteBackArray()
 
    Dim myArray As Variant
    Dim myRow As Long
    Dim myCol As Long
 
    myArray = Range("A1:D4").Value
 
    For myCol = 1 To UBound(myArray, 2)
        For myRow = 1 To UBound(myArray, 1)
            Cells(myRow, myCol).Value = myArray(myRow, myCol)
        Next
    Next
 
End Sub