VBA error 424 when trying to use class method from another module

Question

I have a class called autoCRUD in a class module in excel 2013. From another module (a regular one) I try to call a method from this class and I get the "Object required" exception.

Here's the method:

Public Function CreateCRUDView(TipoCRUD As String) 'TipoCRUD pode ser C (Create), R (Read), U (Update), D (Delete)
    Dim myForm As Object
    Dim NewFrame As MSForms.Frame
    Dim NewButton As MSForms.CommandButton
    Dim NewListBox As MSForms.ListBox
    Dim NewLabel As MSForms.Label
    Dim X As Integer
    Dim Line As Integer
    Dim t As Integer

    Dim arrLeg() As Variant
    arrLeg = legenda

    'This is to stop screen flashing while creating form
    Application.VBE.MainWindow.Visible = False

    Set myForm = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_MSForm)


    'Create the User Form
    With myForm
        .Properties("Caption") = "New Form"
        .Properties("Width") = 300
        .Properties("Height") = 270
    End With

    'Criar labels
    t = 10
    For Each lbl In arrLeg
        Set NewLabel = myForm.Designer.Controls.Add("Forms.label.1")
        With NewLabel
            .name = "lbl_" + Replace(CStr(lbl.Value), " ", "")
            .t = (10 + t)
            .Left = 10
            .Font.Size = 8

        End With
        t = t + 10
    Next

        'Create CommandButton Create
        Set NewButton = myForm.Designer.Controls.Add("Forms.commandbutton.1")
        With NewButton
            .name = "cmd_1"
            If UCase(TipoCRUD) = "C" Then
                .Caption = "Salvar"
            ElseIf UCase(TipoCRUD) = "U" Then
                .Caption = "Alterar"
            End If
            .Accelerator = "M"
            .Top = Top + 10
            .Left = 200
            .Width = 66
            .Height = 20
            .Font.Size = 8
            .Font.name = "Tahoma"
            .BackStyle = fmBackStyleOpaque
        End With
        Top = Top + 10
        End Function

The code from another module that calls the method is :

Public Sub Main()

       Dim ac As autoCrud
       Set ac = New autoCrud

       ac.CreateCRUDView ("c")

End Sub

I don't get it, why am I getting this error?

Here is the code for "legenda":

Public Property Get sht() As Worksheet
    Const shtName As String = "Teste1"
    Set sht = ActiveWorkbook.Worksheets(shtName)
End Property
Public Property Get legenda() As Range
    Const linha As Integer = 3
    Const colunaI As Integer = 2
    Dim colunaF As Integer
    Dim i As Integer
    i = colunaI
    Do While sht.Cells(linha, i).Value <> ""
        i = i + 1
    Loop
    colunaF = (i - 1)
    Set legenda = sht.Range(Cells(linha, colunaI), Cells(linha, colunaF))
End Property

The lbl.Value is supposed to be a string value, the name of the label. And it comes from the spread sheet in the header of the table, teh legenda() only selects that header and the arrLeg takes the legenda as a range and transforms it in an array. Edit:

Apparently the error occurs in the line that says: .name = "lbl_" + Replace(CStr(lbl.Value), " ", "") As you can see, I've tried to take the spaces from the string and also ensure that it is a string, but none of it worked.

Edit 2:

I actually just use a class for organization and re-usability purposes. I take the properties and other methods and use them inside the 'createCRUDView' method, this method will then create a CRUD View, that is, create a form either to "Create", "Read" (not used since it's excel),"Update or "Delete" data entries. It basically creates forms dynamically to any table you make

Answer 1

VBA error 424 is object required error. So I'm now pretty sure that lbl in CStr(lbl.Value) is not an object. With your code legenda is a Range but after

Dim arrLeg() As Variant
arrLeg = legenda

arrLeg will be a variant array. This array does not contain objects. You can debug this with

For Each lbl In arrLeg
 ...
 MsgBox TypeName(lbl)
 ...
Next

So you should use CStr(lbl).

And

Set legenda = sht.Range(Cells(linha, colunaI), Cells(linha, colunaF))

will only work while the "Teste1" sheet is the ActiveSheet because Cells(linha, colunaI) is not explicit assigned to a sheet so the ActiveSheet will be supposed.

Set legenda = sht.Range(sht.Cells(linha, colunaI), sht.Cells(linha, colunaF))

will be better.