#NAME? error with VBA Function 2

[wwgmr]

Hi, I wanted to see if anyone could help me with a problem I am having. I am trying to make a custom function and I keep getting Name? error.
My code looks like this

Function Points(Yrds)
Dim Pts As Integer
Dim Yrds As Variant
Select Case Yrds
Case 0 To 50
Pts = 1
Case 51 To 100
Pts = 2
Case 101 To 150
Pts = 3
Case 151 To 200
Pts = 4
Case 201 To 250
Pts = 5
Case 251 To 300
Pts = 6
Case Else
Pts = CVErr(xlErrNA)
End Select
Points = Pts
End Function

I have also tried to do it like this
Function Points(Yrds As Integer) As Variant
Dim Pts As Integer
Select Case Yrds
Case 0 To 50
Pts = 1
Case 51 To 100
Pts = 2
Case 101 To 150
Pts = 3
Case 151 To 200
Pts = 4
Case 201 To 250
Pts = 5
Case 251 To 300
Pts = 6
Case Else
Pts = CVErr(xlErrNA)
End Select
Points = Pts
End Function

Yet I get same error. In the cell this formula is I am typing =Points(H1)
Where H1 is the value I want to run in my case. What step am I missing I have been trying to follow examples from some books yet Can't seem to get this right. It looks right. I am working in Excel 2k

Thanks alot for any input. I hope to get a grasp on this programing someday lol.

Eric

 

[ttmac]

Eric,

Remove the line Dim Yrds As Variant
and amend your function line to Function Points(Yrds As Variant)

Your function should now look like the following: -

Function Points(Yrds As Variant)
Dim Pts As Integer

Select Case Yrds
Case 0 To 50
Pts = 1
Case 51 To 100
Pts = 2
Case 101 To 150
Pts = 3
Case 151 To 200
Pts = 4
Case 201 To 250
Pts = 5
Case 251 To 300
Pts = 6
Case Else
Pts = CVErr(xlErrNA)
End Select
Points = Pts
End Function)

The reason you were having the problem is tht Yrds is already defined in the function name and you were trying to redefine it.

Hope this helps,

Tom.

 

[wwgmr]

Hi Tom, Thanks for reply. I tried as you said yet still I get #NAME? in the cell. I have values I want to check in cell h1 this formula I am putting in say M1 so the cell m1 says =Points(H1) (I have tried Points(Indirect(h1)) incase it was having problem but both give me the Name error.
I have tried this several ways yet still no go. Am I missing setting? I have other VBA running in button that is macro and it works. THis is my code again

Function Points(Yrds As Variant)
Dim Pts As Integer

Select Case Yrds
Case 0 To 50
Pts = 1
Case 51 To 100
Pts = 2
Case 101 To 150
Pts = 3
Case 151 To 200
Pts = 4
Case 201 To 250
Pts = 5
Case 251 To 300
Pts = 6
Case Else
Pts = CVErr(xlErrNA)
End Select
Points = Pts
End Function

Is there something else missing? This is located under General Points

Thanks again Tom for fast reply.
Eric

 

[ttmac]

Eric,

If your yrds passed into the function exceed 300 you will get an error. In my case, I am getting the #VALUE! error.

What are you trying to do in the case else statement in you code?

Tom.

 

[wwgmr]

I put that in per my Excel 2002 VBA book. It should give error message #N/A Case Else
Pts = CVErr(xlErrNA) xlErrNA stands for Excel error N/A so it should show it. I have tried running the code with out this else case but still get same thing #Name?
I am really puzzeled why I can't create simple custom formula.
Has anyone else had this kind of trouble? It seems so simple in the book lol. Yet I can't get it to work.


Eric

 

[ttmac]

Eric,

Have you spelt the function correctly in you cell. Delete the function from the cell and try inserting it using the menus INSERT | FUNCTION select custom functions and select points function.

From excel help the error #NAME? mean? occurs when Microsoft Excel doesn't recognize text in a formula.

some of the possible causes are : -

Deleting a name used in the formula, or using a name that does not exist.

Misspelling the name.

Using a label in a formula.

Misspelling the name of a function.

If you can and wish to do so email the spreadsheet to me at ttmac@oceanfree.net and copy it to tmcelhin@revenue.ie

Tom.

 

[wwgmr]

Tom
I seem to be missing Insert|Function "Custom Function"
Its listed in my list of functions to insert. This might be the problem? I may need additional tool installed?

I can't seem to find where to add this?
Are you able to run this simple function on excel?

Eric

 

[wwgmr]

This does seem to be issue with Excel not seeing it for a formula. I am spelling it correctly in the cell when I enter it. Does anyone know reason Excel wouldn't see it or call for it? Do I have to register the function in excel?

Eric

 

[acron]

What workbook is the function stored in ?

If you have it in your Personal.xls file you need to use

= Personal.xls!Points(x).

Unless the function is included in a general module of the active workbook OR is part of an AddIn, it needs to have it's name preceeded by the workbook name to which it belongs.

Is it possible that your function is stored in the ThisWorkBook codepane or one of the sheet codepanes rather than a general module ?

A.C.

 

[wwgmr]

A.C. Thanks! That was it. I was entering it into the worksheet. I even tried the workbook. I didn't know that it had to be in modules. Thank you both very much for help. Now I can start making some head way in coding lol.

Eric