Array from Range in Excel VBA

Question

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?

I'm no vba expert, but taking the value of a range (or array) seems odd. Is it returning the first value of the range? — dave
– dave, Commented Oct 10, 2009 at 22:13
Generally VBA would return a 2D array with row values being the 1st dimension and column values being the 2nd dimension , however here I am seeking a range which has 5 cells all in one row, so I'm not quite sure whether a 1d or 2d array will be returned... — Kevin Boyd
– Kevin Boyd, Commented Oct 10, 2009 at 22:16
@dave: and yes its returning only the first value of the range... any ideas why it's doing so?! — Kevin Boyd
– Kevin Boyd, Commented Oct 10, 2009 at 22:47
It does not actually return the first value of the range. Select a 2D-range of cells and press Tab several times. You'll see a selected cell that is being highlighted. That's the cell Value returns the content of. — Atmocreations
– Atmocreations, Commented Oct 10, 2009 at 23:09

2 revs, 2 users 96% · Accepted Answer · 2012-09-12 00:39:52Z

11

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

edited Sep 12, 2012 at 0:39

community wiki

2 revs, 2 users 96%
imfrancisd

Sign up to request clarification or add additional context in comments.

2 Comments

jtolle Over a year ago

That's correct. My edit was meant to describe the form of the array returned by getting the Value property of the Range object and putting it in item. I wasn't intending to suggest that item should be declared as an array, only that once assigned, it would look the same as a variable that had been declared as such.

Kevin Boyd Over a year ago

@imfrancisd: Works like a charm! I still didn't understand what was I doing wrong, I don't seem to understand the array concepts in VBA..

jtolle · Accepted Answer · 2009-10-11 14:55:36Z

3

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.

edited Oct 11, 2009 at 14:55

answered Oct 11, 2009 at 0:19

jtolle

7,1742 gold badges31 silver badges52 bronze badges

Comments

Atmocreations · Accepted Answer · 2009-10-10 22:14:14Z

1

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

answered Oct 10, 2009 at 22:14

Atmocreations

10.1k16 gold badges70 silver badges103 bronze badges

6 Comments

Kevin Boyd Over a year ago

The code snippet here support.microsoft.com/kb/213798 in the "To take values from a worksheet and fill the array" section shows a similar code, so I wonder why my code is not functioning!

Atmocreations Over a year ago

Well I tested this on Excel 2k3. Possibly, this is due to the fact that Value might be 2-dimensional... lbound takes a second parameter which expresses as what we call dimension. I'm not sure anymore which parameter corresponds to which value. What does VarType(Range("D19:H19").Value) return? It should be vbArray... You might try Ubound(item,1) instead of Ubound(1)

Kevin Boyd Over a year ago

well I tried Ubound(item, 1) but to no avail.. I still get the 1 1 from the 2nd MsgBox output.. And regarding what (Range("D19:H19").Value) should return... I think i'm not really sure..I'm just working on code samples..

Atmocreations Over a year ago

hmm strange then... well the article refers to all the versions before 2007, but not 2007 itself. Therefore it's possible that it works differently now. How about the solution with .Cells(number) ? ... Yet another point: Try declaring item with item() instead of item

Kevin Boyd Over a year ago

Using .Cells(number) I will have to iterate through all the cells in the range in a for-next loop, right?... well if there is no other choice I won't mind going for this solution...as long as it works consistently...

|

JimmyPena · Accepted Answer · 2009-10-15 19:14:18Z

1

Try this:

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

answered Oct 15, 2009 at 19:14

JimmyPena

8,7906 gold badges46 silver badges66 bronze badges

2 Comments

Kevin Boyd Over a year ago

I thought "Transpose" was meant for columns... will check this out anyway though...

ErikE Over a year ago

FYI, transpose is not available in Excel 2003.

Nick · Accepted Answer · 2009-10-14 13:51:28Z

0

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.

answered Oct 14, 2009 at 13:51

Nick

3,67312 gold badges40 silver badges44 bronze badges

Collectives™ on Stack Overflow

Array from Range in Excel VBA

5 Answers 5

2 Comments

Comments

6 Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

Comments

6 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related