Custom function returns #NAME?

Question

I created the following function with this code to find the first numeric charactor in a string in a cell. Cell E3 contains the string to be checked. When I enter the formula =Pos_nonalpha(E3) it returns #NAME?. What am I doing wrong?

Thanks,

JCM

Function Pos_nonalpha(cell) As Long Dim i As Long For i = 1 To Len(cell) Dim Number Select Case Asc(Mid(cell, i, 1)) Case 0 To 64, 91 To 96, 123 To 191 Pos_nonalpha = i Exit Function End Select Next i Pos_nonalpha = 0End Function

Answer 1

Maybe you don't have macros enabled?

---

Bill Manville. Excel MVP, Oxford, England. www.manville.org.uk

Answer 2

I followed your instructions. The UDF appears in the User Defined dropdown. However no popup appears for me to enter the cell # so I entered it in the formula =Pos_nonalpha(E3). When I pick from the UDF dropdown it appears in the cell as:

='TEST Time_Sheet_DCMWO_Int_Ord_Data.xls'!Module1.Pos_nonalpha(E3)

Still I get the same problem.

Thanks,

JCM

Answer 3

Hi,

This is odd. Lets first establish beyond doubt the function is in the correct place:-

ALT+F11 to open VB editor

Ensure 'Project explorer' is showing on the left

In project explorer find the name of your workbook and click the + sign

It will look like VBAProject(Workbookname.xls)

Click the + sign next to modules

Double click the module with your code in and you should see it on the right.

On the assumption it's there go back to the wotksheet

Select an empty cell

Click the Fx symbol to the left of the formula bar

Select 'User Defined' from the dropdown

Scroll down to your function name and select it

Click OK

In the popup enter the cell address with your string in (E3)

Click OK

Does it work now?

---

If this post answers your question, please mark it as the Answer.

Mike H

Answer 4

I tried you code same problem.

Also I only see the function code when I double click module1.

Thanks again,

JCM

Answer 5

Hi,

In vb editor 'double click' module 1 to open it an ensure the code is in there. While I agree using cell is bad parctice it wont make any difference in this case. Just out of interest here's an alternative way

Function Pos_nonalpha(cell) As Long

Dim i As Long

For i = 1 To Len(cell)

If Not Mid(cell, i, 1) Like "[A-Za-z]" Then

Pos_nonalpha = i

Exit Function

End If

Next i

Pos_nonalpha = 0

End Function

---

If this post answers your question, please mark it as the Answer.

Mike H