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[i]
Next i

XLModel = ResultValue

End Function

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

Thanks for your input. It is really unfortunate for me that you can't change
values of another cell from within a UDF. It sucks!

However, I have worked out another, though not so elegant workaround. I
created a function which takes all the parameters (thus allowing me to copy
paste it anywhere I like even across large tables, and use function paste
wizard) but does nothing. Once I insert the function at various places in a
worksheet, I run a macro (using a keyboard shortcut). This macro Finds all
the occurances of my UDF, and for each occurance parses it for picking out
parameters, does the job of setting model input values, gets the output from
the model and inserts it in a cell right to where the function is inserted.

Here is the code for the UDF (XLModel) and the macro (ExcelModel):

UDF _XLModel_

Function XLModel(Name As String, InputValueCells As Range, ModelInputCells
As Range, ModelOutputCell As Range)
'Converts any Excel model into a function
'This function is searched by ExcelModel macro which in turn processes
its arguments
'The result is displayed in the next cell
'If the values to be plugged in don't map to the model inputs, show
error message
If (InputValueCells.Cells.Count < ModelInputCells.Cells.Count) Then
MsgBox "Error: Select same number of cells in InputValueCells and
ModelInputCells"
Else: XLModel = Name
End If
End Function

Macro _ExcelModel_
Sub ExcelModel()
'
' Allows an existing Excel model to be used as a function. Searches
=XLModel("Name", Input values, Model Input, Model Output) and gets the result.

Set c = Cells.Find("XLModel")
If Not c Is Nothing Then
firstAddress = c.Address
Do
strRange = Replace(c.Formula, ")", "")
arr = Split(strRange, ",")

Dim InputValueCells As Range
Dim ModelInputCells As Range
Dim ModelOutputCell As Range

Set InputValueCells = Range(arr(1))
Set ModelInputCells = Range(arr(2))
Set ModelOutputCell = Range(arr(3))

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

'Temporarily hold existing input values of the model
Dim TempArray(10) As String

'Run through all the cells of values to be plugged in the model
For i = 1 To InputValueCells.Cells.Count
TempArray(i - 1) = 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.Cells.Count
ModelInputCells.Cells(i) = TempArray(i - 1)
Next i

Range(c.Address).Next.Value = ResultValue

Set c = Cells.FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End Sub


"Scott" wrote:

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