1

I'm writing VBA codes for multiple Excel spreadsheets, which will be shared with others from time to time. At some point I find there are lots of duplications in my works. So I want to find a way to share codes in a sort of Excel add-in, like the .xla file.

But when I tried to save the Excel file containing shared codes as .xla file, I got some problems:

  1. The file cannot be edit anymore after I save it in the default add-in folder
  2. If I move the .xls file to a folder other than the add-in folder, and open it directly - I cannot use its classes - which creates problems for sharing the codes

Any ideas to create add-ins in a flexible and powerful way please?

Thanks a lot for the help

Alpha
  • 2,372
  • 3
  • 21
  • 23
  • 2
    `The file cannot be edit anymore after I save it in the default add-in folder` Till the time you don't password protect the VBA code, you can always edit it :) – Siddharth Rout Apr 15 '12 at 12:17
  • There is no password in the xla file. I'll give it a try tomorrow though. Thanks for the comment – Alpha Apr 15 '12 at 15:34

1 Answers1

1

Not completely sure this is what you're looking for, but ...

(1) save the .xla/.xlam code by clicking the save icon in the VBA editor. HOWEVER, the thing that saves is the thing currently selected in the Project Explorer pane, which lists all open VB Projects and which is usually on the left. Even if you are staring at your just-edited VBA code, clicking the save icon will not save your code unless it is also selected in the Project Explorer pane. You won't get feedback that anything was saved - but you can verify by checking the file timestamp in a separate window.

(2) if you have an *.xls file which (via the formula bar) refers to VBA functions from your *.xla / *.xlam file, then if you open the *.xls file without opening the .xla,.xlam file first, Excel may create external links to resolve the formulas (i.e. referring to a file which is not open). If you have moved or renamed the *.xla file you can get stuck with those "mangled formulas" and need to edit out the pathname links that Excel inserted using a global substitute. If you arrange to open the .xla,.xlam prior to any *.xls file that uses it, you shouldn't have a problem (e.g. by using the default folder).

tpascale
  • 2,516
  • 5
  • 25
  • 38
  • Thanks for the answer. But unfortunately this is not what I'm looking for. One of my problems is, the .xla file, once I make it an add-in in Excel, I cannot modify it anymore. – Alpha Apr 15 '12 at 15:31