VBA, Excel Error 'object required' when passing an array as a variable

Question

I have two VBA procedures one building an array which it will then pass to another. However, when the first procedure is run an Excel Error window pops up saying 'object required'. Here is the code I have. I cannot seem to find what is causing this error.

Sub DetermineLoad()

Worksheets("Gear").Activate

Dim row_src As String
Dim strt_row As String
Dim col_storage As String
Dim Arr() As String
Dim aLength As Integer

row_src = "C"
strt_row = "9"
col_storage = "B"

Range(row_src + strt_row).Activate

While Not IsEmpty(Range(col_storage + Active.Row))
    If Not IsEmpty(ActiveCell) Then
        If ActiveCell.Value Then
            aLength = UBound(Arr) + 1
            ReDim Arr(0 To aLength)
            Arr(aLength) = String(ActiveCell.Column, ActiveCell.Row)
        End If
    End If
    ActiveCell.Offset(0, 1).Activate
Wend

DetermineWeight Arr

End Sub
Sub DetermineWeight(ParamArray Arr() As Variant)

Worksheets("Gear").Activate

Dim weight_lb_trgtCell As String
Dim weight_oz_trgtCell As String
Dim volume_cuin_trgtCell As String
Dim volume_liter_trgtCell As String
Dim col_oz As String
Dim col_lb As String
Dim weight_lb As Double
Dim weight_oz As Double
Dim oz_to_pound As Integer
Dim row_src As String
Dim src_range As Range

weight_lb_trgtCell = "H4"
weight_oz_trgtCell = "I4"

col_oz = "H"
col_lb = "I"
weight_lb = 0       ' 0 out `weight_lb`
weight_oz = 0       ' 0 out `weight_oz`
oz_to_pound = 16    ' 16 ounces to 1 pound

'get sum of weights (pounds, ounces)
For n = LBound(Arr) To UBound(Arr)

    src_range = Range(Arr(n))

    src_row = src_range.Row
    weight_oz = weight_oz + Range(col_oz + src_row).Value
    weight_lb = weight_lb + Range(col_lb + src_row).Value

Next n

'convert pounds
weight_lb = weight_lb + Int(weight_oz / oz_to_pound)
weight_oz = weight_oz - ((weight_oz / oz_to_pound) * oz_to_pound)

Range(weight_lb_trgtCell) = weight_lb
Range(weight_oz_trgtCell) = weight_oz

End Sub

Active.Row Is that valid? Try adding Option Explicit and see if it flags this. — RBarryYoung
– RBarryYoung, Commented Sep 3, 2013 at 19:29
The error seems to be thrown from code on Line 17 where the while loop is started. I think ActiveCell.Row works, but I will see about finding a different solution. — Brook Julias
– Brook Julias, Commented Sep 3, 2013 at 19:32
Moral of the story is always, unless a trivial throwaway hack for something, use the Option Explicit compiler declaration at the top of the module. The reason it failed was Active is not an existing object (like ActiveCell would be), and it was not explicitly assigned to. Option Explicit would require explicit variable declaration which would result in the compiler warning you about the lack of a variable called "Active". — Cor_Blimey
– Cor_Blimey, Commented Sep 3, 2013 at 19:55

tigeravatar · Accepted Answer · 2013-09-03 19:41:13Z

1

Why do you need VBA for this? Put this formula in cell H4 (leftover ounces):

=MOD(SUMIF(C9:C1000,"<>",H9:H1000),16)

And this formula in cell I4 (total pounds):

=SUMIF(C9:C1000,"<>",I9:I1000)+INT(SUMIF(C9:C1000,"<>",H9:H1000)/16)

Adjust ranges to suit. If you need it to pick up new rows as they come in or are removed, use a dynamic named range.

answered Sep 3, 2013 at 19:41

tigeravatar

26.7k5 gold badges32 silver badges42 bronze badges

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

1 Comment

Brook Julias Over a year ago

Thank you for this answer. I hate VBA and would rather not have to bother with it if I don't have to.

BiggerDon · Accepted Answer · 2013-09-03 21:13:02Z

0

An alternative to passing the array is to scope the array at the Module then use a Dynamic Named Range (as suggested), then the redim would be the rows of the Dynamic Named Range

answered Sep 3, 2013 at 21:13

BiggerDon

314 bronze badges

Collectives™ on Stack Overflow

VBA, Excel Error 'object required' when passing an array as a variable

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related