Object Required - VBA Excel

Question

Struggling with this error - I'm not too familiar with object oriented programming so I might just be messing up the syntax or something. I've simplified my code down to show only what seems to be causing the problem and the related variables:

Type layer
    Step As Variant
End Type

Sub PullData()
    j = 6

    Do While a <= j
        steps(1, a) = Sheets("Sheet2").Range("B" & a)
        a = a + 1
    Loop

    a = 1
    For a = 1 To j
        If steps(1, a) = 0
        layer.Step = steps(1, a)
        'From here there is a bunch of code where I use that value to copy a 
        'bunch of other values in the worksheet and paste into a different one, 
        'then move onto the next "item" in the array
    Next a
End Sub

Basically what I'm trying to do is take a range of data from a worksheet, convert that range into a one dimensional array, and then set layer.Step equal each of those values through each iteration. The error is happening at layer.Step = steps(1, a) in the second loop.

You haven't declared layer in your subroutine. If you put Dim layer as layer at the beginning of the PullData subroutine, do you still get the error? I'd recommend naming the variable something else, so you have Dim myLayer as layer and then myLayer.Step = steps(1,a) to help with the confusion. — OpiesDad
– OpiesDad, Commented Dec 29, 2016 at 20:28

41686d6564 · Accepted Answer · 2016-12-29 20:43:00Z

Well, there are a couple things that you're doing wrong here. I'm going to list them out:

First of all, steps is a two dimensional array. To declare a one dimensional array, you don't need the 1 part, you simply declare it like this: Dim steps(n) As variant
In your code, you started an If condition, but you didn't finish it (perhaps it's not the case in the original code).
Now about your error: you declared a user-defined type (layer) but you never created a variable of it. You can't just use the type name. You can create a variable of type layer like this: Dim myLayer as layer, and then you need to replace layer.step = '.. with myLayer.step = '...

A working example that you can modify to suit your requirements:

Type Layer
    Step As Variant
End Type

Sub PullData()
    Dim steps(7) As Variant
    Dim j As Integer: j = 6
    Dim a As Integer: a = 1
    Do While a <= j ' it's better here to use `For` loop instead.
        steps(a) = Sheet1.Range("B" & a)
        a = a + 1
    Loop

    Dim myLayer As Layer
    For a = 1 To j
        myLayer.Step = steps(a)
        ' Rest of your code
    Next a
End Sub

Hope that helps :)

Collectives™ on Stack Overflow

Object Required - VBA Excel

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related