33. VBA Tips - Output Array without looping.
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
- Nick's blog
- Login or register to post comments
- 69982 reads
Recent comments
5 years 36 weeks ago
6 years 22 weeks ago
6 years 34 weeks ago
6 years 37 weeks ago
6 years 38 weeks ago
6 years 43 weeks ago
6 years 52 weeks ago
7 years 2 days ago
7 years 3 days ago
7 years 3 days ago