Find and replace function (not Sub) with VBA

[Horesz89]

Dear All,

I am a newbie at VBA and I would like to create a function (not a Sub) to be used at any range and for any given value in the workbook. The Function should be used for replacing characters in the values. So far I have tried 2 codings:

1st:

Function Chatrim(incorref As Double) As Double
Chatrim = replace(incorref, "2022/", "") incorref is the chosen value and the expected result would be like : incorref = 2022/445 expected result with the function is 445
End Function

2nd:


Function Replacechar(incorvalue As Double) As Double
Range(incorvalue).replace What:="2022/", Replacement:=""
'incorvalue is the chosen value and the expected result would be like : incorvalue = 2022/445 expected result with the function is 445
End Function

For both above functions I get the result: #VALUE!

Thank you for your help!

[Aussiebear]

Welcome to the VBAX forum Horesz89.

[June7]

Review https://www.automateexcel.com/vba/find-replace/#:~:text=Using%20Find%20and%20Replace%20in%20Excel%20VBA%201,Replace%20Text %20Within%20a%20VBA%20Text%20String%20


BTW, you posted in forum for discussion about forum. Should post in appropriate topic forum.

[p45cal]

Your functions are expecting to be passed a value of type Double yet you're processing it as a string.
Change the first line to:

Function Chatrim(incorref) As Double
and it will work well if...
you supply a string that after processing (replacement) can be coerced to a value of type Double, eg. 2022/456. The 2022/ is removed leaving 456 which can be coerced to a double, so the result will be a number 456.
However if you pass the likes of 2022/ert, ert cannot be coerced to a number so you'll again get the #VALUE! error. If you want to return ert in such a case you need to change the first line to:

Function Chatrim(incorref)

[Paul_Hossler]

Not sure what you're looking to do, but try this

30692




Option Explicit


Function Chatrim(incorref As Variant) As Variant
Chatrim = Replace(incorref, "2022/", vbNullString)
End Function