Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 12-25-2020, 12:35 PM
BobBridges's Avatar
BobBridges BobBridges is offline Loading an Excel range to an array in VBS Windows 7 64bit Loading an Excel range to an array in VBS Office 2010 32bit
Expert
Loading an Excel range to an array in VBS
 
Join Date: May 2013
Location: USA
Posts: 700
BobBridges has a spectacular aura aboutBobBridges has a spectacular aura about
Default 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?
Reply With Quote
  #2  
Old 12-25-2020, 01:04 PM
BobBridges's Avatar
BobBridges BobBridges is offline Loading an Excel range to an array in VBS Windows 7 64bit Loading an Excel range to an array in VBS Office 2010 32bit
Expert
Loading an Excel range to an array in VBS
 
Join Date: May 2013
Location: USA
Posts: 700
BobBridges has a spectacular aura aboutBobBridges has a spectacular aura about
Default

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)
This displays "1-28 x 1-2".
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
import excel in Array VBA bertelu PowerPoint 0 10-23-2019 09:16 AM
Loading an Excel range to an array in VBS Error: AGGREGATE_doesn't ignore hidden rows for particualr array range adisco Excel 1 01-10-2017 10:59 AM
Loading an Excel range to an array in VBS Convert String Array to Integer Array from a User Input? tinfanide Excel Programming 4 12-26-2012 08:56 PM
Loading an Excel range to an array in VBS 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

Other Forums: Access Forums

All times are GMT -7. The time now is 01:32 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft