To Object or not to object

[Doc.AElstein]

….After sleeping on this I am not sure if I 100% understand the things which we have been talking about here. I have 2( 3 ) questions ….

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

the code will be much harder to maintain and update in the future.

( 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.. )

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

[HansV]

1) In DoItLate_2, you create a new object of type VBIDE.CodeModule, but unlike in ErlyBerly_1 and in DoItSomehow_3, you don't specify WHICH code module it is. VBA cannot insert lines in an unspecified module - it simply doesn't know where the lines should go.

2) DoItSomehow_3 is a simple example of late binding - it uses the VBIDE library without a reference to it.

3) If you use early binding, and declare

Dim CudMod As VBIDE.CodeModule

and then type CudMod followed by a dot (point, period), the VBIDE will display a popup list of properties and methods of the CodeModule object.

S2477.png

This makes it easier to write and maintain code: you can see which properties and methods are valid.

If you use late binding (no reference) and declare

Dim CudMod As Object

and then type CudMode followed by a dot, nothing pops up.

S2478.png

This makes it harder to write and maintain code: the VBIDE doesn't show you which properties and methods are valid, so you have to look that up elsewhere.

[Doc.AElstein]

Hi Hans…
_1)
I am still not quite sure what is going on here, but I can now see what you mean that there is a difference in the Set in ErlyBerly_1 and in DoItSomehow_3 in that they are specifying a specific code module. I had overlooked that. Clearly that is important. I had missed that. With that in mind I will have another think about that.
I can see immediately now after what you have said, that if that code line, ( Set CudMod = CreateObject("VBIDE.CodeModule") ) did somehow work, then the next line ( CudMod.InsertLines L…. ) , clearly would not for the reason that you say: ….VBA cannot insert lines in an unspecified module - it simply doesn't know where the lines should go. ……Thanks for pointing that out. I had overlooked that.

It is still is not clear to me yet why ( Set CudMod = CreateObject("VBIDE.CodeModule") does not work. But I can see now that the line after would not work as intended.
I expect if I go off and think about this then I may now or later get the point.
I expect if I had more experience in actually using CreateObject(" “), then it might be more obvious to me what things would not work in it.


_2)
In Sub DoItSomehow_3() , CudMod does not have access to all in the VBIDE Library, but it does have access to and use some of the VBIDE library without a reference. And it has that possibility to do that as a result of doing

Code: Select all

 Dim CudMod As Object
 Set CudMod = ThisWorkbook.VBProject.VBE.ActiveCodePane.CodeModule

So possibly this could be regarded as a form of Late Binding, depending on exactly what we define late binding as. I am still not totally sure why in Sub DoItSomehow_3() we are not actually simply setting a variable to an object and not doing anything to do with binding. But maybe I will after I have another think now, based on what you have said.


_3)
Ah, I think I get the point now. I think I was simply misunderstanding what was meant by “maintaining” the code. If “maintaining” the code means doing stuff to it later as well as initially, then , I can see the point Stuart was making. That is all clear now. Thanks. I was just misunderstanding what maintaining was referring to in this context. I probably was simply mistakenly thinking that maintaining was partly talking about supporting the code after giving it to someone, which might be slightly harder if you use Early Binding.

The other two questions , _1 and _2 , I will have a long think about again , and then I may understand a bit better.

Thanks very much for the reply

Alan

[HansV]

You cannot create an object of type VBIDE.CodeModule by using CreateObject. To create a new module, you have to add it to the VB Project of a specific workbook:

Set CudMod = ThisWorkbook.VBProject.VBComponents.Add(1)

Here, 1 is the numeric value of the symbolic constant vbext_ct_StdModule. If you use early binding, you can use

Set CudMod = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_StdModule)

You could then add code to this new module, etc.

In the first and third procedure, you do not create a new module, but refer to an already existing module - the one in the active code window:

Set CudMod = ThisWorkbook.VBProject.VBE.ActiveCodePane.CodeModule

So your second procedure is trying to do something different than the first and third ones.

[LisaGreen]

No, you don't. But keep in mind that the code will be more difficult to write, since you can't rely on IntelliSense.

And, of course, the code will be much harder to maintain and update in the future.

@StuatR

Curious Stuart. Can you explain a little more please?

Lisa

[HansV]

See my reply Post=244196 in this thread.

[LisaGreen]

Hello Hans,

Yes. Thank you. But I was wondering how it specifically applied to maintenance and update. Just intellisence? I'm going to go over that post again I think.

Lisa

[StuartR]

Curious Stuart. Can you explain a little more please?

Intellisense is one thing that makes maintenance easier. The other is the use of named constants.
For example, using early binding you might have code that uses constants such as wdAlignParagraphCentre or wdAlignParagraphLeft. If you used late binding these would just be the values 1 and 0. If you are very good at commenting your code then this might be easy to follow, but using named constants makes it much easier to read.

[LisaGreen]

Thank you Stuart!

I learned, to my cost, that VBS doesn't have any of the nice bits! You have to define everything all over. The object browser is a treasure!

Lisa

[Doc.AElstein]

cannot create VBIDE.CodeModule using CreateObject ...you have to add .... your second procedure is trying to do something different than the first and third ones.

Thanks for the extra info Hans. Its very helpful. I think I see from what you say that it works in a similar way to that for adding a worksheet.
And also, as you said before, what I was trying to do is pretty stupid with hindsight – as you said, I can’t create an object of a type and then try to add code lines to a type of code module. That is stupid.

I still can’t get 100% clear in my head whether my last code Sub DoItSomehow_3() is doing anything to do with binding at all . It is doing something similar to this for the case of a worksheet

Code: Select all

Sub WsIsThisLateBindingIDoubtIt()
Dim Ws As Object
 Set Ws = ActiveSheet
End Sub

I don’t know if that can be regarded as late binding. Maybe the answer is that the code above, Sub WsIsThisLateBindingIDoubtIt() , cannot be considered Late Binding, as the VBA library is already effectively checked. We sort of have Early binding already done for us in the case of all the VBA workbooks, worksheets etc…
But in the case of the VBIDE we don’t have it checked by default. So on that argument, possibly something like this could be regarded as a sort of late Binding. Possibly it is open to personal interpretation to some extent.

Code: Select all

Sub SortOfLateBindingProbably()
Dim CudMod As Object
 Set CudMod = ThisWorkbook.VBProject.VBE.ActiveCodePane.CodeModule
End Sub

_.___
It appears that I cannot get anything to do with VBIDE, not even VBIDE itself to work with anything looking of this form
CreateObject("VBIDE*****")
This might be telling me that I can’t really do Late Binding with VBIDE, and the reason lies in that info which my error handler in code Sub DoItLate_2() chucks up

Another conclusion, in the “object” area of things, from all this perhaps, is to support what I have occasionally been told by VBA experts, which is that the object is a somewhat imprecise concept in VBA.

[HansV]

(A) You can do this (from Word, for example):

Dim xl As Object
Set xl = CreateObject("Excel.Application")

This will create an (invisible) instance of Excel. This is possible because Excel is an application that can run by itself.

But you can't do this:

Dim ws As Object
Set ws = CreateObject("Excel.Worksheet")

since an Excel worksheet cannot exist by itself. It is always a worksheet in an existing workbook.

(B) Like a worksheet, the VBProject object cannot exist by itself. It is always the VBProject of an existing Excel workbook (or Word document, or PowerPoint presentation, etc.). You create the VBProject by creating or recording a macro or by inserting a userform. When you have done that, the workbook/document/presentation has a single VBProject that you can refer to without setting a reference to the Microsoft Visual Basic for Applications Extensibility 5.3 Library. Unlike a worksheet, you cannot add a second VBProject to a workbook.

[Doc.AElstein]

Thanks again Hans. I think I half knew some of that, but it does help to get the thing more clear in my mind when you explain it clearly like that …._
_.... I think this bit of what you said , …. Like a worksheet, the VBProject object cannot exist by itself. ….. … might be something important to bear in mind when understanding whether the CreateObject("****") is likely to be allowed to work.
( Someone told me once that they thought it might be theoretically possible to allow you to make a New instance of a worksheet “ on its own “ as it were, but that would be incredibly complicated, requiring the making of other stuff something similar to an entire Excel application. So that is probably why Microsoft don’t let you do it ). As I understand it, the .Add Method is a way for Microsoft to control how an object is created. In most cases, I guess this means that the object will be part of some existing thing already there.
In the case of the objects we have been talking about, they are possibly somehow all part of this “ OLE COM ActiveX embedding god knows what stuff ” but Microsoft want to limit how much of this embedding we can do ourselves.
I though maybe a worksheet might be one of these “ OLE COM ActiveX embedding god knows what stuff ” things. Maybe it isn’t. I am not sure. It sounds like Excel and Word are. I am thinking this because of what you said about being able to do this from Word

.....You can do this (from Word, for example):
Dim xl As Object
Set xl = CreateObject("Excel.Application")

I have no idea of whether a VBA project or code module is a … “ OLE COM ActiveX embedding god knows what stuff ” Even if it is, it sounds like it is likely to be one that Microsoft don’t want us to use, as I expect is the case of a worksheet. If we were able to use the this “ OLE COM ActiveX embedding god knows what stuff ” thing that is a worksheet, then we could probably make something like Excel without buying it.
I note here, the typical error that I keep seeing …_
” Object creation by ActiveX component not possible “
_...maybe another way of saying that is ….._
” Microsoft don’t want you to be able to create one of these objects on its own. But you can .Add it to some software if you buy it “

I expect I may be getting close now to understanding as far as it is possible. I expect some things in VBA are not defined clearly enough for anyone to get a clear answer in some cases. But I am certainly feeling I have gained some sort of extra knowledge, so thanks again for the replies.

[Doc.AElstein]

A question left open is whether something like this is really Late Binding

Code: Select all

Sub SortOfLateBindingProbablyNotReally()
Dim CudMod As Object
 Set CudMod = ThisWorkbook.VBProject.VBE.ActiveCodePane.CodeModule
End Sub

I am currently still thinking that it might be open to interpretation about what binding is, which might be a slightly vague concept, as is the concept of an object.
_._____________

But I had a think…
I have used something called GetObject( , ) a few times effectively without really understanding what it is. I just re read the documentation on it and it seems to say it is fundamentally something to do with getting hold of one of those “ OLE COM ActiveX embedding god knows what stuff ” things. ( https://docs.microsoft.com/de-de/office ... t-function" onclick="window.open(this.href);return false; : “ Returns a reference to an object provided by an ActiveX component. “ )

I haven’t yet been able to do anything like this …_
Set CudMod = GetObject(, "VBIDE******")
_.. If I try, then I get the same error message ….
” Object creation by ActiveX component not possible “

So I had a think… The documentation say that if you try to get at an “ OLE COM ActiveX embedding god knows what stuff ” thing and one of them is already in use, then you get that one..
So I wrote these and they work: Possibly the first one could be considered as late Binding. The reason that I say that is that the GetObject(, takes a string argument. I read somewhere ( http://www.eileenslounge.com/viewtopic. ... 7&start=20" onclick="window.open(this.href);return false; ) that this means that the thing will be done at run time. ( That could be Late. Sometimes I run routines very late in the day. :) )
The second routine below, I would argue, is similar to my routines Sub SortOfLateBindingProbablyNotReally() and Sub DoItSomehow_3()
In fact all 4 routines ( Sub SortOfLateBindingProbablyNotReally() and Sub DoItSomehow_3() and the two below all do almost the same.
I would say that they are all probably not really Late Binding. But maybe the first below, Sub IAmGettingLostButMaybeThisIsSortOfAlsoNotReallyLateBinding() , could be thought of as the closest to Late Binding for a code module.

Code: Select all

 Sub IAmGettingLostButMaybeThisIsSortOfAlsoNotReallyLateBinding()
Dim objLisXL As Object, CudMod As Object
 Set objLisXL = GetObject(, "Excel.Application") '  Get at an "OLE COM ActiveX embedding god knows what" thing that i might be able to embed via .Add in the "OLE COM ActiveX embedding god knows what"  thing that i atually want
 Set CudMod = objLisXL.ActiveWorkbook.VBProject.VBE.ActiveCodePane.CodeModule ' I will not do the  .Add  because I don't want a new one
 CudMod.InsertLines Line:=CudMod.CountOfLines + 111, String:="' Done it some long winded way that may allow me to confuse somebody enougth to think I know what I am talking about"
 Set objLisXL = Nothing ' probably ought to do this or i might get into a pickle
End Sub







Sub IWouldSayThisIsNotReallyLateBinding()
Dim objLisXL As Object, CudMod As Object
 Set objLisXL = Excel.Application '  Because I missed out the  New  ( which would not work here anyway) , then this defaults to the active Excel Application
 Set CudMod = objLisXL.ActiveWorkbook.VBProject.VBE.ActiveCodePane.CodeModule
 CudMod.InsertLines Line:=CudMod.CountOfLines + 111, String:="' Now everyone is so confused that i can say what I want. So I will say that this is not late binding"
 Set objLisXL = Nothing ' probably ought to do this or i might get into a pickle
End Sub

I think another alternative explanation for my routines ( the ones that I would suggest are not really doing late binding ) is to say something like .. I have a

.....single VBProject that …can refer to without setting a reference to the Microsoft Visual Basic for Applications Extensibility 5.3 Library….. .

….I am referring to it via an object variable CodMod

[HansV]

GetObject can be used with early binding and late binding; that is not essential for its use.
GetObject can be used in two different ways:

(1) To get a reference to an existing file (document/workbook/…):

Dim MyObj As Object
Set MyObj = GetObject(PathName:="C:\Excel\MyWorkbook.xlsx")

(2) To get a reference to a running application:

Dim MyObj As Object
Set MyObj = GetObject(Class:="Excel.Application")

This example will only work if Excel is already running; if it isn't, you'll get an error. This can be used to check if an application is already running:

Code: Select all

    Dim objXL As Object
    ' Suppress error messages
    On Error Resume Next
    ' Try to get a reference to a running instance of Excel
    Set objXL = GetObject(Class:="Excel.Application")
    ' If Excel isn't running, objXL will be Nothing
    If objXL Is Nothing Then
        ' So we start Excel
        Set objXL = CreateObject("Excel.Application")
    End If
    ' Display error messages again
    On Error GoTo 0
    ' Do something with Excel
    ...

[Doc.AElstein]

Hello,

This was an interesting Thread that I have re looked at quite a few times… I can’t quite get a few things 100% clear, and I would like to ask three follow up questions…

_1 Early Binding is mostly / usually only useful for Intellisense and use of a few named constants.. ?
As far as I can tell, the only advantage I have of using Early Binding, ( allowing Diming as VBIDE items ) , for objects involved with the VB Editor , is that I have intellisense and a few named constants.
If I attempt to look much deeper into what is going on, I find that the way of using memory generally has somehow been optimised to allow for Late Binding, as apparently this is a very important integral part of how the whole Windows software currently works. It seems very questionable if there are, at the end of the day, any real noticeable efficiencies in memory of usage using the Early Binding, since the whole system has been optimised to allow Late binding, which is fundamental to the whole dll business…
Question 1 – Any comments on what I have just said.

_.____________________

_2
Question 2 Late Binding in using the VBIDE ?? Huh??
I am happy that I understand Early Binding for the case of programming in the VB Editor.
I am still not so convinced about Late Binding.

Late Binding in terms of VBA coding appears, it is said to be, ( at least by the average of all that is published about it that I have read) , as requiring two things
_ (i) the Dim … .. As Object
_(ii) the CreateObject("…. .. ")
I don’t have the possibility of doing _(ii) , so I don’t think I can Late Bind in VBA to the Microsoft Visual Basic for Applications Extensibility 5.3 VBIDE
Can someone please tell me if I am right or wrong. If wrong , then why please…
_.___________________

_3
Question 3
Are these Late Binding? I don’t think so.

Code: Select all

Dim Ws1 As Object
Set Ws1 = Worksheets(1)


Dim Rng as object
Set Rng =Worksheets(1).Range(“A1”)

Dim CudMod As Object
Set CudMod = Application.VBE.ActiveVBProject.VBComponents.VBE.ActiveCodePane.CodeModule

I would say that I am just assigning a variable to an already instantiated object. ( The thing has been instantiated by Mr Microsoft, or it is done automatically for me at the “Add” point ) ( If I like I can assign as many object variables as I like to the same object)
If anyone thinks that they are Late Binding, then can they explain to me why.

_.____________

I have been letting this run through my mind for a few weeks now, so there is no rush for an answer. I would really appreciate a full explanation to why, if I have anything wrong
At the end of the day , I am thinking, I can’t do Late Binding in the VBE, ( I mean I can’t Late Bind Microsoft Visual Basic for Applications Extensibility 5.3 VBIDE .. me thinks…. )

Thanks.
Alan

[HansV]

1) I have no idea about how Early/Late Binding affects memory usage.
Another advantage of Early Binding is syntax checking.

If x is declared as Outlook.Application, the VBE will know that x.CreateItem is valid, but x.CreatItem isn't.
If x is declared as Object, the VBE can't check that, and you'll only find out that x.CreatItem won't work when you get a runtime error.

[HansV]

2) When programming the VBE, there is no need to use CreateObject. Each Word document, Excel workbook etc. already has a VBProject, you don't have to create it.
If you don't set a reference to the Microsoft Visual Basic for Applications Extensibility 5.3 object library, you can still program the VBE. Since you're using late binding, you'll have to replace constants such as vbext_ct_StdModule with their value.

[HansV]

3) If you are writing code in Excel, there is no point in using

Dim Ws1 As Object
Set Ws1 = Worksheets(1)

In Excel, the Excel object library is ALWAYS loaded, so you might as well use

Dim Ws1 As Worksheet

But if you're writing code that automates Excel from Word, for example, it may be useful to use Late Binding and declare Ws1 as Worksheet.

The lines

Dim CudMod As Object
Set CudMod = Application.VBE.ActiveVBProject.VBComponents.VBE.ActiveCodePane.CodeModule

are a good example of late binding if you haven't set a reference to the Microsoft Visual Basic for Applications Extensibility 5.3 object library. Even without the reference, the lines will still work.

[Doc.AElstein]

Hi Hans

1) ..Another advantage of Early Binding is syntax checking.

Thanks, I had overlooked that.
_.___________________

I agree with most of the rest that you have written. I think I was thinking most of that, but you stating it clearly has helped get that all clear in my mind… almost ….


The one thing that I am still not convinced about or understand is
_A) why is this .._
Dim CudMod As Object
Set CudMod = Application.VBE.ActiveVBProject.VBComponents.VBE.ActiveCodePane.CodeModule
_.. Late Binding.
_B) If the answer is because…. example of late binding if you haven't set a reference to the Microsoft Visual Basic for Applications Extensibility 5.3 object library. Even without the reference, the lines will still work …….
_... then if that is the case, then what is this, 5 lots of Late Binding ?

Code: Select all

 Sub IsThis5lateBindings()
Dim Ob1 As Object, Ob2 As Object, Ob3 As Object, Ob4 As Object, Ob5 As Object
 Set Ob1 = ThisWorkbook.VBProject.VBE.ActiveCodePane.CodeModule
 Set Ob2 = Worksheets(1)
 Set Ob3 = ThisWorkbook.VBProject.VBE.ActiveCodePane.CodeModule
 Set Ob4 = Worksheets(1)
 Set Ob5 = Ob4
End Sub

I am still thinking that I cannot Late Bind to Microsoft Visual Basic for Applications Extensibility 5.3 object library.
I can use pre instantiated objects from it, as objects. This is what I am suggesting that this is…_
Dim CudMod As Object
Set CudMod = Application.VBE.ActiveVBProject.VBComponents.VBE.ActiveCodePane.CodeModule
_.. I am suggesting that I am not Late binding there. I am suggesting that I am assigning a variable to a pre instantiated object. ( The Code Module object is an object which I am not allowed to instantiate myself directly, as is also the case for a Worksheet )

I suspect I am most likely wrong with saying “ I cannot Late Bind to Microsoft Visual Basic for Applications Extensibility 5.3 object library. ”…. But I cannot yet understand why I am wrong? ( or maybe I can suggest an explanation *** )

I expect I may have overlooked something obvious. Possibly the penny will drop eventually. Thanks for the replies

Alan

_.__________________

***P.S: Maybe there is a written definition somewhere that says that one possible definition of Late Binding is that of…. using an object from a library when you do not have the reference checked for it. … if such a definition exists then we could say that in this: …_
Dim Ob1 As Object, Ob2 As Object
Set Ob1 = ThisWorkbook.VBProject.VBE.ActiveCodePane.CodeModule
Set Ob2 = Worksheets(1)
_.... Ob1 is late binding, whereas Ob2 isn’t beacause in the case of Ob2 we have the Library checked for that ( Visual Basic For Applications VBA , possibly?? )

[HansV]

I do not think that I can make it clearer than I already tried to do.