User defined Paste Special Function

Question

Function PS(X As Range) As Double
    Range(X).Select
    Selection.Copy
    Range(X).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
End Function

I am trying to create a function PS where it take value from cell and pastes special in the cell where the PS function is used. The above code not giving the desired value.

The problem is that you are trying to edit / change the content of cells with a worksheet function. That's not something you should be doing. [While some gifted users have found a way to circumvent this restriction](http://stackoverflow.com/questions/12501759/vba-update-other-cells-via-user-defined-function) I strongly advise against it and rather ask you to share with us what you are really trying to achieve so that we can help you find a better solution. — Ralph, Feb 11 '17 at 11:16
I am trying to create a user defined function where it take value from cell and pastes special the value in the cell where the user defined function is used. — vaasusk, Feb 11 '17 at 11:44

CallumDA · Answer 1 · 2017-02-11T12:36:24.137

The first thing you need is a function which gets the value of the range you selected. Place this in a new module:

Function Hardcode(r As Range)
    Hardcode = r.Value
End Function

For example: placing =Hardcode(A1) into cell B1 returns the value of cell A1. However, from my understanding, you also want to hardcode the new value of B1. To do this you need a separate Sub

Place this code in the worksheet module corresponding to the worksheet you wish to use the above function on:

Private Sub Worksheet_Change(ByVal Target As Range)
    If InStr(LCase(Target.Formula), "hardcode") Then
        Target.Value = Target.Value
    End If
End Sub

User defined Paste Special Function

1 Answers1