CODEWITHSUNDEEP
vbaexceladd-in
Alpha
Alpha

Reputation: 2442

How to Create VBA Add-In with Shared Codes for All Excels?

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

Upvotes: 1

Views: 1259

Answers (1)

tpascale
tpascale

Reputation: 2576

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

Upvotes: 1

Related Questions