Consulting

Results 1 to 17 of 17

Thread: Solved: Personal.xls vs xla

  1. #1
    VBAX Expert
    Joined
    Feb 2010
    Posts
    696
    Location

    Solved: Personal.xls vs xla

    Would someone please explain whether there is any difference, and if so the pros and cons, of storing functions/subs used across multiple workbooks in the Personal.xls file vs creating an XLA addin?

    I know an XLA can be linked in the references so that functions/subs can be called from other workbooks as if they were a part of those workbooks. I'm not sure that can be done using the personal.xls file without using Application.run. If, however, the personal.xls file can be lined in the references then I fail to see the difference between the two options.

  2. #2
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    If you want to exchange code sending your personal.xls is of little use.
    If the receiver replaces her/his personal.xls (s)he loses all own macros.
    If you send an addin it adds (instead of replaces) functionality to the receiver.

  3. #3
    VBAX Expert
    Joined
    Feb 2010
    Posts
    696
    Location
    Quote Originally Posted by snb
    If you want to exchange code sending your personal.xls is of little use.
    If the receiver replaces her/his personal.xls (s)he loses all own macros.
    If you send an addin it adds (instead of replaces) functionality to the receiver.
    I didn't think of that aspect. Thanks.

  4. #4
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,720
    Location
    If you use an xla, none of your macros will appear in the macros dialog (although you can run them from there if you know the name). You have to create an interface for them.
    Be as you wish to seem

  5. #5
    VBAX Expert
    Joined
    Feb 2010
    Posts
    696
    Location
    Quote Originally Posted by Aflatoon
    If you use an xla, none of your macros will appear in the macros dialog (although you can run them from there if you know the name). You have to create an interface for them.
    Thanks. Since my objective is not so much to be able to share modules, it sounds as if the Personal.xls file might be the best option. I can link it by reference the same as can be done with the XLA file and thereby call functions/subs as if they are contained in the current workbook, plus they show up in the dialog box. It sounds like the advantage goes to Personal.xls, for my purposes anyway.

  6. #6
    VBAX Expert
    Joined
    Feb 2010
    Posts
    696
    Location
    Quote Originally Posted by Opv
    Thanks. Since my objective is not so much to be able to share modules, it sounds as if the Personal.xls file might be the best option. I can link it by reference the same as can be done with the XLA file and thereby call functions/subs as if they are contained in the current workbook, plus they show up in the dialog box. It sounds like the advantage goes to Personal.xls, for my purposes anyway.
    Well, I may have spoken too soon. I couldn't tell the difference in how a sub ic called (from an XLA vs Personal.xls) so long s the my XLA ws installed and linked. I could call a sub contained in either my XLA or my Personal.xls workbook from another workbook by simply stating:

    [vba]
    call myReset
    [/vba]
    However, once I removed my XLA addin, I'm now back to having to include:

    [vba]
    Application.Run "Personal.xls!myReset"
    [/vba]

    It's a minor annoyance but I was hoping to retain the simplified statement. Now I'm having to rethink whether I want to go back to using the XLA file. Decisions...decisions....

  7. #7
    VBAX Expert
    Joined
    Feb 2010
    Posts
    696
    Location
    Disregard my previous post. I tried to edit it to remove what I had posted but I apparently waited too long to be able to do so. Once I reapplied the linked reference, the code works fine.

  8. #8
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    I would go for XLAs every time. It does need an interface as was mentioned, but that is easy enough to do. I used to use Personal.xls but I switched to XLAs some time ago and it has been a far simpler journey.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  9. #9
    VBAX Expert
    Joined
    Feb 2010
    Posts
    696
    Location
    Quote Originally Posted by xld
    I would go for XLAs every time. It does need an interface as was mentioned, but that is easy enough to do. I used to use Personal.xls but I switched to XLAs some time ago and it has been a far simpler journey.
    Thanks. I learned another downside to the Personal.xls last evening. In order to be able to use "Call myReset" as opposed to "Application.Run "Personal.xls!myReset", one has to link individually each new workbook to the Personal.xls file in the references; whereas, scripts in an XLA file are readily available for use in all Workbooks. I suppose the linking process could be automated but not sure it's really worth it.

    Thanks again.

  10. #10
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    In order to be able to use "Call myReset" as opposed to "Application.Run "Personal.xls!myReset", one has to link individually each new workbook to the Personal.xls file in the references
    ??

    Is the personal.xls file in the Excel Application.StartupPath ?

  11. #11
    VBAX Expert
    Joined
    Feb 2010
    Posts
    696
    Location
    Quote Originally Posted by snb
    ??

    Is the personal.xls file in the Excel Application.StartupPath ?
    Yes. The longer version of the call statement is only needed if Personal.xls is not linked to VBAProject in the references.

    That said, I again spoke too soon in my previous post. It appears after further experimentation that both the Personal.xls and the MyStuff.xla have to be linked individually to each of my other workbooks (as well as to each new workbook created). I was ostensibly mistaken in thinking the XLA was automatically linked to all VBA Projects, unless of course I'm missing something, which is entirely possible. I know just enough to be dangerous.

  12. #12
    VBAX Expert
    Joined
    Feb 2010
    Posts
    696
    Location
    OK. I have automated the process of linking my workbooks to MyStuff.xla (References). All I can come up with so far is to manually insert the code into each workbook (including each new workbook). Unless I'm missing something, that seems like it would be about as much trouble as manually linking the workbooks to the XLA file. I tried including the new modules in a Template in hopes that when I create a new workbook the script would be created along with the new workbook, but that does not seem to be working.

    I've read that it is also technically possible to automate the export and import of modules but that the process could be blocked by ones virus software. Is there an alternative procedure to automatically modify the ThisWorkbook module and insert a new module of code whenever a new workbook is created?

  13. #13
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Have you ever used Application events?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  14. #14
    VBAX Expert
    Joined
    Feb 2010
    Posts
    696
    Location
    Quote Originally Posted by xld
    Have you ever used Application events?
    No, I've only used Workbook and Worksheet Events. My skill level hasn't yet afforded me the ability to grasp the significance of Application Events. I'll read up on them and see if anything sinks in.

    Thanks.

  15. #15
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    You could also create your own Book.xlt(m) with the code pre-loaded so that every new workbook inherits that code.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  16. #16
    VBAX Expert
    Joined
    Feb 2010
    Posts
    696
    Location
    Quote Originally Posted by xld
    You could also create your own Book.xlt(m) with the code pre-loaded so that every new workbook inherits that code.
    I tried that but the code wasn't showing up in the new workbooks. I must have been doing something wrong as I just tried it again and it indeed worked this time. Making progress.

    Thanks.

  17. #17
    VBAX Expert
    Joined
    Feb 2010
    Posts
    696
    Location
    Quote Originally Posted by xld
    Have you ever used Application events?
    I found several examples of capturing a Application (NewWorkbook) Event and was able to incorporate a modified version of the code found here http://www.vbaexpress.com/kb/getarticle.php?kb_id=693 to get it to work like I want. I also tried the Book.xlt option and it also works but I prefer to have the code residing in one place rather than in each new workbook that is created. So far so good. Thanks for all your help.

Posting Permissions

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