View Full Version : Testies external shared Libraries, regedit, registry
DocAElstein
03-23-2018, 12:39 AM
See here for examples of how to bind specific Libraries: 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?p=10970&viewfull=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 (https://www.youtube.com/watch?v=jY-PEeX5xYY&t=2s)
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 (https://www.youtube.com/watch?v=jY-PEeX5xYY&t=2s) 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/showthread.php/2227-VBA-Input-Pop-up-Boxes-Application-InputBox-Method-versus-VBA-InputBox-Function#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
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
_._______________________
DocAElstein
03-23-2018, 12:42 AM
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
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”,”ServerIfNotT hisComputer”)
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__”).Th atPropertormethodOfObject(PossiblyArgumentsEtc)
' or
__With CreateObject(“LibraryName.ObjectfromItIWant__”)
___PropertyOrXvalue = .ThatPropertormethodOfObject(PossiblyArgumentsEtc)
__End With
' Late Binding 2b) With__End With way
PropertyOrXvalue = CreateObject(“LibraryName.ObjectfromItIWant__”).Th atPropertormethodOfObject(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.
DocAElstein
03-23-2018, 12:43 AM
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/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=10547#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/library/aa241517(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 )
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-programming-vba-macros/1214789-late-binding-2.html#post4821675
Ref
http://www.excelfox.com/forum/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=10547#post10547
Stuff Rory and snb told me about
DocAElstein
03-23-2018, 12:45 AM
****"my Left , Mid , Date Function is not working anymore, Bollox!"
One error occurrence that often causes people to get eventually informed about the use of " "Libraries" of things available to us", is not immediately obvious and usually takes a while until it is realised that the problem lies in the checked library references. The problem is often referred to as a broken reference.
In such a case a quick work around seems to be to include an extra VBA. before the Function that errors. Often then it does not error..
To explain what is going on:
As noted, the full way to reference many things from the Visual Basic For Applications library would be to include VBA. at the start of many code lines. It is very likely that the more common functions are in a "shared dll type" library. The link / route will be held in the compiled version of the code, in the "pseudo complied links" part. So the code goes looking there if a function is not completely explicitly referenced and errors when it sees a checked reference that is not available. ( This may often be the case , for example , if in a higher version of Office a library was checked, but the code is being run in an earlier version).
It is not immediate obvious that the error is coming from the "broken reference".
Un checking the reference will usually cure that error.
Of course if you need that reference checked then you will hopefully have an earlier version of the library that you can check and that will work.
_._________________-
A Step by Step example
Just to make it clear as the error which appears to occur can mislead:
"My VBA Left function appears not to work"
The problem is not with the apparently not working function:
I "make a new File" in Excel 2010, and put some simple coding in it.
Option Explicit
Sub TabulatorStinkranartor()
Dim LeftScrots As String
Let LeftScrots = "ScrotScrot"
LSet LeftScrots = "Scrot" ' LeftScrot is now "Scrot " ' http://www.excelfox.com/forum/showthread.php/2230-Built-in-VBA-methods-and-functions-to-alter-the-contents-of-existing-character-strings
End Sub
Sub LeftScrot()
Dim LeftScrots As String
Let LeftScrots = "ScrotScrot"
Let LeftScrots = Left$(LeftScrots, 5) ' LeftScrot is now "Scrot"
End Sub
I check a non standard Library ( Here Word 14 ). Save the file in Excel 2010
Tools 1 Referrences 2 ScrollDown 3 CheckWord14 4 UsingExcel2010.JPG : https://imgur.com/Bgnlgxm
2010
Now I open the File in Excel 2007. I attempt to run the second code. It does not start and highlights _... Left_.... as the source of the problem and gives a compile error message, _
LeftErrorsMissingLibrary.JPG : https://imgur.com/ALCUzKm
2011
Fehler beim Kompilieren:
Projekt oder Bibliothek nicht gefunden
Error while compiling:
Project or library not found
If I change the code to include an extra _ VBA. _ thus:_..
Sub LeftScrot()
Dim LeftScrots As String
Let LeftScrots = "ScrotScrot"
Let LeftScrots = VBA.Left$(LeftScrots, 5) ' LeftScrot is now "Scrot"
End Sub
_.. then I have no problem and the code runs normally.
But the problem is not the Left Function.
To explain: I am not sue if it is strictly a compile error in the classical sense.
DocAElstein
02-14-2019, 11:41 PM
Binding Examples.
I intend giving examples of Binding in the next few post. I will likely add to it and possibly re organise it from time to time.
This post will index and possibly explain briefly the example.
If anyone wants to add an example, or make any comments, then great, go ahead. I will review them from time to time and comment on them.
Example: Binding issues in Programming the VB Editor
Post # 6 & Post #7 : 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?p=10971#post10971
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?p=10975#post10975
It is often said that you need to have Early Binding to library VBIDE ( Visual Basic Integrated Development Environment ) , that is to say the Microsoft Visual Basic for Applications Extensibility 5.3 to do any programming of the VB Editor, that is to say “do coding with coding”…
We will see that that does not appear to be the case
Example Data Objects (and Clipboards) Use Clipboard to manipulate Excel ranges through string manipulation
Post #8: 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?p=11017&viewfull=1#post11017
Post #9: . 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?p=11018#post11018
Early Binding with Later Late Binding the DataObject, that is to say the MSForms.DataObject
DataObject: Object to aid in passing strings into various clipboards. aka. an abortion that did not die
A few simple short lines can be used within coding to pass and retrieve strings of information from the various clipboards. it is hit and miss if they do what you expect.
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.youtube.com/watch?v=SIDLFRkUEIo&lc=UgzTF5vvB67Zbfs9qvx4AaABAg (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=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=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=f7xZivqLZxc&lc=UgzMCQUIQgrbec400jl4AaABAg)
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg (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=ITI1HaFeq_g&lc=Ugx12mI-a39T41NaZ8F4AaABAg.9iDQqIP56NV9iFD0AkeeJG)
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugxa2VYHMWJWXA6QI294AaABAg.9irLgSdeU3r9itU7zdnW Hw (https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugxa2VYHMWJWXA6QI294AaABAg.9irLgSdeU3r9itU7zdnW Hw)
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgzFkoI0n_BxwnwVMcZ4AaABAg (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=Ugz0Uy2bCSCTb1W-0_14AaABAg.9htChVuaX9W9htG01cKBzX)
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg.9ht16tzryC49htJ6TpIO XR (https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg.9ht16tzryC49htJ6TpIO XR)
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgwMKwGZpDjv7vi7pCx4AaABAg (https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgwMKwGZpDjv7vi7pCx4AaABAg)
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg.9ht16tzryC49htOKs4jh 3M (https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg.9ht16tzryC49htOKs4jh 3M)
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgxVW-am20rQ5GFuJ9F4AaABAg (https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgxVW-am20rQ5GFuJ9F4AaABAg)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
DocAElstein
02-14-2019, 11:42 PM
Example VBIDE Microsoft Visual Basic for Applications Extensibility 5.3 For coding coding
Based on notes here http://www.excelfox.com/forum/showthread.php/2295-ExtendingInsensibility-into-Code-modules-Copy-table-contents-to-VBIDE-VB-Editor-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"
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
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.A ctiveCodePane.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..
Sub LateNotEarly() ' http://www.eileenslounge.com/viewtopic.php?f=30&t=31547&p=246518#p246518
MsgBox Prompt:=Application.VBE.ActiveVBProject.VBComponen ts.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.A ctiveCodePane.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.php?f=30&t=31547&start=40#p246586 )
Summary is also in the next Post: ……._
Ref:
' https://www.excelforum.com/excel-programming-vba-macros/1138804-help-understanding-class-instancing-cant-set-ws-new-worksheet-intellisense-offers-it-4.html
' https://www.excelforum.com/excel-programming-vba-macros/1138804-help-understanding-class-instancing-cant-set-ws-new-worksheet-intellisense-offers-it.html#post4384945
' https://www.excelforum.com/excel-programming-vba-macros/1138804-help-understanding-class-instancing-cant-set-ws-new-worksheet-intellisense-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/viewtopic.php?f=30&t=31547&p=246518#p246518
DocAElstein
02-16-2019, 05:12 PM
_.....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.php?f=30&t=31547&start=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-programming-vba-macros/1138804-help-understanding-class-instancing-cant-set-ws-new-worksheet-intellisense-offers-it-4.html
' https://www.excelforum.com/excel-programming-vba-macros/1138804-help-understanding-class-instancing-cant-set-ws-new-worksheet-intellisense-offers-it.html#post4384945
' https://www.excelforum.com/excel-programming-vba-macros/1138804-help-understanding-class-instancing-cant-set-ws-new-worksheet-intellisense-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/viewtopic.php?f=30&t=31547&p=246518#p246518
' HansV eileenslounge.com **
DocAElstein
02-28-2019, 03:09 PM
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/viewtopic.php?f=30&t=31849&start=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
2188
Early Binding Example
See next post
Ref
http://www.eileenslounge.com/viewtopic.php?f=30&t=31849&start=20#p246887
https://docs.microsoft.com/en-us/office/vba/language/concepts/forms/what-is-the-difference-between-the-dataobject-and-the-clipboard
http://excelmatters.com/2013/10/04/late-bound-msforms-dataobject/
https://stackoverflow.com/questions/25091571/strange-behavior-from-vba-dataobject-gettext-returns-what-is-currently-on-the-c
https://stackoverflow.com/questions/25091571/strange-behavior-from-vba-dataobject-gettext-returns-what-is-currently-on-the-c/54960767#54960767
https://docs.microsoft.com/de-de/office/vba/access/Concepts/Windows-API/retrieve-information-from-the-clipboard
https://docs.microsoft.com/de-de/office/vba/access/Concepts/Windows-API/send-information-to-the-clipboard
https://social.msdn.microsoft.com/Forums/en-US/48e8c30c-24ee-458e-a873-a4e6e13f5926/dataobject-settext-and-putinclipboard-sequence-puts-invalid-data-hex-63-characters-in-clipboard?forum=isvvba
https://www.spreadsheet1.com/how-to-copy-strings-to-clipboard-using-excel-vba.html
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.youtube.com/watch?v=SIDLFRkUEIo&lc=UgzTF5vvB67Zbfs9qvx4AaABAg (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=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=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=f7xZivqLZxc&lc=UgzMCQUIQgrbec400jl4AaABAg)
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg (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=ITI1HaFeq_g&lc=Ugx12mI-a39T41NaZ8F4AaABAg.9iDQqIP56NV9iFD0AkeeJG)
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugxa2VYHMWJWXA6QI294AaABAg.9irLgSdeU3r9itU7zdnW Hw (https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugxa2VYHMWJWXA6QI294AaABAg.9irLgSdeU3r9itU7zdnW Hw)
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgzFkoI0n_BxwnwVMcZ4AaABAg (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=Ugz0Uy2bCSCTb1W-0_14AaABAg.9htChVuaX9W9htG01cKBzX)
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg.9ht16tzryC49htJ6TpIO XR (https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg.9ht16tzryC49htJ6TpIO XR)
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgwMKwGZpDjv7vi7pCx4AaABAg (https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgwMKwGZpDjv7vi7pCx4AaABAg)
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg.9ht16tzryC49htOKs4jh 3M (https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg.9ht16tzryC49htOKs4jh 3M)
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgxVW-am20rQ5GFuJ9F4AaABAg (https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgxVW-am20rQ5GFuJ9F4AaABAg)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
DocAElstein
03-02-2019, 10:20 PM
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
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
1cellA1CellB1
2CellA2CellB2
3
and replace it with this:
Row\Col
A
B
C
1cellA1|CellB1
2cellA2|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/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11021&viewfull=1#post11021 )
You will need these two functions:
Clear office Clipboard : http://www.excelfox.com/forum/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11019&viewfull=1#post11019
Examine String : http://www.excelfox.com/forum/showthread.php/2302-quot-What%E2%80%99s-in-a-String-quot-VBA-break-down-Loop-through-character-contents-of-a-string?p=10946&viewfull=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/viewtopic.php?f=30&t=31849&p=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/showthread.php/2302-quot-What%E2%80%99s-in-a-String-quot-VBA-break-down-Loop-through-character-contents-of-a-string ) to examine how that 2x2 spreadsheet range looks typically in a long string. The actual form can be seen to be
"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
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/viewtopic.php?f=30&t=31849&start=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/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11021&viewfull=1#post11021
DocAElstein
05-13-2021, 02:31 PM
Some miscalaneous notes for my further additions....
Notes for my future reference
https://excel.tips.net/T003340_Automatically_Changing_References_to_VBA_L ibraries.html
http://i.imgur.com/bXxYoiB.jpg
http://i.imgur.com/l4EIYsx.jpg
http://i.imgur.com/okZb6mB.jpg
http://i.imgur.com/bXxYoiB.jpg
http://i.imgur.com/l4EIYsx.jpg
http://i.imgur.com/okZb6mB.jpg
DocAElstein
03-31-2025, 10:59 PM
Some Notes in support of this main forum post:
https://eileenslounge.com/viewtopic.php?p=326247#p326247
For some later pondering
DocAElstein
04-02-2025, 10:07 PM
Later
DocAElstein
04-05-2025, 04:44 PM
later
DocAElstein
04-05-2025, 04:44 PM
later still
DocAElstein
04-05-2025, 08:06 PM
In support of this main forum post
https://eileenslounge.com/viewtopic.php?p=326389#p326389 , https://eileenslounge.com/viewtopic.php?p=326431#p326431
It’s in way of a correction and update to this post
https://eileenslounge.com/viewtopic.php?p=326377#p326377
Here is the 12 in total that I missed.
{BED7F4EA-1A96-11D2-8F08-00A0C9A6186D} 1.a Common Language Runtime Library
{A4D51C5D-F8BF-46CC-92CC-2B34D2D89714} e.0 LicLuaLib
{54AF9343-1923-11D3-9CA4-00C04F72C514} 2.32 Microsoft Data Access Components Installed Version
{4AC9E1DA-5BAD-4AC7-86E3-24F4CDCECA28} c.0 Microsoft Office 14.0 Access Database Engine Object Library
{A5EDEDF4-2BBC-45F3-822B-E60C278A1A79} b.0 Microsoft Office Document Imaging 11.0 Type Library
{91493440-5A91-11CF-8700-00AA0060263B} 2.a Microsoft PowerPoint 14.0 Object Library
{AAB9C2AA-6036-4AE1-A41C-A40AB7F39520} a.0 Microsoft Visual Studio Tools for Office Execution Engine Type Library
{FB1B729D-440F-4E42-87F8-5ABF7068E5D4} 14.00 OneNote COM Elevation Type Library
{BEE4BFEC-6683-3E67-9167-3C0CBC68F40A} 1.a System.dll
{D37E2A3E-8545-3A39-9F4F-31827C9124AB} 1.a System.Drawing.dll
{215D64D2-031C-33C7-96E3-61794CD1EE61} 1.a System.Windows.Forms.dll
{4FB2D46F-EFC8-4643-BCD0-6E5BFA6A174C} 1.a System_EnterpriseServices
As a punishment I will look at all those, to both correct if necessary my last conclusions and see if there is anything new to learn
{BED7F4EA-1A96-11D2-8F08-00A0C9A6186D} 1.a Common Language Runtime Library
I got previously 1 Common Language Runtime Library already, so correcting the situation means I have 2 in the list, but as only 1 is showing in the little reference checking window. So I need a correction to my last results, to highlight one of them in yellow to indicate an extra version in the list that does not appear in the reference checking window. (Note in passing for now that the path to the "thing" ends up at a mscorlib.tlb (https://i.postimg.cc/FKbB2n8y/Common-Language-Runtime-Library-Path-to-mscorlib-tlb.jpg)
But we are not finished with this one. In fact there are 3 versions in the registry.
https://i.postimg.cc/MGJL38PZ/Common-Language-Runtime-Library-1-0.jpg
https://i.postimg.cc/dVyX909S/Common-Language-Runtime-Library-1-a.jpg
https://i.postimg.cc/HsKR5Mby/Common-Language-Runtime-Library-2-0-mscorlib-dll.jpg
Weird? – Where's the third in the list then?. I can half explain that. Take a look at the "human readable description" / "TypeLib human readable name" / name in that little reference checking window .
https://i.postimg.cc/HsKR5Mby/Common-Language-Runtime-Library-2-0-mscorlib-dll.jpg
It is different for the third Common Language Runtime Library version (version 2.0 ) , - it looks like a file name, which happens sometimes, by design or accident I am not sure. In this case the file name is mscorlib.dll (https://i.postimg.cc/fW86zpL5/Common-Language-Runtime-Library-little-window-name-mscorlib-dll.jpg)
So scroll down and Bingo, there it is. https://i.postimg.cc/6Q9Fqf5R/Scroll-down-to-where-it-is.jpg
https://i.postimg.cc/HsKR5Mby/Common-Language-Runtime-Library-2-0-mscorlib-dll.jpg
So this third one does not effect my results as it was always in the list, but a bit hidden as it did not have the "human readable description" / "TypeLib human readable name" / name in that little reference checking window name that I expected.
Note a last little weirdness here…. The the "human readable description" / "TypeLib human readable name" / name in that little reference checking window name is mscorlib.dll, but the path*** ends up at mscorlib.tlb like the others.
(*** A little tip I learnt from this is when I look in the little reference window to see if a reference is there, then if I tick it temporarily , then the little box at the bottom gives me the path to the .dll or similar file. (Sometimes unfortunately the path is a bit long for the box, and you can't scroll or expand anything so you miss the last bit. It would not surprise me if we could gat a list of those as well with a Winny or maybe even API coding?)
_.__________________
The following do not effect any of my conclusions and there is nothing new to learn from them. I just missed them, because of there less typical version number, that's all
{A4D51C5D-F8BF-46CC-92CC-2B34D2D89714} e.0 LicLuaLib
{54AF9343-1923-11D3-9CA4-00C04F72C514} 2.32 Microsoft Data Access Components Installed Version
{4AC9E1DA-5BAD-4AC7-86E3-24F4CDCECA28} c.0 Microsoft Office 14.0 Access Database Engine Object Library
{A5EDEDF4-2BBC-45F3-822B-E60C278A1A79} b.0 Microsoft Office Document Imaging 11.0 Type Library
{91493440-5A91-11CF-8700-00AA0060263B} 2.a Microsoft PowerPoint 14.0 Object Library
{AAB9C2AA-6036-4AE1-A41C-A40AB7F39520} a.0 Microsoft Visual Studio Tools for Office Execution Engine Type Library
{FB1B729D-440F-4E42-87F8-5ABF7068E5D4} 14.00 OneNote COM Elevation Type Library
_.______________________
This next one
{BEE4BFEC-6683-3E67-9167-3C0CBC68F40A} 1.a System.dll
,has a story that is a bit of a variation on the first ( {BED7F4EA-1A96-11D2-8F08-00A0C9A6186D} 1.a Common Language Runtime Library ) story, but with a bit of a twisting quirk:
There are three versions. They all end up in a file, system.tlb but for some strange reason the "human readable description" / "TypeLib human readable name" / name in that little reference checking window names for the three versions are not all the same. The first two are, and are of the file form, but the third looks like the .dll fell off
System.dll https://i.postimg.cc/3x59YFgn/System-dll-1-0.jpg
System.dll https://i.postimg.cc/Pf72FjNG/System-dll-1-a.jpg
System https://i.postimg.cc/L5GDqzG4/System-2-0.jpg
How weird and mixed up is that?
(Only 1 System.dll is in the "human readable description" / "TypeLib human readable name" / name in that little reference checking window , so appropriately I modify my previous results to give another yellow background to indicate an extra version in the Winnie list that does not appear in the reference checking window )
_________________________
{D37E2A3E-8545-3A39-9F4F-31827C9124AB} 1.a System.Drawing.dll
I had missed one of these, meaning that there are 3 versions, and as only 2 are in the reference checking window , I need to add a yellow background to indicate an extra version in the Winnie list that does not appear in the reference checking window
https://i.postimg.cc/WzdSb9Wf/System-Drawing-dll-1-0.jpg
https://i.postimg.cc/d1H4FMBy/System-Drawing-dll-1-a.jpg
https://i.postimg.cc/MTxhH8V3/System-Drawing-dll-2-0.jpg
_._____________________________________
{215D64D2-031C-33C7-96E3-61794CD1EE61} 1.a System.Windows.Forms.dll
The same story here as the very fist story. I missed one of 3 in the registry, and with the same twist in the tale of the first two ( version 1.0 and version 1.a ), having the same "TypeLib human readable name" , and that name is different to the last version "TypeLib human readable name" (version 2.0)
https://i.postimg.cc/CK4cvnnJ/System-Windows-Forms-dll-1-0.jpg
https://i.postimg.cc/VNw7BWvd/System-Windows-Forms-dll-1-a.jpg
https://i.postimg.cc/jdG3Cbr6/System-Windows-Forms-dll-2-0.jpg
As before, only one of the first name appears in the little reference checking window, so I need to add a yellow background to indicate an extra version in the Winnie list that does not appear in the reference checking window
The third name, System_Windows_Forms , appears and always did in Winnie lists and so effects nothing
_.________________________________________________ _________________-
{4FB2D46F-EFC8-4643-BCD0-6E5BFA6A174C} 1.a System_EnterpriseServices
I missed one in my Winnie list, making 3 in total, and 3 are also seen in the registry, but only two in the little reference checking window so time to add another yellow background to indicate an extra version in the Winnie list that does not appear in the reference checking window
_._____________________________________-
As far as my previous conclusions are concerned not much error was caused in my general conclusions:
I had to add a few yellow backgrounds to indicate when a version did not appear in the "human readable description" / "TypeLib human readable name" / name in that little reference checking window ;
But none of the files I missed are those with the light blue background, which means there is no change in the conclusion that some things in the Winnie got lists are not at all in any version in the "human readable description" / "TypeLib human readable name" / name in that little reference checking window . Investigating that, and probably a few other things, will need a lot more time a lot later.
Here is the final corrected stats
472 from Winnie
472
- 33 lightblue (not anywhere in the reference checking window)
- 17 yellow ( extra versions not anywhere in the reference checking window )
= 424 in reference checking window
Alan
DocAElstein
04-06-2025, 02:04 PM
This is posts https://www.excelfox.com/forum/showthread.php/2986/page3#post25028
https://www.excelfox.com/forum/showthread.php/2997/page2#post25032
Some extra notes to go with these main forum post
https://eileenslounge.com/viewtopic.php?p=326446#p326446
I am catching up here and responding to some of the interesting info in this post (https://eileenslounge.com/viewtopic.php?p=326247#p326247)
I might also be responding to the second half of this one (https://eileenslounge.com/viewtopic.php?p=326198#p326198) , bit I still have not quite figured out what was being said there. ( I wasn't confused by the AidFromGuid metjhopd , - I know that's the SiP syndrone (Smartphone In Pub) :smile: )
Or maybe I am just pondering, a not uncommon occurrence
So… Update so far, and introduction to current stand
Going back a year and / or 4, we have tangled with "Clsid registry lists" before, and the digressions here forced me to revise them again, from and around here (https://www.excelfox.com/forum/showthread.php/2986/?p=25008&viewfull=1#post25008), or there abouts.
I had back then a bad Winniemidgetgismo coding, and Mike did it better, (a not un common state of affairs in recent years….)
It ended up like this
https://i.postimg.cc/Y9Q7rPYQ/Old-Clsid-Lists.jpg
https://i.postimg.cc/ZBY2jVfq/Old-Clsid-Lists.jpg (https://postimg.cc/ZBY2jVfq)
The coding that got the lists…
What I tried was I Initially got just the Clsid list and then, a while later, I tried to stick them pseudo in a
Obj = CreateObject("New:" & MeClisid & "")
, see what happened. It did not damage or break too much on my computers, at last not all of them always, but I was not too sure what was going on. If an attempt did not error or break something, then I did a
TypeName(Obj)
, on the resulting object. I am not sure why I did that, I expect I just thought at the time that somebody perhaps should. I had no idea what the results were telling me.
You ( You= Mike (https://eileenslounge.com/memberlist.php?mode=viewprofile&u=8741) ) in your coding equivalent got both the Clsid list and a ProgID. (And as I understand a bit better the winygismo codings I know now where that came from). Your code never errored or broke things like mine did, and there were sometimes similarities with your ProgID results and my TypeName(Obj), for the same Clisid
https://www.excelfox.com/forum/showthread.php/2986/?p=25010&viewfull=1#post25010
https://www.excelfox.com/forum/showthread.php/2986/?p=25012&viewfull=1#post25012
CLSDsUndClassNames.xls - https://app.box.com/s/nkjwti5yym9j0v634hrxerz4x7n1o90w
But I was still none the wiser on what it all meant..
But I am getting there now…
So lets look at your current example (https://eileenslounge.com/viewtopic.php?p=326247#p326247): https://i.postimg.cc/NMvg1Dw0/Little-referrence-checking-window-Microsoft-Scripting-Runtime.jpg https://i.postimg.cc/7hD0QSSH/Microsoft-Scripting-Runtime-more-explicitly-human-readable-name-that-appears-when-you-are-adding-a.jpg This is whre we might fist come across it, when "checking a reference for Early Binding"
https://i.postimg.cc/FfG5vjzt/Little-referrence-checking-window-Microsoft-Scripting-Runtime.jpg (https://postimg.cc/FfG5vjzt) We have learnt now that the relevant word to use there is the "human readable description" or "TypeLib human readable name", aka the name to look for in the little window for checking a reference
, - and here it is in "the Clsids" file of mine from s few years back
https://i.postimg.cc/05f8w7X8/Clsid-Microsoft-Scripting-Runtime.jpg ,
{0D43FE01-F093-11CF-8940-00A0C9054228} Tried FileSystemObject {0D43FE01-F093-11CF-8940-00A0C9054228} Scripting.FileSystemObject
https://i.postimg.cc/4m7LvBkX/Microsoft-Scripting-Runtime-in-old-File.jpg (https://postimg.cc/4m7LvBkX)
Here we can see it again in the little reference checking VB Editor window, https://i.postimg.cc/NMvg1Dw0/Little-referrence-checking-window-Microsoft-Scripting-Runtime.jpg https://i.postimg.cc/JD6ssDTv/Microsoft-Scripting-Runtime-more-explicitly-human-readable-name-that-appears-when-you-are-adding-a.jpg (https://postimg.cc/JD6ssDTv) , but that is perhaps not coming from directly from there, well maybe it is sort of,….
This is something new we picked up, Here it is (a different Guid number note), in the Registry Hive HKEY_CLASSES_ROOT , at the path TypeLib\{420B2830-E718-11CF-893D-00A0C9054228}\1.0
https://i.postimg.cc/J01MFh0V/Reg-Type-Lib-Microsoft-Scripting-Runtime.jpg
https://i.postimg.cc/YGy5GMQb/Reg-Type-Lib-Microsoft-Scripting-Runtime.jpg (https://postimg.cc/YGy5GMQb)
And we got some more recent Winnie codings to get that TypeLib info
{420B2830-E718-11CF-893D-00A0C9054228} 1.0 Microsoft Scripting Runtime
So that is a refresh on the Clsid stuff, and a bit extra about the TypeLib Guid
( This is also the Guid used in the .AddFroGuid – We do not use the Clsid Guid there! )
Now we will move on in the next post
DocAElstein
04-06-2025, 09:28 PM
This is posts https://www.excelfox.com/forum/showthread.php/2997-Testies-external-shared-Libraries-regedit-registry?p=25033&viewfull=1#post25033
https://www.excelfox.com/forum/showthread.php/2997/?p=25033&viewfull=1#post25033
https://www.excelfox.com/forum/showthread.php/2997/page2#post25033
https://www.excelfox.com/forum/showthread.php/2986-Version-Info-using-VBA-and-registry-quirks-InterRegional-Settings-GmbH?p=25034&viewfull=1#post25034
https://www.excelfox.com/forum/showthread.php/2986/?p=25034&viewfull=1#post25034
https://www.excelfox.com/forum/showthread.php/2986/page3#post25034
I am slightly upset at this point that the two different Guids in the last post
_ { the Clsid Guid }
and
_ { the other Guid ( related to "human readable description" or "TypeLib human readable name" given in that little window which you use to check a external library reference from the VB Editor , and the one to use in the .AddFromGuid ) }
are not more similar. Here they are again,
{0D43FE01-F093-11CF-8940-00A0C9054228} ' Clsid Guid FileSystemObject Scripting.FileSystemObject
{420B2830-E718-11CF-893D-00A0C9054228} ' TypeLib Guid 1.0 Microsoft Scripting Runtime
( But they are a bit similar. Maybe part of them would always be the same?? )
However, all is not lost., or maybe not always. Take a look at the other info that is sometimes there for a Clsid:
https://i.postimg.cc/HW543w1J/Microsoft-Scripting-Runtime-Inproc-Server32.jpg
https://i.postimg.cc/90GdMhYP/Microsoft-Scripting-Runtime-Prog-ID.jpg
https://i.postimg.cc/XqMK4Jd7/Microsoft-Scripting-Runtime-Type-Lib.jpg
https://i.postimg.cc/Bv6BCRJs/Microsoft-Scripting-Runtime-Version.jpg
https://i.postimg.cc/n9X9PwhW/Microsoft-Scripting-Runtime-Inproc-Server32.jpg (https://postimg.cc/n9X9PwhW)https://i.postimg.cc/rzs4szN9/Microsoft-Scripting-Runtime-Prog-ID.jpg (https://postimg.cc/rzs4szN9)https://i.postimg.cc/R3Tt6HYg/Microsoft-Scripting-Runtime-Version.jpg (https://postimg.cc/R3Tt6HYg)https://i.postimg.cc/tZSVDHpP/Microsoft-Scripting-Runtime-Type-Lib.jpg (https://postimg.cc/tZSVDHpP)
6206620762086209
In particular this: https://i.postimg.cc/nc3B0jrx/Microsoft-Scripting-Runtime-Clsid-telling-me-Type-Lib.jpg
https://i.postimg.cc/nc3B0jrx/Microsoft-Scripting-Runtime-Clsid-telling-me-Type-Lib.jpg (https://postimages.org/)
So we can see that the info to the TypeLib ( related to "human readable description" or "TypeLib human readable name" given in that little window which you use to check a external library reference from the VB Editor, which is also the one to use in the .AddFromGuid ) is also there.
What next, getting a bit confused with different Guids and different registry issues.
I think at this stage, I want to extend the investigation into the Guids on the XP machine that I recently I did the extensive Guid lists on ( https://eileenslounge.com/viewtopic.php?p=326377#p326377
https://eileenslounge.com/viewtopic.php?p=326431#p326431 ) , just to check out the full Clsid/ Guid situation before and perhaps after the TLBINF.dll registration (https://eileenslounge.com/viewtopic.php?p=326155#p326155)
DocAElstein
04-09-2025, 09:14 PM
Clsid and other Guid lists review
We have
_ (i) some newer "TypeLib"codings, ( including a API one!! from Mike (https://eileenslounge.com/viewtopic.php?p=326275#p326275)) which look at the Guid ( "TypeLib Guid" )related to "human readable description" or "TypeLib human readable name" given in that little window which you use to check a external library reference from the VB Editor which is also the one to use in the .AddFromGuid . The full list of these Guids is at the registry path Computer HKEY_CLASSES_ROOT TypeLib
and
_(ii) an older "Clsid Guid" getting set of codings from me and Mike, reviewed again recently from about here: https://www.excelfox.com/forum/showthread.php/2986-Version-Info-using-VBA-and-registry-quirks-InterRegional-Settings-GmbH?p=25008&viewfull=1#post25008
In this post I would like to update again the latter earlier Clsid Guid codings, or rather make a new single one, to include, or at least investigate, the possibility of getting the TypeLib Guid as well from it, and some other information, following the discussions of the last posts, which showed that sometimes we may have that TypeLib Guid and/ or other information "at the Clisid" list, as it were.
This post discusses the coding. The over next posts discusses the results
Rem 1 The first thing it does is get the full Clsid list at the registry Computer\HKEY_CLASSES_ROOT\CLSID using the Winnie way.
There are 4972 Clsids for the XP Notebook that I have recently been using in these recent discussions ( My name for that notebook is Keks, or KeksXP or similar ). (The returned array has 4973 elements, the first one, indicia 0, has a text in it CLSID )
This code next line puts the full CLSID list from here , https://i.postimg.cc/Tw8Th8bH/Computer-HKEY-CLASSES-ROOT-CLSID.jpg , https://i.postimg.cc/L4DhQXL9/HKEY-CLASSES-ROOT-CLSID.jpg , into the Array Clsids(),
RegInfoPro.EnumKey RegHive, "CLSID", Clsids()
In each of the , ====== main loopings, each Clsid is listed in one column, and then that same Clsid is further used to get other column outputs, (if they are available, "at the Clsid") :
https://i.postimg.cc/k2ghHnYw/Other-Info-at-Clsid.jpg (https://postimg.cc/k2ghHnYw)https://i.postimg.cc/NLwtzH64/Other-Info-at-Clsid.jpg (https://postimg.cc/NLwtzH64)
Rem 3 InprocServer32 ' This seems to be the .dll or similar file
Rem 4 ProgID - see Mike here https://eileenslounge.com/viewtopic.php?p=326247#p326247 - ProgID is [Library].[Object]
Rem 5 Version number held "at the Clsid"
Rem 6 TypeLib Guid if there is one held "at the Clsid"
Rem 7 Try to make an object type with Obj = CreateObject("New:" & { The Clsid } & "") , then, if successful try to do a TypeName(LateBndObj) on that object
( In the previous codings from a year or 4 ago, we did just the
ProgID from Mike
, and the
Try to make an object type with Obj = CreateObject("New:" & { The Clsid } & "") , then, if successful try to do a TypeName(LateBndObj) on that object from me
The results were similar. )
So far the coding discussed is a slightly more detailed info getting version of the previous Clsid codings.
But there is one further important new part of this coding:
'6b - we investigate the specific TypeLib Guid , (if there was one at the Clsid) , at the place where the full TypeLib Guid list is. We expect that the TypeLib Guid will mostly be there but we investigate if there are other more than one version, as well as comparing the actual version numbers there with that of Rem 5 Version number held "at the Clsid"
DocAElstein
04-10-2025, 01:27 AM
Coding discussed in last post.
Results and conclusions in next posts
Option Explicit
Sub BonnieBonnieBoundsOfCLSIDs() ' https://www.excelfox.com/forum/showthread.php/2997-Testies-external-shared-Libraries-regedit-registry?p=25037&viewfull=1#post25037 ' KeksXP()
Rem 0
On Error GoTo OtherError
Dim Ws As Worksheet: Set Ws = Me
Let Ws.Range("A1:I1") = Array("Ind Rw", "TypeName(Obj)", "ProgID", "Clsid", "Version at Clsid", "TypeLib Guid", "Version at TypeLib", "Human readable name", "File")
'Me.Columns("A:I").Clear
Dim StrCom As String: Let StrCom = "."
Dim RegInfoPro As Object: Set RegInfoPro = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & StrCom & "\root\default:StdRegProv")
Dim RegHive As Long: Let RegHive = -214748364.8 * 10
Rem 1 Clsids - for Clsid output, (but also to use for the other outputs)
Dim Clsids() As Variant
RegInfoPro.EnumKey RegHive, "CLSID", Clsids() ' Computer\HKEY_CLASSES_ROOT , CLSID , here comes the big array of all ya Clsids
Dim ClsInfCnt As Long, Rw As Long: Let Rw = 1 ' This keeps track of the rows - there will likely be more than there are Clsids as we need an extra row for versions at the TypeLib place
For ClsInfCnt = LBound(Clsids()) To UBound(Clsids()) ' The main Clsid Loop =======================
Let Rw = Rw + 1 ' Every Clsid is on a new line
Let Ws.Range("A" & Rw & "") = ClsInfCnt & " " & Rw ' The first is a 0 and in the array is "CSID" after that in the array you get the { Guid thingy } format https://i.postimg.cc/mrHxggQD/Computer-HKEY-CLASSES-ROOT-CLSID.jpg
Let Ws.Range("D" & Rw & "") = Clsids(ClsInfCnt) ' The main { Clsid thingy }
Rem 3 InprocServer32 ' This seems to be the .dll or similar file
Dim IprcSvr32 As Variant
RegInfoPro.getstringvalue RegHive, "CLSID\" & Clsids(ClsInfCnt) & "\InprocServer32", "", IprcSvr32
If IsNull(IprcSvr32) Then
Let Ws.Range("I" & Rw & "") = " -"
Else
Let Ws.Range("I" & Rw & "") = IprcSvr32
End If
Rem 4 ProgID (- see Mike here https://eileenslounge.com/viewtopic.php?p=326247#p326247 ) if there is one held "at the Clsid"
Dim PrgID As Variant
RegInfoPro.getstringvalue RegHive, "CLSID\" & Clsids(ClsInfCnt) & "\ProgID", "", PrgID
If IsNull(IprcSvr32) Then
Let Ws.Range("C" & Rw & "") = " -"
Else
Let Ws.Range("C" & Rw & "") = PrgID
End If
Rem 5 Version numnber if there is one held "at the Clsid"
Dim CGVers As Variant
RegInfoPro.getstringvalue RegHive, "CLSID\" & Clsids(ClsInfCnt) & "\Version", "", CGVers
If IsNull(CGVers) Then
Let Ws.Range("E" & Rw & "") = " -"
Else
Let Ws.Range("E" & Rw & "") = "'" & CGVers
End If
Rem 6 TypeLib Guid if there is one held "at the Clsid"
Dim TypeLibGuid As Variant
RegInfoPro.getstringvalue RegHive, "CLSID\" & Clsids(ClsInfCnt) & "\TypeLib", "", TypeLibGuid
If IsNull(TypeLibGuid) Then
Let Ws.Range("F" & Rw & "") = " -"
Else ' Time to get the array of versions
Let Ws.Range("F" & Rw & "") = TypeLibGuid ' The Guid related to "human readable description" or "TypeLib human readable name" given in that little window which you use to check a external Library reference from the VB Editor , and the one to use in the .AddFromGuid
Dim Vers As Variant, CntV As Long ' Vers can be ab array or Null
'6b)
RegInfoPro.EnumKey RegHive, "TypeLib\" & TypeLibGuid, Vers
If IsNull(Vers) Then
Let Ws.Range("G" & Rw & "") = "Null versions at Computer\HKEY_CLASSES_ROOT\TypeLib"
Else
For CntV = LBound(Vers) To UBound(Vers)
Let Ws.Range("G" & Rw & "") = "'" & Vers(CntV)
Dim Description As String ' Might think about having this Variant. This is the "human readable description" or "TypeLib human readable name" given in that little window which you use to check a external Library reference from the VB Editor , and the one to use in the .AddFromGuid
RegInfoPro.getstringvalue RegHive, "TypeLib\" & TypeLibGuid & "\" & Vers(CntV), "", Description
Let Ws.Range("H" & Rw & "") = Description
If CntV < UBound(Vers) Then Let Rw = Rw + 1 ' we need an extra line for the next version
Next CntV
End If
End If
Rem 7 Try to make an object type ' Do this last as it can crash and/ or set the Devil loose
Dim LateBndObj As Object
On Error GoTo BadObjType ' I expect the next line often to error when I try to use
Set LateBndObj = CreateObject("New:" & Clsids(ClsInfCnt) & "")
On Error GoTo OtherError
Let Ws.Range("B" & Rw & "") = TypeName(LateBndObj)
BonniBanksOfLochLomond: '
Next ClsInfCnt ' The main Clsid loop ================================================== ===========
Exit Sub
BadObjType:
Let Ws.Range("B" & Rw & "") = "Err '" & Err.Number & "': " & Err.Description
Let Ws.Range("B" & Rw & "").Font.Color = 12632256
On Error GoTo -1
On Error GoTo OtherError
GoTo BonniBanksOfLochLomond ' https://eileenslounge.com/viewtopic.php?p=326446#p326446
OtherError:
Stop
Debug.Print "Err '" & Err.Number & "': " & Err.Description & " " & ClsInfCnt & " " & Rw
Stop
Resume Next
End Sub
DocAElstein
04-10-2025, 12:52 PM
This and the next post discuses the use and results of the coding from the last post in general terms, and in particular the typical errors and crashes. The next post discusses the results and conclusions in more detail.
Coding is development aid, mainly for use in F8 step mode. ( Rem 7 section problems )
The coding is more of a development tool, and aid, but is of little use as a code to run outright. This is mainly because of section Rem 7, which frequently causes crashes and other awkward happenings, and can even cause permanent effects and even damage to a computer.
Often Excel recovers itself after a crash, and fortunately seems to often return to a state just before the crash, so this is why section Rem 7 is done towards the end of a loop, as then information obtained from the other sections is often preserved when Excel re opens after recovering itself after a crash.
Also it is much easier to see accurately where a problem occurs in F8 step mode, as a run may miss a problem occasionally or a few problems may sometimes occur in different places before the coding stops/ crashes
It is unlikely that you can do much useful with this coding in, less than a day or two. It is only of use if you are having a detailed look at the Guids.
Having said that, if you remove section Rem 7, then you may get an entire run without errors. The coding is still quite slow and inefficient, but that is much better for learning and experimenting and learning, since the less efficient spreadsheet interaction codings both allow you to see better what is going on as well as catching results before a crash/ unexpected code stop.
The problems and errors are discussed very approximately in order of most significance and highest number of occurrences.
There are 4972 Clsid entries in the registry on the old XP computer used
Main Crashes
This is both the most predominant and most consistent problem. The cause and effect is almost always the same. A crash occurs at Obj = CreateObject("New:" & [The Clsid under investigation] & ""), and usually Excel recovers itself completely and even has all data obtained up to the crash. So for example in the example screen shot, a crash occurred for the Clsid array element of indicia 168 and 183, so we have no information in cells A168 or A183, ( TypeName(Obj) ), but we have some information in the other columns for those 2 Clsid
https://i.postimg.cc/PqHtYYPd/Crash.jpg
https://i.postimg.cc/4Y8kTK60/Crash.jpg (https://postimg.cc/4Y8kTK60) 6211
https://i.postimg.cc/PqHtYYPd/Crash.jpg (https://postimages.org/)
Often in such a case we also have no information in the third column ( ProgID ), but occasional there is also information there as well
https://i.postimg.cc/C1PY32JK/Crash-2.jpg
https://i.postimg.cc/sQhtSwkF/Crash-2.jpg (https://postimg.cc/sQhtSwkF) 6212
When this crash occurs, (and I would typically be running in F8 step mode, so clearly I would see that the error occurs at section Rem 7 at that indicia/row number), then, on restart, I would change in the coding the lower bound in the loop to 1 above the indicia which caused the problem, ( and correspondingly change the value of the variable Rw )
For example, after the first crash shown, I would, (before running the coding in F8 step mode), change the coding to
Dim ClsInfCnt As Long, Rw As Long: Let Rw = 194 ' This keeps track of the rows - there will likely be more than there are Clsids as we need an extra row for versions at the TypeLib place
For ClsInfCnt = 169(Clsids()) To UBound(Clsids()) ' The main Clsid Loop =======================
Let Rw = Rw + 1 ' Every Clsid is on a new line
Let Ws.Range("A" & Rw & "") = ClsInfCnt & " " & Rw ' The first is a 0 and in the array is "CSID" after that in the array you get the { Guid thingy } format https://i.postimg.cc/mrHxggQD/Computer-HKEY-CLASSES-ROOT-CLSID.jpg
Let Ws.Range("D" & Rw & "") = Clsids(ClsInfCnt) ' The main { Clsid thingy }
Powered by vBulletin® Version 4.2.5 Copyright © 2025 vBulletin Solutions Inc. All rights reserved.