CellColorIndex function for a different workbook?

[Sayre]

I have used Chip Pearson's CellColorIndex function and it is very useful but only seems to work if the cells you reference in your formula (=cellcolorindex(A1,False) are in the same workbook as the function itself.

I have a situation where I need the function to reside in one workbook, but I need to write the formula that references it to be written in another workbook and reference cells in the other workbook.

Hope that makes sense? Is this possible?

The other workbook where I will write the formula will always be called "HDS_1" if that helps. Let me know if there is a link that specifically addresses it and I'll take it from there, thanks!

 

[Sayre]

Neither way seems to work.

When I type in =Personal.xls!CellColorIndex(Sheet1!$A$1,False) and then hit tab, a prompt box opens up asking me to select a file. Upper left-hand corner says "Update values: Sheet1".

Same thing happens when I try it with "HDS_pricebook_analyzer...." in the formula.

I would love to be able to have the macro write the Function directly into the HDS_1 file, but I do not know how to do that. Seems like it should work without having to do that? Any ideas why it is giving me this Prompt that I did not ask for?

 

[HalfAce]

Hmm. Only guess I'd have is maybe you don't have a Personal.xls workbook saved on your machine.

If you have both HDS workbooks open, and the function in a standard module of the 'price analyzer' book, the formula:

=HDS_pricebook_analyzer_9OCT2006.xls!CellColorIndex(Sheet1!$A$1,FALSE)

in the HDS_1 book, it should return the colorindex number for the background of Sheet1 A1 in the HDS_1 book.
(It does for me, but then it works with the function in my Personal.xls as well.)

For an idea of how to use vba to write/install the function in a workbook, check out Chip's site.
http://www.cpearson.com/excel/vbe.htm
Scroll down & find 'Adding a module to a workbook', 'Adding a procedure to a module' or perhaps 'Copying modules between projects'

 

[Sayre]

I just tried it again and now it works! Not sure what I was doing wrong before, probably just typoed something. Thanks! I do need to check out more of Chip's stuff. Moving modules around between workbooks will probably be especially handy for me in the future. Thanks again!

 

[HalfAce]

Most welcome. Glad you got it working.

I haven't messed around with writing/editiing code with vba for a while now but I do remember
there being some issues with the computer being worried about viruses and/or malicious code.
Don't remember if it was the antivirus software or what, I just remember there being something
so keep this in mind when you start playing with it. :wink: