Announcement

Collapse
No announcement yet.

VBA Referencing Library Issue

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • VBA Referencing Library Issue



    Greetings everybody.

    Not an experienced programmer so maybe I'm missing something, but here's what I'm trying to do. I am inserting an add-in as *.xlam workbook that will contain a module with my code. One of the macros in this add-in when called via a button on a ribbon should remove all modules and code from any workbook opened and copy module from the add-in workbook to the open workbook. I have found a standard removal code that I'm using which works fine, however issues arise when using 'copy module' code, which I found somewhere as well:

    Code:
    Sub CopyAllModules()
    
    Dim FName As String
    Dim VBComp As VBIDE.VBComponent
    
    With Workbooks("Book2")
        FName = .Path & "\code.txt"
        If Dir(FName) <> "" Then
            Kill FName
        End If
        For Each VBComp In .VBProject.VBComponents
            If VBComp.Type <> vbext_ct_Document Then
               VBComp.Export FName
               Workbooks("book1").VBProject.VBComponents.Import FName
               Kill FName
            End If
        Next VBComp
    End With
    End Sub
    I know that I need to Reference Extension Library here which I've tried doing different ways. However, the problem is that after I have referenced the library I need to call the macro and that's when I get messages "code cannot be executed in break mode" and "object variable or with block variable is not set". My guess is that a sub that links the Library must be completed before the next should be run, but I don't know how to achieve that. I've tried using functions as below (where DPMacro is the macro that removes original/adds my modules), but that does not help.


    Code:
     
    Sub reference()
    
    refvar = 0
    Call checkreference(refvar)
    If refvar = 0 Then
        Call addreference
    End If
    Call DPMacro
    
    End Sub
    
    
    Function checkreference(refvar As Variant)
    For i = 1 To Application.VBE.ActiveVBProject.References.Count
        If Application.VBE.ActiveVBProject.References.Item(i).Description = "Microsoft Visual Basic for Applications Extensibility 5.3" Then
            refvar = 1
    End If
    Next
    End Function
    
    Function addreference()
    Application.VBE.ActiveVBProject.References.AddFromFile "C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB"
    End Function
    Any ideas on this one. If my explanation of what I trying to achieve is not clear - please let me know.

  • #2
    Re: VBA Referencing Library Issue

    Why are you setting the reference in code? The reference only needs to be set in your addin at design time.
    Rory
    Theory is when you know something, but it doesnít work. Practice is when something works, but you donít know why. Programmers combine theory and practice: nothing works and they donít know why

    Comment


    • #3
      Re: VBA Referencing Library Issue

      Originally posted by rory View Post
      Why are you setting the reference in code? The reference only needs to be set in your addin at design time.
      hm.... somehow every time I set the reference and save the workbook with my macro in it and then close it and open again the reference is not checked in the Tools->References. That's why I was trying to make the reference be set in the add-in code before the main add-in macro is run.

      Comment


      • #4
        Re: VBA Referencing Library Issue

        How are you saving the addin after you add the reference to its project? If you use the Save button in the VBEditor, it's very unreliable. Do it in code.
        Rory
        Theory is when you know something, but it doesnít work. Practice is when something works, but you donít know why. Programmers combine theory and practice: nothing works and they donít know why

        Comment


        • #5
          Re: VBA Referencing Library Issue

          Rory,

          sorry. I'm a bit confused. What I do is I keep Excel workbook where I update the code on as needed basis. Once code is updated I then save the workbook as *.xlam file in the default add-ins location and then every time I run excel the add-in calls for the workbook, which is hidden but can be seen in VBA. My goal is to have a piece of code within this add-in workbook that would delete any and then copy all modules to any open workbook. Apologies for repeating myself but just for the sake of clarity.

          when you say 'save in code' do you mean saving the add-in as *.xlam?

          Thanks.

          Comment


          • #6
            Re: VBA Referencing Library Issue

            OK, so you add the reference to the workbook with the code and save it. Then save it as an add-in. That add-in will now have the reference to the VBA Extensibility library.
            Rory
            Theory is when you know something, but it doesnít work. Practice is when something works, but you donít know why. Programmers combine theory and practice: nothing works and they donít know why

            Comment


            • #7


              Re: VBA Referencing Library Issue

              ah. I must've done something wrong originally. looks like it works now! thanks Rory

              Comment

              Working...
              X