How to run this function in excel using vba

Create a button, add in the following code, you might need to change your code from Private to Public

`Private Sub CommandButton1_Click()

Call Worksheet_Change

End Sub`

The code you posted is for the Worksheet.Change event. The event occurs when cells on the worksheet are changed by the user or by an external link.
All you need to run this sub is to place the code in the sheet module for the relevant sheet and change B12 cell.

A Worksheet Change: Change Range Values on Cell Change

• Worksheet_ in the signature Private Sub Worksheet_Change(ByVal Target As Range) indicates that this procedure belongs in the sheet module, e.g. Sheet1, of the worksheet where you want it applied (not in the ThisWorkbook module nor in a standard module, e.g. Module1). Such a procedure will run automatically (get triggered) when an event occurs, particularly for this procedure, after a manual change has happened in a range i.e. after

  • you write something into the formula bar and press enter,
  • you (copy) paste values to a range, or
  • you use VBA to write values to a range.

• In this procedure, if you want to write something to a range of the worksheet, to not retrigger the event and possibly end up with an endless loop ('crashing' Excel), you will disable events before you start writing, and enable them after writing as you did in your code. If an error occurs between these two lines, the events will stay disabled and the code won't trigger until they are enabled again.

• To check if events are enabled you could use the line Debug.Print Application.EnableEvents in another procedure or in the Immediate window just use ?Application.EnableEvents and press enter. Similarly, if the answer is False, in the Immediate window, you can use Application.EnableEvents = True and press enter to enable events.

• The line Set rOld = Range("A1:E1").Value is wrong and results in

  Run-time error '424': Object required

• To avoid the error you could use one of the following:

  • Dim rgOld As Range: Set rgOld = Range("A1:E1")
    Range("A15:E15").Value = rgOld.Value
    Target.Value = sNewValue
    

  • Dim OldValues() As Variant: OldValues = Range("A1:E1").Value
    Range("A15:E15").Value = OldValues ' or after the following line
    Target.Value = sNewValue
    

  • Range("A15:E15").Value = Range("A1:E1").Value
    Target.Value = sNewValue
    

• Basically, you want to write the data before rewriting the new value. Optionally, in the second case where the data is written to an array (OldValues), you can write the values afterward.

• Since the use of an additional variable is kind of redundant in the first two cases, the last (the simplest, the most straightforward) case is used in the following code.

Private Sub Worksheet_Change(ByVal Target As Range)
     
    If Target.Address <> "$B$12" Then Exit Sub

    Dim NewString As String: NewString = CStr(Target.Value)
    
    Application.EnableEvents = False

        Application.Undo ' this will also write (trigger the event)
        Me.Range("A15:E15").Value = Me.Range("A1:E1").Value
        Target.Value = NewString ' redo
    
    Application.EnableEvents = True

End Sub
       

• If you want to modify (experiment with) the code, you should introduce some error handling so you don't end up with events disabled.

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo ClearError
     
    If Target.Address <> "$B$12" Then Exit Sub
    
    Dim NewString As String: NewString = CStr(Target.Value)
    
    Application.EnableEvents = False

    Application.Undo ' this will also write (trigger the event)
    Me.Range("A15:E15").Value = Me.Range("A1:E1").Value
    Target.Value = NewString ' redo
    
SafeExit:
    On Error Resume Next
        If Not Application.EnableEvents Then Application.EnableEvents = True
    On Error GoTo 0
    Exit Sub
ClearError:
    Debug.Print "Run-time error'" & Err.Number & "': " & Err.Description
    Resume SafeExit
End Sub

One thing is basic: how did you create this function? Did you open the Excel VBA editor, select a sheet and chose the corresponding event, like I did in the following screenshot:

As you see, the macro is linked to "Sheet1", it is linked to the events of the "Worksheet" itself, and it is triggered by any "Change" of that worksheet.