Results 1 to 10 of 10
  1. #1
    5 Star Lounger st3333ve's Avatar
    Join Date
    May 2003
    Location
    Los Angeles, California, USA
    Posts
    705
    Thanks
    0
    Thanked 2 Times in 2 Posts

    VBA library for Access & Word (VBA 6.3 / Office XP)

    I'm a longtime Access user just starting to use Word. I have a library database in Access that includes a lot of VBA procedures (and a few forms), and I'd like to be able to call some of those VBA procedures from Word macros. It looks like I can't set a Reference from Word to a database file. Is there some other kind of file format into which I can put these VBA library procedures so that I can reference the same library from both Access and Word? Or is there some other solution that will avoid my having to maintain 2 separate copies of the same library of procedures?

  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: VBA library for Access & Word (VBA 6.3 / Office XP)

    If you have the Developer Edition of Office or if you have Visual Basic/Visual Studio, you could compile the routines into a COM Add-in. However, this is a lot of work for the application you describe. You might be better off exporting your code to modules and storing them in some kind of version control environment, and then importing them as needed into your Word and Access projects. Lower overhead, and still a single code base on disk, even if you end up with separate containers in each application at various times.

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VBA library for Access & Word (VBA 6.3 / Office XP)

    You wouldn't be able to use Access forms in Word anyhow. The two applications use entirely different forms libraries. You can export individual Access modules as *.bas files, which Word should be able to recognize as code modules, but unless you compile the code as a dll or as Jefferson suggests, there isn't any way to share it between the applications.
    Charlotte

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

    Re: VBA library for Access & Word (VBA 6.3 / Office XP)

    I've not trie this with Access, so I dunno if it will work.

    First approach I would take would be to compile the Access VBA code ino a VB6 DLL.
    The DLL would be compiled with a reference to the relevant Access library.

    As necessary the VB 6 DLL would start an instance of Access to use the Access forms.
    Perhaps, some/all of the Access forms can be concerted to VB 6 forms?

    That's all I can think of at this time of night.

    Word could then call the code in the DLL.

  5. #5
    5 Star Lounger st3333ve's Avatar
    Join Date
    May 2003
    Location
    Los Angeles, California, USA
    Posts
    705
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: VBA library for Access & Word (VBA 6.3 / Office XP)

    Thanks for the replies. As I indicated in my initial post, I was only looking to have some of the procedures (none of the forms) be available to both applications. I only have Office XP Pro, not the Developer's Edition or any stand-alone Visual Basic product, and I believe that means I don't have the ability to compile my procedures as a DLL (or COM Add-In, if that's different). Correct?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA library for Access & Word (VBA 6.3 / Office XP)

    Correct, you need either Office Developer Edition or VB6 to compile your code into a DLL or COM add-in.

    I don't think it's a great loss that you can't - you'd have to recompile the thing each time you change some of the code, so it wouldn't be very flexible. It would have been useful if you have procedures or functions that execute very slowly when interpreted; compiling would speed up execution. For standard routines you'd hardly notice the difference.

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

    Re: VBA library for Access & Word (VBA 6.3 / Office XP)

    There is a large performance difference using a compiled DLL, even with vanilla Excel code.

    And a compiled VB 6 DLL protects the code from prying eyes.

    Not to mention, pulling the code out of the workbook, makes it much easier for the single copy of the code to be used with multiple workbooks.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA library for Access & Word (VBA 6.3 / Office XP)

    <hr>There is a large performance difference using a compiled DLL, even with vanilla Excel code.<hr>
    I don't know what kind of code st3333ve has in his Access database. For many short utility procedures and functions, compiling won't make a substantial difference in real life. If the code involves handling large amounts of data (whether records, cells or paragraphs), yes, the difference will be noticeable.
    <hr>And a compiled VB 6 DLL protects the code from prying eyes.<hr>
    That is a good argument. Don't know, however, if that is what st3333ve needs.
    <hr>Not to mention, pulling the code out of the workbook, makes it much easier for the single copy of the code to be used with multiple workbooks.<hr>
    A regular add-in offers the same advantage.

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

    Re: VBA library for Access & Word (VBA 6.3 / Office XP)

    My experience with DLLs wit hOffice is largely with Word and Excel.

    I have noticed performance improvements of 10-20% using a compiled DLL with a large, perhaps inefficiently coded, Excel app.

    In another example, which I've been intending to post for a loooong time, the performance improvements were even greater using both Word and Excel.

    I do consider protecting the code to be of overwhelming importance.

    Dealing with a DLL seems to be a bit easier than dealing with the additional interface isues of a COM Add-In.

  10. #10
    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: VBA library for Access & Word (VBA 6.3 / Office XP)

    > Dealing with a DLL seems to be a bit easier than dealing with the additional interface isues of a COM Add-In.

    With a COM Add-in, you end up with a single container for your pain. With a DLL, you also need to distribute a calling procedure (e.g., a .DOT or .XLS). Choose yer poison. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

Posting Permissions

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