Excel array formula changes keyboard layout

Question

To enter an array formula in Excel, one uses Ctrl+Shift+Enter and to switch keyboard layout, one uses Ctrl+Shift

Thus my keyboard layout changes every time I enter an array formula.

Is there a more elegant solution than switching back manually each time?

One solution would be to change the switch layout shortcut, but I can't find it in Windows 10 (I could in Win7). Where is this setting?

Answer 1

One alternative would be to create a macro to convert any formula to an array formula, and assigning your own shortcut (ctrl+m for example).

Sub convert_to_array()

Dim formarr As Range
Dim oldrange As Range

Set oldrange = Selection

    For Each formarr In oldrange
        formarr.FormulaArray = formarr.Formula
    Next formarr

End Sub

Then just write normal formulas, select all the ones you want, and hit ctrl+m (or whatever shortcut you assign).

Answer 2

The problem is caused because for a long time now (pre-Windows 10) the default keys for changing the Windows keyboard layout are Ctrl+Shift. Every time you push those two keys together, Windows switches between any layouts you have installed. Given the number of shortcuts out there that use those keys, it's a pretty dumb key setup if you ask me, but anyway...

To correct this problem in Windows 10, you need to go to Control Panel >> Clock, Language, and Region >> Language >> Advanced settings and from this screen, click on the Change language bar hot keys link. This will open a window labeled Text Services and Input Languages. Select the option for Between input languages in the hotkeys box, then click the Change Key Sequence button. It's not very customizable but you can at least change both the Input Language and Keyboard Layout hotkeys to be Not Assigned. This will prevent the problem you had with your Excel macros.

If you have need of hotkeys to switch back and forth, you can set these here as well, though there's not a toggle option like the one you just turned off. Here you have to pick something like Ctrl+Shift+1 for Dvorak and Ctrl+Shift+2 for QWERTY. Make sure you set the non-QWERTY hotkey last just before closing the window, because Windows has a tendency to undo your hotkeys if that's not the last one (no idea why, it's just done it that way to me on every computer I've had since Windows XP).