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.

Improve this question
7
  • 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?" Commented Jun 20, 2018 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. Commented Jun 20, 2018 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 Commented Jun 20, 2018 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? Commented Jun 20, 2018 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. Commented Jun 20, 2018 at 13:29
Load 6 more related questions Show fewer related questions

0

Reset to default

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.