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

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

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

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

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

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

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

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

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