Testies external shared Libraries, regedit, registry

[DocAElstein]

1.5) Laterly Early Binding

I have lately been doing this Early Binding alternative. It is basically Early Binding but usually done quite Late in time, often when a File is shared.

The idea is very simple but I have found it quite effective.

It uses coding to check the reference, so that codes written for Early Binding will work. Ideally several versions of the library should be attempted to be checked. Hopefully one will be successful . It should be if you have included a version for the version of Office in which a code needing the reference is run

You will need to know some information about the library you want to reference. I have found that one of the parameters used to identify the library, the GUID, is quite a reliable thing to use. I have not found a full list yet of all the Libraries and their GUID, but here_..
http://www.excelfox.com/forum/showth...0547#post10547
_.... you can see one way to obtain this information if you can get access to a few Office versions: A simple code can be run to give you the information.


Once you have the GUID information you can arrange that a code is run which will add attempt to .Add the references using the Add From Guid Method https://msdn.microsoft.com/en-us/lib...(v=vs.60).aspx



How it works
Codes written for Early Binding will error if the reference is not checked.
Therefore the code to add the reference needs to be done before the main code
This can be done by a couple of ways:
_A) Rather than running the main code, a short routine is run that first Calls the code to add the reference and then Calls the main Code, pseudo
Sub CallCodes()
_ Call AddRefsByGUID
_ Call MainCode
End Sub

_B) The code to .Add the reference can be organised to be run on opening the Workbook, either by arranging that the code to .Add the reference is called when the Workbook is opened, or the code itself directly is run when the workbook is opened.

For the latter, B) , a code of this form would go in the ThisWorkbook code Module ( ThisWorkbookCodeModule.jpg https://imgur.com/0GpUeUx )
Attachment 2003

This example should add the reference required for using the WORD library in a VBA code running in Office versions of 2003, 2007 or 2010

Private Sub Workbook_Open()
   With ThisWorkbook.VBProject.References
    On Error Resume Next '
       Select Case CLng(Val(Application.Version))
        Case 9: ' Excel 2000
        Case 10: ' Excel 2002
        Case 11: .AddFromguid GUID:="{00020905-0000-0000-C000-000000000046}", major:=8, Minor:=4 ' Office 2003
        Case 12: .AddFromguid GUID:="{00020905-0000-0000-C000-000000000046}", major:=8, Minor:=4 ' Office 2007
        Case 14: .AddFromguid GUID:="{00020905-0000-0000-C000-000000000046}", major:=8, Minor:=5 ' Office 2010
        Case 15: ' Excel 2013"
        Case 16: ' Excel 2016 (Windows)
        Case Else ' Unknown '
       End Select
    On Error GoTo 0
   End With
End Sub

Alternatively the same coding could go in a normal code module, with a name such as Sub AddRefsByGUID(), and then..
The code above ( still in the ThisWorkbook code module ) would be simplified to

ThisWorkbook code Module:

Private Sub Workbook_Open()
 Call Sub AddRefsByGUID()
End Sub

Normal Code module coding for use in conjunction with code above

Sub AddRefsByGUID()
   With ThisWorkbook.VBProject.References
    On Error Resume Next '
       Select Case CLng(Val(Application.Version))
        Case 9: ' Excel 2000
        Case 10: ' Excel 2002
        Case 11: .AddFromguid GUID:="{00020905-0000-0000-C000-000000000046}", major:=8, Minor:=4 ' Office 2003
        Case 12: .AddFromguid GUID:="{00020905-0000-0000-C000-000000000046}", major:=8, Minor:=4 ' Office 2007
        Case 14: .AddFromguid GUID:="{00020905-0000-0000-C000-000000000046}", major:=8, Minor:=5 ' Office 2010
        Case 15: ' Excel 2013"
        Case 16: ' Excel 2016 (Windows)
        Case Else ' Unknown '
       End Select
    On Error GoTo 0
   End With
End Sub

Note: For this way to work when sharing Files the supplied file must either have the Library checked that will not be broken at the recipient ( in which case the code has no effect!! ) OR none of the Library versions should be checked.
This removal can be done programmatically. This and a few other ways to automate the process to allow Early Binding code sharing between different Excel versions are given here:
https://www.excelforum.com/excel-pro...ml#post4821675








Ref
http://www.excelfox.com/forum/showth...0547#post10547
Stuff Rory and snb told me about