PDA

View Full Version : Directing SendKeys to the Correct Application



GarysStudent
05-28-2016, 10:00 AM
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
05-28-2016, 12:52 PM
I'd use:


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

GarysStudent
05-28-2016, 01:14 PM
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
05-28-2016, 01:40 PM
Could you use a named formula as here (http://www.ozgrid.com/Excel/named-formulas.htm)?

GarysStudent
05-28-2016, 01:57 PM
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
05-28-2016, 02:43 PM
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
05-28-2016, 04:28 PM
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
05-28-2016, 05:02 PM
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
05-29-2016, 02:29 AM
in VBA you can


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

Selection.FormulaArray = "=Test"
End Sub