Ask a new question

VBA cannot correctly set a conditional formating rule of Type:=2 with an array formula

Dear all,

I came across an issue when creating in VBA a Format Condition (FC) of Type 2 (xlExpression) with an array formula.

When entering the formula manually in the Excel FC panel (without Shift + Control + Enter), it is automatically recognized as an array formula (no brackets added by the GUI).

I am sure of this because the FC format can only apply to the cell(s) when the Formula1 returns "True". And it returns "True" only when interpreted as an array formula (verified as a cell formula).

Defining this exact same formula (FC.Formula1) in VBA does not make it an array formula !

Here is an Excel file example -> https://cjoint.com/c/JHzsH2QNV64

Note that I suspect a reason, but need your help to confirm (using the example file).

In VBA the FC.Formula1 is valued in local format (here Office French language).

Maybe this would work in native English Office ?

Thanks in advance for help.

Best Regards,

JP

I can confirm that this is a bug. Send a frown:

Got feedback? Send a smile or a frown - Microsoft 365 Blog

Andreas.

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Hi Andreas,

Thank you for your reply and taking time to check.

Unfortunately, I don't understand how to send a frown. The page just says "Now that the Visio Preview is available" but where, how, what ? Is this in Office 365 ? I use Office 2016 and Windows 7.

JP

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Help \ Feedback \ Send a Frown

Image

Image

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Ok, even though the option is proposed in the help, I don't have it available.

I suppose due to the fact I haven't created an account for Office on my PC.

I'll try to assign the one used here.

JP

-------------------------------Help file---------------------------------------Actual options--- Help buttons

ImageImageImage

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Andreas,

Which is your Office version ? I'd like to know if this problem also occurs in Office 365.

JP

Edit: Checking on an Excel Forum with 365 users, this bug does NOT occur in Office 365.

In 365 formulas are primarily analyzed as array formulas. Brackets { } have disappeared.

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

For information, it seems impossible to get these smiley icons for feedback on Office 2016 (French only ?) version.

I tried few things from here, created a HKEY_CURRENT_USER\Software\Microsoft\Office\16.0\Common\Feedback key Enabled = 0x1 DWORD uselessly.

I also tried to log something on https://excel.uservoice.com/ even though they ask to go to feedback smileys for bugs.

Has this been routed to a black hole ? Not sure :)

So I give up reporting this issue at any place, the probability for a fix being anyway close to 0.

JP

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info

Last updated October 5, 2021 Views 66 Applies to: