Loading an Excel range to an array in VBS

BobBridges · #2 12-25-2020, 01:04 PM

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".

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