PDA

View Full Version : Solved: Personal.xls vs xla



Opv
05-01-2012, 09:15 AM
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.

snb
05-01-2012, 02:25 PM
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.

Opv
05-01-2012, 03:41 PM
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.

Aflatoon
05-02-2012, 04:51 AM
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.

Opv
05-02-2012, 07:55 AM
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.

Opv
05-02-2012, 04:55 PM
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:


call myReset

However, once I removed my XLA addin, I'm now back to having to include:


Application.Run "Personal.xls!myReset"


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

Opv
05-02-2012, 06:21 PM
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.

Bob Phillips
05-03-2012, 12:39 AM
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.

Opv
05-03-2012, 07:25 AM
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.

snb
05-03-2012, 08:16 AM
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 ?

Opv
05-03-2012, 09:16 AM
??

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

Opv
05-03-2012, 07:35 PM
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?

Bob Phillips
05-04-2012, 01:27 AM
Have you ever used Application events?

Opv
05-04-2012, 07:23 AM
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.

Bob Phillips
05-04-2012, 08:04 AM
You could also create your own Book.xlt(m) with the code pre-loaded so that every new workbook inherits that code.

Opv
05-04-2012, 08:21 AM
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.

Opv
05-05-2012, 11:05 AM
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.