Results 1 to 10 of 10
  1. #1
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    #NAME for UDF (Excel 2003)

    I copied Chip Pearson's sum by color function from his webpage. Yet, when I try it, (using 3 for a Red background, right?) I get #NAME. So I copied GETFORMULA from someone else's example and I again get #NAME and in the function box it is Undefined. Yet the module shows no red lettering indicating anything wrong, as I understand it. TYIA.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: #NAME for UDF (Excel 2003)

    Where exactly did you copy those functions to?

  3. #3
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: #NAME for UDF (Excel 2003)

    A new module in Personal.xls

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: #NAME for UDF (Excel 2003)

    In that case, you must prefix the function with Personal.xls folllowed by an exclamation mark, for example

    =Personal.xls!SUMIFBYCOLOR(A1:A10,3,B1:B10,FALSE)

  5. #5
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: #NAME for UDF (Excel 2003)

    So UDFs cannot be in Personal.xls without requiring that prefix? They need to be in the workbook directly? I got one to work, but not the Pearson one. I even recopied it from the site. THis should work if I use 3 for a red background, correct?

    Function SumByColor(InRange As Range, WhatColorIndex As Integer, _
    Optional OfText As Boolean = False) As Double
    '
    ' This function return the SUM of the values of cells in
    ' InRange with a background color, or if OfText is True a
    ' font color, equal to WhatColorIndex.
    '
    Dim Rng As Range
    Dim OK As Boolean

    Application.Volatile True
    For Each Rng In InRange.Cells
    If OfText = True Then
    OK = (Rng.Font.ColorIndex = WhatColorIndex)
    Else
    OK = (Rng.Interior.ColorIndex = WhatColorIndex)
    End If
    If OK And IsNumeric(Rng.Value) Then
    SumByColor = SumByColor + Rng.Value
    End If
    Next Rng

    End Function

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: #NAME for UDF (Excel 2003)

    What is the name of the module into which you copied the function?

    Take a good look at User-defined functions (Excel, all versions), including the replies to that post.

  7. #7
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: #NAME for UDF (Excel 2003)

    I called it SumbyColor. I'll read those references.Thanks!

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: #NAME for UDF (Excel 2003)

    Wait! You should NEVER give a module the same name as a procedure or function. This confuses Visual Basic.

    Since you have a function named SumByColor, you should give the module a different name. I usually prefix module names with bas (for Basic), e.g. basColorFunctions.

  9. #9
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: #NAME for UDF (Excel 2003)

    Ah HA! Thanks. That cleared up the problem. So much for being clever about module names. So now I would like to distribute this to certain folks who could use the concept but don't need to know much about the how of it, as an add-in. I will look about for instructions, but any hint or link would be helpful

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: #NAME for UDF (Excel 2003)

    Jan Karel Pieterse has an excellent series of articles on Create Addins.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •