Results 1 to 6 of 6
  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

    Compile-time differentiation (Office97)

    I am using Directives (#Constant and #IF) to differentiate between three methods of doing something in VBA. It all works quite well. I can now store session variables in the Registry, Little (64K/256 limit) INI files, or Huge (>64K,>265char) INI files. Cool. Or keen. Or Both.


    The VBA code works well both in Excel and in Word.

    Differences I've noted are :

    Word: strResult = MacroContainer.Path & strResult
    Excel: strResult = ActiveWorkbook.Path & strResult

    Word: strFullName = Options.DefaultFilePath(wdDocumentsPath) &
    Excel: strFullName = Application.DefaultFilePath &

    The code runs to about 1,100 lines (including comments), so I'm reluctant to maintain two source files.

    I've searched the Forum for "Directive" and read the posts lamenting that we can't distinguish between versions of Word fer goshsakes! so I'm not overly optimistic that anyone has solved the problem of how to differentiate beween appliactions at compile time.

    Nonetheless, I'm curious: Is there a way to differentiate, at compile time, between platforms? I know that I can do it at run-time (my code is, after all, concerned with obtaining the run-tim session varables for the end-user application), but I'd surely like to be able to port the source code between MSOffice's desktop applications without change.

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Compile-time differentiation (Office97)

    If you only have two differences, why not create a pair of Classes that export the same property names (e.g., pathCurrentDoc, pathDefaultDoc), but internally deal with the different plumbing of Word and Excel?

  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: Compile-time differentiation (Office97)

    > why not create a pair of Classes ..

    ... because I don't feel very comfortable with classes, which is really stupid, because I suspect that they are the direct equivalent of genuine "macros" from my old days of mainframe Assembler.

    [weary sigh] OK. I'll give it a shot [/weary sigh]


    ... and thanks for the nudge.

  4. #4
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Compile-time differentiation (Office97)

    Actually, they don't have to be classes. Functions in another module should work fine. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

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

    Re: Compile-time differentiation (Office97)

    I think I understand what you think I might do. I'm ready, at this point, to effect a solution that works in both Excel and Word. I'll tackle migration to PPT and Access when I'm more comfortable.

    WORD
    ====

    For some time now I've had a Utils.DOT for my Word/VBA applications.

    I initialize each user macro with a call to Initialize that says:
    <pre>Public Function Initialize()
    Call u.AutoExec
    c.strApplication = strcApplication
    c.strMacroContainerPath = MacroContainer.Path
    End Function
    </pre>

    My Utils.dot (project name is "U") has an Autoexec:
    <pre>Public Sub AutoExec()
    Set c = New UClass
    End Sub
    </pre>

    My little Class module (thanks to Geoff Whitfield some years ago) has, amongst other things:
    <pre>Property Get strMacroContainerPath() As String
    If strMacroContainerPathValue = "" Then
    strMacroContainerPathValue = MacroContainer.Path
    Else
    End If
    strMacroContainerPath = strMacroContainerPathValue
    End Property
    </pre>


    I'm using a Class module to facilitate different end-user applications being able to store data in their own folders. Since each appliaction will be installed in its own, unique folder, strMacroContainerPath will point to that folder.

    Why not use just "MacroContainer.Path"? because my appliactions make use of each other's engines, and so the primary application, being first to get a kick at the can, gets to define that IT is the host, and that strMacroContainerPath should point to ITS folder. Any other appliaction, called as a slave from the main appliaction, should discover that strMacroContainerPath is already set to a non-empty value, and need not be set to the slave's folder path.

    That should explain why I want a thing called "strMacroContainerPath"; I ought to be able to call my Interesting Words engine, written in Word/VBA, from any other Word/VBA appliaction WITHOUT having the Interesting Words engine destroy where we all want the appliaction data to reside.

    This has been working quite well In Word/VBA for several years now.

    EXCEL
    =====

    Now with Excel/VBA, I have utility modules, such as those that refer to INI files, that want to use the same ProfileStrings.BAS code as is used in my Word utils.dot.

    I have cloned my Word/VBA class module to my Excel/VBA Utility:
    <pre>Property Get strMacroContainerPath() As String
    If strMacroContainerPathValue = "" Then
    strMacroContainerPathValue = Application.Workbook.Path
    Else
    End If
    strMacroContainerPath = strMacroContainerPathValue
    End Property
    </pre>


    ... and to my great surprise, and gratitude for your nudge, it seems to work. (The proof of the pudding is when I wake tomorrow and discover it wasn't just a dream!).

    It's my Excel end-user application that holds the distinction of what makes the path to the container of VBA code.
    It's my Excel Utils.XLS that eases the distinction of what makes the path to the container of VBA code.
    The critical, common code in ProfileStrings.BAS can now refer to the class and talk about the class property "strMacroContainerPath" without having to worry about how it was created or initialized.

  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: Compile-time differentiation (Office97)

    > Actually, they don't have to be classes

    Grrrrrrr! I've got the darned thing working in Classes now.

    I daresay If I go back over my code I'll find that a simple function in the utility library would have sufficed. Anyway, both Excel and Word are enjoying a common independant profiles.BAS as we speak, so I'm not touching it again .....

    Thanks for spurring me on.

Posting Permissions

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