1 Attachment(s)
VBA referring to external shared Libraries, 1) Early, 1.5) Laterly Early and 2) Late Binding Techniques
See here for examples of how to bind specific Libraries: http://www.excelfox.com/forum/showth...ll=1#post10970
VBA referring to external shared Libraries, 1) Early, 1.5) Laterly Early and 2) Late Binding Techniques
I have been doing things like this a bit Lately, or more often Early, Laterly, and I got a bit confused: One usually hears of the two ways, Early and Late Binding, but then when one actually comes to “do it” you see that there are a few ways based on those two basic ways..
So I wrote myself some notes and thought I would share them.
Background. What is a “Library” in VBA
If you are happy with what you think is a Library and have a vague idea about Early and Late Binding in VBA, and / or maybe have used the “libraries available in a VBA code” one once or twice, and/ or are not too bothered with the background ideas, then you might want to skip this post.
You don’t need to know it and I am probably a bit wrong with some of the things as I made most of it up from inspired guessing and listening to the best experts who mostly don’t know what they are talking about.
Shared Libraries
Quite often in computing, and certainly in Windows, different software packages share stuff. This can go loosely under the term of shared libraries , but Libraries in this sense is not quite ( yet – maybe a bit laterly at runtime ) our Libraries
Background . The End Goal. What are we trying to do?
In many cases what we finally want to do is have an object variable in our coding which can be used in similar ways to one of the large standard available VBA objects.
As a simple explanation: Imagine you wanted to use the range object, but for some reason is was not available in the standard VBA library. ( The standard VBA library is one of ( usually 4 ) such libraries that is always made available by default). If it wasn’t available in the standard libraries, then it very likely would be in one the massive amount of additional libraries available. What Binding does is organises that you have any additional Libraries available to a code. Typically then from those you could organise to have an object from it in your code. That is usually the end goal: To have an object, ( or possibly a few ) in your code that is in one of the additionally “available to VBA Libraries”
Linking and Embedding Component Model Object, LECMO
I think maybe about 25 years ago Microsoft tried to
_ a) get in early and make it difficult for anyone else to do anything,
and they
_ b) tried to confuse anyone trying as well with poor choice of words.
_ a) Things were organised so that they could be Linked and Embedded , and a bit later that lead to Models of how Components needed to be. So all were forced into making sure interfaces fitted.
_b) the word Object was thrown in to confuse with the word used in Object Orientated Programming which is not quite meant to be the same, well maybe Laterly later.
The end result is some technology named as Object Linking and Embedding and then the Component Object Model
The idea of the COM is that things ( objects ) can implement their functionality through the interfaces.
In the meantime everyone forgot what actually went on and the OLE is often referred to as the later stuff coming from the COM stuff. The word OLE is more likely used to describe something when you are not quite sure what it is you are talking about.
Maybe at the time, OLE had some thing to do with something understandable like making a simple copy and paste work between different things. But as the workings of the clipboard puzzle some of the best computer experts , then even that is suspect. If you want to try and look at COM as some logical progression from OLE, then you could think of that allowing say an Excel Worksheet to be in a Word document without ever opening the Excel application. You could also think of something disgusting, as I just did, if you want to. That might be just as relevant to the discussion at hand.
Previous to all this , and more classically in computing , a compilation, that is to say production of a final runable lower level computer program, would have the complied equivalents of copies of all the things ( functions ) from the shared libraries.
However the COM and OLE technologies allow for files which are effectively runable allowing a link at runtime to required functions. These functions are typically, ( but not always ) held in Folders or Files with the .dll extension.
The former case (“ “static” link libraries “ ) could lead to a duplication of complied functions.
This basic idea was then found to be useful do more pseudo real time things – allowing interaction , for example with things in the internet. Without this technology, you could, to a first approximation expect to have to pseudo download a lot previously and store , and possible re organise it, such that a running code could use things from the source
ActiveX Bollox
Parallel to the LECMO technologies, or partially developed from it, where technologies optimised for internet use. This goes by the name of ActiveX.
In fact you will find that the word ActiveX can almost be used for many things and usually is when the person may know what he is talking about but can’t easily explain it. It is like using expressions like “this shit” or “that bollox” in normal conversations, an “ActiveX crap” would be equivalent to the former two expressions. Bollox = crap = shit = ActiveX to a first approximation
In fact, the object, which having available is our final goal, can be referred to as an ActiveX object.
Although it was optimised for internet “communication” of functionalities, it more or less took over the computer control generally.
Most things then are somehow controlled by these things.
In fact this developed into a cancerous type growth into the whole computer system: Visual Basic provides polymorphism (polymorphism : here approximately the ability to do slightly different variations of the same thing using the same basic instructions, like a universal control Button should be possible to make which on the view of different uses in different situation does a very similar thing, but “behind the interface” might be a lot of stuff to make that button universal ) through these multiple ActiveX interfaces. In the Component Object Model (COM) that forms the infrastructure of the ActiveX specification, multiple interfaces allow systems of software components to evolve and break existing code. ……. In this sense interface is a set of related properties and methods. Much of the ActiveX specification is concerned with implementing standard interfaces to obtain system services or to provide malfunctionality to other programs. It allows for the possibility , for example, for updates to break things.
“VBA available Libraries” of things available to us, “application ( Excel ) programmers”
We might be loosely called “application ( Excel ) programmers”.
We, as application programmers, can access directly or indirectly a lot of the software used to create the application in the first place . ( A running or “open” Excel is often referred to as an application, or the Excel application. I suppose it is talking about the software being applied ). The term “Application Programmes Interface” as well as expressions of the form us “having access to the Dynalic ( or direct ) Link Libraries” are loose and intimidating term used, amounts other things, generally to us referencing the runable files held often , but not always, in Folders or files with the letters ddl in their names.
Broadly speaking we can get at them in two different ways summarised below. In simple terms we set a fixed reference or path or route to get at the tools at runtime
Using one or more dlls in a VBA code
The basic fundamental singular case,
It is possible to get at a specific single or a few similar dll files function runable things.. To do this in VBA code an initial code line something similar to a Dim code line is done. This is a Declare type code line at the top of a code module. After that an “API Function” is used in a code similar to a normal VBA Function
( For example http://www.excelfox.com/forum/showth...tion#post10465 ).
Our aim is to get at a lot of those functions, those related to some common “thing”, as well as possibly a few other things. In simple terms, the Libraries we are talking about are an attempt to organise in what might be typically required collections of those functions and other things, such as some additional coding that might use a few of those things
“Libraries available to VBA”
We are talking basically about these things:
1 Tools 2 referrences 3 Scroll Find Check.JPG . https://imgur.com/jyLjdBb
Attachment 2001#
The word Library is used sometimes like Object a bit loosely to help contribute to the Obfuscation technology used in most of the things being discussed here.
The word Library is used often when use of dll stuff is bundled up into something like a collection of Functions and other stuff that include things that use one or more of similar dlls or use dlls that do similar stuff. This is organised something like a Visual Basic Class Object, ( An example of such a Class Object is a VBA Class Object called Worksheet ) with the available Methods Properties Constants and Functions etc which can be made available to any VBA code. The collection goes by the name of a Libraray. Specifically it is one of those Libraries that you often hear about as requiring a check against in the way of … “…… -- VB Editor -- Tools ---- references --- scroll down and check the appropriate library from the massive list of those available….”……
As far as in VBA is concerned, the aim is to have the available “API ddl” stuff available in a code via a Object.
Often instead of calling these things Libraries they are referred to themselves as an API. So one can see that the term is used in a very loose Obfuscation type way.
It is the way of getting access in a code to this second larger group of tools that is concerned with the Terms Late, Early Late, and Late Binding.
The end result is having access to the tools in an Object Orientated Programming way such as we would everything else in our code. Indeed if you look initially at the checked items in the list, you will always see a few default libraries checked.
The checked things are to some extent intuitive as being reasonable in the first place for our codes to have any chance of working. In particular the Visual Basic For Applications is a direct example of a “Libraries available to VBA”. You can in fact add an extra VBA. at the start of many code lines to completely explicitly reference the library to be sure you are accessing the Visual Basic For Applications library.****
The start point of getting access to other libraries in a code will be therefore to organise that we have objects variables in our code which similarly can be accessed in code via:
___= ThatLibraryObject.StufffromItIWant
_._______________________
1 Attachment(s)
Referring/ making available the "VBA available Libraries". Late and Early Binding
Referring/ making available the "VBA available Libraries". Late and Early Binding.
The terms Early and Late Binding are often heard in connection with making available the “VBA available Libraries”.
In this post I am comparing those and explaining 4 ways to do Binding, 2 using Early and 2 using Late.
Because of the nature of the “direct link libraries” principle there is not a major difference and in most cases theses two basic ways can be used interchangeably.
A final “product” of Binding, a final goal, is to have a variable object available in the code which has the Methods and Properties available in the usual object orientated programming way:
___ ThatLibraryObject.StufffromItIWant
There are several ways to achieve this, some using the Early and some using the Late as well as a pseudo “Laterly Early” “Binding” way. I am discussing the 4 common ways, 2 using Early and 2 using late
Early and Late Binding General Considerastiions: Early V Late Binding
The main difference in the definition here lies in how/when the object is created.
1) Early Binding
Early Binding will require the ….” .. -- VB Editor -- Tools ---- references --- scroll down and check the appropriate library from the massive list of those available ..”… stuff
1 Tools 2 referrences 3 Scroll Find Check.JPG . https://imgur.com/jyLjdBb
Attachment 2001
Early somehow makes the library links “known” to VBA before any code writing is done. The most noticeable distinguishing characteristics are then that a variable can be declared ( Dim’d ) as an object from that library, and that intellisense will work thereafter.
Apparently this way makes a code faster than the Late Binding way, but I expect other factors concerning the implementation may influence this. So using Early Binding will result in the complied code effectively having a “complied link” section.
A variable must generally be used to assign the object type to. This could be chosen to be of Object type , but intellisense will not work and then it effectively becomes a form of Late Binding: Sometimes the definition is contested by some, but generally the documentation says words to the effect that when a variable is declared to be of a specific object type, the compiler allocates memory and perform other optimizations before an application executes. The various dynamic help including intellisense is not available for a declaration of Object type. This is then the definition and characteristics of early Binding.
Early Binding 1)(a) Set way. In the simplest form of Early Binding we would do something like this in our code, pseudo
Dim myThing As SpecificClassOrObject
_ Set myThing = NewInstance of SpecificClassOrObject
The first line effectively makes the variable of the type required, and the second effectively fills the variable creating an actual instance of it. Instance in this respect means that everything possibly with the thing is made available. The Dim is done at compile and so Excel VBA Knows about / is aware of it allowing intellisense. The Set effectively “makes it live.”
Early Binding 1)(b) Auto instancing Early binding “Way Dim” ( using an auto instancing variable )
Dim myThing As New SpecificClassOrObject
Excel VBA knows about the type once again. But the instance has not been created. VBA is written generally in such a way that if it “knows” about a type but does not have it instance , it
_(i) has no record of it being instanced, will always check on encountering the variable,
and
_(ii) if it has not been instance then it will do so. ( But nether the less on encountering it will still check every time.
This “Way Dim” may make a code run a little slower.
The main disadvantage of the Early Binding are in sharing when you cannot control what is checked in the library list of the recipient. Indeed they may have a version of Office that either does not have the library or may have a different version.
( In general Excel will update a recognised library to a newer version but not always the other way around, at least for libraries other than the main ones. )
2) Late Binding
The main distinguishing characteristic of the Late binding is that a function , ( CreateObject(“ “) ) , ( sometimes referred to as a method ) is used at run time to return the necessary reference to the object. So in the simplest form we have, if using a variable , we have Pseudo
Late Binding 2a) Variable way.
Dim myThing As Object
_ Set myThing = CreateObject(“SpecificClassOrObject”,”ServerIfNotThisComputer”)
or like more commonly seen
Dim myThing As Object
_ Set myThing = CreateObject(“LibraryName.ObjectfromItIWant__”)
As the argument is taken as a string, it is, as is usual in compilation, ignored. The compilation can then have no knowledge of the object type.
In terms close to what happens is that windows has an entry in a registry that associates the string with the dll that implements this object. Sometimes larger things don’t install named class entries in the registry for its objects. The CreateObject(“ “) will also take its unique Class ID (CLSID) if you know it , in this form , for example for a data object, CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
The usual disadvantage of Late Binding given is the lack of intellisense.
The usual advantage given is not having to have the appropriate library in the reference list checked.
A further feature, debatably an advantage of sorts is that the function can be embedded in a code line in a typical Object Orientated Hierarchy way such that no object variable for the main object must be used, pseudo
Late Binding 2b) With__End With way
_ PropertyOrXvalue = CreateObject(“LibraryName.ObjectfromItIWant__”).ThatPropertormethodOfObject(PossiblyArgumentsEtc)
' or
__With CreateObject(“LibraryName.ObjectfromItIWant__”)
___PropertyOrXvalue = .ThatPropertormethodOfObject(PossiblyArgumentsEtc)
__End With
Code:
' Late Binding 2b) With__End With way
PropertyOrXvalue = CreateObject(“LibraryName.ObjectfromItIWant__”).ThatPropertormethodOfObject(PossiblyArgumentsEtc)
' or
With CreateObject(“LibraryName.ObjectfromItIWant__”)
PropertyOrXvalue = .ThatPropertormethodOfObject(PossiblyArgumentsEtc)
End With
_.____
Available object constant variables in Early Binding
A final minor consideration in the comparison of the Early and Late Binding concerns some simple constant variables. Some libraries contain some of these. Using Early Binding these are available. As the compilation has no knowledge of the object type in Late Binding, those variables are not “put in” the Object variable. These constants are usually simple numbers, and I personally would tend to use the literal numbers in a code anyway, be it Early or Late Blinded.
Sometimes the names of the variable can give an indication of what they are, but I would tend to write the variable name of any I use and possibly further explanations of it in the ' comments
Scope Issues and Early and late Binding, Public Pubics.
In all ways that use a variable, that is to say all the Early Binding ways, 1a) 1b) and the first shown way of Late Binding 2a) , the declare, ( Dim ) , statement could be inside a routine or at the beginning of a Module.
If it were at the beginning of a normal code Module, then the object would be available in all codes which would be defined as Pubic scope.
If it were within a routine, then the object would be available in that routine which would be defined as Private scope.
The last shown ways of Late Binding, 2b) , which do not use a variable can only be Private scope as it is not possible to have a the outside the CreateObject(“ “) outside of a routine.
( Note in passing that within a Class module we can pseudo have the Pubic scope by declaring as
Pubic myThing = xxxxx
This has the effect of making our object an object in that Class. It can then be referenced in all codes , for example if it is in the ThisWorkbook class module by
__ ThisWorkbook.myThing
Technically this may not be a Pubic variable, but to all practical intents and purposes it is . I have found some novel uses of it actually as you can use sometimes something like Sheet1.myThing in a code which exposes the interface and sets something off in a way that is not always possible with a true Pubic variable.
1 Attachment(s)
1.5) Laterly Early Binding
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
Code:
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:
Code:
Private Sub Workbook_Open()
Call Sub AddRefsByGUID()
End Sub
Normal Code module coding for use in conjunction with code above
Code:
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
Example VBIDE Microsoft Visual Basic for Applications Extensibility 5.3 For coding coding
Example VBIDE Microsoft Visual Basic for Applications Extensibility 5.3 For coding coding
Based on notes here http://www.excelfox.com/forum/showth...r-code-modules and a discussion here http://www.eileenslounge.com/viewtopic.php?f=30&t=31547
This is a good example to show the inexactness and sometimes uncertainty amongst even experts in the theme of Binding…
_a) Unlike often seen in literature, we can in fact do things in the VB Editor without a reference to this Library "Microsoft Visual Basic for Applications Extensibility 5.3" , ( Class Name: VBIDE ). It is also not at all clear at all if strictly speaking we can do Early Binding in this case.
_b) Also I am not convinced that we can do Late binding either. In a very indirectly way we could argue that somebody once maybe did a reference to "Microsoft Visual Basic for Applications Extensibility 5.3" to get the things there in front of us..
Before we start, lets look at the Laterly Early Binding Technique , as this appears to be most useful to have to hand.
At the same time we will remind ourselves that doing anything to references empties global variables because it resets the VBA project..
Here is a set of fairly self explanatory demo routines. All routines should be run sequentially in the order given. They attempt to remove and add the reference to VBIDE , "Microsoft Visual Basic for Applications Extensibility 5.3"
At he end we have the reference set to VBIDE , "Microsoft Visual Basic for Applications Extensibility 5.3"
Code:
Option Explicit
Dim StringyGloby As String
Sub FillMeGloby()
Let StringyGloby = "Hi, I am here"
End Sub
Sub WotsMeGloby()
MsgBox Prompt:=StringyGloby
End Sub
Sub TakeItOff()
Dim objThisVBAProject As Object: Set objThisVBAProject = Application.VBE.ActiveVBProject
Dim MyToolBoxRefutations As Object: Set MyToolBoxRefutations = objThisVBAProject.References
Dim Tool As Object, TrashtVBIDE As Boolean: Let TrashtVBIDE = False
For Each Tool In MyToolBoxRefutations
Dim ToolGUID As String
Let ToolGUID = Tool.GUID
If ToolGUID = "{0002E157-0000-0000-C000-000000000046}" Then
MyToolBoxRefutations.Remove Tool ' Remove reference for "Microsoft Visual Basic for Applications Extensibility 5.3"
MsgBox Prompt:="You will find that your globies are empty because you took off the referrence and in doing so reset the VBA project at the end of this procedure"
Let TrashtVBIDE = True
Else
End If
Next Tool
If TrashtVBIDE = False Then MsgBox Prompt:="You did not remove the referrence, so presumably it was not checked, and so at the end of this procedure your globies should be intact"
End Sub
Sub WotsMeGloby2andPhilIt()
MsgBox Prompt:=StringyGloby
Let StringyGloby = "Hi, I am here"
End Sub
Sub LaterlyEarlyBinding()
' Add the referrence to Microsoft Visual Basic for Applications Extensibility 5.3 VBIDE if it is not already there. Note: This will take into efffect when this procedure ends
With ThisWorkbook.VBProject.References
On Error Resume Next ' Just in case you already have it checked, as the next code line would error if yoou had it checked
.AddFromguid GUID:="{0002E157-0000-0000-C000-000000000046}", Major:=5, Minor:=3 '
If Err.Description = "" Then ' this section must go before On Error GoTo 0 because On Error GotTo 0 will empty Err registers
MsgBox Prompt:="You have added a referrence so at the end of this procedure the VBA project will be reset and your globies will be empty"
Else
MsgBox Prompt:="You did not add the referrenc so presumably it was checked already and your globies should be intact"
End If
On Error GoTo 0
End With
End Sub
Sub WotsMeGloby3andPhilIt()
MsgBox Prompt:=StringyGloby
Let StringyGloby = "Hi, I am here"
End Sub
Running those procedures should get us at the start point of looking at Binding issues.
The following procedures address these issues
As far as I can tell,
the following is sometimes regarded as Early Binding
Code:
Sub EarlyBirly()' Early Binding to Microsoft Visual Basic for Applications Extensibility 5.3 VBIDE
Dim vbcmlCodeModule As VBIDE.CodeModule
' Set vbcmlCodeModule = New VBIDE.CodeModule ' I am not allowed to do this
Set vbcmlCodeModule = Application.VBE.ActiveVBProject.VBComponents.VBE.ActiveCodePane.CodeModule
MsgBox Prompt:=vbcmlCodeModule.CountOfLines ' Count of number of used lines in this code module
MsgBox Prompt:=VBIDE.vbext_pk_Proc
End Sub
The last Message box uses the one of only things that I have, ( apart from Intellisense ), which I have as extra using Early Binding: I can use some constants which are predefined in the library, instead of having to use the constants you use their literal values instead. ( In this case the literal value is that identifying a procedure type of the type normal Sub or Function
I am not allowed to use New. This has been "decided by Microsoft". The Instantiating has effectively been done when the VBA Project was created.
As far as I can tell, apart from the use of the named constants, I can do all without this Early Binding that I can with Early Binding.
( Another advantage that I have if I do it this way , is that typing mistakes in things from that library will be noticed at compile, as apposed to at runtime if I do not have the reference checked )
We should note finally that we are prevented ( by Microsoft ) in using New in the Set code line. Effectively The Set = New __ code line has been done for us. We are then only permitted to assign a variable to that of the VBIDE object. As we are using a VBIDE object type in the Dim, it is sometimes regarded as Early Binding.
It is typical of objects which are dependant on others and cannot exist alone , that we are prevented by Microsoft from creating new instances of them.
( Not ) Late Binding in VB Editor
There is no clear definition or statement as to whether there is such a thing as Late Binding to the Microsoft Visual Basic for Applications Extensibility 5.3 VBIDE
We are prevented ( by Microsoft ) of either using New or using CreateObject("…. .. "). It is typical of objects which are dependant on others and cannot exist alone , that we are prevented by Microsoft from creating new instances of them.
It appears that we can do anything to the VB Editor without Early Binding, ( that is to say without a reference to the Microsoft Visual Basic for Applications Extensibility 5.3 VBIDE ) that we can with early Binding.
If you wish to , you can say that the following routine is using Late Binding. It is up to debate. You could explain it as Late Binding by saying it is Late Binding because it is making use of an object without the use of a reference to the appropriate Library.
An alternative explanation of the situation would simply to say that we are referring to an existing object. As such, Binding issues are no longer relevant..
Code:
Sub LateNotEarly() ' http://www.eileenslounge.com/viewtopic.php?f=30&t=31547&p=246518#p246518
MsgBox Prompt:=Application.VBE.ActiveVBProject.VBComponents.VBE.ActiveCodePane.CodeModule.CountOfLines ' Count of number of used lines in this code module
Dim vbcmlCodeModule As Object
Set vbcmlCodeModule = Application.VBE.ActiveVBProject.VBComponents.VBE.ActiveCodePane.CodeModule
MsgBox Prompt:=vbcmlCodeModule.CountOfLines ' Count of number of used lines in this code module
'MsgBox Prompt:=VBIDE.vbext_pk_Proc ' I cannot do this with .. this...not Early Binding
End Sub
Understanding automation ,: Error 429 Object creation by ActiveX component not possible
Attempting to use New or CreateObject( .. .. ) or ( even GetObject( .. ) ) will fail, typically with the shown error _ 429 Object creation by ActiveX component not possible.
The technical terminology is that their "Automation Object Linking and Embedding, or Automation OLE or Automation" is not "exposed". In other words an object from the Class of this thing which you are trying to assign to a variable is not made available to be included in another thing.
This possibly explains why it is difficult to give a precise definition of Late Binding in some cases.
The ability to do almost anything is windows is attributed to optimisation of memory to allow for runtime getting of things. Clearly there are conflicts here in any attempt to make a rational explanation of what is going on.
Hear is one attempt ( http://eileenslounge.com/viewtopic.p...art=40#p246586 )
Summary is also in the next Post: ……._
Ref:
' https://www.excelforum.com/excel-pro...fers-it-4.html
' https://www.excelforum.com/excel-pro...ml#post4384945
' https://www.excelforum.com/excel-pro...offers-it.html
' Generally for Objects where there is dependency, that is to say....... they cannot exist independently and / or there are other Objects which are affected by the existence of such Objects..... , you will not be allowed to make a New Instance. Instancing by the user directly will not be allowed. This is likely because there will be some very complicated "Wiring" involved. There will need to be information given, for example, as "where should it go", as other Objects may be effected. So those things are best left to a Function or Method, ( a .Add or .Insert , for example ). There will always be arguments associated and require them ( if you ,leave them about, VBA at compile will try to guess ( based on other available information and / or arguments), what you want, or always using a particular default when you omit an argument )
' Book: VBA for the VBE, Lisa Green. thinkz1.com
' http://www.eileenslounge.com/viewtop...246518#p246518
Dealing with objects when programming the VB Editor
_.....Continued from last post….
Dealing with objects when programming the VB Editor
Something going by the name of Automation Object Linking and Embedding, or Automation OLE or Automation is part of some very fundamental software things going way back that Microsoft did: It is especially good apparently at making it possible to run some application in others.
There are two ways to use Automation to programmatically control another application. These are referred to as Early Binding or Late Binding
For Early Binding you need to set a reference in your project to the application you want to manipulate. However this does not necessarily mean that you can manipulate the application.
Possibly then , ( I suggest, I don't know for sure) , that to set a reference does not always mean that you have Early Binding.
( The advantages we have discussed of using Early Binding rather than Late Binding are:
_ Access to the application's built-in constants
_ Ability to declare to the object types of the application leading to
____Intellisense
____Syntax checking at Compile
_ There maybe some minor efficiencies to do with how the computer handles Early Binding compared to Late Binding
_ You may have access to the application's object model via the Object Browser and VBA Help )
If you are attempting to access the Class of some application, then that could well mean that you are trying to programmatically control another application. In other words you want to do Automation . In oher words you need to do Binding.
Then to do this, for the Early Binding case you would tend to
_(i) check the reference,
_(ii) Dim a variable to the objects Class
_(iii) then you need to Instantiate an instance using the class ( you could argue that _(i) and / or _(ii) is the first part of this )
To Instantiate you can
either
__ use the New stuff in your Set line
or
__ use the CreateObject("….","…. ") thingy in your Set line
( The Diming step in _(ii) could be regarded as meaning that you are Early Binding. I would suggest that that is debatable. )
For Late Binding you only have the possibility to use the CreateObject("….","…. ") thingy in your Set line, and as you do not have the object types available to Dim. You must Dim As Object
If you try either of those Binding ways in the case of the VBIDE being discussed in this Thread, then they will fail. The short answer to why that is, is that Microsoft do not allow Automation in this case. The in depth explanation of this is galaxies beyond my level of computer understanding, but it is something like the Component Object Model used by Microsoft to allow this Automation is not "exposed"
A common circumstance for Microsoft to do this forbidding is when something is dependant on something else for its existence, as is the case of the things to do with the VB Editor
My conclusion is that Late Binding, and possibly Early Binding also, is not possible in for VBIDE
We can do, ( as far as I can tell all ) the same programming in the VB Editor with or without a reference to Microsoft Visual Basic for Applications Extensibility 5.3, ( Class Name: VBIDE ) . In other words .. We can do, ( as far as I can tell all ), the same programming in the VB Editor with or without "Object" as the Dim )
( If we check the reference then we can Dim to an object type from VBIDE, and have the advantages mentioned above. )
For all day use in most situations other than ones like the VBIDE, this would do as an alternative, definition:
Alternative View Point ( http://eileenslounge.com/viewtopic.p...art=40#p246593 ** )
Dim x As _....
_Set x As _....
Late Binding: things like cannot use IntelliSense with x , x is an object , (cannot you check the syntax for x)
Early Binding: things like can use IntelliSense and syntax checking for x. x is an application object
If you wish to extend this to cover the VBIDE, then :
Set a reference to Microsoft Visual Basic for Application Extensibility 5.3
Dim x As VBIDE.CodeModule
_Set x = ThisWorkbook.VBProject.VBComponents("ThisWorkbook" ).CodeModule
__This is early binding, since x is a VBIDE.CodeModule object. You can use IntelliSense and syntax checking for x.
Set a reference to Microsoft Visual Basic for Application Extensibility 5.3
Dim x As Object
_ Set x = ThisWorkbook.VBProject.VBComponents("ThisWorkbook" ).CodeModule
__Although you set a reference to the VBIDE object library, this is late binding, since x is a generic object. You cannot use IntelliSense with x, nor can you check the syntax for x.
Do NOT set a reference to Microsoft Visual Basic for Application Extensibility 5.3
Dim x As Object
_Set x = ThisWorkbook.VBProject.VBComponents("ThisWorkbook" ).CodeModule
__Late binding. You cannot use IntelliSense with x, nor can you check the syntax for x.
Ref:
' https://www.excelforum.com/excel-pro...fers-it-4.html
' https://www.excelforum.com/excel-pro...ml#post4384945
' https://www.excelforum.com/excel-pro...offers-it.html
' Generally for Objects where there is dependency, that is to say....... they cannot exist independently and / or there are other Objects which are affected by the existence of such Objects..... , you will not be allowed to make a New Instance. Instancing by the user directly will not be allowed. This is likely because there will be some very complicated "Wiring" involved. There will need to be information given, for example, as "where should it go", as other Objects may be effected. So those things are best left to a Function or Method, ( a .Add or .Insert , for example ). There will always be arguments associated and require them ( if you ,leave them about, VBA at compile will try to guess ( based on other available information and / or arguments), what you want, or always using a particular default when you omit an argument )
' Book: VBA for the VBE, Lisa Green. thinkz1.com
' http://www.eileenslounge.com/viewtop...246518#p246518
' HansV eileenslounge.com **
1 Attachment(s)
Example; Data Objects (& Clipboards) Use Clipboard to manipulate Excel ranges through string manipulation
Example Data Objects (and Clipboards) Use Clipboard to manipulate Excel ranges through string manipulation
.
Data Objects and Clipboards
It cannot be easily explained what a data object is. It could be considered to be an abortion that didn’t die. It is difficult to discuss the DataObject without discussing the clipboards
Clipboards and the DataObject
The last title was Data Objects and Clipboards and this one is Clipboards and the DataObject: It is a spaghetti of things that anyone has long since given up trying to straighten out or figure out.
When you play around with Excel VBA stuff, there are often three basic “Clipboards” hanging around that no one really understands ( http://www.eileenslounge.com/viewtop...art=20#p246887 )
_ Excel Clipboard
_ Windows Clipboard (Sometimes referred to as the System Clipboard)
_ Office Clipboard
Introduction
This is my light hearted interpretation: You might want to skip this.. and go on to ….Early and Late Binding the DataObject, that is to say the MSForms.DataObject…
Way back when Microsoft started playing with Windows things, some people in authority abused their authority badly by having their entire IT department trying to work out how to put a pretty picture or diagram in a report they were doing to try and contribute to making them look good to get a not deserved pay rise.
There was the ideas discussed at the outset of this Thread of some all encompassing idea of actively at run time Xtraordinary Object Linked and Embedded Component Object Model to make , as most practical usage, ease of putting one application in another. They never really got it right and while they still kept trying real life pressures meant something had to be done that mostly worked, so the end result was that we have a few clipboards. They work as independent software and with a bit of constant tweaking, such as through updates, they work after a fashion. We are still left with the remnants of doing it as they originally wanted and indeed the whole ActiveX OLE COM CodsWollups has grown into a beast that everyone is afraid of admitting they know nothing about. Inevitable then we have some object close to hand, or Class that is a library that can be regarded as to do with the whole ActiveX OLE COM CodsWollups, and with some justification we can say that a A DataObject is a standard OLE object. In fact it is very limited what you can do with a Data object. You can pass text into or get text out of the clipboard, but then the actual Clipboard can do that, or you can do that with API calls. It is all deliberately left vague to disguise the mess made at the start and to make it all sound more clever than it is.
Alternatively, if you want to put all that mildly and politely……. MSForms library contains an object called the DataObject that provides support for working with text strings on the Windows clipboard. VBA does not support the data type required for other, non-text, values on the clipboard. …. … later you can die ……. … you are experiencing a car accident…. The hell I am…no robot. https://www.youtube.com/watch?v=qhAFWW-p7PQ......
The end result of all that is that in a very round about sort of way, we have something that can be used in a few simple code lines to do something, for example of the form:
Do a typical Excel VBA Copy thing on a range; get hold of how that might look in a continuous single string form in a clipboard;
manipulate that somehow using string manipulation functions and techniques;
put that back in a modified but recognisable Excel range form and do a typical Excel VBA Paste back out
Pseudo code:
Excel Range Copy
Get some string version from some clipboard using a DataObject method
__Do some modification of the string
Replace the version previously got using another DataObject method
Excel Range Paste
(You can usually do something just as good directly with the clipboard to replace the DataObject bit… )
We know that works, as it has been tried. Exactly why is lost in the spaghetti that is way the clipboards are organised. It is thought that we are likely paying around with the Windows Clipboard. But somehow the clipboards are related or the above would not work. No one is quite sure anymore what is happening
Dataobject
If this has any speciality, it is probably that being there from the start it has some inside knowledge that a lot of things don’t, and moving text strings about with it, ( which of course is extremely fundamental to computing ) , often results in them coming on in the right place. If you are looking to use the “clipboard” in its general term to move stings about, then this is a good thing to get familiar to using.
Working example.
This will be done in a somewhat overview way, as is the typical usage in a final coding. It centres on using a methods to
_ get ext out of a . ..clipboards….
and to
_ put a text into ..clipboards….
In the next post we will try to makes some sense, at least as far as is possible, about what is actually going on and what the ..clipboards…. is or are
Early and Late Binding the DataObject, that is to say the MSForms.DataObject
MSFORMS, Forms , UserForms
If you are familiar with Visual Basic generally, then you may know that Windows and Forms and user Forms are the basis technology for most of Microsoft Windows stuff. The DataObject Class is held in the larger class of MSForms , Microsoft Forms 2.0 Object Library
Early Binding
Add a UserForm and then remove it
The library would be required for the Visual Basic Forms , ( typically referred to as UserForms), which have many similarities to the Forms in Visual Basic. They differ in one respect in that the UserForm cannot exist as a standalone program. Whether by coding or manually we use an available .Adding process to get one, in a similar way to that which we must create new worksheets. Part of the .Adding process for a UserForm will add the references to MSForms , Microsoft Forms 2.0 Object Library.
Checking the reference
Inn the usual way, (Extras) – References(Verweise) - scroll down to Microsoft Forms 2.0 Object Library -- put checkmark in.
Note if you cannot find , or it is not there, ..you can add that manually: VBA Editor – Tools(Extras) – References(Verweise) -- Browse -- and find FM20.DLL file under a path similar to C:\WINDOWS\system32, and select it -- Open -- OK.
RefMSFORMS.JPG : https://imgur.com/8zKpyr2
Attachment 2188
Early Binding Example
See next post
Ref
http://www.eileenslounge.com/viewtop...art=20#p246887
https://docs.microsoft.com/en-us/off...-the-clipboard
http://excelmatters.com/2013/10/04/l...ms-dataobject/
https://stackoverflow.com/questions/...ently-on-the-c
https://stackoverflow.com/questions/...60767#54960767
https://docs.microsoft.com/de-de/off...-the-clipboard
https://docs.microsoft.com/de-de/off...-the-clipboard
https://social.msdn.microsoft.com/Fo...d?forum=isvvba
https://www.spreadsheet1.com/how-to-...excel-vba.html
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=SIDLFRkUEIo&lc=UgzTF5vvB67Zbfs9qvx4AaABAg
https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgxLtKj969oiIu7zNb94AaABAg
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgzMCQUIQgrbec400jl4AaABAg
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugx12mI-a39T41NaZ8F4AaABAg.9iDQqIP56NV9iFD0AkeeJG
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugxa2VYHMWJWXA6QI294AaABAg. 9irLgSdeU3r9itU7zdnWHw
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgzFkoI0n_BxwnwVMcZ4AaABAg
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg.9htChVuaX9W9htG01cKBzX
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg. 9ht16tzryC49htJ6TpIOXR
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgwMKwGZpDjv7vi7pCx4AaABAg
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg. 9ht16tzryC49htOKs4jh3M
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgxVW-am20rQ5GFuJ9F4AaABAg
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
1 Attachment(s)
Early and Later Late Binding the DataObject, that is to say the MSForms.DataObject
Early and Later Late Binding the DataObject, that is to say the MSForms.DataObject
MSFORMS, Forms , UserForms
If you are familiar with Visual Basic generally, then you may know that Windows and Forms and user Forms are the basis technology for most of Microsoft Windows stuff. The DataObject Class is held in the larger class of MSForms , Microsoft Forms 2.0 Object Library
Early Binding(… and better later Late Binding while we are at it.)
Add a UserForm and then remove it
The library would be required for the Visual Basic Forms , ( typically referred to as UserForms), which have many similarities to the Forms in Visual Basic. They differ in one respect in that the UserForm cannot exist as a standalone program. Whether by coding or manually we use an available .Adding process to get one, in a similar way to that which we must create new worksheets. Part of the .Adding process for a UserForm will add the references to MSForms , Microsoft Forms 2.0 Object Library.
Checking the reference
In the usual way, (Extras) – References(Verweise) - scroll down to Microsoft Forms 2.0 Object Library -- put checkmark in.
Note if you cannot find , or it is not there, ..you can add that manually: VBA Editor – Tools(Extras) – References(Verweise) -- Browse -- and find FM20.DLL file under a path similar to C:\WINDOWS\system32, and select it -- Open -- OK.
RefMSFORMS.JPG : https://imgur.com/8zKpyr2
Attachment 2189
Early (and better later Late Binding while we are at it ) Binding Example
A somewhat detailed example will be given initially, but directly after a much simplified version will be given. What I want to do is to take a range like this:
| Row\Col |
A |
B |
C |
|---|
1 |
cellA1 |
CellB1 |
|
2 |
CellA2 |
CellB2 |
|
3 |
|
|
|
and replace it with this:
| Row\Col |
A |
B |
C |
|---|
1 |
cellA1|CellB1 |
|
|
2 |
cellA2|CellB2 |
|
|
3 |
|
|
|
The idea , or way of doing it, using clipboard things is the following: Most things to do with computer software are long strings of characters. Often the actual thing held in a clipboard during a copy / paste action is a such a long string. This is the case for the values across a simple spreadsheet range. In fact the clipboards is one of life’s great mysteries. It is a complicated monster allowing all sorts of things to be transferred around with a simply copy / paste action. I expect it was given up to incorporate it into any simply object embedding Model as once all things were planned to be. What it finally came out to be, is hidden from us. The remnants of the aborted attempts exist as the data object. We find that we can use that to influence the clipboards
Code example ( Final coding here: http://www.excelfox.com/forum/showth...ll=1#post11021 )
You will need these two functions:
Clear office Clipboard : http://www.excelfox.com/forum/showth...ll=1#post11019
Examine String : http://www.excelfox.com/forum/showth...ll=1#post10946
Get range in, get its string, modify it
Rem 0
Early Binding in the standard way
Rem 1
The original range above is created in the active worksheet. It is copied.
Rem 2 Clipboard Data object stuff
The copy is done be the Excel Range.Copy Method. This is a very commonly done thing, and seems a fairly bullet proof way to ensure that all Clipboards are filled, ( http://www.eileenslounge.com/viewtop...246889#p246887 )
As far as can be humanly determined, the .GetFromClipboard Method in a code of this form will fill one or more registers in the data object, and possibly cause some irritation to other processes causing them to hang up. Whether by design or accident this complements our bullet proof copy process and gives a fairly good chance of us having the long single string text representation of our range in the data object. We can , ( but must not ) , clear our Excel and Office clipboards at this point. ( So simply 'comment out Call ClearOffPainBouton if you have difficulties with this Called routine )
There is some belief that the DtaObj.GetText() Method returns the string in the data object. It may be more of a text indication of what has been passed, and this can often be that passed during the .GetFromClipboard Method
Rem 3 uses the referenced routine ( http://www.excelfox.com/forum/showth...ts-of-a-string ) to examine how that 2x2 spreadsheet range looks typically in a long string. The actual form can be seen to be
Code:
"CellA1" & vbTab & "CellB1" & vbCr & vbLf & "CellA2" & vbTab & "CellB2" & vbCr & vbLf
For convenience, the routine also shows it in a slightly easier to see form:
& vbCr & vbLf & "CellA1" & vbTab & "CellB1"
& vbCr & vbLf & "CellA2" & vbTab & "CellB2"
& vbCr & vbLf
(I have also added here some color here to demonstrate how we might picture the two rows)
We can see that Excel is apparently recognising vbTab as pseudo a cell wall. ( The vbCr & vbLf combination is a very common way in computer strings to indicate to go back to the start of a new line )
We do a modification to replace the vbTabs with a “|” which should give us the required example manipulation discussed at the star of this post, pseudo we would imagine then this form to have
& vbCr & vbLf & "CellA1|CellB1"
& vbCr & vbLf & "CellA2|CellB2"
& vbCr & vbLf
We now have our modified string and we need a reverse process to replace the string in the clipboard.
Reverse process: put modified string in, pate it out
At first glance, this is straight forward.
Reverse to method .GetText() is approx method Set.GetText()
Reverse to method .GetFromClipboard is approx method .PutInClipboard
For the opposite of .Copy we must use in our case the Worksheet.Paste method, because this uses the Windows clipboard
In some cases the above will work. It does not always, and indeed in our case it doesn’t. We find that an attempt to do this will fail and we will end up pasting out our original string. Why this is the case is not clear. Similarly un clear is why we could not Clear our test range after .GetFromClipboard: We had to wait until .GetText()
There appears to be a spaghetti of dependencies meaning the things often don’t get done as we might expect.
In our case what seems to be putting a spanner in the works is some dependency somewhere. The unpredictability of what form this dependency takes can be seen by the fact that clearing the windows clipboard at this point also messes the simple reverse process from working. This makes no sense at all: Clearly clearing does not always clear things: It may do this in many occasions as one of its actions, but it can also do things which have something near to the opposite effect.
So the simple reverse action would be
Code:
DtaObj.SetText Text:=strGet: ' Let strGet = DtaObj.GetText() ' - This always gets the last "addition" ... https://stackoverflow.com/questions/25091571/strange-behavior-from-vba-dataobject-gettext-returns-what-is-currently-on-the-c/54960767#54960767
DtaObj.PutInClipboard
It doesn’t in our case. We find that after these simple reverse steps , although the .GetText() confirms the entry, will not give us finally our modified output paste: We get instead the original. …!!! .. Further, using a function to clear the Windows clipboard would result in no output. It appears that somewhere our original output is held, possibly in the windows clipboard and we can’t overwrite it. If we remove it, we still go back there looking for it. How strange… …!!!
So far two things seem to make things work as expected. Using either removes the strange effects.
_ using DtaObj.Clear
_ starting again with a new object, and using the reverse steps with that.
There appears to be some mess in the spaghetti of dependencies. I will use the two workarounds, and take it a bit further an Late Bind my second data object: Just an inspired guess to ward off some other problem in the future. ( I maight later consider ending this code with finally scheduling a new code to run the reverse process.. )
‘4b) Better later Late Binding
Probably makes some abstract sense to do things like this: You will find you can’t take off the reference to Microsoft Forms 2.0 Object Library once you put it on in a file, so there is a good chance that you do your Late Binding Later after Early Binding….
Later Late Binding
The usually way is done for Late Binding, ( using the unique Class ID (CLSID) option for the string )
The three reverse steps are than done.
Rem 5 ' The third and final step use the Worksheet.Paste method, because this uses the Windows Clipboard ( http://www.eileenslounge.com/viewtop...art=20#p246887 ) . (It is not clear if it might choose in preference the Excel Clipboard if available, or try to …which might possibly explain that strange behaviour where something was being held in some register assigned to a place for the Excel copy…!!!)
_.______________________
demo coding here:
Sub MSFORMS_Early_Copy_and_Later_Late_Binding_Paste()
http://www.excelfox.com/forum/showth...ll=1#post11021
Extra rough notes for my later use and possible additions to this Thread
