User defined function gives #VALUE error

B

benniegibson

New Member
Joined
Oct 11, 2008
Messages
11
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.
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Sort by date Sort by votes
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
Got it - changed the sub name from "Workbook_Sheetchange" to "Worksheet_Change" and it's being triggered now. Thanks again!
 
Upvote 0
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.
 
Upvote 0
If you use the functions between sheets, you'll need to move the Calculate routine to ThisWorkbook.

I'm glad I could help.
 
Upvote 0
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.
 
Upvote 0
If you are putting =MyColor(RC[-3]) in cell $C$2 , it is pointing to the column to the left of column A.
 
Upvote 0
You must log in or register to reply here.

Similar threads

O
  • Question Question
Frustrating intermittent VBA error dialog telling me that "User-define Type Not Defined"
Replies
1
Views
257
Micron
M
B
  • Question Question
#value! error
Replies
6
Views
124
Barny72
B
E
  • Solved
Have date and text in same cell, but still have cell function as date for formulae
Replies
3
Views
204
ExcelNew7
E
U
  • Question Question
When a value is detected in a cell, a VBA code or function will insert a new row with specific values and functions already in each cell
Replies
0
Views
347
ubergreen
U
B
  • Question Question
Function (using VBA macro) Runs Too Frequently
Replies
4
Views
378
Alex Blakenburg
A

Forum statistics

Threads
1,224,522
Messages
6,179,299
Members
452,904
Latest member
CodeMasterX

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top