Populate a one-dimensional array with values from an Excel Table column using VBA

Question

The code below is meant to read columns from an Excel table into arrays, which can then be used to determine whether each "Project" belongs to the Environment "Group", and if so, to add the project number and dollar value to another array. I am having some issues with my code, and have been searching the internet and StackOverflow but have been able to find very little information on dealing with Excel Tables using VBA. I am using Excel 2010.

Sub UpdateProjectsAndCharges()
'Define arrays to be used
Dim projectArray() As Variant
Dim uniqueProjectArray(100) As Variant
Dim dollarValue() As Variant
Dim envProjectArray(100) As Variant
Dim envDollarValue(100) As Double
Dim cumulativeCosts(100) As Double
'Define all tables in this sheet as list objects
Dim UnitsValues As ListObject
Dim ChargingTracking As ListObject
'Define counters to be used
Dim counter As Integer
Dim counter2 As Integer
'Set variables for each table in sheet
Set UnitsValues = Sheets("Cluster Data").ListObjects("UnitsValues")
Set ChargingTracking = Sheets("Cluster Data").ListObjects("ChargingTracking")
'Find last row in table
With Sheets("Cluster Data")
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
End With
'Define variables to be used in loops
Dim userGroup As Variant
Dim project As Variant
Dim Value As Variant
'Set arrays to respective columns from UnitsValues table
userGroups = Range("UnitsValues[Group]")
projectArray = Range("UnitsValues[Project]")
dollarValue = Range("UnitsValues[Dollar Value]")
'Redefine length of arrays to number of rows in table
ReDim Preserve projectArray(lastRow)
ReDim Preserve dollarValue(lastRow)
'Set counter values
counter = 1
counter2 = 1

For Each userGroup In userGroups
    project = projectArray(counter)
    Value = dollarValue(counter)
    If userGroup = "Environment" Then
        envProjectArray(counter2) = project
        envDollarValue(counter2) = Value
        counter2 = counter2 + 1
        MsgBox ((envProjectArray(counter2) & " " & envDollarValue(counter2)))
    End If
    counter = counter + 1
Next userGroup

I was receiving the "Subscript out of range" error with these lines:

project = projectArray(counter)
Value = dollarValue(counter)

I looked up the error and thought that these lines would perhaps fix the problem:

ReDim Preserve projectArray(lastRow)
ReDim Preserve dollarValue(lastRow)

Now, I am receiving the same error on the lines above instead, and have run out of ideas on how to fix the error. I suspect it is happening because I assigned a range into an array, but I'm not certain.

@enderland, I looked at this question earlier and tried to implement parts of it, but it didn't solve the issue, unfortunately. — David
– David, Commented Nov 24, 2014 at 22:20
Just by the way, Dim projectArray As Variant is sufficient. You only need a Variant, not an array of Variant. The result when you do the assignment is a Variant Array. — Cool Blue
– Cool Blue, Commented Nov 25, 2014 at 3:18
Also +1 by the way because I didn't know you could pass a structured references to the Range property. — Cool Blue
– Cool Blue, Commented Nov 25, 2014 at 3:22

David · Accepted Answer · 2014-11-24 23:25:49Z

3

Change:

project = projectArray(counter)
Value = dollarValue(counter)

to

project = projectArray(counter, 1)
Value = dollarValue(counter, 1)

Arrays read from worksheets always are multidimensional even if you just have 1 column.

In this case you are specifying that column to be 1, every time.

edited Nov 24, 2014 at 23:25

David

472 silver badges6 bronze badges

answered Nov 24, 2014 at 22:22

enderland

14.2k18 gold badges105 silver badges157 bronze badges

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

1 Comment

Cor_Blimey Over a year ago

A little tip to supplement this: if a 1-dimensional array is really needed (useful as an argument for some functions like Join) then a simple way to convert a 1-column array to it is to use WorksheetFunction.Transpose. Using it once on a multi-row 1-column range will get a 1-d array, or using twice on a 1-row multi-column range (i.e. transpose the transpose) will get a 1-d array.

Collectives™ on Stack Overflow

Populate a one-dimensional array with values from an Excel Table column using VBA

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related