UDFs in Excel

Yeah, the cells that are inspected for color are included in the
argument list of the function. And I use the Volatile method, which I
thought would make sure the cells are recalculated.

Here is the function:
Function CellColorIndex(inRange As Range, Optional ofText As Boolean =
False) As Integer

Application.Volatile
If ofText = True Then
CellColorIndex = inRange(1, 1).Font.ColorIndex
Else
CellColorIndex = inRange(1, 1).Interior.ColorIndex
End If

End Function

So I would change the color on cell B23 inside of a macro, then use
Calculate inside the macro. The cell that contains the formula
'=CellColorIndex(B23)' would now return a #VALUE, or also sometimes
#NAME. All I have to do is click on the cell and press enter and the
formula works again.



Niek Otten wrote:
> Are all the cells that are inspected for color included in the argument list of the function call?
> BTW, changing colors of cells does not trigger a recalculation.
> So, it depends on the code of your function and the action you take which you expect to recalculate the function call.
> An example maybe?
>
> --
> Kind regards,
>
> Niek Otten
> Microsoft MVP - Excel
>
> "VBAnewbie" <[email protected]> wrote in message news:[email protected]...
> |I have spreadsheet that hinges around counting colored cells. I used a
> | user-defined CellColorIndex function to do this. It works great 90% of
> | the time. I use it in another spreadsheet that is dependent on macros
> | to move a bunch of information around. For some reason, about every
> | tenth time i run the macro, I get a value error in the cells where I
> | use the CellColorIndex function. To fix the error, all I have to do is
> | click on the offending cell and hit return. Without changing the
> | formula at all, the UDF works perfectly again. Has anyone else had
> | this problem? Does anyone know how to fix the problem?
> |

Just to update you guys...I put in a error handler and I think the
problem is solved. Thanks for the discussion.

crl

VBAnewbie wrote:
> Yeah, the cells that are inspected for color are included in the
> argument list of the function. And I use the Volatile method, which I
> thought would make sure the cells are recalculated.
>
> Here is the function:
> Function CellColorIndex(inRange As Range, Optional ofText As Boolean =
> False) As Integer
>
> Application.Volatile
> If ofText = True Then
> CellColorIndex = inRange(1, 1).Font.ColorIndex
> Else
> CellColorIndex = inRange(1, 1).Interior.ColorIndex
> End If
>
> End Function
>
> So I would change the color on cell B23 inside of a macro, then use
> Calculate inside the macro. The cell that contains the formula
> '=CellColorIndex(B23)' would now return a #VALUE, or also sometimes
> #NAME. All I have to do is click on the cell and press enter and the
> formula works again.
>
>
>
> Niek Otten wrote:
> > Are all the cells that are inspected for color included in the argument list of the function call?
> > BTW, changing colors of cells does not trigger a recalculation.
> > So, it depends on the code of your function and the action you take which you expect to recalculate the function call.
> > An example maybe?
> >
> > --
> > Kind regards,
> >
> > Niek Otten
> > Microsoft MVP - Excel
> >
> > "VBAnewbie" <[email protected]> wrote in message news:[email protected]...
> > |I have spreadsheet that hinges around counting colored cells. I used a
> > | user-defined CellColorIndex function to do this. It works great 90% of
> > | the time. I use it in another spreadsheet that is dependent on macros
> > | to move a bunch of information around. For some reason, about every
> > | tenth time i run the macro, I get a value error in the cells where I
> > | use the CellColorIndex function. To fix the error, all I have to do is
> > | click on the offending cell and hit return. Without changing the
> > | formula at all, the UDF works perfectly again. Has anyone else had
> > | this problem? Does anyone know how to fix the problem?
> > |