#value error returns with excel user defined function

Question

i have created user defined function to calculate football match results.

My Root Function looks that:

Function calculatePoints(personTypes As Range, matchesResults As Range) As Integer
    calculatePoints = getAllPersonPoints(personTypes, matchesResults)
End Function

getAllPersonPoints function:

Private Function getAllPersonPoints(personTypes As Range, matchesResults 
AsRange) As Integer
    Dim x As Long
    Dim y As Long
    Dim isTheSurest As Boolean
    getAllPersonPoints = 0

    For x = 1 To personTypes.Rows.Count
        For y = 1 To personTypes.Columns.Count
            isTheSurest = isTheSurestResult(personTypes.Cells(x, 
y).DisplayFormat.Interior.PatternColorIndex)
            getAllPersonPoints = getAllPersonPoints + 
getPoints(matchesResults.Cells(x, y).Value, personTypes.Cells(x, y).Value, 
isTheSurest)
        Next y
    Next x
End Function

When i am trying to call this function by setting manually parameters: personTypes range and matchesResults ragne - everythink works fine.

But when i am trying to call it from sheet i got #VALUE error in selected cell.

But at function form there is correct result:

A have been trying to debug return value and always i got correct value. I have problem only with error in return cell.

Any ideas ?

Why do you have a separate function calculatePoints that does nothing except call another function? — ashleedawg
– ashleedawg, Commented Jun 18, 2018 at 23:25

chris neilsen · Accepted Answer · 2018-06-18 23:50:24Z

2

The issue is that DisplayFormat object does not work with UDF's

See MSDN article

The usual solution to this is to evaluate the Conditional Format conditions to determine which one is active. For example, see cpearson.com

answered Jun 18, 2018 at 23:50

chris neilsen

53.3k10 gold badges91 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jackuss · Accepted Answer · 2018-06-19 19:01:35Z

0

I resolved problem by code:

personTypes.Cells(x, y).Interior.ColorIndex

instead of:

personTypes.Cells(x,y).DisplayFormat.Interior.PatternColorIndex

This solution works.

answered Jun 19, 2018 at 19:01

jackuss

375 bronze badges

1 Comment

chris neilsen Over a year ago

You realise that doesn't detect conditional format colors, right?

Collectives™ on Stack Overflow

#value error returns with excel user defined function

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related