Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDFs in 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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDFs in Excel
That is usually an indication of an un-handled error somewhere in your code.
HTH, Bernie MS Excel MVP "VBAnewbie" wrote in message oups.com... 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDFs in Excel
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 wrote: That is usually an indication of an un-handled error somewhere in your code. HTH, Bernie MS Excel MVP "VBAnewbie" wrote in message oups.com... 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDFs in Excel
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 "VBAnewbie" wrote in message ups.com... 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 wrote: That is usually an indication of an un-handled error somewhere in your code. HTH, Bernie MS Excel MVP "VBAnewbie" wrote in message oups.com... 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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDFs in Excel
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" wrote in message oups.com... |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? | |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" wrote in message oups.com... |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? | |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDFs in Excel
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" wrote in message oups.com... |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? | |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help and description for UDFs | Excel Worksheet Functions | |||
Excel path problem with UDFs stored at different locations | Excel Programming | |||
UDFs | Excel Programming | |||
VBA, UDFs and VSTO | Excel Programming | |||
Acrobat 6.0 and UDFs | Excel Programming |