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.

## Like this thread? Share it with others