Format a Cell from within a defined function

[kkjensen]

Hi there,

I've written my own little function to do some particular bits of math that I don't want to have to repeat over and over.

When I write the function into a cell (not using my defined function) I generally do a custom format like:

[Red][>2]">2";0.00

I don't want to have my defined function outputting the text ">2" since I want to be able to link to the cell from other functions. Is there a way to (in a defined function) change the format of a cell to "custom" and enter something? I know how to do this in a macro using Activecell.numberformat = "0.00" etc etc but this does not work in a defined function.

 

[Andrew Poulsom]

Functions can only return values. They can't perform actions like formatting cells, as you have discovered.

So the answer is no.

 

[kkjensen]

Could a function call a macro? (which would then do the formatting)?

 

[Andrew Poulsom]

Could a function call a macro? (which would then do the formatting)?

No. Functions can only return values. They can't call a procedure that changes formats.

 

[kkjensen]

Thanks.

I was always a bit curious about the differences between functions and macros. I thought they were essentilaly the same thing but a macro only ran when you told it to and a function updated according to the changing value of the other cells or values it refers to.

 

[Andrew Poulsom]

Have a read of this:

http://www.cpearson.com/excel/differen.htm