Excel Forum Add-In - Xlbb

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
1,493
One thing I've noticed is that occasionally the "dynamic array function" message does not appear when it is. Here's an example of a formula using FILTER that doesn't show the message:
That's correct, Eric. It happens if the dynamic array formula doesn't generate a spilled range.
The way how XL2BB knows if there is a dynamic array formula or not in the range is by checking if any of the cells in the selection has spilled or not in the code. If the array formula doesn't generate a spill then XL2BB doesn't know about it since it is not actually checking the function names.

(Note: If there is a workaround that I don't know for this, other than checking the function names, then I would love to hear it, so we can implement in XL2BB.)
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,123
Gotcha! I had assumed you were looking for the function names. But it's good to know that it's not a bug, it's intended behavior.
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,668
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Hi

For some reason XLTOBB does not convert formulas used in Conditional Formatting in Portuguese to English.

See

Portuguese formulas used in cells are correctly converted to English. Therefore, I think it is possible to do the same in CF.

Greetings,

M.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,453
Office Version
  1. 365
Platform
  1. Windows
Marcelo, unfortunately it is still as you reported back in 2019.

The issue has been investigated but it seems to be that those formulas are stored as text strings, not actual formulas, so there is no easy language translation process.
@smozgur may elaborate further if my understanding or explanation is not the best.
 

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
1,493
those formulas are stored as text strings, not actual formulas
This is the actual reason that Peter perfectly explained. That's why the object model has a separate VBA property called Range.FormulaLocal to retrieve the formula in the client language besides Range.Formula that returns English formula names, but since the Formula1 property of FormatCondition object is a string value, we can't easily translate it to English like normal cells.

I attempted to solve this limitation at the XL2BB side, and I had a pretty good workaround for that. It is actually simple; copying the expression into a cell, then getting the corresponding formula property in English and use it! It sounds like a great plan, however, it might have some unexpected results and also requires worksheet editing that XL2BB never does (even it would be reversed). If the Excel object model lets creating a range object on the fly then this might have been possible to try, but VBA was never that good "Object-Oriented". And I honestly don't want XL2BB to change any user settings (like turning on/off automatic calculation), create temporary worksheets, or fails with unexpected formula results during this process.

So, I finally and unfortunately gave up on it.
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,668
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Peter and smozgur,

Thank you very much for the replies. Realizing your effort to improve the forum more and more, I was afraid that only something serious would prevent the formulas from being translated correctly. Unfortunately, this was confirmed.

I mentioned this problem once more so that it, too, would be clear to me that it had not been forgotten.

Thanks again

M.
 

Forum statistics

Threads
1,136,762
Messages
5,677,599
Members
419,706
Latest member
lydytunes13

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top