Replace using arrays within UDF

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Replace using arrays within UDF

Post by YasserKhalil »

Hello everyone
I have the following UDF that I am using to replace old strings with new ones

Code: Select all

Function Repc(ByVal s As String) As String
    Dim o, r, n As Long
    s = " " & s & " "
    o = Array(" Abda ", " Alam ")
    r = Array(" Abdallah ", " Allam ")
    For n = LBound(o) To UBound(o)
        Repc = Replace(s, o(n), r(n)): s = Repc
    Next n
    Repc = Application.Trim(s)
End Function
Say we have this string

Code: Select all

Sub Test()
    MsgBox Repc("Abda Alam Abda Abda")
End Sub
The outcome is OK except for the last instance Abda at the end of string as it doesn't change .. Why???

User avatar
HansV
Administrator
Posts: 78229
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Replace using arrays within UDF

Post by HansV »

This is because you have two occurrences of Abda next to each other with only one space in between. If there had been two spaces, it would have worked.

Workaround 1:

Code: Select all

Function Repc(ByVal s As String) As String
    Dim o, r, n As Long
    s = " " & s & " "
    o = Array(" Abda ", " Alam ")
    r = Array(" Abdallah ", " Allam ")
    For n = LBound(o) To UBound(o)
        Do While InStr(s, o(n))
            s = Replace(s, o(n), r(n))
        Loop
    Next n
    Repc = Application.Trim(s)
End Function
Best wishes,
Hans

User avatar
HansV
Administrator
Posts: 78229
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Replace using arrays within UDF

Post by HansV »

Workaround 2:

Code: Select all

Function Repc(ByVal s As String) As String
    Dim o, r, n As Long
    s = " " & Replace(s, " ", "  ") & " "
    o = Array(" Abda ", " Alam ")
    r = Array(" Abdallah ", " Allam ")
    For n = LBound(o) To UBound(o)
        s = Replace(s, o(n), r(n))
    Next n
    Repc = Application.Trim(s)
End Function
Best wishes,
Hans

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Replace using arrays within UDF

Post by YasserKhalil »

Thanks a lot my tutor. I have tested both and I found the second workaround is more suitable and faster than the first one.
Thank you very much.

But I am still didn't get the reason .. The problem was with the third ccurrence not with the second one
This was the result
Abdallah Allam Abdallah Abda
and if the problem with s[ace in the replacement array, I put space before and after, so what's the problem exactly or how did it work?

User avatar
HansV
Administrator
Posts: 78229
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Replace using arrays within UDF

Post by HansV »

The replace works from left to right. After replacing the second " Abda " with "Abdallah ", it resumes looking at the remaining part of the string after " Abdallah ". That is only "Abda ". There is no " Abda " anymore.
Best wishes,
Hans

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Replace using arrays within UDF

Post by YasserKhalil »

Forgive me. If it works from left to right then
" Abda Alam Abda Abda " >> this is my original string
for the first step it would be " Abdallah Alam Abda Abda "
for the next step it would be " Abdallah Alam Abdallah Abda "
Here is the problem in the last occurrence it is supposed " Abda " is there in the string so I epxect to be replaced to be like that " Abdallah Alam Abdallah Abdallah "

User avatar
HansV
Administrator
Posts: 78229
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Replace using arrays within UDF

Post by HansV »

I'll use | to indicate the current position in the string.

1) Initially:
" Abda Alam Abda Abda "

2) " Abda " is found at the beginning.
"| Abda Alam Abda Abda "

3) It is replaced with "Abdallah ":
" Abdallah |Alam Abda Abda "

4) The second instance of " Abda " is found:
" Abdallah Alam| Abda Abda "

5) It is replaced:
" Abdallah Alam Abdallah |Abda "

6) There is no " Abda " after the | anymore, so there is nothing to replace.
Best wishes,
Hans

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Replace using arrays within UDF

Post by YasserKhalil »

Thanks a lot, my tutor.
Best and Kind Regards