Fix VBA with VBA

In order to create a macro that modifies code, you need to set a reference to the Microsoft Visual Basic for Applications Extensibility Library. Chip Pearson has some great pages dealing with this library Programming to the Visual Basic Editor.

Jake asks:

I have a several ( 14 ) templates which contain an incorrect reference within the VBA, I would like to fix by sending out sheet which contains VBA to fix the incorrect reference.

Okay, that’s not really a question, but there was a question in the email. I swear.

I’m probably not the guy to be giving anyone advice on changing references programmatically. I don’t think I’ve ever had to do it. My workbooks are used in an environment that I pretty much control, so I don’t have a lot of problems with references. But I gave it a shot anyway. I’m sure if there are better approaches, someone will let me know in a comment.

Sub FixRef()
   
    ‘Changes reference from ADO x.x to ADO 2.8
   Dim wb As Workbook
    Dim ref As Reference
    Dim sRefFile As String
   
    ‘The file path for ADO 2.8
   sRefFile = “C:Program FilesCommon FilesSystemadomsado15.dll”
   
    ‘Open the workbook to change
   Set wb = Workbooks.Open(ThisWorkbook.Path & “WrongRef.xls”)
   
    ‘Loop through the references and delete the ADO one
   For Each ref In wb.VBProject.References
        If InStr(1, ref.Description, “ActiveX Data Objects”) > 0 Then
            wb.VBProject.References.Remove ref
            Exit For
        End If
    Next ref
   
    ‘Add the new reference
   wb.VBProject.References.AddFromFile sRefFile
   
    wb.Save
    wb.Close
   
End Sub

This assumes that you have a reference to a certain version of ADO and you want to change it to version 2.8. There’s probably no reason to actually do that, but that’s what I’m going with. I put the path to new library in a variable. I got this path by going to Tools > References, but it will no doubt be different on every machine. So what’s a good way to add that reference? AddFromGUID is probably better after you’ve scoured the registry with APIs, but if that’s so, I’ll let someone else figure that out.

I use a loop to remove the old reference, searching for a unique string in the Description. I could have also looked at the Name and deleted any with the name ADODB. As long as you’re scouring the registry for the new reference, I suppose you can get some info on the old reference and forget about the loop.

Other than telling you to set a reference and showing you the VBProject and Reference objects, this post sucks. Hopefully it will serve to provide better approaches via the comments.

Posted in Uncategorized

11 thoughts on “Fix VBA with VBA

  1. One method might be to set up a workbook containing the references you want to use for replacement.

    This macro will list the details before those references, including GUID, Major and Minor (required parameters for AddFromGUID:

    (PS: I’ve used special html code for nonbreakingspace nbsp so it tabs the code properly – I hope it works)

    Sub test()
        Dim ref As Reference, rng As Range

        Set rng = Range(“A1?)
        rng.Value = “Name”
        rng.Offset(, 1).Value = “Type”
        rng.Offset(, 2).Value = “Description”
        rng.Offset(, 3).Value = “Major”
        rng.Offset(, 4).Value = “Minor”
        rng.Offset(, 5).Value = “GUID”
        rng.Offset(, 6).Value = “Is Broken”
        rng.Offset(, 7).Value = “Full Path”
        rng.Offset(, 8).Value = “Built In”
        For Each ref In ActiveWorkbook.VBProject.References
            Set rng = rng.Offset(1)
            rng.Value = ref.Name
            Select Case ref.Type
                Case vbext_rk_TypeLib: rng.Offset(, 1).Value = “TypeLib”
                Case vbext_rk_Project: rng.Offset(, 1).Value = “Project”
            End Select
            rng.Offset(, 2).Value = ref.Description
            rng.Offset(, 3).Value = ref.Major
            rng.Offset(, 4).Value = ref.Minor
            rng.Offset(, 5).Value = ref.GUID
            rng.Offset(, 6).Value = ref.IsBroken
            rng.Offset(, 7).Value = ref.FullPath
            rng.Offset(, 8).Value = ref.BuiltIn
        Next
    End Sub

    Rob

  2. FWIW, I’ve used a method based on GUID, but always had problems. Referance are not something that are easy to fix.

    Both of the above depend on assumeing the full path for a referance is known(???), which if often not possible.

    I think this is one of the “problmes” of not “intalling” a app.

  3. Hmmmm…

    What library needs to be ‘referenced’ in order that “Dim ref As Reference” no longer produces “Compile Error: User-defined type not defined”?

  4. Hmmm…

    I don’t have 5.3 available but 6.0.
    However, the struct –
    For Each ref In ActiveWorkbook.VBProject.References

    returns ‘type mismatch’ error.

    I run XL 2000 Premium.

  5. You’re effectively late-binding.

    You could just as well write:
    Dim ref As Object

    For the code I comment_posted, you’ll also need to change:
    vbext_rk_TypeLib and vbext_rk_Project
    to 0 and 1 respectively.

    PS. Interesting that you dont see “Microsoft Visual Basic for Applications Extensibility 5.3?
    That reference is C:Program FilesCommon FilesMicrosoft SharedVBAVBA6VBE6EXT.OLB on my computer

    Rob

  6. doco: Are you sure you’re not referencing the “Microsoft Visual Basic Extensibility 6.0?? rather than the “… for Applications Extensibility 5.3??

    Rob: When using AddFromGUID, you can use zero for the major and minor versions to pick the latest.

    Dick/Jake: For this technique to work, the user needs to “Trust access to Visual Basic project” in Excel’s Tools > Macro > Security > Trusted Sources, and if the projects are protected, you’ll have to use the dreaded SendKeys to unprotect and reprotect them.

    Regards

    Stephen Bullen

  7. Rob:found the *.olb and now the snippet works without tweaking…

    Stephen: guess I am going to have to take remedial reading :eek: I did have the VB Extensibility in place of ‘Applications’ Extensibility.

    Thanks
    doco

  8. Is it posible to set microsoft Excel not to change refrerence’s.
    It is set on my laptop on “Microsoft Office 11.0 Object library”. I develop on my laptop and other people use it on theire desktop with an older office version.

    If i set it off the problem is gone!


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published. Required fields are marked *