Extracting Data from Excel Sheet to VBA: Empty Variant/Array, but UBound Returns Number

Question

I am trying to extract text data from an Excel sheet into an array (defined as a variant in this case).

The below code does not return desirable results: When I try to access an element in the SearchItems variant array, an error pops up saying subscript out of range.

However when I run UBound(SearchItems) the system returns the value of LR (instead of LR-1?).

In any case does that indicate that data if already loaded onto the array?

Sub Testing()

Dim SearchItems As Variant
Dim LR As Integer

LR = Sheets("MySheet").Cells(Rows.Count, "A").End(xlUp).Row 'Get number of cells in column A

SearchItems = Sheets("MySheet").Range("A1:A" & LR).Value

End Sub

you actually have 2D-array, this answer may help you: http://stackoverflow.com/questions/18481330/2-dimensional-array-from-range/18481730#18481730 — Dmitry Pavliv, Mar 26 '14 at 18:30

score 2 · Accepted Answer · answered Mar 26 '14 at 18:32

2

You are dealing with a two dimensional array:

Sub Testing()
    Dim SearchItems As Variant
    Dim LR As Integer, i As Integer
    LR = Sheets("MySheet").Cells(Rows.Count, "A").End(xlUp).Row 'Get number of cells in column A
    SearchItems = Sheets("MySheet").Range("A1:A" & LR).Value
    For i = 1 To LR
        MsgBox SearchItems(i, 1)
    Next i
End Sub

answered Mar 26 '14 at 18:32

Gary's Student

95,722
10
59
99

Thank you, I am used to MATLAB, did not realize it would default to a 2D. – msmf14 Mar 26 '14 at 18:35
The only time its one dimensional is if its a single cell. – Gary's Student Mar 26 '14 at 18:37

score 0 · Answer 2 · answered Mar 27 '14 at 12:07

the array searchitems starts from 0, so of course ubound will add +1 to the size u think it has.

If you need Ubound to work (as the tittle of the post suggests) :

Sub Testing()
Dim SearchItems() As Variant 'we want SeachItems to be a dynamic array
Dim LR As Long, i As Long

with Sheets("MySheet")
    LR = .Cells(.Rows.Count, 1).End(xlUp).Row 'an other way of Getting the number of cells in column A, note the '.' before rows
    redim SearchItems ( 1 to LR, 1 to 1) ' this way ubound should work
    SearchItems = .Range(.cells(1,1) , .cells(LR,1) ).Value 'an other way of doing it (strangely faster considering its bigger code, tested it)
end with

For i = 1 To LR 'or to Ubound (SearchItems)
    'do stuff with  SearchItems(i, 1) 
Next i


'to write it back to the worksheet :
Sheets("MySheet").Range("A1:A" & LR).Value = SearchItems

End Sub

Extracting Data from Excel Sheet to VBA: Empty Variant/Array, but UBound Returns Number

2 Answers2