Loading an Excel range to an array in VBS

BobBridges · #1 12-25-2020, 12:35 PM

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?

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
import excel in Array VBA	bertelu	PowerPoint	0	10-23-2019 09:16 AM
Error: AGGREGATE_doesn't ignore hidden rows for particualr array range	adisco	Excel	1	01-10-2017 10:59 AM
Convert String Array to Integer Array from a User Input?	tinfanide	Excel Programming	4	12-26-2012 08:56 PM
Word 2010 - Style Sets not fully loading / loading properly	emilypage	Word	3	11-09-2012 01:11 AM
Excel Error Loading Custom UI XML	sumdumgai	Office	1	05-10-2010 06:59 PM