Forum Discussion
Solved
Issue in VBA Function to copy the formula result in one cell and paste as a value into another cell
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
Dim ActiveCellAddress As Variant
ActiveCellAddress = Excel.ActiveCell.Address
Excel.ActiveSheet.Range(FromCell).Select
Excel.Selection.Copy
Excel.ActiveSheet.Range(ToCell).Select
Excel.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Excel.Application.CutCopyMode = False
If ReturnToActiveCell = True Then
Excel.ActiveSheet.Range(ActiveCellAddress).Select
End If
CopyPasteValueFn = True
End Function
- Functions called from a formula in a cell cannot make changes to any other cells, they return their result to the cell that uses the function in its formula.
4 Replies
- Functions called from a formula in a cell cannot make changes to any other cells, they return their result to the cell that uses the function in its formula.
@Jan Karel Pieterse Thank you. It looks like I will need to save my template as a macro-enabled file. Or, continue to manually copy the formula cell and paste it as a value.
- 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.
