Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: User defined function gives #VALUE error

  1. #1
    New Member
    Join Date
    Oct 2008
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default User defined function gives #VALUE error

    I have a function that sets the .FORMULA of an adjoining cell. When I run the function from Visual Basic Editor - it works great. When I enter the function in the spreadsheet cell, I get #VALUE error. What I'm trying to do is have the function as a generic way of inserting values based on the relative position of the cell (i.e. function is coded in cell c4, the function inserts a formula in cell b4 using values from cells a4 and the same cell in another workbook.

    Any ideas or input will be appreciated.

  2. #2
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    21,700
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    3 Thread(s)

    Default Re: User defined function gives #VALUE error

    UDF's when called from a spreadsheet formula can not change the worksheet's environment by coloring cells, putting values or formulas in cells or a host of other things.

    When called by a VB routine, they can do all those things, but not when called by a spreadsheet formula.

    Here is a work-around for that. The Calculate Event occurs after the UDF has calculated. The UDF can use public variables (Collections in this example) to pass arguments to the Calcualte event which will then do the "forbidden" actions.

    Put this in a normal module
    Code:
    Public cellsToFill As New Collection
    Public formulasToPut As New Collection
    Public abortUDF As Boolean
    
    Function PutNthFormulaIn(destCell As Range, whichFormula As Long, ParamArray FormulasArray() As Variant) As Boolean
        Dim addressKey As String
        Dim formulaStr As String
        
        On Error Resume Next
            If Not abortUDF Then
                addressKey = destCell.Address(, , , True)
                formulaStr = FormulasArray(whichFormula - 1)
                
                cellsToFill.Add Item:=destCell, key:=addressKey
                formulasToPut.Add Item:=formulaStr, key:=addressKey
            End If
            PutNthFormulaIn = (Err = 0)
        On Error GoTo 0
    
    End Function
    and this in ThisWorkbook code module
    Code:
    Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
        Dim oneCell As Range
        If 0 < cellsToFill.Count Then
            Application.EnableEvents = False
            abortUDF = True
            For Each oneCell In cellsToFill
                oneCell.Formula = formulasToPut(oneCell.Address(, , , True))
            Next oneCell
            Set cellsToFill = Nothing
            Set formulasToPut = Nothing
            abortUDF = False
            Application.EnableEvents = True
        End If
    End Sub
    Then if you put this formula in any cell
    =PutNthFormulaIn(E3,A1,"=B2+10","=SQRT(B3)")
    E3 will hold the formula =B2+10 or =SQRT(B3) depending on if A1 holds 1 or 2. One can use an indefinite number of formulas, if the second term is more than the number of formulas as arguments, the destinationCell will be emptied.

    I hope this helps.

  3. #3
    New Member
    Join Date
    Oct 2008
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: User defined function gives #VALUE error

    Great suggestion - I've used the worksheet change event before with luck. The worksheet calculate code is not triggering - trying to figure out where I went wrong (including - using a new 'test' workbook/sheet. Any ideas on why a calculate event would not trigger?

  4. #4
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    21,700
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    3 Thread(s)

    Default Re: User defined function gives #VALUE error

    Is Application.EnableEvents = True? Is Calculation automatic?
    Are you updating the arguments or the precedents of the UDF. Try adding Application.Volatile until the function has been debugged.
    Put MsgBox "Calc" at the top of the Calculate event and MsgBox "UDF" in the UDF to track what is/isn't happening.

  5. #5
    New Member
    Join Date
    Oct 2008
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: User defined function gives #VALUE error

    Good questions - I should have explained why I don't think the code is being triggered.

    Application.Enablevents is TRUE and calculation is automatic (have also forced calculation via F9). Placed a MSGBOX at front of the calculation code - definitely not being triggered. Weird.

  6. #6
    New Member
    Join Date
    Oct 2008
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: User defined function gives #VALUE error

    Got it - changed the sub name from "Workbook_Sheetchange" to "Worksheet_Change" and it's being triggered now. Thanks again!

  7. #7
    New Member
    Join Date
    Oct 2008
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: User defined function gives #VALUE error

    One final clarification... I had the workbook_sheetchange code in the worksheet rather than the workbook location - fixed and all better. Thanks again - this will really help.

  8. #8
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    21,700
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    3 Thread(s)

    Default Re: User defined function gives #VALUE error

    If you use the functions between sheets, you'll need to move the Calculate routine to ThisWorkbook.

    I'm glad I could help.

  9. #9
    New Member
    Join Date
    Dec 2014
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Arrow Re: User defined function gives #VALUE error

    I am sorry if am doing something wrong here.

    I have a different senerio but a similar one. I am also getting #value error.

    I have a user defined function as below.
    ======================
    Function myColor(r As Range) As Integer
    myColor = r.Interior.ColorIndex
    End Function
    ======================
    which is defined in a module

    and i use =myColor(A2) in sheet. Then i need to copy the formula to end of the row in that sheet.

    but the formula works only for first cell, then it gives #value error when pasted.

    This copying and pasting is automated as below.

    ================
    Sub Macro44()
    '
    ' Macro44 Macro
    '
    Dim LastRow As Integer

    Range("C1").Select

    LastRow = Range("A" & Rows.Count).End(xlUp).Row

    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "Color Index"
    Range("C2").Select
    ActiveCell.FormulaR1C1 = "=mycolor(RC[-3])"

    Range("C3").Select
    Range("C2").Copy Range("C2:C" & LastRow)
    =========================

    thanks in advance.

  10. #10
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    21,700
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    3 Thread(s)

    Default Re: User defined function gives #VALUE error

    If you are putting =MyColor(RC[-3]) in cell $C$2 , it is pointing to the column to the left of column A.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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