Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
External References to Libraries
Here's some code I wrote to handle various aspects of linking to
external libraries. I found existing material in books and on the web to be a little tough going to separate fact from fancy. But it was ultimately useful with a bit of work on my part, so I thought I'd pay it forward by making it easier for others. One way to do it, of course, is manual -- in the VBA Editor, use tools references to set references to libraries and/or browse for them. If you want to be sure, however, that a given application always has the non-default references you want linked in, do the following... 1. Set it manually, using tools references. 2. Run the GetCurrentExternalReferences sub below, which will place many of the parameters of existing libraries into the spreadsheet; this will include the one you want, since you just added it manually. 3. Using the values in the worksheet, create new constants from the Name and GUID of the library you want, like the two existing ones 4. Write specific calls to AddExternalReference with those constants; don't forget the Major and Minor versions from the worksheet table, unless they are 1 and 0 respectively. For example, for regular expression handling, you want major.minor = 5.5, since it is a superset of the older 1.0. 5. Embed AddExternalReference and your newly constructed call in your code, ideally at some initialization point for the application. Note that AddExterenalReference won't try to re-add a reference that is already there, which would cause a runtime error, so you can call it with impunity. Here's the code. Feedback is welcome -- though this seems to work, I may not have all aspects of it completely figured out. Const RegExpName As String = "VBScript_RegExp_55" Const RegExpGUID As String = "{3F4DACA7-160D-11D2-A8E9-00104B365C9F}" Const ScriptingName As String = "Scripting" Const ScriptingGUID As String = "{420B2830-E718-11CF-893D-00A0C9054228}" Sub AddRegExpReference55() Call AddExternalReference(RegExpName, RegExpGUID, 5, 5) End Sub Sub AddScriptingReference() Call AddExternalReference(ScriptingName, ScriptingGUID) End Sub Sub DeleteRegExpReference() '-- for illustration; little real use Call DeleteExternalReference(RegExpName) End Sub Sub GetCurrentExternalReferences() Dim I As Integer Dim R As Range '-- Workbooks.Add '-- uncomment to put results in new workbook SetColumnTitles _ "Name", "GUID", "Major", "Minor", "Description", _ "Type", "VBE.Version", "FullPath" SetColumnWidths 20, 40, 6, 6, 40, 6, 12, 60 For I = 1 To ThisWorkbook.VBProject.References.Count Cells(I + 1, 1) = ThisWorkbook.VBProject.References(I).Name Cells(I + 1, 2) = ThisWorkbook.VBProject.References(I).GUID Cells(I + 1, 3) = ThisWorkbook.VBProject.References(I).Major Cells(I + 1, 4) = ThisWorkbook.VBProject.References(I).Minor Cells(I + 1, 5) = ThisWorkbook.VBProject.References(I).Description Cells(I + 1, 6) = ThisWorkbook.VBProject.References(I).Type Cells(I + 1, 7) = ThisWorkbook.VBProject.References(I).VBE.Version Cells(I + 1, 8) = ThisWorkbook.VBProject.References(I).FullPath Next I End Sub Sub AddExternalReference( Name As String, GUID As String, Optional Major As Integer = 1, Optional Minor As Integer = 0) Dim RI As Integer Dim Found As Boolean Dim RName As String Found = False With ThisWorkbook.VBProject For RI = 1 To .References.Count RName = .References(RI).Name If Name = RName Then Found = True Exit For End If Next If Not Found Then .References.AddFromGuid GUID, Major, Minor End With End Sub Sub DeleteExternalReference(Name As String) '-- not sure why you'd ever do this Dim RI As Integer With ThisWorkbook.VBProject For RI = 1 To .References.Count If Name = .References(RI).Name Then .References.Remove .References(RI) Exit For End If Next End With End Sub '-- A couple of useful utilities for making worksheet headers Sub SetColumnTitles(ParamArray VS() As Variant) Dim R As Integer For R = 0 To UBound(VS) With Cells(1, R + 1) .Value = VS(R) .Interior.ColorIndex = 15 .Font.Bold = True End With Next R End Sub Sub SetColumnWidths(ParamArray VN() As Variant) Dim R As Integer For R = 0 To UBound(VN) Columns(R + 1).ColumnWidth = VN(R) Next R End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
References to external workbooks | Excel Worksheet Functions | |||
Using external references as hyperlinks | Excel Discussion (Misc queries) | |||
External References in Functions | Excel Worksheet Functions | |||
External references | Excel Programming | |||
External References | Excel Discussion (Misc queries) |