Log In
Register
Home
April 16, 2024
Offline
Hi,
I have created a User Defined Function which is working as it shows the correct result in the following window:
But in the cell it is showing as #VALUE.
We use Microsoft 365, local desktop app.
Would anyone know how to fix this?
Many thanks,
Karla
See Answer
Trusted Members
October 17, 2018
Offline
If any of the cells in the selected range contain an error like #DIV0, #NA, #VALUE your result will not calculate correctly unless you include an error trap or correct your formulas so that they thae care of the error calculation.
In Formulas you can use the IFERROR()
Trusted Members
Moderators
November 1, 2018
Offline
I suspect your UDF does something that is not allowed for a function called from a sheet. If you post the code, we can probably tell you what.
April 16, 2024
Offline
Many thanks for looking into my issue, my code is as follows:
Function GETCOLORCOUNT(CountRange As Range, CountColor As Range) As Single
Dim CountColorValue As Long
Dim TotalCount As Single
Dim rCell As Range
Application.Volatile
CountColorValue = CountColor.Interior.Color
Set rCell = CountRange
For Each rCell In CountRange
If rCell.DisplayFormat.Interior.Color = CountColorValue Then
TotalCount = TotalCount + 1
Else
End If
Next rCell
GETCOLORCOUNT = TotalCount
End Function
Trusted Members
Moderators
November 1, 2018
Offline
You can't use DisplayFormat in a function called from a cell. The easiest solution is usually to use something like COUNTIF(S) together with whatever criteria are being used by the conditional formatting.
April 16, 2024
Offline
I was able to remove DisplayFormat from the function. The updated code gives the same, correct Formula Result as before in the Function Arguments window (which is 5), but it is now showing 0 in the cell.
I was unsure how to rewrite the code using COUNTIF. The updated code is as follows:
Function GETCOLORCOUNT(CountRange As Range, CountColor As Range) As Single
Dim CountColorValue As Long
Dim TotalCount As Single
Dim rCell As Range
Application.Volatile
CountColorValue = CountColor.Interior.Color
Set rCell = CountRange
For Each rCell In CountRange
If rCell.Interior.Color = CountColorValue Then
TotalCount = TotalCount + 1
Else
End If
Next rCell
GETCOLORCOUNT = TotalCount
End Function
Trusted Members
Moderators
November 1, 2018
Offline
Are the cells coloured through conditional formatting, or do they have colours applied to them manually?
April 16, 2024
Offline
The cells that are being counted are coloured using Conditional Formatting. I have removed this and applied the colouring manually, and the function now works, amazing!
I assume I should work on rewriting the code using Countifs, unless you have any other suggestions?
Thank you so much for your help, if this function works it will save us a huge amount of time. I work in a secondary school and we are currently doing the colour counting using VBA code that is run using a button. But on some of our spreadsheets, which are used by all our teachers for reporting assessment results to our students, the code takes a long time to run.
Trusted Members
Moderators
November 1, 2018
Offline
No, what I'm suggesting is using a regular COUNTIF(S) function without any code. If you can apply the same criteria that are used to colour the cells, you don't need code at all. And your function will always be up to date, unlike pretty much any code version.
April 16, 2024
Offline
Many thanks for your reply, apologies for not getting back to you sooner, it has been a very busy week.
I have just tried to work out how to do this using COUNTIF but have not got anywhere with it. Are you able to send me an example formula?
The following users say thank you to Karla de Visser for this useful post:
VelouriaTrusted Members
Moderators
November 1, 2018
Offline
If you can give an example of the criteria used for the conditional formatting, I will see what I can do.
April 16, 2024
Offline
That's amazing thank you, here are the criteria:
April 16, 2024
Offline
I don't know if this helps but I have also attached an example of a row for one student.
I am determining the colour counters in the first 3 cells.
Trusted Members
Moderators
November 1, 2018
Offline
For the green count:
=SUMPRODUCT(--(O13:s13/O$7:S$7>=0.75))
For the red (first):
=SUMPRODUCT(--(O13:s13/O$7:S$<0.5))
For the balance, the simplest option is just to count the numbers in the range - COUNT(O13:S13) - and then subtract the results of the other two calculations.
Answers Post
1 Guest(s)
