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.

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.

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.

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.

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.

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....

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.

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.

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.

Quote:

---

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 ?

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. :)

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?

Have you ever used Application events?

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.

You could also create your own Book.xlt(m) with the code pre-loaded so that every new workbook inherits that code.

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.

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.