Fri 30 Oct 2009, 12:51Delegate William said...
William has attended:
Excel VBA Intro Intermediate course
Excel Advanced course
Functions in VBA
Could you please tell me if it is possible to write functions in Visual Basic which then display the formula script in Excel?
I think this would be useful for 2 reasons:
1) The user can see the formula and understand how the values have been calculated by selecting a cell.
2) Cell values will be udpated if the source data changes without having to rerun the function.
Many thanks for your help
Fri 30 Oct 2009, 14:17Trainer Stephen said...
RE: Functions in VBA
Hi William
Thanks for your question
You can add a formula programmatically using the formulaR1C1 method of the range object
The following places a sum function that sums a range specified in terms of its relative position to the formula
Worksheets("Sheet1").Range("d10").FormulaR1C1 = "=SUM(R[-12]C[-2]:R[-6]C[-1])"Regards
Stephen
