CODEWITHSUNDEEP
vbaexceloffice-2007
Kevin Boyd
Kevin Boyd

Reputation: 12379

Array from Range in Excel VBA

Well I've been struggling with the little bit of code and can't seem to get around it ... I'm trying to get an array from a range of cells, the array however is showing up to be 1 element wide.
Well here's the code: 

Dim item As Variant
MsgBox Range("D19:H19").Count    
item = Range("D19:H19").Value
MsgBox LBound(item) & " " & UBound(item)

as per my understanding item should contain a 2D array... however I'm getting the following result 1st MsgBox prints 5 2nd MsgBox prints 1 1

What's going wrong?

Upvotes: 7

Views: 70536

Answers (5)

imfrancisd
imfrancisd

Reputation: 21

The problem is in LBound and UBound

jtolle was correct about the LBound and UBound.

LBound(item, 2)

UBound(item, 2)

However, item must not be dimmed as an array (you'll get an error).

I think this is what you want

Dim item As Variant
MsgBox Range("D19:H19").Count
item = Range("D19:H19").Value

MsgBox LBound(item, 2) & " " & UBound(item, 2)

For i = LBound(item, 2) To UBound(item, 2)
  MsgBox item(1, i)
Next

Upvotes: 11

JimmyPena
JimmyPena

Reputation: 8754

Try this:

Dim item As Variant
MsgBox Range("D19:H19").Count
item = Application.Transpose(Range("D19:H19").Value)
MsgBox LBound(item) & " " & UBound(item)

Upvotes: 1

Nick
Nick

Reputation: 3643

if you want a 1D array, to join it for an IN clause, for example, you should transpose your range. I've found you have to transpose twice for a row, once for a column of data like this:

Dim rngRow As Range, rngColumn As Range

Set rngRow = Sheets(1).Range("A1", "Z1")
Set rngColumn = Sheets(1).Range("A1", "A20")

Dim arrRowValues, arrColValues
arrRowValues = WorksheetFunction.Transpose(WorksheetFunction.Transpose(rngRow))
arrColValues = WorksheetFunction.Transpose(rngColumn)

Dim numList As String, stringList As String
numList = Join(arrRowValues, ",")
stringList = "'" & Join(arrColValues, "','") & "'"

worth a play.

Upvotes: 0

jtolle
jtolle

Reputation: 7113

Your item should contain a 2-D array as expected. If you stick a breakpoint in your code and look at the little "Locals" window in the VBA editor, you should see that. Your calls to LBound and UBound are getting the bounds in the first dimension. If you call Lbound(item,2) and UBound(item,2), you should get 1 and 5 as you expect.

EDIT: That is, once you've made the assignment, item would look like something you could have declared as such:

Dim item(1 to 1, 1 to 5)

One of the banes of VBA programming is that arrays can have arbitrary lower bounds. So all of your code needs to be aware of that.

Upvotes: 3

Atmocreations
Atmocreations

Reputation: 10061

That's correct as is. Even if you select an array of cells, you still have the option to select one single cell out of the array (and step for example with tab through the items of this array)

.Value

only gets you the content of the currently single-selected cell.

if you want the enumeration of the array, you may call the .Cells()-method of the Range-object

Assuming that D19 until H19 contain "a" through "e" respectively, calling

Range("D19:H19").Cells(2)

returns you "b". Note that this is a one-based array and can be 2-dimensional. Cells() takes at most 2 parameters to specify the inner offset from the selection's origin.

hope that clarifies... regards

Upvotes: 1

Related Questions