What you're trying to do, you can't do, unless you put all the data
processing into your function. One of the limitations of UDFs is:

- More limited than regular VBA macros. UDF's cannot alter the
structure or format of a worksheet or cell.
- Cannot place a value in a cell other than the cell (or range)
containing the formula. In other words, UDF's are meant to be used as
"formulas", not necessarily "macros".

You can either put all the calculations of your Model into the
function, or go the other route and set it up as a Macro.

Scott

Ankur wrote:[i]
Hello,

I am new to UDF and VBA. I have this small function here, could you please
provide me the corrected syntax? This UDF should allow me to use any Excel
model (collection of cells linked with each other through a chain of
formulas, starting with some input cells and ending with a result cell) as a
function.

Thanks.

Function XLModel(Name As String, InputValueCells As Range, ModelInputCells
As Range, ModelOutputCell As Range)
'Converts any Excel model into a function

'If the values to be plugged in don't map to the model inputs, show
error message
If (InputValueCells.Areas.Count < ModelInputCells.Areas.Count) Then
XLModel = "Error: Select same number of cells in InputValueCells and
ModelInputCells"
End If

'Temporarily hold existing input values of the model
DIM TempArray As String[InputValueCells.Areas.Count]


'Run through all the cells of values to be plugged in the model
For i = 1 To InputValueCells.Areas.Count
TempArray [i] = ModelInputCells.Cells[i]

'Plug in the values into the model input
ModelInputCells.cells[i] = InputValueCells.cells[i]
Next i

'Pick up the model output value
ResultValue = ModelOutputCell.Value

'Reset the model input values to what they were originally
For i = 1 To InputValueCells.Areas.Count
ModelInputCells.cells[i] = TempArray
Next i

XLModel = ResultValue

End Function