excel UDF doesn't work when used in sheet

Question

I created a function that is working when activated through a sub in my workbook, but when trying to activate it in a worksheet (using =function_name(arguments))

Function calclkg(Optional table_name As String = "lkg_calc_params", Optional ByVal temp As Double = 110) As Double
    SetTableValue table_name:=table_name, row_name:="Temp", col_name:="value", value:=temp
    calclkg = GetValueFromTable(table_name:=table_name, row_name:="LKG calc", col_name:="value")
End Function

this function is calling this sub that generated the error I'm getting

Sub SetTableValue(table_name As String, row_name As String, col_name As String, value As Variant, Optional ByVal sheet_name As Variant)
    On Error GoTo errline
    Dim WS As Worksheet
    Dim wb As Workbook
    Dim tbl As ListObject
    Set WS = Application.Caller.Worksheet.Parent.Worksheets(sheet_name)
    Set tbl = WS.ListObjects(table_name)
    Dim Rhead As Range
    Set Rhead = tbl.HeaderRowRange
    col_index = Application.WorksheetFunction.Match(col_name, Rhead, 0)
    Dim Rcol As Range
    Set Rcol = tbl.ListColumns(1).DataBodyRange
    row_index = Application.WorksheetFunction.Match(row_name, Rcol, 0)
    ' here is the line where I get the error:
    tbl.ListColumns(col_index).DataBodyRange.Rows(row_index) = value
Exit Sub
errline:
   MsgBox "Error # " & Err & " : " & Error(Err)
   Resume Next
End Sub

while calling function through this sun, I'm getting the desired result:

Sub Calc()
    Dim temp As Double
    temp = InputBox("temp:")
    calclkg temp:=temp
End Sub

I'm not able to update table cell value using this line when calling the function from my sheet directly (getting 1004 error)

tbl.ListColumns(col_index).DataBodyRange.Rows(row_index) = value

I tried various ways but none of them worked. like:

tbl.Range.Item(row_index + 1, col_index) = value

what am I doing wrong?

You cannot use a UDF in a cell to assign values to other cells. — Rory, Jan 02 '15 at 10:20
I would ask what you are trying to do that you'd need the UDF there? It looks like you might benefit from VLOOKUP. — peege, Jan 02 '15 at 14:52

score 0 · Answer 1 · answered Jan 02 '15 at 14:34

A UDF cannot change the structure of the worksheet in any way e.g. copying cells or changing fonts; the UDF can call other functions and subroutines but they are then under the same constraints as the UDF.

Essentially a UDF takes parameters and returns a value to the cell the formula is in.

excel UDF doesn't work when used in sheet

1 Answers1