VBA Excel object required passing string array variable

Question

I am attempting to pass an array of strings into a function as a variable and am getting a '424 Object required' error when I try to compare the values in the array to values in a given cell. I am new to VBA so this may be a simple syntax error but I cannot seem to figure it out. Here's my code:

Method being called:

Sub InitializeCharts()
    'Set's up the array for checking data names in the social groups
    Dim socialArray As Variant
    socialArray = Array("Chores", "Meat & Potatos", "Work", "Wind Down", "Reward")
    '...
    Call ChartLogic(Range("'ActivityTracker'!B12"), Range("'Groups'!F4"), socialArray)
End Sub

ChartLogic Method:

Sub ChartLogic(dataCell As Range, tableCell As Range, socialArray As Variant)
    Dim temp As Double
    Dim count As Integer

    '...
    'Loops through the table and looks for the social cells with the same name, adding them to the chart
    Do Until IsEmpty(dataCell)
        For count = LBound(socialArray) To UBound(socialArray)
            If socialArray(count).Value = dataCell.Value Then   '<---Error Here
                temp = socialCell.Offset(count, 0).Value
                socialCell.Offset(count, 0).Value = temp + dataCell.Offset(0, 4).Value
            End If
        Next
        Set dataCell = dataCell.Offset(1, 0)
    Loop
End Sub

Thanks in advance!

As answered below you need to change this If socialArray(count).Value = dataCell.Value Then to this If socialArray(count) = dataCell.Value Then — Stupid_Intern
– Stupid_Intern, Commented Jun 2, 2016 at 3:00

Andrew Cheong · Accepted Answer · 2016-06-02 02:44:05Z

4

You're getting an Object required error because socialArray(count) does not yield an object that has the property Value.

In other words, since socialArray is an Array of strings, socialArray(count) already yields a string—there's no need for Value.

answered Jun 2, 2016 at 2:44

Andrew Cheong

30.4k17 gold badges103 silver badges173 bronze badges

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

1 Comment

Alex Over a year ago

Thanks for the speedy reply, this seems obvious once you pointed it out!

Steve Kline · Accepted Answer · 2016-06-02 03:00:48Z

1

As Andrew pointed out - socialArray(count).Value = will cause an error because it's a variant. You can store it as a local variable like this.

ArrVal = socialArray(count)

For count = LBound(socialArray) To UBound(socialArray)
    ArrayVal = socialArray(count) 
    If ArrayVal = dataCell.Value Then   '<---Error Here
        temp = socialCell.Offset(count, 0).Value
        socialCell.Offset(count, 0).Value = temp + dataCell.Offset(0, 4).Value
    End If
Next

Or you could just take off the .value as it's not a cell and is not a worksheet object but a variant.

If socialArray(count) = dataCell.Value Then

answered Jun 2, 2016 at 3:00

Steve Kline

8054 silver badges11 bronze badges

1 Comment

Alex Over a year ago

That makes perfect sense, it's really obvious once you pointed it out. Thanks for the help!

Collectives™ on Stack Overflow

VBA Excel object required passing string array variable

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related