UDF Application.Volatile and Other Workbooks

[MikeMcCollister]

I have some user defined functions (UDF) that I have added Application.Volatile to. They work fine and if I make changes to a sheet in my workbook the functions get called. However, I just found out that if I have a second workbook open and make changes to that then the workbook with Application.Volatile in the UDFs gets updated. Is there a way to get Application.Volatile so that if another workbook is changed that those UDFs don't run?

Thanks,

Mike

 

[Jeffrey Mahoney]

I have tried to solve that before. I think I did a test in the Function to see if the current workbook was "Thisworkbook" Can't remember if I got it to work

 

[MikeMcCollister]

Jeffrey,

Thanks. I've investigated that and I can't get it to work. With my code below it runs the first time I open the xlsm sheet and subsequent times. However, when I make changes to the non-xlsm sheet it gets called once and then never again until I force the xlsm sheet to recalculate.

Function SheetName() As String
    Debug.Print "Enter SheetName()"
    
    If (Application.ThisWorkbook.Name = Application.ActiveWorkbook.Name) Then
        Application.Volatile
            
        Debug.Print "Volatile SheetName()"
    End If
    
    SheetName = Application.Caller.Parent.Name
End Function

From the documentation, Application.Volatile method (Excel) | Microsoft Docs, it states that "The function will be recalculated when any cell in any workbook in the application window changes value."

It looks like what I want to do is not possible. Thanks for your help. This is not a major issue, just a curiosity at this point.

Regards,

Mike

 

[Jeffrey Mahoney]

Oh BTW, if you want a formula instead of a UDF to get the sheet name, here ya go:
=MID(@CELL("filename",Payroll!AC1),FIND("]",@CELL("filename",Payroll!AC1))+1,100)

Replace Payroll with any sheet name. If you change the name of the sheet, it will also change.

 

[MikeMcCollister]

Jeffrey,

Thanks. I'm familiar of not using a UDF to get the sheet name. However, I have other UDF functions that need to get the sheet name so I encapsulated it into a function.

Regards,

Mike