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
- 69962 reads
Recent comments
5 years 34 weeks ago
6 years 20 weeks ago
6 years 32 weeks ago
6 years 35 weeks ago
6 years 36 weeks ago
6 years 42 weeks ago
6 years 50 weeks ago
6 years 50 weeks ago
6 years 50 weeks ago
6 years 50 weeks ago