Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing a cell color inside a function called by that cell
When I try to change the color of cell from a function called from that cell, the change never takes place.
function SetMyColor (colorindex) Application.Caller.Interior.ColorIndex = colorindex 'this line never works If Application.Caller.Interior.ColorIndex = xlNone Then SetMyColor = "None" Else SetMyColor = Application.Caller.Interior.ColorIndex End If end function I've tried any number of variations, including hardcoding the range, for example: Range("J4").interior.ColorIndex = 17 and the color is never changed. I can change the color from a stand-along macro, just not from a function. If I change the color manually, the function will return the colorIndex successfully. Is this symptom part of a general issue of changing cells from within functions they call? Is there a way around it? Thanks! ---Wayne Erfling |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing a cell color inside a function called by that cell
Yes it is a general issue - in that this action is not allowed.
Functions in Cells may only return values to the cell in which they are entered. They may not change the excel environment. You might look at conditional formatting. -- Regards, Tom Ogilvy "Wayne Erfling" wrote in message ... When I try to change the color of cell from a function called from that cell, the change never takes place. function SetMyColor (colorindex) Application.Caller.Interior.ColorIndex = colorindex 'this line never works If Application.Caller.Interior.ColorIndex = xlNone Then SetMyColor = "None" Else SetMyColor = Application.Caller.Interior.ColorIndex End If end function I've tried any number of variations, including hardcoding the range, for example: Range("J4").interior.ColorIndex = 17 and the color is never changed. I can change the color from a stand-along macro, just not from a function. If I change the color manually, the function will return the colorIndex successfully. Is this symptom part of a general issue of changing cells from within functions they call? Is there a way around it? Thanks! ---Wayne Erfling |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing a cell color inside a function called by that cell
Functions return values, they don't normally "do" things (like change
formatting - unless they are called from VBA, but IMO that's a bad practice anyway). "Wayne Erfling" wrote: When I try to change the color of cell from a function called from that cell, the change never takes place. function SetMyColor (colorindex) Application.Caller.Interior.ColorIndex = colorindex 'this line never works If Application.Caller.Interior.ColorIndex = xlNone Then SetMyColor = "None" Else SetMyColor = Application.Caller.Interior.ColorIndex End If end function I've tried any number of variations, including hardcoding the range, for example: Range("J4").interior.ColorIndex = 17 and the color is never changed. I can change the color from a stand-along macro, just not from a function. If I change the color manually, the function will return the colorIndex successfully. Is this symptom part of a general issue of changing cells from within functions they call? Is there a way around it? Thanks! ---Wayne Erfling |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing a cell color inside a function called by that cell
unless they are called from VBA, but IMO that's a bad practice
anyway). Given VBA functions called only from VBA: How about a function that performs actions and returns the success or failure of those actions. Why is that a bad practice? Isn't that what many, many Windows API functions do? Just curious about your statement. Or were you saying functions shouldn't be called from VBA? English can be so ambigous. -- Regards, Tom Ogilvy "JMB" wrote in message ... Functions return values, they don't normally "do" things (like change formatting - unless they are called from VBA, but IMO that's a bad practice anyway). "Wayne Erfling" wrote: When I try to change the color of cell from a function called from that cell, the change never takes place. function SetMyColor (colorindex) Application.Caller.Interior.ColorIndex = colorindex 'this line never works If Application.Caller.Interior.ColorIndex = xlNone Then SetMyColor = "None" Else SetMyColor = Application.Caller.Interior.ColorIndex End If end function I've tried any number of variations, including hardcoding the range, for example: Range("J4").interior.ColorIndex = 17 and the color is never changed. I can change the color from a stand-along macro, just not from a function. If I change the color manually, the function will return the colorIndex successfully. Is this symptom part of a general issue of changing cells from within functions they call? Is there a way around it? Thanks! ---Wayne Erfling |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing a cell color inside a function called by that cell
Since I'm actually working with the colors themselves (rather than setting
colors based on some "user" data) conditional formatting isn't appropriate. Too bad there isn't a way to return a value OBJECT, that of course could return more than just a 1980's compatible value. I think maybe if I was stubborn I could change the color of a cell in an EVENT, as long as I could maintain a list of cells to be changed. ---Wayne "Tom Ogilvy" wrote in message ... Yes it is a general issue - in that this action is not allowed. Functions in Cells may only return values to the cell in which they are entered. They may not change the excel environment. You might look at conditional formatting. -- Regards, Tom Ogilvy "Wayne Erfling" wrote in message ... When I try to change the color of cell from a function called from that cell, the change never takes place. function SetMyColor (colorindex) Application.Caller.Interior.ColorIndex = colorindex 'this line never works If Application.Caller.Interior.ColorIndex = xlNone Then SetMyColor = "None" Else SetMyColor = Application.Caller.Interior.ColorIndex End If end function I've tried any number of variations, including hardcoding the range, for example: Range("J4").interior.ColorIndex = 17 and the color is never changed. I can change the color from a stand-along macro, just not from a function. If I change the color manually, the function will return the colorIndex successfully. Is this symptom part of a general issue of changing cells from within functions they call? Is there a way around it? Thanks! ---Wayne Erfling |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing a cell color inside a function called by that cell
Sure, events are not restriced.
-- Regards, Tom Ogilvy "Wayne Erfling" wrote in message ... Since I'm actually working with the colors themselves (rather than setting colors based on some "user" data) conditional formatting isn't appropriate. Too bad there isn't a way to return a value OBJECT, that of course could return more than just a 1980's compatible value. I think maybe if I was stubborn I could change the color of a cell in an EVENT, as long as I could maintain a list of cells to be changed. ---Wayne "Tom Ogilvy" wrote in message ... Yes it is a general issue - in that this action is not allowed. Functions in Cells may only return values to the cell in which they are entered. They may not change the excel environment. You might look at conditional formatting. -- Regards, Tom Ogilvy "Wayne Erfling" wrote in message ... When I try to change the color of cell from a function called from that cell, the change never takes place. function SetMyColor (colorindex) Application.Caller.Interior.ColorIndex = colorindex 'this line never works If Application.Caller.Interior.ColorIndex = xlNone Then SetMyColor = "None" Else SetMyColor = Application.Caller.Interior.ColorIndex End If end function I've tried any number of variations, including hardcoding the range, for example: Range("J4").interior.ColorIndex = 17 and the color is never changed. I can change the color from a stand-along macro, just not from a function. If I change the color manually, the function will return the colorIndex successfully. Is this symptom part of a general issue of changing cells from within functions they call? Is there a way around it? Thanks! ---Wayne Erfling |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing a cell color inside a function called by that cell
A very poor choice of words on my part (English isn't ambiguous - just mine).
I probably should have said I personally have not seen many examples of custom VBA functions that "do things" implemented very well (but the examples I'm thinking of were not written by any of the regular posters to this site). In most of the cases I've seen, I thought they tried to do too much. By that, I mean they try to alter a worksheet and have the function return some calculation (unrelated to the success/failure of the worksheet alterations). Or, they perform so many worksheet alterations that it is difficult to determine exactly what caused the function to fail. As you say, though, that does not make it a bad practice in general. I guess it is more a matter of good design and skill. "Tom Ogilvy" wrote: unless they are called from VBA, but IMO that's a bad practice anyway). Given VBA functions called only from VBA: How about a function that performs actions and returns the success or failure of those actions. Why is that a bad practice? Isn't that what many, many Windows API functions do? Just curious about your statement. Or were you saying functions shouldn't be called from VBA? English can be so ambigous. -- Regards, Tom Ogilvy "JMB" wrote in message ... Functions return values, they don't normally "do" things (like change formatting - unless they are called from VBA, but IMO that's a bad practice anyway). "Wayne Erfling" wrote: When I try to change the color of cell from a function called from that cell, the change never takes place. function SetMyColor (colorindex) Application.Caller.Interior.ColorIndex = colorindex 'this line never works If Application.Caller.Interior.ColorIndex = xlNone Then SetMyColor = "None" Else SetMyColor = Application.Caller.Interior.ColorIndex End If end function I've tried any number of variations, including hardcoding the range, for example: Range("J4").interior.ColorIndex = 17 and the color is never changed. I can change the color from a stand-along macro, just not from a function. If I change the color manually, the function will return the colorIndex successfully. Is this symptom part of a general issue of changing cells from within functions they call? Is there a way around it? Thanks! ---Wayne Erfling |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing a cell color inside a function called by that cell
JMB,
Thanks for taking the time to explain/clarify! -- Regards, Tom Ogilvy "JMB" wrote in message ... A very poor choice of words on my part (English isn't ambiguous - just mine). I probably should have said I personally have not seen many examples of custom VBA functions that "do things" implemented very well (but the examples I'm thinking of were not written by any of the regular posters to this site). In most of the cases I've seen, I thought they tried to do too much. By that, I mean they try to alter a worksheet and have the function return some calculation (unrelated to the success/failure of the worksheet alterations). Or, they perform so many worksheet alterations that it is difficult to determine exactly what caused the function to fail. As you say, though, that does not make it a bad practice in general. I guess it is more a matter of good design and skill. "Tom Ogilvy" wrote: unless they are called from VBA, but IMO that's a bad practice anyway). Given VBA functions called only from VBA: How about a function that performs actions and returns the success or failure of those actions. Why is that a bad practice? Isn't that what many, many Windows API functions do? Just curious about your statement. Or were you saying functions shouldn't be called from VBA? English can be so ambigous. -- Regards, Tom Ogilvy "JMB" wrote in message ... Functions return values, they don't normally "do" things (like change formatting - unless they are called from VBA, but IMO that's a bad practice anyway). "Wayne Erfling" wrote: When I try to change the color of cell from a function called from that cell, the change never takes place. function SetMyColor (colorindex) Application.Caller.Interior.ColorIndex = colorindex 'this line never works If Application.Caller.Interior.ColorIndex = xlNone Then SetMyColor = "None" Else SetMyColor = Application.Caller.Interior.ColorIndex End If end function I've tried any number of variations, including hardcoding the range, for example: Range("J4").interior.ColorIndex = 17 and the color is never changed. I can change the color from a stand-along macro, just not from a function. If I change the color manually, the function will return the colorIndex successfully. Is this symptom part of a general issue of changing cells from within functions they call? Is there a way around it? Thanks! ---Wayne Erfling |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing a cell color inside a function called by that cell
No problem. I should have thought it through more before posting.
"Tom Ogilvy" wrote: JMB, Thanks for taking the time to explain/clarify! -- Regards, Tom Ogilvy "JMB" wrote in message ... A very poor choice of words on my part (English isn't ambiguous - just mine). I probably should have said I personally have not seen many examples of custom VBA functions that "do things" implemented very well (but the examples I'm thinking of were not written by any of the regular posters to this site). In most of the cases I've seen, I thought they tried to do too much. By that, I mean they try to alter a worksheet and have the function return some calculation (unrelated to the success/failure of the worksheet alterations). Or, they perform so many worksheet alterations that it is difficult to determine exactly what caused the function to fail. As you say, though, that does not make it a bad practice in general. I guess it is more a matter of good design and skill. "Tom Ogilvy" wrote: unless they are called from VBA, but IMO that's a bad practice anyway). Given VBA functions called only from VBA: How about a function that performs actions and returns the success or failure of those actions. Why is that a bad practice? Isn't that what many, many Windows API functions do? Just curious about your statement. Or were you saying functions shouldn't be called from VBA? English can be so ambigous. -- Regards, Tom Ogilvy "JMB" wrote in message ... Functions return values, they don't normally "do" things (like change formatting - unless they are called from VBA, but IMO that's a bad practice anyway). "Wayne Erfling" wrote: When I try to change the color of cell from a function called from that cell, the change never takes place. function SetMyColor (colorindex) Application.Caller.Interior.ColorIndex = colorindex 'this line never works If Application.Caller.Interior.ColorIndex = xlNone Then SetMyColor = "None" Else SetMyColor = Application.Caller.Interior.ColorIndex End If end function I've tried any number of variations, including hardcoding the range, for example: Range("J4").interior.ColorIndex = 17 and the color is never changed. I can change the color from a stand-along macro, just not from a function. If I change the color manually, the function will return the colorIndex successfully. Is this symptom part of a general issue of changing cells from within functions they call? Is there a way around it? Thanks! ---Wayne Erfling |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I know which cell a function is called from? | Excel Worksheet Functions | |||
From which cell is my VBA function called ? | Excel Programming | |||
Need to know what cell called the custom VBA function? | Excel Programming | |||
Excel VBA-Changing cell color with if then function | Excel Programming | |||
How do I find which cell called a function? | Excel Programming |