Convert an array of string into new string in VBA

Question

I tried to create a VBA code that convert a string (in an array) to a new string (which also in an array). However, i'm getting a problem with those original strings that almost identical. E.g. "Power Partner", "Power Partner OEM".

Is there any way I can convert them accurately? Thanks!

Private Function cvrt(orig As Variant) As Variant

    Dim orgVal As Variant, newVal As Variant, i As Integer

    cvrt = orig

    orgVal = Array("Central", "Clarke", "Power Partner", "Power Partner OEM")
    newVal = Array("Central Data", "Clarke Data", "Onsite Power Partner", "Onsite Energy OEM")

    For i = 0 To 3
        cvrt = Replace(cvrt, orgVal(i), newVal(i), vbTextCompare)
    Next i

End Function

What problem are you getting? What is the value of orig that you are passing to the function? — Siddharth Rout
– Siddharth Rout, Commented May 29, 2017 at 6:52
I'm getting a problem with the last two strings in my code as they are almost identical. When I execute the code, the result for "Power Partner OEM" is incorrect, it became "Onsite Onsite Energy OEM" — kulapo
– kulapo, Commented May 29, 2017 at 6:57
What is the value of orig that you are passing to the function? — Siddharth Rout
– Siddharth Rout, Commented May 29, 2017 at 6:57
the value of orig came from a range, which is technically listed in the orgVal — kulapo
– kulapo, Commented May 29, 2017 at 7:01
Aha! I didn't see you already accepted an answer. I was testing the code. Anyways, I still posted it for future visitors :) — Siddharth Rout
– Siddharth Rout, Commented May 29, 2017 at 7:17

A.S.H · Accepted Answer · 2017-05-29 07:08:25Z

1

If you dont want to enter the complex world of regular expressions,

do it in 2 phases using an intermediate replacement
Follow the rule of putting the "largest strings first" in the original array. Hence Power Partner OEM should come before Power Partner.

.

Private Function cvrt(orig As Variant) As Variant
    Dim i As Long, orgVal, newVal, interm
    cvrt = orig
    orgVal = Array("Power Partner OEM", "Power Partner", "Central", "Clarke")
    interm = Array("xxx0", "xxx1", "xxx2", "xxx3")
    newVal = Array("Onsite Energy OEM", "Onsite Power Partner", "Central Data", "Clarke Data")
   For i = 0 To 3
       cvrt = Replace(cvrt, orgVal(i), interm(i), vbTextCompare)
   Next i
   For i = 0 To 3
       cvrt = Replace(cvrt, interm(i), newVal(i), vbTextCompare)
   Next i
End Function

Notice the intermediate array is not necessary, you can use "xxx" & i everywhere instead of interm(i)...

edited May 29, 2017 at 7:08

answered May 29, 2017 at 6:58

A.S.H

29.4k6 gold badges25 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2020-06-20 09:12:55Z

I'm getting a problem with the last two strings in my code as they are almost identical. When I execute the code, the result for "Power Partner OEM" is incorrect, it became "Onsite Onsite Energy OEM" – kulapo

the value of orig came from a range, which is technically listed in the orgVal – kulapo

The most simplest way to do it is not to use Variants and use Range and then use the inbuilt Range.replace with xlWhole to avoid false positive matches.

Private Function cvrt(orig As Range) As Range '<~~~~~
    Dim orgVal As Variant, newVal As Variant, i As Integer
    Dim rng As Range
    
    Set rng = orig
    
    orgVal = Array("Central", "Clarke", "Power Partner", "Power Partner OEM")
    newVal = Array("Central Data", "Clarke Data", "Onsite Power Partner", "Onsite Energy OEM")

    For i = 0 To 3
        rng.Replace What:=orgVal(i), Replacement:=newVal(i), LookAt:=xlWhole
    Next i
    
    Set orig = rng
End Function

Screenshot

Sub Sample()
    Dim r As Range
    
    Set r = cvrt(Range("A1:A4"))
End Sub


Private Function cvrt(orig As Range) As Range
    Dim orgVal As Variant, newVal As Variant, i As Integer
    Dim rng As Range
    
    Set rng = orig
    
    orgVal = Array("Central", "Clarke", "Power Partner", "Power Partner OEM")
    newVal = Array("Central Data", "Clarke Data", "Onsite Power Partner", "Onsite Energy OEM")

    For i = 0 To 3
        rng.Replace What:=orgVal(i), Replacement:=newVal(i), LookAt:=xlWhole
    Next i
    
    Set orig = rng
End Function

I will also try this way because of its simplicity. Thanks @SiddharthRout!

Collectives™ on Stack Overflow

Convert an array of string into new string in VBA

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related