CODEWITHSUNDEEP
excelvba
Kyle
Kyle

Reputation: 27

Application.Volatile not recalculating functions when values on another workbook are changed

I have an excel workbook that uses Application.Volatile in three user-defined functions. Below is an example of one of the functions, the other two are very similar. All they do is find a value on a specified worksheet and return the value to a summary worksheet.

Function estimated_commission(client As Range) As Double

    Application.Volatile

    Name = CStr(client.Value)
    pull_value = Worksheets(Name).Range("A500").End(xlUp).Offset(0, 5).Value

    estimated_commission = pull_value

End Function

The function works how I intended when the end user only has the workbook that uses the functions open. Calculation options for the workbook are set to Automatic.

The issue comes when the user makes a change to a cell value or saves another workbook. These workbooks have no relation to the workbook with the user-defined functions. When the user re-activates the workbook with the functions, all of the user-defined functions return #VALUE error. Pressing F9 or recalculating the worksheet will remove all the errors and return the correct values.

Is there a way to prevent this issue from occurring?

Upvotes: 1

Views: 715

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57743

Worksheets(Name) is missing the reference to its workbook. If you don't reference the workbook Excel assumes ActiveWorkbook which is the one that has focus / is on top. So that is the workbook the user switched to and it probably doesn't have a worksheet called Name and therefore your function errors and returns #VALUE.

Reference to ThisWorkbook which is the workbook the code is running at.

ThisWorkbook.Worksheets(Name)

Upvotes: 0

Related Questions