Using Named Range in VBA Function "Invalid Name Error"

[ExcelVBA]

Hi All,

So happy there's such a great forum to help out the NewBs and the like.

So, here's what I got:

Function Efficiencys(ProfitList As Range)

Dim Winner As Integer
Winner = 0
Dim Loser As Integer
Loser = 0

    For a = 1 To ProfitList.Count
        If ProfitList.Item(a) > 0 Then
            Winner = Winner + 1
        ElseIf ProfitList.Item(a) < 0 Then
            Loser = Loser + 1
        End If
    Next
    
    Efficiencys = Winner / Loser
    
End Function

in Cell H52 =Efficiencys(ProfitNLoss)

ProfitNLoss is a named range $G$2-$G$49.
I can see it properly defined in the Workbook in the Name Manager.

in H52, I get a little green corner dog ear, which once i click on I get the ! yield sign to the left, and the drop down says "Invalid Name Error"

At first I thought Efficiency was a reserved word, but that didn't work.


Any ideas?

Thanks!

 

[jbeaucaire]

Using builtin functions:

=COUNTIF(ProfitNLoss, ">0") / COUNTIF(ProfitNLoss, "<0")

 

[ExcelVBA]

Re-Inventing the Wheel!

Thanks

p.s. One day I will be back to ask this question again, for another reason

 

[jbeaucaire]

Just for chuckles, this is how I would correct your existing UDF:

Option Explicit

Function Efficiencys(ProfitList As Range)
Dim Winner As Long: Winner = 0
Dim Loser As Long:  Loser = 0
Dim Itm As Range

    For Each Itm In ProfitList
        If Itm > 0 Then
            Winner = Winner + 1
        ElseIf Itm < 0 Then
            Loser = Loser + 1
        End If
    Next

    If Winner = 0 Or Loser = 0 Then
        Efficiencys = 0
    Else
        Efficiencys = Winner / Loser
    End If

End Function