How to combine FormulaArray and FormulaLocal options in Excel VBA?

[cercig]

I am sending functions inside VBA code to the cells with a VBA-code line like below:

[COLOR=black][FONT=Consolas]Sheets[/FONT][/COLOR][COLOR=black][FONT=Consolas]([/FONT][/COLOR][COLOR=maroon][FONT=Consolas]"Sheet1"[/FONT][/COLOR][COLOR=black][FONT=Consolas]).[/FONT][/COLOR][COLOR=black][FONT=Consolas]Range[/FONT][/COLOR][COLOR=black][FONT=Consolas]([/FONT][/COLOR][COLOR=maroon][FONT=Consolas]"B2"[/FONT][/COLOR][COLOR=black][FONT=Consolas]).[/FONT][/COLOR][COLOR=black][FONT=Consolas]FormulaLocal [/FONT][/COLOR][COLOR=black][FONT=Consolas]=[/FONT][/COLOR][COLOR=maroon][FONT=Consolas]"=somefunction_in_local_language"[/FONT][/COLOR]

I am using <code style="margin: 0px; padding: 1px 5px; border: 0px; font-size: 13px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: pre-wrap; background-color: rgb(238, 238, 238);">FormulaLocal</code> option because the functions in cells are in the local language, not in English.
Now I want to send array functions, and I am supposed to use <code style="margin: 0px; padding: 1px 5px; border: 0px; font-size: 13px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: pre-wrap; background-color: rgb(238, 238, 238);">FormulaArray</code> to do this. However, even the array functions will be in the local language. I guess I am supposed to combine both <code style="margin: 0px; padding: 1px 5px; border: 0px; font-size: 13px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: pre-wrap; background-color: rgb(238, 238, 238);">FormulaArray</code> and <code style="margin: 0px; padding: 1px 5px; border: 0px; font-size: 13px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: pre-wrap; background-color: rgb(238, 238, 238);">FormulaLocal</code> somehow, but how?
I tried to find if there is something like <code style="margin: 0px; padding: 1px 5px; border: 0px; font-size: 13px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: pre-wrap; background-color: rgb(238, 238, 238);">FormulaArrayLocal</code>, but there is not such a thing. So any idea?

 

[Jonmo1]

Try it with 2 lines
1 to just put the formula in the cell in Non Array format
Then use .FormulaArra = .FormulaLocal to enter it as an array.

Sheets("Sheet1").Range("B2").FormulaLocal ="=array_formula_in_local_language"
Sheets("Sheet1").Range("B2").FormulaArray = Sheets("Sheet1").Range("B2").FormulaLocal

 

[cercig]

Try it with 2 lines
1 to just put the formula in the cell in Non Array format
Then use .FormulaArra = .FormulaLocal to enter it as an array.

Sheets("Sheet1").Range("B2").FormulaLocal ="=array_formula_in_local_language"
Sheets("Sheet1").Range("B2").FormulaArray = Sheets("Sheet1").Range("B2").FormulaLocal

Thanks a lot. It gave error for the second line when I used with FormulaLocal, but it worked when I changed it to only Formula...

 

[Jonmo1]

You're welcome.