UDFs in Excel

[VBAnewbie]

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?

 

[Bernie Deitrick]

That is usually an indication of an un-handled error somewhere in your code.

HTH,
Bernie
MS Excel MVP

 

[VBAnewbie]

What do you mean by and unhandled error? Is the error in the user
defined function or in the larger macro? Any suggestions as to how I
can find the error? I don't get any run-time errors or anything like
that; my code runs fine. Short of posting my entire code here (it's
very long), is there anything I can do to make sure the #VALUE error in
cells using my user defined function doesn't show up or to identify
what is causing the unhandled error? Thanks

crl

 

[Bernie Deitrick]

crl,

When I see problems like that, it is usually because (in my code) I have
turned calculataion to manual prior to doing something, and then forget to
turn it back to automatic, or I stop the code to debug, or get an error that
stops the code. An unhandled error is one where a procedure simply stops,
and doesn't exit properly, resetting the application settings, etc.

Only you can tell for sure what is happening - complex code can be a bear to
debug properly...

Bernie

 

[Niek Otten]

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

|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?
|

 

[VBAnewbie]

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.

 

[VBAnewbie]

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

crl