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 example-Range("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.
Travis
shared this idea
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 VBA-like instead of Excel-like, 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