Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 7 of 7

Thread: VBA Referencing Library Issue

  1. #1
    Join Date
    29th March 2012
    Posts
    4

    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:

    VB:
    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.


    VB:
     
    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.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    2nd May 2008
    Location
    Ubique
    Posts
    1,894

    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

  3. #3
    Join Date
    29th March 2012
    Posts
    4

    Re: VBA Referencing Library Issue

    Quote 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.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    2nd May 2008
    Location
    Ubique
    Posts
    1,894

    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

  5. #5
    Join Date
    29th March 2012
    Posts
    4

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    2nd May 2008
    Location
    Ubique
    Posts
    1,894

    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

  7. #7
    Join Date
    29th March 2012
    Posts
    4

    Re: VBA Referencing Library Issue

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

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Missing Library?
    By FINgers in forum EXCEL HELP
    Replies: 11
    Last Post: September 30th, 2006, 23:58
  2. FileSystemObject Library
    By jsteinbeck in forum EXCEL HELP
    Replies: 1
    Last Post: August 12th, 2005, 04:53
  3. Library
    By kim11vagabond in forum EXCEL HELP
    Replies: 19
    Last Post: August 8th, 2005, 20:04
  4. Referencing the Type Library
    By elopez in forum EXCEL HELP
    Replies: 2
    Last Post: August 12th, 2003, 08:28

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno