Excel/Personal.xlsb or ?
QUESTION: I just learned about personal.xlsb even thought i have written some extensive multiple file excel applications. In the near future some of my applications will not only be on my computer but someone else. I thought of putting common macros in the personal file but wondered if have a common file just holding the common macros makes more sense.
I also probably can't control file locations, so how can i make this flexible and where can i find some articles on how to use common commands with variable input?
ANSWER: Hi Joe,
Yes, Personal.xlsb is great if you have macros or custom functions you use frequently with many of your workbooks. As you mentioned, it does have the downside that it makes the distribution of your apps to others more complicated. This is especially true if the "others" have their own Personal.xlsb workbooks--and they must then decide whether to combine your Personal file with theirs, temporarily replace theirs with yours, or perhaps merge it into the application workbook you are providing. For this reason I generally try to make my workbooks "stand alone" in that they contain all the macros and functions they need.
You mentioned "common" macros, and I'm not sure what you mean by this, but am guessing that you are distributing multiple apps to other users, and all these apps have many of your macros and functions in common. Unless these macros and functions involve thousands of lines of code they probably add very little to the size of the files and the only reason I can see for putting them all in one place (such as in Personal.xlsb) is if you are frequently making updates to them and you want to make the changes once and have all the dependent apps benefit from the updates. Of course you would still have to re-distribute the Personal.xlsb to all the users, but might not have to re-distribute the dependent apps if there is no need to.
I should mention that an alternative to using a Personal.xlsb is creating an Add-In. Add-Ins can contain multiple macros and functions and have the advantage (or disadvantage) that they are "compiled" by Excel, and therefore not readable or changeable by the users. In addition to placing the add-in file in the appropriate Excel Library folder, the users must also install the add-in in Excel.
I didn't understand the part of your question about "how to use common commands with variable input". If you would care to clarify this I would be happy to attempt to answer this as well.
---------- FOLLOW-UP ----------
QUESTION: Thanks for the answer. Some followup:
Since I will be distributing applications - i was wondering if it makes sense to have an application specific file that contains all the common macros that are used for the application. One application contains about 10 "master" files that are then used quarterly for the 20 different entities, so I end up with about 200 files a quarter. The "common" macros are potentially used in all the 10 files. So if i had a MACRO.xlsm, instead of putting those common macros into personal.xlsb, for the application I was wondering if it would be easy to call macros from that file, because they do change regularly.
The common command will be once the application is distributed - the file structure will probably be different. So for 1 user the files maybe in C:\excel\app and for another user the files maybe in c:\joe\app. So I was looking for information on getting a variable for "c:\excel" or c:\joe", etc to use in the applications.
I am self taught so if you could direct me to a good resource I will be glad to learn more about this.
Hi again Joe,
Yes, you can put the macros in MACRO.xlsm and run them from your app that is a different workbook. Let's say you have a macro named MyMacro in the MACRO.xlsm file. To run MyMacro from your app:
You can also pass arguments to MyMacro if it has any, but the arguments must be passed by position, not by name.
As you can see, the macro workbook does have to be opened by Excel to run the macros. The same is actually true of the Personal.xlsm file, except Excel opens this file invisibly.
Regarding your second question about putting the path to the location of the app into a variable, you can do this also. In the code of your app you can use the workbook's path property, like this:
Dim AppPath As String
AppPath = ThisWorkbook.Path
which would set AppPath to "C:\Joe", for example.
There are hundreds of great resources for VBA programming. I consider the books by John Walkenbach among the best. For online resources I really like Chip Pearson's website www.cpearson.com. There are also some great bulletin boards for asking specific questions, such as MrExcel.com.
I hope you find this helpful.