Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Why did Range("E3").Resize(UBound(Items, 1), 1) = Items just repeat the 1st element

0

Unexpected result using Range("E3").Resize(UBound(Items, 1), 1) = Items

What I got was array element 1 repeated ubound(item) times

Here's the code

 ' This routine fills an array with headers to detailed info in a separate worksheet

  ' Only headers reside in column 1 while the detail is in subsequent columns

  ' We use an array to establish the number of rows required for borders as well as

  ' to optimize speed.

  Dim ws As Worksheet, rg As Range, Items() As String, i As Long, Row As Long, wsRow As Long

    Set ws = Sheets("Member Passwords")

  For wsRow = 2 To ws.UsedRange.Rows.Count

    If Not IsEmpty(ws.Cells(wsRow, 1)) Then

      i = i + 1

      ReDim Preserve Items(1 To i)

      Items(i) = ws.Cells(wsRow, 1)

    End If

  Next wsRow  

  Set rg = Range("E3:E" & 2 + UBound(Items))

  With rg

    .Cells.Clear

    .BorderAround LineStyle:=xlContinuous, Weight:=xlMedium

    .Borders(xlInsideHorizontal).LineStyle = xlNone

  End With

-----

  ' This just repeated the first array element ubound(items,1) times

'  Range("E3").Resize(UBound(Items, 1), 1) = Items

-----

' This was required to write all array elements

  Row = 2

  For i = LBound(Items) To UBound(Items)

    Row = Row + 1

    Range("E" & Row) = Items(i)

  Next i

excel
MS Office Version: Microsoft 365
posted by: r_guild (rep: 6) on Wed May 12, 2021 at 9:05 am
Answer
Discuss

Answers

0
Selected Answer

Robert

Replace your commented out line with:

Range("E3").Resize(UBound(Items, 1)) = WorksheetFunction.Transpose(Items)

Then you can delete the loop you made to overcome that.

Problem was your statement Range("E3").Resize(UBound(Items, 1), 1) = Items tried to paste a horizontal array into a vertical range.

You'll see that if (as a trial only) you resize E3 to be horizontal too using Range("E3").Resize(1, UBound(Items, 1)) = Items when the values will be "pasted horizontally). A matrix (repeated vertically) would arise if you used resized in both directions with Range("E3").Resize(UBound(Items, 1), UBound(Items, 1)) = Items (and your original result was just the left-most column E). 

Also, as an aside, I find it helpful to declare Option Base 1 (so the first index number of an array is 1 not the default 0) - it makes it much easier for me as a mere human to understand! See the discussion point below from Variatus for more detail.

Hope this fixes your issue.

posted by: John_Ru (rep: 6152) on Wed May 12, 2021 at 9:42 am
Discuss

Discussion

Oops! Was adding more detail as you were selecting the Answer. Thanks Robert!
John_Ru (rep: 6152) May 12, '21 at 10:04 am
I'm a little confused about array declarations. When you dim a fixed parameter array the statement is Dim ARR(Rows,Columns). 

I would have expected Redim ARR(1 to #) would have resulted in an array of rows, not columns)

As an experiment, I attempted Redim Items(1 to i,1) and got a runtime error, "Subscript out of range".

Can you reolve my confusion?
r_guild (rep: 6) May 12, '21 at 10:14 am
Robert

An array with 2 dimensions has indices for rows/columns but note that ReDim only works on the last dimension of the array (so the columns). To add extra rows, you can transpose the array (to columns/rows), Redim (since the last dimension is now the rows) then transpose back to rows/columns. Don't forget to use Preserve (to retain the contents of the array)
John_Ru (rep: 6152) May 12, '21 at 1:06 pm
@John It's not correct that the lower bound of all all arrays will be 1 if you declare Option Base 1, nor will the base be 0 in every case if you don't. The array resulting from reading a worksheet range will always be 1-based and an array created by a Split function will always be 0-based. Therefore, to avoid confusion, I never set the option base and declare arrays individually as 1-based if that is what I need. 
Incidentally, I declare 1-based arrays about as often as I declare arrays starting at a higher number, which is useful when aligning arrays with sheet rows or columns. This goes to show that the choice isn't limited to 0 and 1. I guess the option is really useful only when looking at a class where the expected scope is visible from the start.
Variatus (rep: 4889) May 12, '21 at 8:12 pm
@Variatus. Thanks for the correction, I didn't know that. I've modified that paragraph in my Answer and referred readers to your post.

Once again this is a case where you TeachExcel!
John_Ru (rep: 6152) May 13, '21 at 1:56 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login