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
Comments
Post a Comment