none
Referencing a "Code Behind" Function from Within a Cell RRS feed

  • 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

Answers

  • Hello,

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

    Best regards,

    Wouter

    • Marked as answer by Doug Pruiett 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 Doug Pruiett 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 ryguy72 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 Doug Pruiett 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 Doug Pruiett 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