VBA referring to external shared Libraries, 1) Early, 1.5) Laterly Early and 2) Late Binding Techniques

[DocAElstein]

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
RefMSFORMS.jpg

Early (and better later Late Binding while we are at it ) Binding Example
A somewhat detailed example will be given initially, but directly after a much simplified version will be given. What I want to do is to take a range like this:

Row\Col	A	B	C
1	cellA1	CellB1
2	CellA2	CellB2
3

and replace it with this:

Row\Col	A	B	C
1	cellA1|CellB1
2	cellA2|CellB2
3

The idea , or way of doing it, using clipboard things is the following: Most things to do with computer software are long strings of characters. Often the actual thing held in a clipboard during a copy / paste action is a such a long string. This is the case for the values across a simple spreadsheet range. In fact the clipboards is one of life’s great mysteries. It is a complicated monster allowing all sorts of things to be transferred around with a simply copy / paste action. I expect it was given up to incorporate it into any simply object embedding Model as once all things were planned to be. What it finally came out to be, is hidden from us. The remnants of the aborted attempts exist as the data object. We find that we can use that to influence the clipboards

Code example ( Final coding here: http://www.excelfox.com/forum/showth...ll=1#post11021 )
You will need these two functions:
Clear office Clipboard : http://www.excelfox.com/forum/showth...ll=1#post11019
Examine String : http://www.excelfox.com/forum/showth...ll=1#post10946

Get range in, get its string, modify it
Rem 0
Early Binding in the standard way
Rem 1
The original range above is created in the active worksheet. It is copied.
Rem 2 Clipboard Data object stuff
The copy is done be the Excel Range.Copy Method. This is a very commonly done thing, and seems a fairly bullet proof way to ensure that all Clipboards are filled, ( http://www.eileenslounge.com/viewtop...246889#p246887 )
As far as can be humanly determined, the .GetFromClipboard Method in a code of this form will fill one or more registers in the data object, and possibly cause some irritation to other processes causing them to hang up. Whether by design or accident this complements our bullet proof copy process and gives a fairly good chance of us having the long single string text representation of our range in the data object. We can , ( but must not ) , clear our Excel and Office clipboards at this point. ( So simply 'comment out Call ClearOffPainBouton if you have difficulties with this Called routine )
There is some belief that the DtaObj.GetText() Method returns the string in the data object. It may be more of a text indication of what has been passed, and this can often be that passed during the .GetFromClipboard Method
Rem 3 uses the referenced routine ( http://www.excelfox.com/forum/showth...ts-of-a-string ) to examine how that 2x2 spreadsheet range looks typically in a long string. The actual form can be seen to be

 "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/viewtop...art=20#p246887 ) . (It is not clear if it might choose in preference the Excel Clipboard if available, or try to …which might possibly explain that strange behaviour where something was being held in some register assigned to a place for the Excel copy…!!!)

_.______________________

demo coding here:
Sub MSFORMS_Early_Copy_and_Later_Late_Binding_Paste()
http://www.excelfox.com/forum/showth...ll=1#post11021