#1
|
||||
|
||||
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 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 |
#2
|
||||
|
||||
Never mind; I tried a number of different combinations before posting, but stumbled across the correct one only afterward. The secret in VBS is not to declare the array. In VBScript, arrays have zero as the bottom boundary in all dimensions; apparently it then complains when I try to load ranges from Excel into such an array.
But when I don't declare ar at all, it works correctly, and furthermore the LBOUND in both dimensions is 1, not 0: Code:
' owb is the workbook set owsSet=owb.worksheets("Settings") rZ=LastRow(owsSet,1,1) ar=oxl.range(owsSet.cells(2,1),owsSet.cells(rz,2)).Value msgbox lbound(ar,1) & "-" &ubound(ar,1) & " x " & lbound(ar,2) & "-" & ubound(ar,2) |
|
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 |