Thisworkbook no longer works in UDF but Activeworkbook does? RRS feed

  • Question

  • Can anyone tell me why all of a sudden my Public Functions no longer work and come up with "Automation Error".
    to Fix it all i need to do is change Thisworkbook to Activeworkbook as below

    Set DSETable = ThisWorkbook.Sheets("Profile").ListObjects("DSETable")

    change to

    Set DSETable = ActiveWorkbook.Sheets("Profile").ListObjects("DSETable")

    I know a solution is to replace that line of code or replace the Module in the files but i'm wondering/hoping if there is a setting that has changed or something. The reason is i have a few thousand files to change if not.

    Thanks in advance for any info

    Monday, December 2, 2019 5:56 AM

All replies

  • Did you store the function(s) in another workbook, for example your personal macro workbook PERSONAL.XLSB or in an add-in? ThisWorkbook always refers to the workbook that contains the code.

    Regards, Hans Vogelaar (

    Monday, December 2, 2019 8:47 AM