CODEWITHSUNDEEP
vbaexcelvariables
jeppesr
jeppesr

Reputation: 21

Excel VBA - Set variable to value of formula

Is there anyway to set a variable in VBA to the result/value of a formular. I need it to calculate the result in VBA and not in a cell.

Variable1 = "=SUBTOTAL(3,INDIRECT('New Sheet 1'!A3:A4500))"
Variable2 = "=SUMPRODUCT(--ISERR('New Sheet 1'!J3:J4000))"
Variable3 = "=COUNTBLANK('New Sheet 1'!P2:P4000)"

Already here I see some issues using the '-marks.

Thanks in advance!

Upvotes: 2

Views: 7067

Answers (1)

Scott Craner
Scott Craner

Reputation: 152660

Use Evaluate

Variable1 = Application.Evaluate("SUBTOTAL(3,INDIRECT('New Sheet 1'!A3:A4500))")
Variable2 = Application.Evaluate("SUMPRODUCT(--ISERR('New Sheet 1'!J3:J4000))")
Variable3 = Application.Evaluate("COUNTBLANK('New Sheet 1'!P2:P4000)")

Or you can use the short hand for Evaluate:

Variable1 = [SUBTOTAL(3,INDIRECT('New Sheet 1'!A3:A4500))]
Variable2 = [SUMPRODUCT(--ISERR('New Sheet 1'!J3:J4000))]
Variable3 = [COUNTBLANK('New Sheet 1'!P2:P4000)]

[] used this way is shorthand for Application.Evaluate And with the formulas you are using it will work.

If your formulas contained a concatenated string with variables then the shorthand method would not work, and the Application.Evaluate would need to be spelled out with the quotes

Upvotes: 8

Related Questions