0

So I have an excel add-in that, amongst other things, contains a huge list of aliases. For example, Country names with their ISO codes, Countries with their continents, etc etc (so that I can easily know that Canada is in America or that Côte D'Ivoire's national language is French, etc). Currently, I have the xlam with all the relevant functions that check named ranges in another workbook.

So summarizing, I have 2 files:

  • macros.xls (has all the named ranges with things like Angola = AGO, etc), and

  • my_functions.xlam (has the functions that I can call from excel to get the Alias name). An example function is:

    Function nti(v) nti = WorksheetFunction.VLookup(Trim(v), Range("macros.xls!nti"), 2, 0) End Function

Where nti is the function that I use and macros.xls!nti is the named range that I am referencing.

This all works great, but that means that this functionality requires two files, since an .xlam file can not be opened and edited in excel, only in VBA explorer.

Question: How can I have an easily modifiable lists and functions referring to these lists in one file? The functions should be available to other files I'm working on.

Disqualified solutions:

  • Save the my_functions.xlam as an .xls temporarily, copy all the named ranges in, and then save it back as an .xlam. This would be annoying to do every time I want to make a change to the named ranges (which is fairly frequent).
  • Hardcode a bunch of 2 column arrays with all the aliases into each function (thousands of lines long sometimes). PLEASE tell me there's a sexier way...

Thanks in advance!

Amit Kohli
  • 2,860
  • 2
  • 24
  • 44
  • How about `PERSONAL.XLS / PERSONAL.XLSB`? Check [THIS](http://www.rondebruin.nl/win/personal.htm) out. – L42 Apr 10 '14 at 09:04
  • The problem is that for functions to be globally available, they should be in a .xlam, which seems mutually exclusive with having some kind of editable platform. – Amit Kohli Apr 10 '14 at 09:24
  • What do you mean by globally available? `PERSONAL.XLSB` is globally available in my opinion. – L42 Apr 10 '14 at 10:04
  • Check out: http://support.microsoft.com/kb/151490 – Amit Kohli Apr 10 '14 at 14:12

1 Answers1

2

I usually just toggle the IsAddin property of the XLAM from true to false, edit the worksheet and then toggle the property back again before saving.

Charles Williams
  • 23,121
  • 5
  • 38
  • 38
  • Well that certainly was something! First time I tried to save it crashed my excel! Second time I tried, it allowed me to save, but now none of the functions work. It doesn't even call the function at all (I set a breakpoint and it's not even pausing the function), even if I'm calling the function from the .xlam itself. I checked and it's still being read as an add-in. In other words, it REALLY didn't like me messing around in the sheet. But this is definitely what I would have wanted to do... good call. – Amit Kohli Apr 10 '14 at 14:15
  • I copied everything to a fresh sheet, changed all my named ranges, resaved, re-add-in'ed ... basically I just redid every step (I had to anyway since everything was broken), and it works! Thanks Charles! – Amit Kohli Apr 11 '14 at 08:46