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 )
' https://www.excelforum.com/excel-programming-vba-macros/1214789-late-binding-2.html#post4819517
Sub LeftToGuessWhereToGo() ' Workbook "MidTestJeffMose.xlsm" Made Possibly in Excel 2010 or possibly originally Excel 2013. I am b
Dim strAnything As String ‘ "Pointer" to a "Blue Print" (or Form, Questionaire not yet filled in, a template etc.)"Pigeon Hole" in Memory, sufficient in construction to house a piece of Paper with code text giving the relevant information for the particular Variable Type. VBA is sent to it when it passes it. In a Routine it may be given a particular “Value”, or (“Values” for Objects). There instructions say then how to do that and handle(store) that(those). At Dim the created Paper is like a Blue Print that has some empty spaces not yet filled in. A String is a a bit tricky. The Blue Print code line Paper in the Pigeon Hole will allow to note the string Length and an Initial start memory Location. This Location well have to change frequently as strings of different length are assigned. Instructiions will tell how to do this. Theoretically a specilal value vbNullString is set to aid in quich checks.. But..http://www.mrexcel.com/forum/excel-questions/361246-vbnullstring-2.html#post44116
Let strAnything = "AnyFink"
Dim strAny As String
Let strAny = Left$(strAnything, 3) 'VBA Strings collection Left function returns a Variant- initially tries to coerces the first parameter into Variant, Left$ does not, that's why Left$ is preferable over Left, it's theoretically slightly more efficient, as it avoids the overhead/inefficieny associated with the Variant. It allows a Null to be returned if a Null is given. https://www.excelforum.com/excel-new-users-basics/1058406-range-dimensioning-range-and-value-referencing-and-referring-to-arrays-5.html#post4084816 .. it is all to do with ya .."Null propagation".. maties ;) '_-.. http://allenbrowne.com/casu-12.html Null is a special "I do not know, / answer unknown" - handy to have... propagation wonks - math things like = 1+2+Null returns you null. Or string manipulation stuff like, left(Null returns you Null. Count things like Cnt (x,y,Null) will return 2 - there are two known things there..Hmm - bit iffy although you could argue that Null has not been entered yet.. may never
End Sub
Sub RightYouAreExplicitlyreferencin() ' “VBA. Vba dot” worksaround Workbook "MidTestJeffMose.xlsm" Made Possibly in Excel 2010 or possibly originally Excel 2013
Dim strAnything As String
Let strAnything = "AnyFink"
Dim strAny As String
Let strAny = VBA.Right$(strAnything, 4)
Let strAny = VBA.Strings.Right$(strAnything, 4)
End Sub
_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
‘'The # (hash) symbols represent a preprocessor command, which are commands that are processed prior to compilation http://www.jkp-ads.com/articles/apideclarations.asp?AllComments=True#23075 http://www.excelforum.com/the-water-cooler/1068075-just-testing-img-cannot-do-it-in-test-forum-as-img-is-off-there-no-reply-needed-2.html#post4110298 http://www.excelforum.com/development-testing-forum/1086445-forum-tools-test-no-reply-needed.html#post4115901
#If vba7 Then ‘ add things like PtrSafe and other crap introduced with Excel 2010 http://www.jkp-ads.com/articles/apideclarations.asp
‘Code to put on references
#Else ‘ For Excel 2007
‘Code to take off references
#End If
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
…. ‘_- …. “.. When you have a missing reference and make calls to a function in an unspecified library , 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 “going to“ a different library for different versions, just that the compiler never actually looks in the correct library if a reference is broken..”. –Don ‘_……
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
Bookmarks