Advice on Debugging Machine-Specific Excel VBA Issue -

i have excel workbook dependencies on code in other other excel workbooks (these dependent .xls's vb-level references, i.e. via tools->references dialog box in vba editor), , dependencies on dll's such as: microsoft scripting runtime microsoft forms 2.0 object library

this sheet has worked 2 years on around 20 machines running windows xp , office xp. have taken delivery of 3 new machines (same os, same office version) refuse run sheet. when sheet opens, throws 'compile error', , session hangs.

if open sheet on 'bad' machine, hold down left shift key stop macro's running, , go vba editor->debug->complie vbaproject, compiles fine. able save sheet , open on 'bad' machine. new version of sheet refuses run on 'good' machine!!

i think there must sort of version mismatch between dll's on 'good' , 'bad' machines. how establish causing issue? there tools available comparing versions of com components?

two suggestions

1) first open file macros disabled. , check vba editor | tools | references. check missing references , let know they. take there.

2) references "microsoft scripting runtime object library" never use binding. binding major cause these kind of errors. fyi: binding creating references beforehand via vba editor | tools | references. recommend changing code late binding. here 2 examples of same code using "microsoft scripting runtime object library" binding , late binding

early binding example

'~~> set reference "microsoft scripting runtime object library"  sub ebexample()     dim fso scripting.filesystemobject     dim sourcefolder scripting.folder     dim fileitem scripting.file      set fso = new scripting.filesystemobject     set sourcefolder = fso.getfolder(sourcefoldername)      each fileitem in sourcefolder.files         '~~> code     next fileitem end sub 

late binding example

'~~> doesn't need reference sub lbexample()     dim fso object, sourcefolder object, fileitem object      set fso = createobject("scripting.filesystemobject")     set sourcefolder = fso.getfolder(sourcefoldername)      each fileitem in sourcefolder.files         '~~> code     next fileitem end sub 

as me use binding take advantage of intellisense convert late binding avoid version-specific code before distributing code. ways code works. :)

imp note: late binding fails in scenarios destination machine doesn't have relevant dll registered.

recommended link:

topic: using binding , late binding in automation

link: http://support.microsoft.com/kb/245115

hope helps

sid