CODEWITHSUNDEEP
vbafunctionexcel
Sebastian Koefoed
Sebastian Koefoed

Reputation: 143

VBA function error when other users try to use it

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))&","

Upvotes: 0

Views: 326

Answers (2)

Maarten van Stam
Maarten van Stam

Reputation: 1899

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

Upvotes: 1

Rosetta
Rosetta

Reputation: 2725

  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

Upvotes: 1

Related Questions