Menu
I can evaluate a normal Excel formula within VBA like:
Sub dural()
MsgBox Evaluate("SUM(A1:A10)")
End Sub
How can I evaluate an array formula??
629
Be sure to select cells E2:E11, enter the formula =C2:C11*D2:D11, and then press Ctrl+Shift+Enter to make it an array formula. In the sample workbook, select cells E2 through E11. These cells will contain your results.
You use the LEN function to return the length of the text string in each individual cell, and then you use the SUM function to add up those numbers. For example, the array formula =SUM(LEN(A1:A10)) calculates the total number of all chars with spaces in range A1:A10.
Sum, SumProduct, Frequency, Linest, lookup functions, etc. take both range and array objects. To use countif, you have to use range in cells, defining the array in the formula on the go will not work.
The VLOOKUP function can be combined with other functions such as the Sum, Max, or Average to calculate values in multiple columns. As this is an array formula, to make it work we simply need to press CTRL+SHIFT+ENTER at the end of the formula.
As suggested by L42, you only have to use the string variant of Evaluate to get it to work with array formulas.
For instance, does
Sub dural()
MsgBox Evaluate("SUM(A1:A10)")
MsgBox Evaluate("=SUM(G5:G10-F5:F10)")
MsgBox [=SUM(G5:G10-F5:F10)]
MsgBox [Sum(G5:G10-F5:F10)]
End Sub
work for you (with appropriate values in G5:G10 and F5:F10)?
Some further information is here.
134
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
