That fact alone makes it at least 10x faster and probably 100 – 1000x faster. But the more common use of VBA is taking a manual process and automating it. When your job involves delivering a polished end product, and speed is absolutely a component of that, I agree with Jon completely. I am sadly among the latter group, and the difference is pretty significant. people who make their living easier using VBA. The funny thing about this thread is that you’ve got people who make their living writing VBA vs. To work around this problem, populate each cell in your worksheet one at a time from the array, instead of populating the whole range at one time.ġ66342 ( ) Description of the limitations for working with arrays in Excel In Excel 2003 and in earlier versions of Excel, the VBA array is longer than 1,823 characters in length. In Excel 2007, the VBA array is longer than 8,203 characters in length.

This problem may occur when one of the following conditions is true:

This issue may occur if one or more of the cells in an array (range of cells) contain a character string that is set to contain more than 911 characters.ĭata may be truncated when you transfer array data to cells in an Excel worksheet You may receive a “Run-time error 1004? error message when you programmatically set a large array string to a range in Excel 2003 Just to point to a limitation of the array method: no element can have more than 911 characters, if it has the write stops at that point.

Api vba range.cells()