'If an error was encountered, inform the user 'Clear any errors so that error trapping for GUID additions can be evaluated 'Macro purpose: To add a reference to the project using the GUID for the 'reference libraryĭim strGUID As String, theRef As Variant, i As Long NOTE: In Office 2002 or later, the TRUST ACCESS TO VISUAL BASIC PROJECT, in the Macro security settings, MUST be checked, or the code will not work.įor a KB Entry to get the GUID for the library you need, see. (The GUID provided below will add a reference to the Microsoft Word library, and is NOT VERSION SPECIFIC!) (This happens because they've been upgraded to a newer version that Office 97 does not recognize.) This code will remove any missing references, and then add a reference on the fly, based on the GUID you specify. When the 97 user sends the file on, the workbook opens fine, and the reference libraries are automatically updated, but when they are returned to the 97 user, the reference libraries are listed as "missing". Useful if you have users who share workbooks between different versions of Excel.Īssume that your users share workbooks between Office 97 and Office 2000 (or higher). Automatically adding VBA code to files is a security concern because it is a common way of spreading malware, it might set antiviruses off, and it requires the Trust access to the VBA project object model setting to be set in the user interface (which I would personally never set).This code allows you to add reference to a specific library on the fly.
However I would advise you consider a different approach, such as moving the code you want to putting into files into an adding that the files can refer to. Make sure you have Option Explicit at the top to catch any now-undeclared constants you might be using. What does require a reference is the variable declaration above: Dim VBProj As VBIDE.VBProjectĪs soon as you have replaced them with As Object you can run the rest of the code as is without adding a reference. This code does not require any references per se. There is a false premise in your original code: Set VBProj = ActiveWorkbook.VBProject ' requires Ref: MS.5.3 Set CodeMod = CreateObject("VBComp.CodeModule")Įxcel threw error "Run-time error '429': ActiveX component can't create object".Īny idea how I can either modify this code to utilize late binding, or otherwise load the library reference and run the rest of the code in the same procedure/module? Set VBComp = CreateObject("VBProj.VBComponents(""Module1"")") Set VBProj = CreateObject("ActiveWorkbook.VBProject") Using this post as a guide I modified part of the code to look like this: Dim VBProj As Object
The answer may be to adapt my code to use late binding so that Excel won't look for that library until after part of the script has executed and the library is available. Excel looks for the library before any code is executed and can't find it. If I understand correctly, my code uses something called early binding. Excel throws compile error "User defined type not defined". The problem arises when I try to combine the two in a single procedure. InsertLines LineNum, " MsgBox " & DQUOTE & "Hello World" & DQUOTE InsertLines LineNum, "Public Sub SayHello()" Set CodeMod = VBComp.CodeModule ' requires Ref: MS.5.3 Set VBComp = VBProj.VBComponents("Module1") ' requires Ref: MS.5.3 Set VBProj = ActiveWorkbook.VBProject ' requires Ref: MS.5.3 On Error GoTo 0 ' Resume normal error handlingĭim VBProj As VBIDE.VBProject ' requires Ref: MS VB for Apps Extensibility 5.3ĭim VBComp As VBIDE.VBComponent ' requires Ref: MS.5.3ĭim CodeMod As VBIDE.CodeModule ' requires Ref: MS.5.3 On Error Resume Next ' If library already referenced, ignore the error Here's the code that adds the library reference to Excel. The code requires the Microsoft Visual Basic for Applications Extensibility 5.3 library which I can add manually, but I'm interested in having a single script that can add that library into Excel and then use it. The final version will add a procedure that auto-saves backup copies of the workbook. I have a VBA script for Excel that adds a small procedure in the active workbook.
0 kommentar(er)
