CODEWITHSUNDEEP
vbaexcelauto-update
Joe
Joe

Reputation: 23

Recalculate sheet excel in VBA when getting numbers from cells with Cells.Value

Here is a simple example to explain what I want to do:

Function fun(X As Double)

    Worksheets("Sheet1").Activate

    Y = Cells(2, "C").Value

    fun = X + Y

End Function

So, if I am changing the value of X inside the excel sheet, result of fun will change as expected, but if I change the value of Y, nothing happens. I must change X again so the function will get the new value of Y and calculate the proper value.

Is there a way to add code in this VBA function so the result of fun will automatically update with a change of value for Y?

Upvotes: 2

Views: 119

Answers (1)

user4039065
user4039065

Reputation:

Add Application.Volatile to your function as the first line.

Function fun(X As Double)
    Application.Volatile
    'Worksheets("Sheet1").Activate
    Dim Y As Long

    Y = Worksheets("Sheet1").Cells(2, "C").Value

    fun = X + Y

End Function

The assignment to Y should be done directly, not by activating a worksheet.

Upvotes: 1

Related Questions