Changing a cell color inside a function called by that cell

[Wayne Erfling]

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

 

[Tom Ogilvy]

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


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

 

[Guest]

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).

 

[Tom Ogilvy]

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.

 

[Wayne Erfling]

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]

Sure, events are not restriced.

 

[Guest]

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]

JMB,

Thanks for taking the time to explain/clarify!

 

[Guest]

No problem. I should have thought it through more before posting.