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.
'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 Files\Common Files\System\ado\msado15.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.
Rob van Gelder:
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
11 May 2005, 1:27 amRob van Gelder:
hmph - smileys - i never win
replace the smiley with the number 8 followed by close bracket )
11 May 2005, 1:28 amross:
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.
11 May 2005, 2:25 amdoco:
Hmmmm...
What library needs to be 'referenced' in order that "Dim ref As Reference" no longer produces "Compile Error: User-defined type not defined"?
11 May 2005, 7:09 amDick Kusleika:
Doco: It's called "Microsoft Visual Basic for Applications Extensibility 5.3".
11 May 2005, 8:39 amdoco:
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.
11 May 2005, 6:23 pmdoco:
I replaced 'ref' with a variant 'vItem' and it works great now...
comments?
11 May 2005, 6:31 pmRob van Gelder:
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 Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB on my computer
Rob
11 May 2005, 6:43 pmStephen Bullen:
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
11 May 2005, 11:48 pmdoco:
Rob:found the *.olb and now the snippet works without tweaking...
Stephen: guess I am going to have to take remedial reading
I did have the VB Extensibility in place of 'Applications' Extensibility.
Thanks
12 May 2005, 5:55 amdoco
ravaoo:
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!
8 December 2006, 7:29 am