My simple custom function is throwing the #name error as if I'm entering it wrong

Question

I'm trying to figure out why my custom function is throwing the #name error as if I'm entering it wrong. The function takes two cells and divides them and throws a string telling you if it's healthy, not, or warns you. Below is my VBA script that calculates everything. I'm not sure where I can go wrong with this? This is located in a module folder and it shows up when I enter it in formula. enter image description here

Public Function PHealth(Budget As Double, Actual As Double)

    ph As Double
    ph = Actual / Budget

    If ph > 0.7 Then PHealth() = " Good"
    
    End If
    
    If ph < 0.7 And ph > 0.8 Then PHealth() = "Warning"
        MsgBox "Warning Project is past 70% consumption"
    End If
    
    If ph < 0.8 Then PHealth() = "Poor"
        MsgBox "Warining! Project Nearing 90% consumption. Get CO Signed"
    End If
    

End Function

Try {Alt+D,Enter} to compile the project and review compiler errors. Make sure the first thing in at the top of all modules is an instruction that says `Option Explicit`. The `#NAME?` error is returned because the VBA project isn't compilable, therefore the `PHealth` function isn't defined. — Mathieu Guindon, Feb 15 '21 at 17:06

Warcupine · Answer 1 · 2021-02-15T16:53:46.727

You have a few things going on here:

ph As Double Needs a dim

If ph > 0.7 Then PHealth() = " Good"

End If

Single line ifs don't get an End If

Your message boxes will trigger every time because they aren't in an If (see above)

PHealth() You aren't returning an array so no parenthesis on that.

All together, with a couple of non critical issues:

Public Function PHealth(Budget As Double, Actual As Double) As String
'Good to declare what type you want to return
Dim ph As Double
ph = Actual / Budget

If ph > 0.7 Then
    PHealth = " Good"

ElseIf ph < 0.7 And ph > 0.8 Then
    PHealth = "Warning"
    MsgBox "Warning Project is past 70% consumption"

ElseIf ph < 0.8 Then
    PHealth = "Poor"
    MsgBox "Warining! Project Nearing 90% consumption. Get CO Signed"
End If
End Function

I think you also have an issue in the middle statement, I'm unsure of how ph can be both < .7 and > .8.

Something also seems off with the first condition being > .7 and the last condition being < .8 If that is intentional you'll need to remove the elseifs because it will not reach the last condition if it is between .7 and .8

Thank you for the advice, i've adjusted the mistakes. I've added greater than or equals to and les than or equals to to account for the times in between. After all the changes, i can use it if i dig into the functions tab and locate it there. But when i try to enter as a normal function it's still having issues. — joey, Feb 16 '21 at 21:12

score 0 · Answer 2 · answered Feb 15 '21 at 16:54

0

You probably get the #NAME error because your public function is on a sheet and not in a module. See eg. How to call VBA function from Excel cells (2010)?. You will need to fix your code logic, though, which is flawed. You can test the same function by just typing ? PHealth(3,2) in the immediate window - saves you messing with the sheet (but will still give you an error if the code is not in a module.)

answered Feb 15 '21 at 16:54

Morten

148
6

This is in a module, and i've gone through and adjusted my code. Currently works, i just have to search it in the functions tab. – joey Feb 16 '21 at 21:00
Does it run when you type `call PHealth(3,2)` in the immediate window? There's some additional details here https://stackoverflow.com/questions/22561422/excel-udf-not-appearing-in-drop-down-menu about where the module needs to be – Morten Feb 17 '21 at 14:57

My simple custom function is throwing the #name error as if I'm entering it wrong

2 Answers2