put results of a vba function in another excel cell

Question

I need a VBA function that stores one of its results in a cell other than the cell that calls the function. I have successfully passed to the function the address of that other cell. My function works perfectly when called from another VBA macro, but when my function is called from a cell in an Excel worksheet it crashes with #VALUE! in the calling cell and without putting anything in the target cell.

When I step through my function I see that it correctly has the address of the cell where I want to place a value.

Sub callMyFunc()     'when I call myFunc this way it works perfectly

    Dim theAddrRang As Range, resp%

    Set theAddrRang = Range("B17")

    resp = myFunc(theAddrRang)

    MsgBox "resp = " & resp

End Sub  



Function myFunc(addrR As Range) As Integer  
                        'If this function is called from worksheet it crashes
    Dim theAddr$, addrRstr$

    addrRstr = addrR.Address

    theAddr = "B14"

    MsgBox "Two address strings: " & addrRstr & "    " & theAddr

    Worksheets("Linda").Range(theAddr).Value = 44      'B14   crashes at this step

    Worksheets("Linda").Range(addrRstr).Value = 66     'B17

    myFunc = 88

End Function

I want function myFunc to put the values 44 and 66 in cells B14 and B17. When I call my function from another macro it works perfectly. When I type into a cell on my worksheet the following: =myFunc(B17) , it correctly shows the message box with two addresses and then crashes on the next line of code. (Two addresses just to test that both work; I really need only the one passed as argument.)

This is a well-known (here at least) limitation of using UDF in Excel: see https://support.microsoft.com/en-us/help/170787/description-of-limitations-of-custom-functions-in-excel — Tim Williams, Oct 02 '19 at 05:07
Possible duplicate of [Using a UDF in Excel to update the worksheet](https://stackoverflow.com/questions/23433096/using-a-udf-in-excel-to-update-the-worksheet) — Storax, Oct 02 '19 at 06:00

score 1 · Accepted Answer · answered Oct 02 '19 at 05:18

...but here is a work-around using Evaluate:

Function myFunc(addrR As Range) As Integer

    Dim wb, sht, addr

    'where is the target cell?
    addr = addrR.Address           'cell
    sht = addrR.Parent.Name        'sheet
    wb = addrR.Parent.Parent.Name  'workbook

    'pass the location of the cell to be updated and the value to be inserted
    Application.Evaluate "SetIt(""" & wb & """,""" & sht & """,""" & addr & """,99)"

    myFunc = 88 'return a value to the calling cell

End Function


'Calling this using evaluate seems to bypass the restrictions
'  Excel places on UDF's called from a worksheet
Function SetIt(wb As String, sht As String, addr As String, v)
    Workbooks(wb).Sheets(sht).Range(addr).Value = v
End Function

put results of a vba function in another excel cell

1 Answers1