Formula vsFormulaR1C1 vs WorksheetFunction - which one to use?

[makiwara]

This is what I have:

FormulaR1C1: R1C1 notation style
Formula: A1 style

FormulaLocal: A1 style + your own language

________
- formulaR1C1 and Formula is written in English and translated automatically, but is this 100% perfect in all languages?

1.
I guess no, but why?
- using FormulaLocal: then it's not universal but 100% working for your language

And where to put Worksheetfunction in this system? It gives Intellisense, any other thing why then formula is needed, if worksheetfunction is better at that? I guess only difference is
that Formula accepts the same formula as I would write on the sheet, while Worksheetfunction gives me a VBA environment to that.

2. Do I see things right or do I miss something, that is the only difference between Formula and Worksheetfunction?

Thank you very much, have a nice day!

 

[aRandomHelper]

My personal preference is to avoid WorksheetFunction, I just feel it's easier to just write the formula if I want to insert a formula.

I use Formula when I know exactly which columns/cells to input.
I use FormulaR1C1 when I want my formula to "reference offset" from its cell -- that is R[#]C or RC[#], etc. Also this is usually my preferred method if the end result/output is supposed to be dynamic. Having dynamic row and column numbers, it's easier to enter RC100 instead of cell "CV" & whichever row it is.

 

[makiwara]

Thank you very much, so Formula and WorksheetFunction is a matter of style according to your opinion, and no other big differences, if I understand you right.

My personal preference is to avoid WorksheetFunction, I just feel it's easier to just write the formula if I want to insert a formula.

I use Formula when I know exactly which columns/cells to input.
I use FormulaR1C1 when I want my formula to "reference offset" from its cell -- that is R[#]C or RC[#], etc. Also this is usually my preferred method if the end result/output is supposed to be dynamic. Having dynamic row and column numbers, it's easier to enter RC100 instead of cell "CV" & whichever row it is.

 

[aRandomHelper]

The main difference would be, I think, WorksheetFunction gives you the result from the formula while using .Formula or .FormulaR1C1 leaves the formula within the cell and Excel would recalculate it whenever you refresh.

The reason why I avoid WorksheetFunction altogether is because there are many functions actually having their VBA equivalents, such as:
=TEXT and Format() in VBA
=REPLACE and Replace() in VBA

If there are no equivalent function in VBA, such as the famous VLOOKUP, there is usually a better way to code it within VBA.
There are of course rare situations where using the WorksheetFunction vs the VBA version gives some slight difference in the results, but off my head I can only think of the TRIM function, which works slightly differently.

 

[RoryA]

Round calculates differently, as I think does Mod.