I have three demo codes below which are addressing the issues of this Thread. I think. They all attempt to do the same thing, which is to add a code line at the end of a code module
The first routine, Sub ErlyBerly_1() , I think we are all happy with. Its your “Early Binding” way and all works well. ( as long as you have the reference checked to the “..Microsoft Visual Basic for Applications Extensibility 5.3, Class Name: VBIDE ..” thing ) So that I think is OK, no problem there…
The second routine, Sub DoItLate_2() , is a typical way we seem to do things in Late Binding. In this particular example it doesn’t work. It doesn’t work in the usual way that things like this sometimes don’t work. I know parrot fashion the explanations typically given. For convenience I put in some error handling and Help getting stuff in the code so it gives you the usual documentation stuff telling you why. But I can’t understand that yet. So
Question 1 ( regarding routine Sub DoItLate_2() )
Can anyone offer an explanation in more simple terms that I might be able to understand for this sort of not working Late Binding scenario
The third code, Sub DoItSomehow_3(), works and does not need the reference set.
Question 2 ( regarding last routine Sub DoItSomehow_3() )
What is that third code doing? ( My guess was / is that it is not directly to with Binding issues and is just assigning a variable to an existing object.. If I am right, then it is not a case of “the code working in Late Binding” it is rather a case of the object that is already there having the properties that we want. For convenience we assign a variable to that object. )
( Edit: I might be also saying here that if you are not using CreateObject(“ Library.Object ” ) then you are not doing late binding. I might also be saying that if you are Setting an object, it doesn’t necessarily mean that you are doing Binding. Maybe you are. I don't know. I don't know 100% what Binding means )
Question 3 @ Stuart – which way are you saying is .. harder to maintain and update
( I doubt I understand all the issues here. The only bit I thought I understood was that Late Binding was probably better in sharing as the CreateObject(“ Library.Object ” ) takes a string which means it only does stuff at run time, and what it does is look for the Object in the Library so it likely finds whatever version is available.. )StuartR wrote: the code will be much harder to maintain and update in the future.
Thanks
Alan
Codes: ( also in module objectBindingIssues ) in attached file
Code: Select all
' Cuds to put a single line of ' comments text at end of module
Sub ErlyBerly_1() ' _1 Early Binding needs ref to Microsoft Visual Basic for Applications Extensibility 5.3, Class Name: VBIDE
Dim CudMod As VBIDE.CodeModule
Set CudMod = ThisWorkbook.VBProject.VBE.ActiveCodePane.CodeModule
CudMod.InsertLines Line:=CudMod.CountOfLines + 473855, String:="' Done it with Early Binding"
End Sub
'
Sub DoItLate_2() ' _2 Late Binding. Does not need ref
On Error GoTo Bed
Dim CudMod As Object
Set CudMod = CreateObject("VBIDE.CodeModule")
CudMod.InsertLines Line:=CudMod.CountOfLines + 42, String:="' Done it with Late Binding"
Exit Sub
Bed: ' error handling cud section to get error info
MsgBox Prompt:=Err.Number & vbCrLf & Err.Description: Debug.Print Err.Number & vbCrLf & Err.Description
Application.Help HelpFile:=Err.HelpFile, HelpContextID:=Err.HelpContext
End Sub
'
Sub DoItSomehow_3() ' _3 It works but i am not quite sure what this is in terms of Binding issues. Does not need ref
Dim CudMod As Object
Set CudMod = ThisWorkbook.VBProject.VBE.ActiveCodePane.CodeModule
CudMod.InsertLines Line:=CudMod.CountOfLines + 999, String:="' Done it somehow"
End Sub
' http://www.excelfox.com/forum/showthread.php/2295-ExtendingInsensibility-into-Code-modules-Copy-table-contents-to-VBIDE-VB-Editor-code-modules
' http://www.excelfox.com/forum/showthread.php/2240-VBA-referring-to-external-shared-Libraries-1)-Early-1-5)-Laterly-Early-and-2)-Late-Binding-Techniques
' Done it with Early Binding
' Done it somehow