|
12-25-2020, 12:35 PM
|
 |
Expert
|
|
Join Date: May 2013
Location: USA
Posts: 700
|
|
Loading an Excel range to an array in VBS
When working with large amounts of data in Excel (say more than a few score cells) it's sometimes much faster to load the values from the worksheet to an array and then examine them there. If the range has thousands of cells, it's much faster. It works something like this:
Code:
' ows is the Worksheet object
Dim ar()
rZ = LastRow(ows) 'get the last used row
Redim ar(1 to rZ, 1 to 12) 'prep the array to hold the data
ar = Range(ows.Cells(1, 1), ows.Cells(rZ, 12)).Value
At that point I can work with the data in the array in milliseconds rather than seconds or even minutes.
Now I'm trying to do the same thing in VBScript. The code is similar:
Code:
' oxl is the Excel.Application object
' ows is the Worksheet.Application object
Dim ar()
rZ = LastRow(ows) 'get the last used row
Redim ar(rZ, 12) 'lower bound on VBS arrays is always zero
ar = oxl.Range(ows.Cells(1, 1), ows.Cells(rZ, 12)).Value
When I run this code, VBScript complains of a "type mismatch" starting in the first character of the last line. Does anyone know what has to be done differently in VBS?
|