The Function below copies a formula result from one cell and pastes it as a value into a different cell in the same worksheet. I expected to store this Function in an .xlam file in order to avoid saving my Workbook as a macro-enabled file.
The problem I am having is that the Function works only if invoked from within an event triggered by an ActiveX or Form control, but not if invoked as a Function from within a Worksheet (it executes with no errors but doesn't actually do anything, at all).
Is there something wrong with the coding?
Option Explicit Public Function CopyPasteValueFn(FromCell As Variant, ToCell As Variant, Optional ReturnToActiveCell As Boolean = False) As Boolean
It does not matter where this code is located, as long as it is called from a worksheet function it cannot affect other cells than the one it was called from. Period. But you can write a sub which does what you need and call that in an add-in or in your personal macro workbook.
I realize now that my initial reply was not clear enough. My apologies. I had already understood that an Excel function invoked from a cell in a worksheet can not modify other cells. On the other hand, I already tried the workaround you suggested, and it obviously works. Thank you so much! Cheers.