VBA formulas same as excel formulas
I would like to see the abiltiy to use any excel formula used in VBA, with the same syntax.
For exampleRange("A1").value = ExcelFormula.=countif(B2:B30),testing
Something like that to signfiy to vba that an excel formula in the exact same way that it is used in VBA, so you could even cut and paste any formula between excel and VBA.
Hi,
We have no plans to structurally update the VBA environment (see “Bring VBA into the modern world”). However, you can work with Excel functions in VBA already, using the WorkbookFunction object. The syntax is still VBAlike instead of Excellike, but the functionality is there.
Alternatively, you can use Evaluate, as Kenneth suggested:
Dim x As Double
x = Evaluate(”SUM(A1,A2)”) ‘Evaluate function
x = [SUM] ‘Shortcut
Cheers,
Dan [MS]
2 comments
Comments are closed
Fried Eggs commented
The trouble with Evaluate is there is a 255 character limit which often renders it useless.

Kenneth Barber commented
Try the Evaluate function or its shortcut.
Dim x As Double
x = Evaluate("SUM(A1,A2)") 'Evaluate function
x = [SUM(A1,A2)] 'Shortcut