Error when trying to populate array using range function with variables

Question

I'm having a problem populating an array. For some reason all of the tutorials I can find would rather list the data & sizing explicitly in the code itself (which sort of defeats the purpose of using VBA to begin with for this task).

My question is about how to dynamically size an array, if it's even possible to do so in the manner that I'm trying to. The code itself is commented with the background and specific questions.

When I run the code I get an "Application-defined or object-defined error" (1004)

Also, using a for... next loop to populate the arrays may end up being computationally inefficient as the datasets typically average 23k rows

Code:

Sub DCList()
ReDim DCList(0, 0)

Dim cnum As Integer

' count the number of columns that have been used in the workbook
cnum = ActiveWorkbook.Sheets("data").UsedRange.Columns.Count

' set array dimensions to 1 row & Cnum columns
ReDim DCList(1, cnum)


' set array values to values found in the range (A1, cnum 1)
DCList = ActiveWorkbook.Sheets("data").Range(Cells(1, 1), Cells(1, cnum)).Value


'Other info:
' DCList is a global variable set as Variant type
' The overarching goal of this sub is purely to determine the column names of
' a dataset so that (in another sub) the user can select which column to lookup



End Sub

Thanks Gary's Student, was trying to figure out how to format this thing correctly when you did so. — smpat04
– smpat04, Commented Jul 27, 2014 at 16:45
replace ActiveWorkbook.Sheets("data").Range(Cells(1, 1), Cells(1, cnum)).Value with ActiveWorkbook.Sheets("data").Range(ActiveWorkbook.Sheets("data").Cells(1, 1), ActiveWorkbook.Sheets("data").Cells(1, cnum)).Value ? — Siphor
– Siphor, Commented Jul 27, 2014 at 16:46

Gary's Student · Accepted Answer · 2014-07-27 16:47:18Z

1

Try this version:

Sub DCList_sub()
    Dim DCList
    Dim cnum As Long

    ' count the number of columns that have been used in the workbook
    cnum = ActiveWorkbook.Sheets("data").UsedRange.Columns.Count

    ' set array dimensions to 1 row & Cnum columns
    ReDim DCList(1 To 1, 1 To cnum)

    ' set array values to values found in the range (A1, cnum 1)
    DCList = ActiveWorkbook.Sheets("data").Range(Cells(1, 1), Cells(1, cnum)).Value

    'Other info:
    ' DCList is a global variable set as Variant type
    ' The overarching goal of this sub is purely to determine the column names of
    ' a dataset so that (in another sub) the user can select which column to lookup

End Sub

answered Jul 27, 2014 at 16:47

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

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

3 Comments

smpat04 Over a year ago

It works! Now the question: why did this one work and my original code did not? Is it because I had the dclist array redim with only 1 to cnum?

Gary's Student Over a year ago

1 I changed the name of the sub to be different from the name of the array. 2 I changed Integer into Long. I ReDimmed DCList as indicated.

Patrick Lepelletier Over a year ago

in fact it shouldn't work, exept if activesheet is already sheets("Data").

Ron Rosenfeld · Accepted Answer · 2014-07-27 18:02:11Z

0

If you want DCList to contain the first row of the worksheet, there's no need to redim DClist. Simply:

Dim DCList As Variant
DCList = ActiveWorkbook.Sheets("Data").UsedRange.Rows(1)

should do that.

If you need the number of columns for some other reason

cnum = ubound(DCList,2)

answered Jul 27, 2014 at 18:02

Ron Rosenfeld

62k7 gold badges35 silver badges71 bronze badges

Comments

Patrick Lepelletier · Accepted Answer · 2014-07-28 21:03:31Z

0

Gary's student code corrected:

Sub DCList_sub()
Dim DCList() as variant
Dim cnum As Long


with ActiveWorkbook.Sheets("data") 'might be better with thisworkbook, if the code is in the same workbook...    

    ' count the number of columns that have been used in the workbook
    cnum = .UsedRange.Columns.Count 

    ' set array dimensions to 1 row & Cnum columns
    ReDim DCList(1 To 1, 1 To cnum)

    ' set array values to values found in the range (A1, cnum 1)
    DCList = .Range(.Cells(1, 1), .Cells(1, cnum)).Value 'note the two "." before "Cells"

end with

'do stuff here with the array...

'free memory Erase DClist

End Sub

answered Jul 28, 2014 at 21:03

Patrick Lepelletier

1,6522 gold badges19 silver badges24 bronze badges

Collectives™ on Stack Overflow

Error when trying to populate array using range function with variables

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related