# Referencing a "Code Behind" Function from Within a Cell

• ### Question

• Excel 2013.

I created a function in code-behind of a sheet in an Excel workbook.  The function is "UniqueNames()"  and received one parameter (e.g., UniqueNames(1)).  I declared the function as public and tried putting a formula in a cell (=UniqueNames(1)) and the cell displays #NAME?  What am I doing wrong?

The test code behind the sheet is:

Public Function UniqueNames(ByVal Phase As Integer) As Integer
UniqueNames = 10
End Function

Thursday, August 20, 2015 9:54 PM

• Hello,

It works for me if you put the code in a module (not behind the sheet)

Best regards,

Wouter

• Marked as answer by Friday, August 21, 2015 12:51 PM
Friday, August 21, 2015 11:29 AM
• I created a function in code-behind of a sheet in an Excel workbook.

the cell displays #NAME?  What am I doing wrong?

Move the function from the code module of the sheet into a regular module.

Andreas.

• Marked as answer by Friday, August 21, 2015 12:51 PM
Friday, August 21, 2015 11:36 AM

### All replies

• Please see this for several options about how to get started with searching for unique values, strings, etc.

http://www.listendata.com/2013/05/excel-3-ways-to-extract-unique-values.html

Also, consider using this.

```Function listUnique(rng As Range) As Variant
Dim row As Range
Dim elements() As String
Dim elementSize As Integer
Dim newElement As Boolean
Dim i As Integer
Dim distance As Integer
Dim result As String

elementSize = 0
newElement = True

For Each row In rng.Rows
If row.Value <> "" Then
newElement = True
For i = 1 To elementSize Step 1
If elements(i - 1) = row.Value Then
newElement = False
End If
Next i
If newElement Then
elementSize = elementSize + 1
ReDim Preserve elements(elementSize - 1)
elements(elementSize - 1) = row.Value
End If
End If
Next

distance = Range(Application.Caller.Address).row - rng.row

If distance < elementSize Then
result = elements(distance)
listUnique = result
Else
listUnique = ""
End If
End Function```

Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

• Proposed as answer by Friday, August 21, 2015 2:14 PM
Thursday, August 20, 2015 10:25 PM
• Hello,

It works for me if you put the code in a module (not behind the sheet)

Best regards,

Wouter

• Marked as answer by Friday, August 21, 2015 12:51 PM
Friday, August 21, 2015 11:29 AM
• I created a function in code-behind of a sheet in an Excel workbook.

the cell displays #NAME?  What am I doing wrong?

Move the function from the code module of the sheet into a regular module.

Andreas.

• Marked as answer by Friday, August 21, 2015 12:51 PM
Friday, August 21, 2015 11:36 AM
• Thank You.
Friday, August 21, 2015 12:51 PM
• Thank you for answering.
Friday, August 21, 2015 12:52 PM
• Thank you for answering.
Friday, August 21, 2015 12:52 PM