0

I'm trying to use my own VBA function in an Excel sheet with a return value and the same function manipulates a cell on the same or on an other sheet, but the result is #VALUE! A minimal working example (Office Prof Plus 2010, 32-bit):

Function abc() As Integer
   Dim i%
   i = 0
   Sheet1.Cells(1, 2).Value = 2
   abc = i
End Function

When I execute Debug.Print abc it obviously writes a 2 to the cell B2 and the printed result is 0. What I want to do now is =abc() in cell A1 on Sheet1, but I only get #VALUE!. Btw, it doesn't work either if Application.EnableEvents and Application.Calculation is disabled resp. set to manual.

Two questions: Why? And any idea how to resolve? Thx.

dan
  • 25
  • 6
  • [See this Q for a workaround](http://stackoverflow.com/q/23433096/445425) – chris neilsen Mar 03 '17 at 01:18
  • Note that in the workaround, there is a problem (bug?) that the `Evaluate` is run twice. [See here](http://stackoverflow.com/q/2611929/445425) for a workaround to _that_ – chris neilsen Mar 03 '17 at 01:45

2 Answers2

1

It's well known that you can't update the worksheet with a UDF (other than the cell with the UDF in). However, there is a pretty awful workaround which checks every time you make a change on your worksheet and places a 2 in B1 for you if your function happens to be =abc()

Have your dummy function in in a standard module

Public Function abc() As Integer
    abc = 0
End Function

Then in the Sheet1 module place the following code

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub

    If Target.Formula = "=abc()" Then
        Me.Cells(1, 2).Value = 2
    End If
End Sub
CallumDA
  • 12,025
  • 6
  • 30
  • 52
0

This type of User Defined Function (called from within a cell) can't modify cell values other than to return a value to the cell containing the UDF.

Gary's Student
  • 95,722
  • 10
  • 59
  • 99