none
User-defined type not defined even after adding reference from file RRS feed

  • Question

  • We have around 100 Word templates each of which dynamically loads a common VBA .bas file at run time which in turn retrieves data from a custom application. To do this I need to create and send SOAP messages using the MSXML2.XMLHTTP interface. If I add a refence in debug mode to the msxml6.dll library then it all works fine, but this reference is lost at run time when the vba module is dynamically loaded.

    I have read articles which suggest I should be able to create a reference at run time using code of the form:

    Application.ActiveDocument.VBProject.References.AddFromFile ("C:\Windows\System32\msxml3.dll")

    I have include this code in the initialization code called in the module but I still get the error "User-defined type not defined" when the complier tries to parse the a line of the form Dim XMLDOM As New MSXML2.DOMDocument at run time,

    I have added some test code just before the declaration of the form

      For Index = 1 To Application.ActiveDocument.VBProject.References.Count
           MsgBox Application.ActiveDocument.VBProject.References.Item(Index).Name
        Next

       Dim HTTP As MSXML2.XMLHTTP

    and this does show that the library MSXML2 has been loaded but I still get the compile error when the complier parses the Dim statement. I have also tried the statement

     Application.ActiveDocument.VBProject.References.AddFromFile ("C:\Windows\System32\msxml6.dll")

    but with the same result.

    I could get round things by calling CreateObject instead of using early binding but this seems messy. Any advice on what I am doing wrong would be much appreciated. Ideally I want to only patch the common .bas file as we do not really want to make changes to 100+ document templates.


    Nigel T

    Thursday, May 31, 2012 8:26 AM

All replies

  • When you add the reference at runtime, you are doing, by definition, late binding.  Set the reference using Tools, and then your dimensioning will work using an object from the reference. Otherwise, you need to follow the CreateObject route.

    After you type

    Dim HTTP As ms

    if MSXML2 is not an option in the automcomplete, you are not doing early binding.


    HTH, Bernie

    • Proposed as answer by taeurn Tuesday, May 14, 2019 7:33 PM
    Thursday, May 31, 2012 2:18 PM
  •  

    Thanks for the reply - I have done a bit more research and what it looks like is that if you execute the statement Application.ActiveDocument.VBProject.References.AddFromFile ("C:\Windows\System32\msxml3.dll" from the main template maco code (not the dynamically inserted code) then it works OK. It just looks like even if you load the reference from the dynamically loaded module, it loads but the compiler does not know about it.

    So, what I could do is:

    Add a reference from the IDE when developing the code and add update the main startup module in the template to dymically load the reference if it missing. This makes debuging work Ok and means I can use early binding throughout. It also means at run time the same code will work.

    However, this means patchinh 100+ templates which is not somthing I want to do.

    Many thanks for your help

    Nigel


    Nigel T

    Thursday, May 31, 2012 3:21 PM
  • Can't you just loop through the files, open them, set the reference, save and close them? In Excel, setting a reference programatically sets it for good - you can also modify the code in each template programatically - that's how viruses work...

    HTH, Bernie

    Thursday, May 31, 2012 5:41 PM
  • Bernie

    I have tried that but the code monly seems to work if it executed before the dynamically loaded module in loaded which kind of makes sense. I find that if I put code of the form you suggest in the AutoNew macro everything works fine, but it seems the dynamically loaded module cannot load references it uses itself (or it seems they do load but the compiler still can't find them)


    Nigel T

    Friday, June 1, 2012 2:54 PM
  • We have around 100 Word templates each of which dynamically loads a common VBA .bas file at run time which in turn retrieves data from a custom application. To do this I need to create and send SOAP messages using the MSXML2.XMLHTTP interface. If I add a refence in debug mode to the msxml6.dll library then it all works fine, but this reference is lost at run time when the vba module is dynamically loaded.

    I have read articles which suggest I should be able to create a reference at run time using code of the form:

    Application.ActiveDocument.VBProject.References.AddFromFile ("C:\Windows\System32\msxml3.dll")

    I have include this code in the initialization code called in the module but I still get the error "User-defined type not defined" when the complier tries to parse the a line of the form Dim XMLDOM As New MSXML2.DOMDocument at run time,

    I have added some test code just before the declaration of the form

      For Index = 1 To Application.ActiveDocument.VBProject.References.Count
           MsgBox Application.ActiveDocument.VBProject.References.Item(Index).Name
        Next

       Dim HTTP As MSXML2.XMLHTTP

    and this does show that the library MSXML2 has been loaded but I still get the compile error when the complier parses the Dim statement. I have also tried the statement

     Application.ActiveDocument.VBProject.References.AddFromFile ("C:\Windows\System32\msxml6.dll")

    but with the same result.

    I could get round things by calling CreateObject instead of using early binding but this seems messy. Any advice on what I am doing wrong would be much appreciated. Ideally I want to only patch the common .bas file as we do not really want to make changes to 100+ document templates.


    Nigel T

    IMO, using CreateObject, i.e., late binding, is far more straightforward than trying to create runtime references to a library. With the latter, you cannot predict the filename of the library on every machine.  I use a compile time constant to switch between early and late binding.  That way, my code remains very clean and requires minimal, if any, additional testing.

    For a couple of examples of how, see

    Factors of a number, prime numbers, and prime factors of a number
    http://www.tushar-mehta.com/excel/tips/factors.html

    and

    List all folders in a Microsoft Outlook account
    http://www.tushar-mehta.com/publish_train/xl_vba_cases/1201%20Outlook%20folder%20info.shtml


    Tushar Mehta (Technology and Operations Consulting)
    www.tushar-mehta.com (Excel and PowerPoint add-ins and tutorials)
    Microsoft MVP Excel 2000-Present

    Saturday, June 2, 2012 8:22 PM