How-to use function stored in another workbook

[BuddJ]

I have a worksheet that has cells coloured via conditional formatting. I need to count the various cells by colour - after much searching & experimenting I've got the two functions below to work with the following formula :-
=CountCellsByColor(range, color code)

Problem is that I need to do this in many different workbooks. All my other macros are housed in a central workbook so that I don't have to do much more than open the "storage" workbook, the file to be worked on & then run the appropriate macro.

My question is how do I use the two functions below via that central workbook ??
What changes do I need to make to the functions (if any) & how would I "execute" the formula via the macro or would it be as simple as Range("A1").formula="=CountCellsByColor(range, color code)" ??

Many thanks

Please Login or Register  to view this content.

[alansidman]

create a personal.xlsb file and put all UDFs in that file. Here is a link for a complete explanation of Personal.xlsb.

https://www.rondebruin.nl/win/personal.htm

[BuddJ]

Thanks Alan - didn't know about those files.

Issue is that, unfortunately, I'm not always the person running the routines hence looking at ways so that anyone can run the macros which incorporate the functions.

[alansidman]

Need to create Personal.xlsb on PC of each user. It is PC centric not network centric.

[MrShorty]

I like to store my frequently used UDFs in add-ins. As with a personal.xls file, it still needs to be stored by each user/on each PC. Installing add-ins makes UDFs available to all open workbooks without the need to preface the function call with the file name (though the file/path are implicitly prefaced by Excel, so things get broken if the add-in is moved). http://www.ozgrid.com/VBA/excel-add-in-create.htm