Results 1 to 10 of 10
  1. #1
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Runtime references and global variables (2000 sp-3/ 98SE/ VB6)

    I've followed a couple of Lounge threads on setting VBA references at runtime. Works quite nicely from the Workbook_Open event, for instance:
    <div style="width: 100%; background-color: #FFFFFF;"><code><font color=black> <font color=blue>With</font color=blue> ThisWorkbook
    .VBProject.References.AddFromFile _
    (.Path & Application.PathSeparator & "MY.dll")
    <font color=blue>End</font color=blue> <font color=blue>With</font color=blue> </font color=black></code></div hiblock>
    My problem is that I declare several global variables in a standard module, and these are UDTs, defined in class modules in the reference. I had hoped that the Workbook_Open event would complete fully, before any other code loaded, but it appears not to be the case. I receive a compile error saying "UDT not defined".

    I'm hoping for a way around this. I'm afraid that the requirement is that the referenced libraries be in the same folder as the XL workbook, but that particular folder might be named anything.

    Alan

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Runtime references and global variables (2000 sp-3/ 98SE/ VB6)

    Is there no way to get rid of the global variables?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Runtime references and global variables (2000 sp-3/ 98SE/ VB6)

    One of them is one of yours actually - Public blnVarsOK As Boolean <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Some I might be able to pass around the procedures, but with others, this would be a real headache.
    I was really hoping for some "miracle" method to get the reference to register before anything else happened.

    Alan

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Runtime references and global variables (2000 sp-3/ 98SE/ VB6)

    Of course I meant to say get rid of the global variables that are part of a UDT and need the dll
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Runtime references and global variables (2000 sp-3/ 98SE/ VB6)

    <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15> Yep, I figured that. And in fact, that's all of them except yours. I'll take a deeper look, but I think it's going to make things awfully messy.

    Alan

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Runtime references and global variables (2000 sp-3/ 98SE/ VB6)

    So what about just declaring them as Variants? During development you can keep the UDT declaration (to have intellisense) and when ready to publish you comment those out and make them variants.
    You might even use a compiler constant to switch between development version and production version.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Runtime references and global variables (2000 sp-3/ 98SE/ VB6)

    Funny, I had just thought about that possibility after reading some of Jefferson's code. I followed your lead and declared them as generic "objects" ** then cast them as specific types in late-bound declarations. In the course of the process, I discovered a further "hitch", which I feel makes the whole idea unworkable. The Workbook_Open event is not recognized until after VBA does its checking on all syntax of all modules. Naturally, mine contains a mass of "errors" for undefined UDTs, since VBA has not even heard of my DLL yet.

    Unless I or some smarter head can come up with something else, I think I'll be telling them that the idea of such referencing at runtime is not feasible.

    Thanks for your ideas on this.
    Alan

    ** I used UDT in its broadest sense here, including class-based objects as well as user-defined data types. Sorry for any confusion.

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Runtime references and global variables (2000 sp-3/ 98SE/ VB6)

    Well, not entirely. For instance, if you put code specific to Excel 2000 or later in a separate sub (e.g. Userform1.Show vbModeless),, you get no compile errors.

    But only if you have "Compile on demand" set (default).
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Runtime references and global variables (2000

    I think I understand the general problem.

    I use a little developer application called "Generator", which inspects the doc variables of a template to obtain a list of folders, and Inserts every module from those folders into a template. Thus Utils holds generic VB code (string & array manipulation) and Word holds word-related modules (Document and Selection). I can just drop extra utility BAS and FRM into those folders and the code will be absorbed in the next generation of any appliaction that points to those folders.

    Very handy for tripping between Win98/office97 & WinXP/OfficeXP, and for disseminating new and really useful common code to all applications.

    Setting references can be a mess, though.

    Tonight I wondered what would happen if each module carried a recognisable comment such as <pre>'References:VBA332.DLL</pre>

    , and the generator.dot, besides issuing a (VBE) File Import for each BAS in the folder also inspected the contents of each BAS for such comments and made a valiant attempt to establish the reference, not in the template being run (a.k.a. generator.dot) but in the template currently on the operating table (e.g. LSUC.DOT or MyAp.dot).

    So, for a single application, here are the references from XP<pre>C:Program FilesCommon FilesMicrosoft SharedVBAVBA6VBE6.DLL VBA{000204EF-0000-0000-C000-000000000046}
    C:Program FilesMicrosoft OfficeOffice10MSWORD.OLB Word{00020905-0000-0000-C000-000000000046}
    C:WINDOWSsystem32FM20.DLL MSForms{0D452EE1-E08F-101A-852E-02608C4D0BB4}
    C:Program FilesCommon FilesMicrosoft SharedOffice10MSO.DLL Office{2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}
    C:Program FilesCommon FilesMicrosoft SharedVBAVBA6VBE6EXT.OLB VBIDE{0002E157-0000-0000-C000-000000000046}</pre>

    and here are the references from 97<pre>C:PROGRAM FILESCOMMON FILESMICROSOFT SHAREDVBAVBA332.DLL VBA{000204EF-0000-0000-C000-000000000046}
    C:Program FilesMicrosoft OfficeOfficeMSWORD8.OLB Word{00020905-0000-0000-C000-000000000046}
    C:WINDOWSSYSTEMMSForms.TWD MSForms{5D3BA762-73AC-11D9-BDC4-F9D349F3E549}
    C:PROGRAM FILESMICROSOFT OFFICEOFFICEMSO97.DLL Office{2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}
    C:PROGRAM FILESCOMMON FILESMICROSOFT SHAREDVBAVBEEXT1.OLB VBIDE{0002E157-0000-0000-C000-000000000046}</pre>


    At this stage I'm not quite sure how to interpret these. or how to make use of, say, the XP set to map to the 97.

    My main thrust is that I'm using one template to set references, programatically, in a second template, with automated means.

  10. #10
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Runtime references and global variables (2000

    I just played with a modification to Rob Bovey's code cleaner; I dump a module called "references", so that my generator can collect the information as a series of comments on the other side. This morning I thought 'Why not dump this information as Public Constants".

    So now I can look inside a project that has missing references, and at least see what OUGHT to be referenced.

    Here are the contents of the uniquely-named BAS module called "References20051027041244"<pre>Public Const strcShell32 As String = "C:WINDOWSsystem32SHELL32.dll"
    Public Const strcNorml As String = "F:Norml061.dot"
    Public Const strcSeqFld As String = "F:SeqFld061.dot"
    Public Const strcUW As String = "C:GreavesStartupWORDUW.dot"
    Public Const strcWord As String = "C:Program FilesMicrosoft OfficeOffice10MSWORD.OLB"
    Public Const strcVBA As String = "C:Program FilesCommon FilesMicrosoft SharedVBAVBA6VBE6.DLL"
    Sub test2()
    MsgBox UW.strDateTime
    End Sub</pre>

    The little test module runs IF the template identified by "strcUW " is hooked up, fails at run-time otherwise. But a call like this<pre>Sub test()
    MsgBox erase1.strcUW
    End Sub</pre>

    always gets results.

    At this point I may be able to detect that the project is not working, and at least I get can get a list of references for which it hungers.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •