Application or Object Defined Error related to Range.Formula2 function

Question

Hello,

I have two computers, one has Office 365 for business with Monthly Channel (1 st screenshot below), the other has Office 365 ProPlus with semi-annual Channel. (2nd screenshot below)

I run the same sample code from Range.Formula2 Office Documentation as below:

https://docs.microsoft.com/en-us/office/vba/api/excel.range.formula2

Sub InsertTodaysDate() ' This macro will put today's date in cell A1 on Sheet1

Sheets("Sheet1").Select

Range("A1").Select

Selection.Formula2 = "=text(now(),""mmm dd yyyy"")"

Selection.Columns.AutoFit

End Sub

The first computer works, the other one said "Application-error or object defined error".

Please note:

1. For the second computer, the tools-> references under VBA are exactly the same as first computer (which is shown in the 3rd screenshot below)
2. range.formula2 is one of the available functions in BOTH computer's VBA because I can see "formula2" in intellisense dropdown when I typed "range."

Could anyone please help me identifying why the second computer did not work?

Thank you!

David

Answer 1

You're getting the second @ because you have that full table column reference (e.g. TableMEL[Generalized Equipment Name]) wrapped in TRIM and TRIM is designed to work on a single string not a column of strings. Suggest you proof your data for leading/trailing spaces and remove them if found then remove the TRIM wrapper. The first @ is inserted for the same reason; IFERROR is designed to work on a single entry. Even though it looks like INDEX is returning a single entry, Excel mistakenly thinks it's returning more than one and inserting a @ to keep the peace.

Answer 2

The Range.Formula2 property is intended to apply formulas like the new FILTER function, etc. which require a unique and specific method of applying them. If your version of Excel does not support these new functions then you cannot use the .Formula2 property. 

Note: This function is currently available to Microsoft 365 subscribers in the Monthly channel. It will be available to Microsoft 365 subscribers in the Semi-Annual channel starting in July 2020. For more information on how features are rolled out to Microsoft 365 subscribers, see When do I get the newest features in for Microsoft 365.

Your Office 365 Monthly channel does support these functions; your Office 365 ProPlus does not.

FWIW, that documentation page is **** and I'd be ashamed to use code like that sample. It was obviously given no thought and published in a rush.

Answer 3

Hi David

Where in the file is the code located?  Is it in a module or is it pasted into one of the sheet objects?  If it is pasted into the code sheet of a specific sheet, is it pasted into the sheet that will have the formula written to it? Code that reference sheets other than those whose code pages contain the code can behave unpredictably.  Sheet code pages and the ThisWorkbook code page should usually only be used to hook events relating to those objects.  Typically, the type of code you are trying to run should stored be in a generic code module.

Also, does the failure take place in the exact same file too or are you running the same code from two different files on two different PCs? If you are using the same code but in two files, one of them may have the code stored in the wrong place in the file.

Hope that helps!  Keep us posted.

Best wishes,

Tim

Answer 4

For both computers, I just opened an empty excel workbook and then go to VBA and insert a module under Modules

Oh, one thing I might miss above:

The first computer is 64-bit MSO

The second computer is 32-bit MSO

I am not sure if it makes the difference.

Thanks

David

Answer 5

Thanks Jeeped,

May I have two follow up questions?

1. Will Office 365 ProPlus have Formula2 working properly after July 2020?
2. The reason why I need to use the .formula2 instead of .formula is that: I have the following formula stored as string which will be added to the cell by VBA code "range.formula2=xxx", unfortunately, when I used .formula instead, it will always add two extra "@" automatically(highlighted as red text below): 

a. The first @ I think there is no impact on my formula results whether I deleted it or not.

b. The second @ is accompanied with an extra [], please note ideally it should be [xxx], but it is automatically changed to [@[xxx]]. I think it caused the trouble, because it refers to current table row (not expected) rather than entire table databodyrange (expected), and that caused the incorrect results than expected.

Working: (with .Formula2)

=IF(MAXIFS(VulOutputTb_Filtered[Pv Tb Severity],VulOutputTb_Filtered[Conca Vul-MoF],[@[Conca Vul-MoF]],VulOutputTb_Filtered[Legend],"Unit 15")=0,IF(IFERROR(INDEX(TRIM(INDIRECT("TableMEL[["&INDEX(MELIDMap[MEL Header],MATCH(INDEX(LegendIDMap[Project ID],MATCH("Unit 15",LegendIDMap[Legend],0)),MELIDMap[Project ID],0))&"]]")),MATCH([@[Equipment from Vul]],TRIM(TableMEL[Generalized Equipment Name]),0)),"MEL N/A")="","Eqmt N/A",""), MAXIFS(VulOutputTb_Filtered[Pv Tb Severity],VulOutputTb_Filtered[Conca Vul-MoF],[@[Conca Vul-MoF]],VulOutputTb_Filtered[Legend],"Unit 15"))

Not working: (with .Formula)

=IF(MAXIFS(VulOutputTb_Filtered[Pv Tb Severity],VulOutputTb_Filtered[Conca Vul-MoF],[@[Conca Vul-MoF]],VulOutputTb_Filtered[Legend],"Unit 15")=0,IF(IFERROR(@INDEX(TRIM(INDIRECT("TableMEL[["&INDEX(MELIDMap[MEL Header],MATCH(INDEX(LegendIDMap[Project ID],MATCH("Unit 15",LegendIDMap[Legend],0)),MELIDMap[Project ID],0))&"]]")),MATCH([@[Equipment from Vul]],TRIM(TableMEL[@[Generalized Equipment Name]]),0)),"MEL N/A")="","Eqmt N/A",""), MAXIFS(VulOutputTb_Filtered[Pv Tb Severity],VulOutputTb_Filtered[Conca Vul-MoF],[@[Conca Vul-MoF]],VulOutputTb_Filtered[Legend],"Unit 15"))

Is there work around to that or the only way is to use .Formula2?

Thanks

David