Directing SendKeys to the Correct Application

[GarysStudent]

Because of the length limitations of Range.FormulaArray, when I need to enter a large array formula in a cell, I enter it as a normal formula and then apply Cntrl+Shift+Enter with SendKeys. For example:

Sub ArFormula()
    ActiveCell.Formula = "=MAX(IF(A2>B1:B12,B1:B12))"
    With Application
        .SendKeys "{F2}" ' get into edit mode
        .SendKeys "^+~" '  use CNTRL + Shift + Enter
    End With
End Sub

This works fine if I am running it from the worksheet using the Ribbon Menu. However, if I try to run it from the VBA window, it fails. It fails because its the VBE that gets the keystrokes, not Excel.

In Excel F2 gets into the Edit mode for a cell, in the VBE, F2 does something else.

Is there any way to modify the code to insure that Excel gets the keystrokes and not the VBE ??

[snb]

I'd use:

sub M_snb()
  CallByName ActiveCell, "formulaarray", 4, "=MAX(IF(A2>B1:B12,B1:B12))"
End Sub

[GarysStudent]

Thanks for responding.........sadly once the number of characters in the formula exceeds 256, the CallByName method fails:

Sub ytrewq3()
    Dim ReallyBigFormula As String, piece As String
    
    piece = "+A1"
    With Application.WorksheetFunction
        ReallyBigFormula = "=A1" & .Rept(piece, 90)
    End With
    
    CallByName ActiveCell, "formulaarray", 4, ReallyBigFormula
    
End Sub

[mdmackillop]

Could you use a named formula as here?

[GarysStudent]

Very interesting!!.....................I am starting with the proposed array formula as a String variable in VBA..............is there a way to create a Named Formula from a String ??

[mdmackillop]

Can't say I understand it but the non-array Named formula appears to give the same results as the array formula, at least with your sample formula.

[GarysStudent]

Thanks for the help !! Your Sub has an extra Exit Sub in the middle....................take that out and it runs just fine!

Here is an adaptation of your method:

Sub RBF()
    Dim ReallyBigFormula As String, piece As String
    
    piece = "+$A$1"
    With Application.WorksheetFunction
        ReallyBigFormula = "=$A$1" & .Rept(piece, 90)
    End With
    
    ActiveWorkbook.Names.Add Name:="Test", RefersTo:=ReallyBigFormula
    
    Selection.FormulaArray = "=Test"
End Sub

[SamT]

A formula that long probably has several parts that return values on their own. Each of those parts can be a named formula, then use that name in another part that needs the returned value.

=IF(CONCATENATE(VLOOKUP(BAH, BLAH, BLAH), HLOOKUP(,BLAHBLAH)),BLEHBLEHBLEH,WHAWHAWHA)

Named Formula VL1 = VLOOKUP(BAH, BLAH, BLAH)
Named Formula HL1 = HLOOKUP(,BLAHBLAH)
Named Formula CON1 = CONCATENATE(VL1, HL1)
Etc

Then:
{=If(CON1,BLEH1,WHA1)}

I'm not really a formula guy, you might have to use R1C1 notation.

[snb]

in VBA you can

Sub M_snb() 
  ActiveWorkbook.Names.Add "Test", "=" & replace(string(91,"|"),"|","+$A$1")
     
  Selection.FormulaArray = "=Test" 
End Sub