1

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.

mp9007
  • 79
  • 2
  • 11
  • Unable to recreate the error whether I run this from an XLAM or an XLSM. Can you please clarify what this means: *using a Macro in a cell that call a Sub method ? Calling the same code from a button works perfectly.* What is a "macro in a cell that call a sub method?" – David Zemens Jun 20 '18 at 13:12
  • Also, it looks like the `.Delete` is going to delete the worksheet that you just added. I'm not sure that is intentional. – David Zemens Jun 20 '18 at 13:13
  • 2
    It may be an issue with the newly created sheet not properly being defined. Try defining `ThisWorkbook.Sheets.Add` as `Dim nwSht as Sheet: Set nwSht = ThisWorkbook.Sheets.Add`. Then start your `With` as `With nwSht` – A Cohen Jun 20 '18 at 13:27
  • 2
    Call from a cell? If you mean your code is part of a Public Function you call like =PFunction(), then I doubt your code will ever work. Please explain how you call from a cell? – JvdV Jun 20 '18 at 13:27
  • 1
    To add/explain @JvdV 's comment: it is generally forbidden to alter the Excel Environment during the execution of a UDF worksheet function call. So if you are expecting that a function evaluation will be able to add a worksheet, this is probably not possible without taking some extraordinary measures. – David Zemens Jun 20 '18 at 13:29
  • @DavidZemens it is what I thought that it was forbidden to alter the Excel Environment. I was just not sure and wasn't able to find the good words to find my answer. – mp9007 Jun 20 '18 at 13:58
  • There is a way to get around this limit, by modifying the approach outlined here: https://stackoverflow.com/questions/9476282/cannot-vba-write-data-to-cells-in-excel-2007-2010-within-a-function/9493006#9493006 – David Zemens Jun 20 '18 at 14:23

0 Answers0