Excel function set (target) cell value

NOTE: the UDP_ prefix for the initial function doesn't appear to be required, but mind the syntax on the Evaluate.

This is a function to clean up values using RegExp. It has an optional UpdateTarget which will change the value o the target cell if set to true.

Use excel EVALUATE to run a private SUB as if the cell had called the sub as a function to set the value.

Based on:

https://stackoverflow.com/questions/3844792/vba-how-to-change-the-value-of-another-cell-via-a-function 

 Attribute VB_Name = "Excel_set_cell_formula"

Option Explicit Const VBQT = """" Function UDF_reReplace(ByRef Target As Range, Optional UpdateTarget As Boolean = False) As String Dim re As New RegExp Dim strValue As String Dim strFrmla As String strValue = Target(1, 1).Value With re re.Global = True re.IgnoreCase = True re.MultiLine = True re.Pattern = "(?:[_\-]wm[_\-])" If re.Test(strValue) Then ''prefix WM strValue = re.Replace(strValue, "") re.Pattern = "[^0-9A-Z\-\+\(\)]{1,}" If re.Test(strValue) Then strValue = re.Replace(strValue, "-") End If strValue = strValue & "-WM" UDF_reReplace = strValue ''updated value to reflect If RenameTarget Then ''so we can see the formula strFrmla = "SetValue(" & Target(1, 1).Address & "," & VBQT & strValue & VBQT & ")" Evaluate strFrmla End If End If End With End Function Private Sub SetValue(ResultCell As Range, strValue As String) ''Resturns set value for cell ResultCell = strValue MsgBox "stop" End Sub