Excel VBA Issue machine product debugging guidelines

Question

I have an Excel workbook with code dependencies in other other Excel workbooks (these dependent .xls are VB level links, that is, through the Tools-> References dialog box in the VBA editor) and some dll dependencies, such as: Runtime Microsoft Scripting Microsoft Forms 2.0 Object Library

This sheet has been working for about 2 years on 20 machines running Windows XP and Office XP. We recently accepted the delivery of 3 new machines (the same OS, the same office version) that refuse to run this sheet. When the sheet opens, it throws a "Compilation Error" and the session hangs.

If I open the sheet on a "bad" machine, press and hold the left shift key to stop the macro, and then go to VBA Editor-> Debug-> Complie VBAProject, it compiles fine. Then I can save the sheet and open it on a "bad" machine. However, this new version of the sheet refuses to work on a "good" machine!

I think there should be some version mismatch between some dlls on the “good” and “bad” machines. How to determine the cause of the problem? Are there any tools to compare lumpy versions?

Answer 1

Two suggestions

1) First open the file with disabled macros. And then check out VBA Editor | Tools | References Check for missing links and then let us know what it is. We will take it from there.

2) For links such as the Runtime Object Library for Microsoft Scripting . I never use early binding. Early binding is the main cause of such errors. Just FYI: Early Binding creates links in advance through the VBA Editor | Tools | References I would recommend changing your code to Late Binding. Here are two examples of the same code using the Runtime Object Library for Microsoft Scripting "with early binding and late binding

EARLY TREATMENT EXAMPLE

'~~> Set Reference to "Microsoft Scripting Runtime Object Library" Sub EBExample() Dim FSO As Scripting.FileSystemObject Dim SourceFolder As Scripting.Folder Dim FileItem As Scripting.File Set FSO = New Scripting.FileSystemObject Set SourceFolder = FSO.GetFolder(SourceFolderName) For Each FileItem In SourceFolder.Files '~~> You code Next FileItem End Sub 

NEXT EXAMPLE

 '~~> This doesn't need a reference Sub LBExample() Dim FSO As Object, SourceFolder As Object, FileItem As Object Set FSO = CreateObject("Scripting.FileSystemObject") Set SourceFolder = FSO.GetFolder(SourceFolderName) For Each FileItem In SourceFolder.Files '~~> You code Next FileItem End Sub 

Like me, I use early binding to take advantage of Intellisense, but then convert it to late binding to avoid version-specific code before distributing the code. This way the code always works. :)

IMP NOTE: Late Binding is not performed in scenarios where the destination computer does not have a corresponding DLL registered.

RECOMMENDED LINK :

Topic: Using Early Binding and Late Binding in Automation

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

Hope this helps

Sid

Answer 2

The easiest way to compare links on two different machines is to run a small macro on each computer to show me all the details.

Make sure Excel is configured to trust access to the VBA project object model and run the code below in two versions of your macro.

 Sub GetReferences() Dim r As Object For Each r In ActiveWorkbook.VBProject.References Debug.Print r.Name, r.Description, r.FullPath Next r End Sub