CODEWITHSUNDEEP
vbaexcelexcel-formulaexcel-2010
dan
dan

Reputation: 25

Excel VBA own function in worksheet which changes value on a sheet

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.

Upvotes: 0

Views: 540

Answers (2)

CallumDA
CallumDA

Reputation: 12113

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

Upvotes: 1

Gary's Student
Gary's Student

Reputation: 96773

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.

Upvotes: 0

Related Questions