Late Binding

Hello All,

i am here again asking for the help for you

My organization is slowly rolling out the Windows-10 and Microsoft Office will get updated to 2016 from 2010.
But the issue is, once we save any excel file having macros in 2016, the macros wont work in 2010 since there is a mismatch in Object library (14 &16)

When searched, i found that the only permanent solution is to do 'late binding'
I have an excel workbook with tons of macros in which I need to do the late binding. Is there any easy method by which we will be able to know the replacement codes for late binding?

For example pplayouttext is 2...

Please help

thank you..

Hi

There is no simple replacement method- you will need to look up the values in the Object Browser.

Did you consider moving macros out of the workbook into add-in. It could be an easier to implement...

Although you will still need to late bind it if you have to support different versions of Office.

Originally Posted by xlnitwit

Although you will still need to late bind it if you have to support different versions of Office.

No he need to maintain only the 2010 version and when installed on 2016, the references will be updated. As consequently the add-in will not be opened on lower version this will not be a problem.
I speak from own experience - I maintain addin only on 2010, and then it is updated without problem on PCs with 2013 and 2016.
Added benefit is that with addin you can implement auto-update functionality. see http://www.jkp-ads.com/Articles/UpdateAnAddin.asp

Interesting- does it not prompt the user to save the add-in due to the changes in references?

does it ask you to save a regular file, created on a lower version, when opened on a newer one and closed without any changes have been made? I think in the internals the references is unchanged, but when opened on higher version it just resolves to this higher version and works, while if you make any changes in the code in the addin on this newer version and then save it, you will not be able to open it on lower versions, without changing the reference again (i.e. the original problem of OP).

Are you sure the code won't work after you've upgraded?

If the code was developed in Excel 2010 then it should still work in Excel 2010 and Excel 2016.

Originally Posted by buran

does it ask you to save a regular file, created on a lower version, when opened on a newer one and closed without any changes have been made?

We all use exactly the same version of Office, so I couldn't say- that's why I asked.

So, file created in Excel 2016 with references to PowerPoint 2016 and Word 2016 - These are Object Model 16. When open in 2013 (that is Object Model 15) excel reference resolves to the lower one. word and ppower point are missing. see screenshot.

Screenshot 2018-01-09 13.14.21.png

Next - excel workbook created on 2013, with reference to word and powerpoint - all object model 15, when open in excel 2016 - all references resolved to higher version. See the screenshot

16.JPG

Now, if you save it, references will be saved to the higher one and code will not work on the lower one, because word and powerpoint referencess are missing. excel will resolve to the lower one without problem (i.e. otherwise it will not be possible to exchange files between 2010/2013 and 2016).

Buran

You are right but the OP didn't mention references to any other libraries.

He did mention pplayouttext which is from the PowerPoint library.

Originally Posted by Norie

Buran
You are right but the OP didn't mention references to any other libraries.

Quite the opposite - read OP again. He is using macros in each workbook, early binding and enumerated constants. However, because of 2016 his references break up, because the workbook is saved in 2016 and then no longer works on 2010. And he is considering to switch to late binding, however the enumerated constants will no longer work and they need to replace these with numerical values .

Originally Posted by akhileshgs

Hello All,

i am here again asking for the help for you

My organization is slowly rolling out the Windows-10 and Microsoft Office will get updated to 2016 from 2010.
But the issue is, once we save any excel file having macros in 2016, the macros wont work in 2010 since there is a mismatch in Object library (14 &16)

When searched, i found that the only permanent solution is to do 'late binding'
I have an excel workbook with tons of macros in which I need to do the late binding. Is there any easy method by which we will be able to know the replacement codes for late binding?

For example pplayouttext is 2...

Oops, didn't notice that.

Yeah, it happens :-)

Thanks for your inputs guys..
There is going to be an issue when somebody else save the file in 2016 and then send it to someone having 2010 version. This risk is there since organization is going to roll out the update to all in 6 months. So for 6 months some will have 2016 and some 2010.
As per your suggestion, I took the file to computer having Office 2010 and reassigned the object library and saved it there. So now it is working for 2016 & 2010 as of now. I think this will do for current release.


For late binding, 'looking up the values is object browser ' is a good solution since i wanted to know from where I can refer the values. I didnt know that its there in object browser.

A Big thanks to you all

Hi
Just a minor point to a related issue that might be worth you noting if you are developing codes in a later version of Excel and then running those on earlier versions.
The issue I know effects codes developed in Excel 2010 and then used in Excel 2007. I do not have later versions so do not know if there might be any similar issues with later versions…

The problem which can stop your codes working is that some basic functions that were always available are somehow available additionally in some more recently available Libraries. Somehow in newer Excel versions the codes using those basic functions will “go to” those libraries instead of the basic library to “get” those functions, That cause the codes written in the later Excel versions to error when run in the earlier versions.
This happened to me a lot as I have mostly Excel 2007 and Excel 2010. ( One of Microsoft’s advancements in Excel 2010 over 2007 seems to have been to do stuff which then meant codes working in Excel 2010 had to be modified to working in Excel 2007 *** I have had to modify many code given to me to get them to work in XL 2007 )

The “trick” to get over this little problem is to get in the habit of adding an extra _..
VBA.
_.. before some basic VBA functions

I expect there is a more subtle way to fix such problems but I could never figure out how, and anyway lots of extra _ VBA._ bits make my codes even more ' pretty ' so I like that solution anyway..
Possibly there might be some merit in adding some extra terms in some codes to make sure that the correct libraries are referenced when there is a possibility that otherwise different libraries may be referenced as the default. I have no idea how and what decides what is the default in such cases…

Alan


Ref

*** http://www.jkp-ads.com/articles/apid...nts=True#23075 http://www.jkp-ads.com/articles/apideclarations.asp http://www.jkp-ads.com/articles/apid...nts=True#24690 https://www.excelforum.com/microsoft...ml#post4757666
https://www.mrexcel.com/forum/excel-...-left-etc.html
https://www.excelforum.com/microsoft...ml#post4757666
http://www.mrexcel.com/forum/excel-q...eft-etc-2.html
http://www.mrexcel.com/forum/excel-q...r-version.html
http://www.mrexcel.com/forum/excel-q...ng-2013-a.html
https://www.excelforum.com/excel-new...ml#post4083494

Hi Alan,

I think what you are talking about is actually just a references issue. When you have a missing reference and make calls to a function in an unspecified library (e.g. you use Trim rather than VBA.Trim), the compiler has to try and resolve them by searching through all the referenced libraries and if you have a broken reference, you see the "Can't find project or library" error, even if the function in question is not actually in the missing library. It's not a question of the code being in a different library for different versions, just that the compiler never actually looks in the correct library if a reference is broken.

Hi Don,..

Originally Posted by xlnitwit

... just a references issue. .. a missing reference and make calls to a function in an unspecified library ..the compiler .. try and resolve .. by searching through all the referenced libraries and if you have a broken reference, you see the "Can't find project or library" error, even if the function in question is not actually in the missing library. .. not question of the code being in a different library for different versions, just that the compiler never actually looks in the correct library if a reference is broken.

… Thanks for that extra input. It made me think… and I took another look at some previous problems I had worked around with this _ VBA. _ thingy workaround.
I may have been slightly off course with my thinking….. ( aka Pissing up the wrong tree )

I started again. I made two new Files from scratch,
_A ) one in Excel 2007
and
_B) one in Excel 2010.
( “MadeInExcel2007” and “MadeInExcel2010” ) . I put two simple test codes in them like this: ( Sub LeftToGuessWhereToGo and Sub RightYouAreExplicitlyreferencin )

Please Login or Register  to view this content.

_A) Codes and Workbook made in Excel 2007, ( saved as “MadeInExcel2007.xls” and “MadeInExcel2007.xlsm” )
“MadeInExcel2007.xls” https://app.box.com/s/1u3u5a89qk1yxbg8ocg0ftbtppfv59dk
“MadeInExcel2007.xlsm” https://app.box.com/s/6k5pbtg32kz2mm9eow9kdte9121sn0r6 )

Here seen in my Excel 2007 VB Development Environment along with my references
MadeInExcel2007inExcel2007.jpg https://app.box.com/s/z36vuom1eljxdi898gzy5a331zxu60ou
https://imgur.com/HOy9lUo ( Office 12 references )
All codes in all my Excel versions, ( Excel 2003, Excel 2007 and Excel 2010 ) work

_B) Codes and Workbook made in Excel 2010, ( saved as “MadeInExcel2010.xls” and “MadeInExcel2010.xlsm” )
“MadeInExcel2010.xls” https://app.box.com/s/amp9qg3hdy07c7f15xdvazigds0brmah
“MadeInExcel2010.xlsm” https://app.box.com/s/h607xan32t6m6v9zo661ji3c0zywavyn )
Here seen in my Excel 2010 VB Development Environment along with my references
MadeInExcel2010inExcel2010.jpg https://app.box.com/s/h1zeu5mtxgxi118kg0dvnzn3vfpl8zjz
https://imgur.com/CF0ZReo ( Office 14 references )
Here again seen in my Excel 2007 VB Development Environment along with my references
MadeInExcel2010inExcel2007.jpg https://app.box.com/s/8rggecmwgz03qsp6h3iymr6y390a43lp
https://imgur.com/a9gTsbu ( Office 12 references ?? - It would appear that somehow my Excel 2007 changes the Office 14 references to Office 12 references ?? – ties up with what buran showed and said in post #10 https://www.excelforum.com/excel-pro...ml#post4817039 )
All codes in all my Excel versions, ( Excel 2003, Excel 2007 and Excel 2010 ) work. I did not expect that … - I expected _ Sub LeftToGuessWhereToGo() _ not to work in Excel 2007.

So I dug out a File from an OP ( “MidTestJeffMose” ) where the issues had cropped up before. This File would originally of been made in Excel 2010 or Excel 2013, and had loads of stuff in it, which for the purposes of this experiment I wiped out… and then put the simple test codes in it
_C) Codes made in my Excel 2010. Workbook originally made in Excel 2010 or Excel 2013. I am not sure if made in excel 2010 or Excel 2013 as … most OPs are Poo and don’t give accurate info…
( saved as “MidTestJeffMose.xls” and “MidTestJeffMose.xlsm” )
“MidTestJeffMose.xls” https://app.box.com/s/amp9qg3hdy07c7f15xdvazigds0brmah
“MidTestJeffMose.xlsm” https://app.box.com/s/h607xan32t6m6v9zo661ji3c0zywavyn )
As I had previously found the first of the test codes , ( the one without the _ VBA. _ workaround ( Sub LeftToGuessWhereToGo() ) works in my Excel 2010 but errors in my Excel 2003 and Excel 2007 … ( As I expected it highlights as error source the VBA strings collection Library function _ Left$ )
SignToSendYouPissingUpTheWrongTree.JPG https://app.box.com/s/5dwukawa2d3p4t9eadjk31xi62llsa1x
https://imgur.com/TGZaVwz
SignToSendYouPissingUpTheWrongTree2.JPG https://app.box.com/s/05qtkv82pvnara63uoj3sf4c7l5aaxi5
https://imgur.com/DDJVN9x
I took a closer look at – Tools –- Extras -- references – in the VB Editor
MissingLibraries.JPG https://app.box.com/s/05qtkv82pvnara63uoj3sf4c7l5aaxi5
https://imgur.com/pqYpKSz
PissUpATreeInXLTwoThousandAndThree.JPG https://app.box.com/s/w9v2xw8euffg7kino2il2z03d0sbkh3n
https://imgur.com/ik6pdu4 ( Office 11 references )
So it would appear that I have broken references for Microsoft Word 14.0 and Microsoft Outlook. (.. I seem to remember the OPs issue being to do some writing to Word #### and the outlook was some Bollox I did not understand )
I unchecked those missing library references and.. all codes worked in all Excel versions !!!
( Thinking back, I think one reason why I may have missed this was that at some point I had some problems with references that Excel would not allow me to remove, ( I believe they may have been something to do with forms or something similar, I can’t quite remember now.. ) )
In any case I am glad to have a better idea now of where the problem is coming from. Thanks Don.

The VBA. _ workaround might still be appropriate if for some reason , such as passing on a file, I may want not to remove those references..

I am thinking there may be also some more professional way to do this..
_... Possibly I could put a code in ( possibly in the “Thisworkbook” code module to fire up on opening the workbook ) , something of the form

Please Login or Register  to view this content.

I think I have had a code somewhere that puts on or takes off references…..

_... The other solution I suppose might be add those libraries to an Excel version that does not have them. I am probably talking rubbish there – may be that sort of thing is not possible..

_.________________________________________________


Thanks again for that extra follow up input, I have this now stored in a few places

Please Login or Register  to view this content.

If I stumble across the code that I think that can fiddle around with what references you have, then I will post a follow up.

Alan

P.s.
All those screen shots can also be found here: https://www.excelforum.com/developme...ml#post4819842
P.s.2. The file with the broken references is also attached ( “MidTestJeffMose” )

EDIT . P.s. 3
#### Edit P.s. I think Norie came close to answering this one for me some time ago, but I missed it ( https://www.excelforum.com/excel-new...ml#post4083710

Originally Posted by Norie

..... if there was code in a workbook that was automating another application, eg Word, that could cause version problems......

Ref
https://www.excelforum.com/excel-new...ml#post4083710
http://www.jkp-ads.com/articles/apid...nts=True#23075

Originally Posted by Doc.AElstein

I am thinking there may be also some more professional way to do this..

Yes- late binding (the topic of this thread ). It removes the need for the references, hence they cannot cause problems.

Hi Don
I like the Intellisense from Early Binding, but I agree, for passing files on to people, certainly Late Binding is usually a good / safe option. ( I have had occasions when things work differently in Early and Late Binding ( https://www.excelforum.com/excel-pro...ml#post4443658 ) but that is fairly rare I believe. )
I think I have also seen a way to go through the available references and add a reference using code, - if I stumble across it sometime then I will post a follow up.
Up until now the problems I have had have been solved by being a bit more Explicit, like with the _ VBA. _ thing.
A “belt and braces approach” of sorting out the broken reference issue and ‘Explicitly referencing with lots of extra pretty bits in the code I personally favour also. That probably is not such a professional approach, but I am not professional, not at computing anyway.
I do like the ‘Explicit Pedantry approach in code – text is cheap, does no harm if you don’t read it, and helps me remember much later what I was doing , - then it is worth reading it .. later
Thanks again
Alan

It is not too hard to write early bound, then convert to late bound for distribution. For me, that would be preferable to all the possible issues with adjusting references at run time.

I think the issue in the thread you linked to is the only instance I have ever seen where late bound code would not work at all, though I admit I haven't examined that to verify there is no functional late bound method- I'll simply accept Kyle's word for it for now!

Originally Posted by xlnitwit

It is not too hard to write early bound, then convert to late bound for distribution. For me, that would be preferable to all the possible issues with adjusting references at run time....

I agree, usually I try to have both versions in the code, and get the code working with both. ( not at the same time obviously – I ‘comment out one and get the other to work , then visa versa. Usually I start with the Early Binding because, as I mentioned , I get the intellisense which helps me write the code ) Then if I pass a file on to someone then I comment out all the Early Binding bits, … ‘ but leave them in … ‘_- along with everything else in the ‘Comments

Originally Posted by xlnitwit

.....I think the issue in the thread you linked to is the only instance I have ever seen where late bound code would not work at all, though I admit I haven't examined that to verify there is no functional late bound method- I'll simply accept Kyle's word for it for now!......

Leith Ross told me this too.
It came up in a scrapping code that they both helped me with.
https://www.excelforum.com/developme...ml#post4449914
( *** Edit: In this case Leith was talking about the .Write, ( Kyle was talking about the _ GetElementsByClassName _ in his code version http://www.excelforum.com/developmen...ml#post4439349 – I did not understand why that needed to be late Binding – Kyle did mention that it is something that we are not supposed to see or use, https://www.excelforum.com/excel-pro...ml#post4446628 , but you can “get at it” through exposing the interface that it uses , IHTMLElementCollection, https://www.excelforum.com/excel-pro...ml#post4446584 ) )

So in Leith’s code it was the .Write on HTML Document object model stuff…. Leith:……. "This is a case where late binding has to be used. The htmlfile is an ActiveX object that is a wrapper function for the IHTMLDocument2 interface in MSXML2. This gets into a lot of low level system operation......." https://www.mrexcel.com/forum/excel-...ml#post4031122
Uncharacteristically for me.. I did not ask further on that one… I had a feeling if I did … then 3 years later , now , I would probably still be posting follow ups trying to understand. I did almost two hundred follow up posts in total in Two Threads to understand 99% of the code they did for me !! )

Shame, I always like to get to the bottom of these things….. ( but that scrapping code is only for personal stuff for me so the sharing thing is not an issue.. )
Alan



**** Edit: Just to clarify that I was talking about two different things

Originally Posted by Doc.AElstein

....If I stumble across the code that I think that can fiddle around with what references you have, then I will post a follow up. ...

Here is another form of Late Binding as an alternative Late Binding solution to some of the issues raised in this Thread.
The worked example I give is a rather long way around to solve the problem I had of going from XL2010 to XL2007 and visa verse. I do it just as a working example.
Assume in this case I have added libraries in a Workbook made and used in Excel 2010 which I want to preserve as some codes used in that Workbook need these library references. But assume also that I also have codes in the File which I want to use in Excel 2007. Those codes do not need the references to those libraries. Also those added libraries are not available in Excel 2007 and lead to the broken references issues discussed in this Thread.


So what these Main codes ( Main codes posted here: https://www.excelforum.com/developme...ml#post4821644
https://www.excelforum.com/developme...ml#post4821653 ) are about….
It does one of the Binding options, ( the Early Binding ), a bit later than usual. So it does that particular Binding later, or Late. So it is a form of Late Binding , - Late Early Binding, …I should of done it ages ago actually, so it is very Late. It is quite late this evening as I finished it as well. So it is definitely
Very Late Early Binding

It is not particularly dynamic, versatile or particularly useful, not even so practical for my application, but it just demonstrates a slightly untypical way to do something… you never know, a variation of the idea might, in an untypical situation be useful.. .. For example you could build yourself up a set of lists for the references you want for each Office version and these will be added ( checked in the references list ) or removed ( unchecked in the references list) as appropriate..
So I am just doing a demo on an example which I can use myself currently, ‘
_- … Going from Excel 2010 to Excel 2007 and visa versa

A few note on the codes and way to use them:
I take the start point in all this of having my Workbook up and running in Excel 2010
All the main codes are in any Normal code module in that Workbook. I use the Workbook example I used previously, “MidTestJeffMose.xlsm”. All latest codes are in it, and I upload it here and there ( https://www.excelforum.com/developme...ml#post4821644
https://www.excelforum.com/developme...ml#post4821653 )
It turned out to be quite a messy process:
You must do the following:
_ Save and then close the File in Excel 2010
_ Open Save and close the File in Excel 2007
_ Open and save and then close the File in Excel 2010
After that all is well in both Excel versions
This is how it works:
Save and then close the File in Excel 2010
Sub VeryLateEarlyBinding_GetRefromClsinXL2010Book()
This makes a list of the current Libraries in use ( Excluding those that we think are always there and that are additionally usually adjusted for versions if necessary. ( I think we think that there are 4 of those std Libraries , by their Name: VBA , Excel , stdole , Office
The list is made in a new Worksheet, Worksheets("VBCodeInfo")
By this first save, nothing is done to the added library references, but a new worksheet is made, Worksheets("VBCodeInfo")
Using Excel 2010 32 bit

Row\Col	A	B	C	D
1
2		Microsoft Outlook 14.0 Object Library	Microsoft Word 14.0 Object Library
3		Outlook	Word
4		{00062FFF-0000-0000-C000-000000000046}	{00020905-0000-0000-C000-000000000046}
5		C:\Program Files\Microsoft Office\Office14\MSOUTL.OLB	C:\Program Files\Microsoft Office\Office14\MSWORD.OLB
6

Worksheet: VBCodeInfo

Open Save and close the File in Excel 2007
Sub HeavysideWndBreaksInMyToolBoxRefutations()
In this I tried to removes broken references. But I had 2 problems there###:
_ I could not find anyway to uncheck a reference with code . It appears you can only remove a reference by the_.....
_ References.Remove _ ReferedToObject
_.. …. Method – and that needs the _ ReferedToObject _ , which you don’t have if it is missing. Some literature I goggled did suggest this was possible. I could not do it
_ The _ .IsBroken _ Property appeared unreliable. So were some of the others. In fact the only reliable thing that seemed to always work for a broken reference was the GUID. At least this was often the case for me. ( https://www.excelforum.com/developme...ml#post4820754 )

So this code tries to determine the GUIDs of broken references. Those broken GUIDs are indicated as such on a new Worksheet, Worksheets("VBCodeInfoExcel2007WndBreaks")
Using Excel 2007 32 bit

Row\Col	A	B	C	D
1
2		Microsoft Outlook 14.0 Object Library	Microsoft Word 14.0 Object Library
3		Outlook	Word
4		{00062FFF-0000-0000-C000-000000000046} Not in Excel2007	{00020905-0000-0000-C000-000000000046} Not in Excel2007
5		C:\Program Files\Microsoft Office\Office14\MSOUTL.OLB	C:\Program Files\Microsoft Office\Office14\MSWORD.OLB
6

Worksheet: VBCodeInfoExcel2007WndBreaks

Open and save the File in Excel 2010
Sub VeryLateEarlyBinding_AddRefOnOpeninXL2010Book
This code Adds all the non standard required Library references if they are not already.

Close the File in Excel 2010.
Sub VeryLateEarlyBinding_TraschRefromClsinXL2010Book
This uses the information on Worksheets("VBCodeInfoExcel2007WndBreaks") to determine which references to remove for use of the File in Excel 2007. ( They will be re added when the Workbook is re opened in Excel 2010 using the information in Worksheets("VBCodeInfo") )

Those are the main codes, and the ‘Comments have the full explanations and other vulgarities and trivialities as usual


_.____________________


The following simple codes are also in the _ ThisWorkbook _ code module: ( These codes set off the other codes appropriately at opening or closing of the Workbook )

Please Login or Register  to view this content.

Notes / Conclusions:
I expect there could be some errors for certain situations... I have not checked the code extensively. - I only wanted to check the general idea out in case some variation of it might be useful for me or anyone in the future .
I doubt I would bother to use it much yet. Manually unchecking broken references is I expect the best idea… but I might have another think about this… …. … I wonder if I found the missing Library files at their path in my Excel 2010 computer, . copied them and put them in the same path in my Excel 2007 computer … then I wonder what would happen … sounds a bit like Open Heart Surgery to me…
###What made the final solution very messy was not being able to cleanly remove broken reference checks with the code ( https://www.excelforum.com/developme...ml#post4820754
) .
Like most people I would probably just do it manually.
I just thought it was worth having a quick go at the idea for future reference for my own use, and thought I would share it while I was at it.


Alan





The 2 attached Files:… There is the original _..
__1__ “MidTestJeffMose.xlsm “
_.. but with the codes discussed in this Post also in it.

__2__Then there is the same file after you have done the --- Save, Close in Excel 2010 ( Choose to accept changes when asked on closing ) --- Open, Save, Close in Excel 2007 --- Open, Save in 2010. This final file will have any extra wanted libraries added on opening in Excel 2010, but it will remove those references on closing if they are ones that would be broken in Excel 2007.
“MidTestJeffMoseWndBreakReflatulations.xlsm”

In other words, “MidTestJeffMose.xlsm “ will become identical to “MidTestJeffMoseWndBreakReflatulations.xlsm” after doing the --- Save, Close in Excel 2010 (Choose to accept changes when asked on closing ) --- Open, Save, Close in Excel 2007 --- Open, Save in 2010




Ref
snb: codes bits he gave me and also here for example: http://www.snb-vba.eu/VBA_Sortedlist_en.html
various links in the code ‘comments also