Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Why migrate to a Utility DLL? (Word97SR2 et al.)

    Musings:

    Three or four years later I'm still struggling with the concept of utility functions for VBA development.

    Until a couple of months ago, my solution(s) were based on a template Utils.dot which held a module U, which held about 400 utility procedures, mainly Functions. As the moon waxed, I'd split the module U into a set of modules (UStrings, UFiles, UEnvironment etc); as the moon waned I'd merge them back again.


    I've just moved my module U of Utils.dot to a VB6 project Utils.VBP. ref attached AllProcs.doc

    I now have 400 utility functions available to me (and you!) in Utils.dll, and the old-fashioned copies of many of the procedures in each of about 40 application templates.


    Problems present themselves:

    Because the module U was so large, I wrote the Procedure Stripper to remove unreferenced procedures from that copy of U which was placed in any application, so the applications each have a stripped-down version of U, to which I have added utility functions local to that application. I can't rip the module U out of each application and replace it with calls to the DLL.

    I should first standardise my DLL procedures so that they use prefixes "bln" rather than a mixture of "bool" and "bln", but then how would I locate the references to "bool" functions in the applications once I've changed the names?

    Early procedures used the name of a document (strDoc) as a parameter, because I wasn't confident in using the document object directly (nowadays I prefer the object!). I should upgrade these procedures first, because so many procedures are DOCument utility functions.

    I should document the 400 procedures before making them public to myself, but suppose I spent 5 minutes per procedure, that's 2,000 minutes or 300 hours; I can't set aside that much time.

    I can write code that will trawl each module of each application and locate references to procedures that exist in the DLL and make the appropriate substitution, including assigning a reference to the DLL. That job could run overnight (grin!)


    All in all, it seems like a lot of work.

    What's the payoff to migrating to a DLL? It can't execute much faster than VBA, if only because in many cases I'll be calling the Word object.


    My conclusion is that the only real payoff (in migrating my Utils.dot to a Utils.dll) will be reduced labour in keeping each utility function up to date. After all, if I upgrade a procedure in my Utils.dll, it becomes available immediately to any one of the 40 applications, whereas when the procedure existed solely as a source function in a local copy of the U module, I had to remember which applications made use of that function, and copy/paste the upgraded source into each affected application template - which didn't get done.

    (to be continued ...)
    Attached Files Attached Files

  2. #2
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Why migrate to a Utility DLL? (Word97SR2 et al.)

    I can't help thinking that in excel, what you're attempting to do would be normally done via an .xla, thus making the functions freely available but only having one copy - so the best move for your idea would surely be to declare your functions .doa, no? <img src=/S/innocent.gif border=0 alt=innocent width=20 height=20>

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

    Re: Why migrate to a Utility DLL? (Word97SR2 et al.)

    > .doa,

    This brooks no comment, but what the hey!


    You raise (again) the distinction between the different aps; Charlotte never tires of telling us that Access is different, now you have joined in the fray.

    Tell me, oh Excellent wizard, if I elected to port my WordVBA procedures to an XLA, could I then access them as easily from Word, or do you thing the VB6_DLL is the better route?

    Let me rephrase that - would the XLA method be the poor man's solution, for those who didn't want to spring for VB6?

  4. #4
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Why migrate to a Utility DLL? (Word97SR2 et al.)

    Well I guess you could, but if you were to dump them in an xla then you'd either need to rewrite them all in xlspeak and make sure your xla was installed in excel on all your target machines, or wrap all your functions with something like

    Set WordObj = GetObject(, "Word.Application.9")
    If Err.Number = 429 Then
    Set WordObj = CreateObject("Word.Application.9")
    Err.Number = 0
    End If
    On Error GoTo 0

    'your old code here

    WordObj.Quit
    Set WordObj = Nothing

    and then make sure the xla was installed on every target machine.

    seems like a lot of work. And not every machine will have excel on it.

    Your Customer:
    "Explain again why I need to purchase an excel license for every machine that I want to install your word app on when you're already charging me more money than I can afford/want to pay because I'm a cheapskate*delete as appropriate?"


    No, I'd say you're better off sticking to one app. Feel free to prove me wrong!

  5. #5
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: Why migrate to a Utility DLL? (Word97SR2 et al.)

    Chris, Brooke:

    This is what global .dots in Word are for, no?

    (Your global functions don't go into individual templates; you put them into one (or more) global templates which can be made always available by dint of their being located in Word's Startup directory.
    That way all your version updates are just done to that one central .dot.)

    Unless I'm misunderstanding - Chris, do you need to run these functions from applications other than Word?

    Gary

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

    Re: Why migrate to a Utility DLL? (Word97SR2 et al.)

    > all your version updates are just done to that one central .dot

    Desirable, but my understanding is that only macros - SUBroutine procedures without parameters - can be accessed from global dots.

    I can Application.Run strMacroName from any added in template, but when I want to make use of my strreplaceAll(str1,str2) function, globally, either I must copy/paset updated source to a template or make use of a DLL. That's my current understanding.

    I don't (yet) need to run these functions from other than Word, but I have a couple of Excel apps, a PowerPoint app or two, and I have previously dragged my entite U module (400+ functions) into an Access App.

    There's no reason that I can see that the Utils.DLL shouldn't be attractive to a VB6 developer - after all, there's a slew of dandy string handlers PLUS a gateway to stuff that goes on in Word Docs, especially components of project templates.

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

    Re: Why migrate to a Utility DLL? (Word97SR2 et al.)

    > I'm a cheapskate

    ... but see poor man's solution above

    You're right.

    It would make sense to ME as a developer, for my applications that I develop and run on my machine, which has all of MSOffice.

    Another way: If Brooke were doing this, he being an XLA guru, it would make sense for him to gather his utility procedures (SUBs and FUNCTIONs) into one file that was globally available.

    I suspect that Excel has some sort of sharing that is better than Word. We only get to share macros (SUB without parameters).

    Any client that has Word is, say, 99% certain to have Excel.



    > Feel free to prove me wrong!

    I'm trying to learn not to do that (grin!)

  8. #8
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: Why migrate to a Utility DLL? (Word97SR2 et al.)

    Chris,

    There is a way to access functions, and subs that take parameters, that are stored in a global .dot:

    Assuming your documents are based on a custom template or templates - open Template.dot, in the VBE go to Tools > References and add a reference to Global.dot. You now have access to everything in Global.dot as if it were in Template.dot. If you set a reference to Global.dot in all your templates, you can store all your utilities and classes in Global .dot and they will be available to all your other templates. (In addition to access to subs and functions, another benefit of this is that you can store application-global constants and variables, in the global.dot.)

    Back to the DLL topic: starting with Office 2000 you have the ability to build what MS calls 'COM Add-ins" but which are just a kind of .dll - you can build these using either Office 2000 Developer Edition or VB6. There's an intro to this on MSDN here.

    Gary

  9. #9
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Why migrate to a Utility DLL? (Word97SR2 et al.)

    Chris,

    I just store everything (update - now including those routines you and I were talking about; <img src=/w3timages/censored.gif alt=censored border=0> of a job but it had to be done - came down to a piece of code from jujuraf) in one or two xls - not even xla. I don't understand why what Gary's suggesting won't work for you. Or is it that you've just never been down that road?

  10. #10
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Why migrate to a Utility DLL? (Word97SR2 et al.)

    Also, unless it is necessary to use Word 97, Word 2000 and Word 2002 allow args to be passed in Application.Run.

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

    Re: Why migrate to a Utility DLL? (Word97SR2 et al.)

    > set a reference to Global.dot in all your templates,

    You are right. I just got around to trying it, and it works fine.

    Was I asleep when this was discussed, or were you all talking when I left the room?



    So, from a Word/VBA developer's point of view, that removes the use of VB6-DLLs as a distribution mechanism - except for timing.

    I could as easily distribute client source code in a client template, and bundle it with a set of common utility procedures in a locked Utils.DOT.

    The only reasons, now, for making VB6-Dlls are (1) possible improvement in execution speed and (2) common use between other MSOffice aps and other comp0ilable aps in VB6, C++ maybe.

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

    Re: Why migrate to a Utility DLL? (Word97SR2 et al.)

    > you've just never been down that road?

    Right. I don't know how I missed it.

    I seem to recall, about a year ago, Geoff W starting a discussion on this. At that time I was convinced (by my own arguments) that only by passing values through the registry or an INI-like file could one achieve parametric calls between Global templates

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

    Re: Why migrate to a Utility DLL? (Word97SR2 et al.)

    I can get basic functions to work for me as Global templates, but I can't get Class Modules to work for me without a fudge.

    The fudge is to create a cover function in a User Module of the global template, and to use the cover function to call the functions in the Class Module of the global template.


    I have created and used Class Modules successfully in VB6 work (see UGreaves.dll for example), but when I set up a Word97SR2 global template (Global2.dot) with a class module, I don't seem to be able to reference the [serach]"Property Procedures"[/search] directly. Instead I access them via simple functions in the User Module.


    The attached PKZIP25 file contains four templates:

    These first two demonstrate the principles that Gary and others pounded into my thick skull a month or two ago. examine these if you've never played with Global templates before.

    Global1.dot : Example of a function that operates on a string.
    Local1.dot : Example use of Global1.dot


    These next two demonstrate my problem and weak solution. Come straight to these if you name is Gary Frieder.


    Global2.dot : Example of a Class Module function.
    Local2.dot : Example use of Global2.dot



    If you are a guru, see if you can get Local2 to do its thing without the use of the Global2 functions strGetApp and strLetApp.
    Attached Files Attached Files

  14. #14
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: Why migrate to a Utility DLL? (Word97SR2 et al.)

    Hi Chris,

    Have been away for a while hence the delay in responding.
    I'm not near my work stuff at the moment to jog my memory as to how we did it, but the following does work:

    Put the following code into a regular code module in the global template:

    <pre>Option Explicit
    Public c As Class2
    Public Sub AutoExec()
    Set c = New Class2
    End Sub
    Public Sub AutoExit()
    Set c = Nothing
    End Sub</pre>

    The global template needs either to be in the Startup directory or else loaded as an add-in after Word starts.

    This should make the procedures in the class module directly callable from code in the local template.

    Possible drawback is that you do have an instance of Class2 in memory throughout the Word session; probably doesn't matter if you're not using loads of classes. I think there is a way to do it where you can instantiate the class as needed from the local template, but don't recall how - will check when I get back into work.

    Gary

  15. #15
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Why migrate to a Utility DLL? (Word97SR2 et al.)

    It's also not much trouble (if you don't want the class instantiated at global load time) to do this:

    With New Class2
    .classpropertyFilename = "bla"
    .classmethodCopytoFloopyorwhatever
    end with
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

Page 1 of 2 12 LastLast

Posting Permissions

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