CODEWITHSUNDEEP
excelvba
maximus
maximus

Reputation: 131

Error importing module in VBA Excel

I am attempting to import a module through Visual Basic.

Below is the code that I used to import the module :

Sub CreationMacroAuto()
   Dim Modulos As String

   Modulos = "C:\My_macro\macro.bas"

   With Workbooks("try.xlsm").VBProject
   .VBComponents.Import Modulos
   End With

   'assign value is the name of the sub in macro.bas
   Call assign_value()

End Sub

I am having an error 1004 : object defined error on this line :

With Workbooks("try.xlsm").VBProject

Can anybody help me?

Upvotes: 0

Views: 3104

Answers (1)

Sun
Sun

Reputation: 762

Final Edit:

Sub CreationMacroAuto()

Dim Modulos As String


    Modulos = "C:/users/Sun/Desktop/StackOverflow/assign_value.bas" 'change filepath to yours


    ThisWorkbook.VBProject.VBComponents.Import Modulos

    Application.OnTime Now + TimeValue("0:00:01"), "callingNewSub"


End Sub


Sub callingNewSub()

    Call Module1.assign_value

End Sub

Put above code into one module. This code worked for me. You may also want to rename your Module1 to something else, for example "assign_value_mod", to reduce chance of errors.

Original Answer and Edits:

You need to add this reference to your code:

On Error Resume Next 'in case reference already exists

    ThisWorkbook.VBProject.References.AddFromGuid _
        GUID:="{0002E157-0000-0000-C000-000000000046}", _
        Major:=5, Minor:=3

On Error GoTo 0

EDIT: In fact, the error may rather be with Workbooks("try.xlsm"), as you should write the whole filepath here... Or if it is the workbook where you are calling the code from, then just use ThisWorkbook

EDIT2: try this:

Sub CreationMacroAuto()

    Dim Modulos As String

    Modulos = "C:\My_macro\macro.bas"

    On Error Resume Next 'in case reference already exists

        ThisWorkbook.VBProject.References.AddFromGuid _
            GUID:="{0002E157-0000-0000-C000-000000000046}", _
            Major:=5, Minor:=3

    On Error GoTo 0

    With ThisWorkbook.VBProject
        .VBComponents.Import Modulos
    End With

    Call Module1.assign_value()

End Sub

As for calling a macro: don't write Call macro.bas but call the sub that is inside it. For example, the sub above is called CreationMacroAuto(), so you would call it Call CreationMacroAuto().

Upvotes: 1

Related Questions