[Solved] How to use Excel Formulas in VBA?

Question

To use Excel formula in VBA you have two choices: you can either Evaluate the function string as in @YowE3K’s example given here, or alternatively you can use the WorksheetFunction method to perform most Excel functions. The Evaluate method is best if you want to do a fixed formula like your example suggests and return the result in VBA, the WorksheetFunction method allows you to specify the arguments in VB language, which (in my opinion) is better for a dynamic function (there is however a trade-off in terms of speed if you’re trying to do this multiple times, such as iterating over a range of cells etc.) However, since you have another function embedded in the IfError, you’d need to nest another WorksheetFunction within the IfError one, like below:

WorksheetFunction.IfError([expression], WorksheetFunction.NetworkDays([date from], [date to], [holidays]))

However, it is not clear from your post what you want to do. The key thing here would be to provide some further information about what you are trying to do overall – i.e. what is the ‘big picture’ that you’re trying to achieve? Do you just want to test the value in one cell? Will this be something that you need to repeat for multiple cells? Why do you need to do this in VBA, when you could much more easily use the formula in a worksheet?

Accepted Answer

To use Excel formula in VBA you have two choices: you can either Evaluate the function string as in @YowE3K’s example given here, or alternatively you can use the WorksheetFunction method to perform most Excel functions. The Evaluate method is best if you want to do a fixed formula like your example suggests and return the result in VBA, the WorksheetFunction method allows you to specify the arguments in VB language, which (in my opinion) is better for a dynamic function (there is however a trade-off in terms of speed if you’re trying to do this multiple times, such as iterating over a range of cells etc.) However, since you have another function embedded in the IfError, you’d need to nest another WorksheetFunction within the IfError one, like below:

WorksheetFunction.IfError([expression], WorksheetFunction.NetworkDays([date from], [date to], [holidays]))

However, it is not clear from your post what you want to do. The key thing here would be to provide some further information about what you are trying to do overall – i.e. what is the ‘big picture’ that you’re trying to achieve? Do you just want to test the value in one cell? Will this be something that you need to repeat for multiple cells? Why do you need to do this in VBA, when you could much more easily use the formula in a worksheet?