Forum

UDF - Correct resul...
 
Notifications
Clear all

UDF - Correct result but #VALUE in Cell

15 Posts
3 Users
0 Reactions
144 Views
(@karladevisser)
Posts: 8
Active Member
Topic starter
 

Hi,

I have created a User Defined Function which is working as it shows the correct result in the following window:

UDF_Result.JPG

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

 
Posted : 16/04/2024 10:58 am
(@keebellah)
Posts: 373
Reputable Member
 

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()

 
Posted : 17/04/2024 3:12 am
(@debaser)
Posts: 836
Member Moderator
 

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.

 
Posted : 17/04/2024 4:54 am
(@karladevisser)
Posts: 8
Active Member
Topic starter
 

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

 
Posted : 17/04/2024 6:18 am
(@debaser)
Posts: 836
Member Moderator
 

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.

 
Posted : 17/04/2024 11:57 am
(@karladevisser)
Posts: 8
Active Member
Topic starter
 

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

 
Posted : 18/04/2024 9:58 am
(@debaser)
Posts: 836
Member Moderator
 

Are the cells coloured through conditional formatting, or do they have colours applied to them manually?

 
Posted : 18/04/2024 12:02 pm
(@karladevisser)
Posts: 8
Active Member
Topic starter
 

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.

 
Posted : 19/04/2024 3:59 am
(@debaser)
Posts: 836
Member Moderator
 

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.

 
Posted : 19/04/2024 4:41 am
(@karladevisser)
Posts: 8
Active Member
Topic starter
 

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?  

 
Posted : 25/04/2024 9:54 am
(@debaser)
Posts: 836
Member Moderator
 

If  you can give an example of the criteria used for the conditional formatting, I will see what I can do.

 
Posted : 25/04/2024 10:07 am
(@karladevisser)
Posts: 8
Active Member
Topic starter
 

That's amazing thank you, here are the criteria:

UDF-Conditional-Formatting-Criteria.JPG

 
Posted : 25/04/2024 10:41 am
(@karladevisser)
Posts: 8
Active Member
Topic starter
 

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.

 
Posted : 25/04/2024 10:48 am
(@debaser)
Posts: 836
Member Moderator
 

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.

 
Posted : 25/04/2024 2:03 pm
(@karladevisser)
Posts: 8
Active Member
Topic starter
 

This is brilliant, it all works, thank you so much! 

I added another column to count the number of blank cells to calculate the middle/amber cells correctly.   

This will save us a lot of time 🙂

 
Posted : 26/04/2024 7:29 am
Share: