I was wondering, can I add an excel sheet using a Macro in a cell that call a Sub method ? Calling the same code from a button works perfectly.
My code crash a the .Range("A1") = "Col1" with the wonderful error message Application-defined or Object-defined error. I have read that my cells needs to be fully qualified. So I tried replacing ThisWorkbook with Workbooks("MyExcelAddins.xlam"), but no success.
Is it because when I'm in a cell, the state of the application is not the same ? I suppose that adding a sheet didn't work...
Thank you.
My code:
Application.ScreenUpdating = False Application.DisplayAlerts = False With ThisWorkbook.Sheets.Add .Range("A1") = "Col1" .Range("B1") = "Col2" .Range("C1") = "Col3" .Range("D1") = "Col4" .Range("A2") = filter1 .Range("B2") = filter2 .Range("C2") = filter3 .Range("D2") = filter4 Workbooks("MyExcelAddIns.xlam").Sheets("MyDataSheet").Range("tblMyDataToFilter").CurrentRegion.AdvancedFilter xlFilterInPlace, .Range("A1:D2") .Delete End With Application.ScreenUpdating = True Application.DisplayAlerts = True EDIT
Clarification 1: what I mean by "using a Macro in a cell that call a Sub method" is =MyFunc(A1;A2;A3;A4).
Clarification 2: yes I know that my code is deleting the sheet right away. I wanted to be able to filter a table without looping throught each row and I found this technique in this answer : VBA (Excel): Find Based on Multiple Search Criteria Without Looping .
Finally, I'll mark this post as "Answered", because the comment of David Zemens.
it is generally forbidden to alter the Excel Environment during the execution of a UDF worksheet function call.
It is the answer I was looking for. Thank you guys.