Excel VBA: VBA Function Called as Formula Runs Differently?

Question

Here is the macro that I have

Option Explicit

Function AutoFormula(blocks As Range, target As Range)
    Dim blockedArr() As String
    Dim cellValue As String
    Dim ret As String
    Dim i As Integer

    'sprawdzenie zakresu
    If (blocks.Cells.Count > 1) Then
        AutoFormula = "Tylko 1 komórka jako parametr!"
        Exit Function
    End If

     'wczytanie komorki
    cellValue = blocks.Cells(1, 1).Value

    'split
    blockedArr = Split(cellValue, ",")

    ret = "=WYSZUKAJ.PIONOWO(E7;$E$2:$G$5;3;FAŁSZ)"

    For i = LBound(blockedArr, 1) To UBound(blockedArr, 1)
        ret = ret + "+SUMA.JEŻELI(A7:A1000;" + blockedArr(i) + ";G7:G1000)"
    Next i

    target.Cells(1, 1).FormulaLocal = ret

    AutoFormula = 1
End Function

Sub auto()
    Call AutoFormula(Worksheets("reorganizacja").Range("D7"), Worksheets("reorganizacja").Range("G7"))
End Sub

When I run it using the auto() sub it works fine and produces a desired result. However, if I call it as a formula in excell cell like =AutoFormula(D7;G7) it doesn't work. In the Excell cell I get the #ARG error. When I debug the macro it goes properly until target.Cells(1, 1).FormulaLocal = ret line and when I step over it breaks the function immediately without completing it. Any ideas?

user_5448626 · Answer

A UDF can only return a value to the cell(s) whence it was called - it must not modify the contents or formatting of any cell and must not modify the operating environment of Excel. If you attempt to change anything, the function will terminate immediately and return a #VALUE error to the calling cell. Source - Cpearson.com

In your case, you are trying to change the value of a range argument in Excel, e.g. target is an argument and through target.Cells(1, 1).FormulaLocal = ret you are asking Excel to change it.

With other words, imagine that you want to double the value of A1 through a UDF. A way to achieve it is through this:

Option Explicit

Public Function DoubleArgument(a As Range) As Double
    a = a * 2
    DoubleArgument = a
End Function

Public Sub TestMe()
    Debug.Print DoubleArgument(Range("A1"))
End Sub

However, as mentioned, you cannot change values in Excel through a UDF, thus if you run =DoubleArgument(A1) in Excel it would be an error.

If you want to simplify the example, eliminating the parameters, try the following:

Public Function Put5InA1()
    Range("A1") = 5
End Function

Public Sub TestMe()
    Debug.Print Put5InA1
End Sub

TestMe() would generate 5 in A1 and the UDF =Put5InA1() would return an error.

Excel VBA: VBA Function Called as Formula Runs Differently?

1 Answers