Excel Forum Add-In - Xlbb

[smozgur]

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]

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]

Hi

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

See

Test Formula CF

Testing if the formula in CF is translated to English ABC215/07/202105:45 AMtest1315/07/202106:05 AMtest2415/07/202106:12 AMtest4516/07/202106:22 AMtest2616/07/202106:47 AMtest3716/07/202107:18 AMtest4816/07/202107:21 AMtest5916/07/202109:54 AMtest61016/07/202110:38 AMtest71116/07/202111:45...

www.mrexcel.com

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]

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]

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]

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.