Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
User Defined Function Syntax
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
User defined functions without using VBA. | Excel Discussion (Misc queries) | |||
Custom functions calculating time arguments Help Desperate | Excel Worksheet Functions | |||
Getting Yahoo Stock Quotes by a USer Defined Function | Excel Worksheet Functions | |||
Date & Time | New Users to Excel | |||
how to move user defined function | Excel Worksheet Functions |