SOLVED
Home

Issue in VBA Function to copy the formula result in one cell and paste as a value into another cell

%3CLINGO-SUB%20id%3D%22lingo-sub-486376%22%20slang%3D%22en-US%22%3EIssue%20in%20VBA%20Function%20to%20copy%20the%20formula%20result%20in%20one%20cell%20and%20paste%20as%20a%20value%20into%20another%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-486376%22%20slang%3D%22en-US%22%3E%3CP%3EThe%20Function%20below%20copies%20a%20formula%20result%20from%20one%20cell%20and%20pastes%20it%20as%20a%20value%20into%20a%20different%20cell%20in%20the%20same%20worksheet.%20I%20expected%20to%20store%20this%20Function%20in%20an%20.xlam%20file%20in%20order%20to%20avoid%20saving%20my%20Workbook%20as%20a%20macro-enabled%20file.%3C%2FP%3E%3CP%3EThe%20problem%20I%20am%20having%20is%20that%20the%20Function%20works%20only%20if%20invoked%20from%20within%20an%20event%20triggered%20by%20an%20ActiveX%20or%20Form%20control%2C%20but%20not%20if%20invoked%20as%20a%20Function%20from%20within%20a%20Worksheet%20(it%20executes%20with%20no%20errors%20but%20doesn't%20actually%20do%20anything%2C%20at%20all).%3C%2FP%3E%3CP%3EIs%20there%20something%20wrong%20with%20the%20coding%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3E%3CFONT%3EOption%20Explicit%3CBR%20%2F%3EPublic%20Function%20CopyPasteValueFn(FromCell%20As%20Variant%2C%20ToCell%20As%20Variant%2C%20Optional%20ReturnToActiveCell%20As%20Boolean%20%3D%20False)%20As%20Boolean%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Dim%20ActiveCellAddress%20As%20Variant%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20ActiveCellAddress%20%3D%20Excel.ActiveCell.Address%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Excel.ActiveSheet.Range(FromCell).Select%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Excel.Selection.Copy%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Excel.ActiveSheet.Range(ToCell).Select%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Excel.Selection.PasteSpecial%20Paste%3A%3DxlPasteValues%2C%20Operation%3A%3DxlNone%2C%20SkipBlanks%3A%3DFalse%2C%20Transpose%3A%3DFalse%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Excel.Application.CutCopyMode%20%3D%20False%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20If%20ReturnToActiveCell%20%3D%20True%20Then%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Excel.ActiveSheet.Range(ActiveCellAddress).Select%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20End%20If%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20CopyPasteValueFn%20%3D%20True%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%3CBR%20%2F%3EEnd%20Function%3CBR%20%2F%3E%3C%2FFONT%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-486376%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-500033%22%20slang%3D%22en-US%22%3ERe%3A%20Issue%20in%20VBA%20Function%20to%20copy%20the%20formula%20result%20in%20one%20cell%20and%20paste%20as%20a%20value%20into%20another%20c%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-500033%22%20slang%3D%22en-US%22%3EFunctions%20called%20from%20a%20formula%20in%20a%20cell%20cannot%20make%20changes%20to%20any%20other%20cells%2C%20they%20return%20their%20result%20to%20the%20cell%20that%20uses%20the%20function%20in%20its%20formula.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-500153%22%20slang%3D%22en-US%22%3ERe%3A%20Issue%20in%20VBA%20Function%20to%20copy%20the%20formula%20result%20in%20one%20cell%20and%20paste%20as%20a%20value%20into%20another%20c%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-500153%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F22322%22%20target%3D%22_blank%22%3E%40Jan%20Karel%20Pieterse%3C%2FA%3EThank%20you.%20It%20looks%20like%20I%20will%20need%20to%20save%20my%20template%20as%20a%20macro-enabled%20file.%20Or%2C%20continue%20to%20manually%20copy%20the%20formula%20cell%20and%20paste%20it%20as%20a%20value...%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-500288%22%20slang%3D%22en-US%22%3ERe%3A%20Issue%20in%20VBA%20Function%20to%20copy%20the%20formula%20result%20in%20one%20cell%20and%20paste%20as%20a%20value%20into%20another%20c%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-500288%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F22322%22%20target%3D%22_blank%22%20rel%3D%22noopener%22%3E%40Jan%20Karel%20Pieterse%3C%2FA%3E%20Thank%20you.%20It%20looks%20like%20I%20will%20need%20to%20save%20my%20template%20as%20a%20macro-enabled%20file.%20Or%2C%20continue%20to%20manually%20copy%20the%20formula%20cell%20and%20paste%20it%20as%20a%20value.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-502513%22%20slang%3D%22en-US%22%3ERe%3A%20Issue%20in%20VBA%20Function%20to%20copy%20the%20formula%20result%20in%20one%20cell%20and%20paste%20as%20a%20value%20into%20another%20c%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-502513%22%20slang%3D%22en-US%22%3EIt%20does%20not%20matter%20where%20this%20code%20is%20located%2C%20as%20long%20as%20it%20is%20called%20from%20a%20worksheet%20function%20it%20cannot%20affect%20other%20cells%20than%20the%20one%20it%20was%20called%20from.%20Period.%20But%20you%20can%20write%20a%20sub%20which%20does%20what%20you%20need%20and%20call%20that%20in%20an%20add-in%20or%20in%20your%20personal%20macro%20workbook.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-503244%22%20slang%3D%22en-US%22%3ERe%3A%20Issue%20in%20VBA%20Function%20to%20copy%20the%20formula%20result%20in%20one%20cell%20and%20paste%20as%20a%20value%20into%20another%20c%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-503244%22%20slang%3D%22en-US%22%3EI%20realize%20now%20that%20my%20initial%20reply%20was%20not%20clear%20enough.%20My%20apologies.%20I%20had%20already%20understood%20that%20an%20Excel%20function%20invoked%20from%20a%20cell%20in%20a%20worksheet%20can%20not%20modify%20other%20cells.%3CBR%20%2F%3EOn%20the%20other%20hand%2C%20I%20already%20tried%20the%20workaround%20you%20suggested%2C%20and%20it%20obviously%20works.%20Thank%20you%20so%20much!%3CBR%20%2F%3ECheers.%3C%2FLINGO-BODY%3E
Carlos Spörk
New Contributor

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

 

4 Replies
Solution
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.
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.