0

I've made this short function to find whether a name is "given name surname" or "surname, given name", however when this is run by another user (on another PC), the result function In error #NAME? :

Function FindName_Function(NameCell As String) As String

Dim FindComma As Long
Dim FindName As String

FindComma = InStr(1, NameCell, ",")

If FindComma <> 0 Then

    FindName = VBA.Right(NameCell, Len(NameCell) - FindComma)

Else

    FindName = VBA.Left(NameCell, InStr(1, NameCell, " ") - 1)

End If

FindName_Function = FindName


End Function

This is how the function is called: This is how the function is called:

This is the formula:

="Hello "&FindName_Function(INDEX(Table_HP_Effective_contact_list;MATCH(SiteID;Table_HP_Effective_contact_list[Site];0);4))&","
Improve this question
3
  • And you saved this function where? Commented Jan 28, 2016 at 12:03
  • In the workbooks modules Commented Jan 28, 2016 at 12:08
  • Try the standard module and make sure it's the correct workbook. Commented Jan 28, 2016 at 12:11

2 Answers 2

Reset to default
1

I believe you use the function as a UDF (User Defined Function) and the #NAME error indicates that the function can't be found or executed. Make sure you store the UDF on a discoverable location and has permission to run. It is not clear from your question -where- you stored the UDF and what the security settings are on the client machines.

What I did is create a new Workbook, added a new Module to the Workbook, copied the UDF in the Module, used it in a cell on the new Workbook and worked without problems. So my guess from the limited information provided is that you stored the UDF in a different location outside the Workbook, inaccessible for the other users to find.

On a side note: - the VBA. prefix is not necessarily needed - test if the name is empty, InStr will fail if the name is empty

If you want a better answer, please elaborate on the location of the UDF (where did you create/store the UDF) and what are the macro security settings currently in place on the machines you see the error on.

enter image description here

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

I'm not sure I can answer that question correctly. I guess the UDF is stored as a standard code module. As for my security settings, other users have no issue running the other modules in the workbook.
How do you call the UDF @SebastianKoefoed? Also if you open the Developer Ribbon, Click Visual Basic do you see the Module that contains the function under the right VBAProject (i.e. the workbook opened)?
This is exactly how the function is called. See the update in my original post with picture. See original post above for formula.
Can you check on the client machines where it failed if they have all the right references? (VBA Editor, Tools, References) Make sure there is no MISSING reference in there ...
Just noticed your latest addition, maybe it is caused by another part. First try to access the function on the non working client the way I showed here above. Then slowly enhance the function part by part.
|
1

  1. if u save the function in the same workbook and saved the workbook in *.xlsm format, then the possible cause is user did not enable macro when opening the file.

  2. if u save the function in the same workbook and saved the workbook in *.xlsx format, then u saved it in the wrong format.

  3. if u save the function in another workbook, then that workbook should be saved in Excel Add-In format (*.xlam) and the Add-in must be loaded in Excel.

hope this helps

+

Try use the insert function window to find the function. Select category = "User Defined".

  1. If the function is listed, then try call it from there.
  2. If the function is not listed, then for sure macro for that workbook is not enabled.

enter image description here

Improve this answer

4 Comments

It's saved as an .xlsm format, and the user have no issue running other macros in the workbook.
@SebastianKoefoed Exactly how is this function called from the sheet?
@SebastianKoefoed refer + answer
The formula in the cell contains text + findname_function + text, so I cant use the insert function.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.