Using Named Range in VBA Function "Invalid Name Error"

ExcelVBA

New Member
Joined
Jul 3, 2010
Messages
5
Hi All,

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

So, here's what I got:

Code:
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!
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

jbeaucaire

Well-known Member
Joined
May 8, 2002
Messages
6,012
Using builtin functions:

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

ExcelVBA

New Member
Joined
Jul 3, 2010
Messages
5
:ROFLMAO: Re-Inventing the Wheel!

Thanks

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

jbeaucaire

Well-known Member
Joined
May 8, 2002
Messages
6,012
Just for chuckles, this is how I would correct your existing UDF:
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,224
Messages
5,594,916
Members
413,952
Latest member
JGer

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top