Excel VBA. Compare if all letters in a string is in another string

Question

I would like to compare if a string has all the characters of another string.

Example:

String 1 Hejslclo

String 2 Hello

True string 2 has all the characters of string one.

I have turned string 1 into an array and I think there should be a loop to check using Instr

My first attempt at this:

Sub StringintoArray()


Dim Temp As String

Dim MyString As String

Dim String2 As String


MyString = "Heaslsflo"

String2 = "Hello"


Temp = StrConv(MyString, vbUnicode)

Temp = Left(Temp, Len(Temp) - 1)

aLetter = Split(Temp, Chr(0))



'Dim StartRow As Integer

Dim i As Integer



 For i = 0 To L(Temp)

   If InStr(i, String2, aLetter(I)) <> 0 Then

        MsgBox ("Yes")

   Else

       MsgBox ("No")

   End If

Next i

End Sub

You really need to delve into this code yourself. Somebody may answer if they are bored enough, but widely you will find that the helpful folks here won't bother answering if it seems you haven't put any effort into coding it for yourself. — n8.
– n8., Commented Nov 17, 2016 at 19:56
That should actually return False, by the way you explained. String 2 does not have all the characters of String 1. That's actually the other way around. String 1 has all characters of String 2. String 2 is "contained" in String 1...This is one of the issues you might find when coding for yourself and that you need to decide. You want to compare String 1 against String 2 or String 2 against String 1? Depending on what you want, the string that will become an array will change. You are in the right path though, on creating one as an array and loop through the other with InStr. — Victor Moraes
– Victor Moraes, Commented Nov 17, 2016 at 20:03

Tom · Accepted Answer · 2016-11-17 20:58:46Z

Ok - I got bored and bit. Had a look at your code, you were very nearly there. Think this should do what you want

Option Explicit
Public Function CompareStrings(string1 As String, string2 As String) As Boolean
    Dim i As Long, j As Long, k As Long
    Dim arr1 As Variant, arr2 As Variant

    ' Set to default
    CompareStrings = False

    ' Split strings into arrays
    arr1 = Split(StrConv(string1, vbUnicode), Chr(0))
    arr2 = Split(StrConv(string2, vbUnicode), Chr(0))
    ' Initialise counter
    k = 0
    ' Loop over both arrays
    For i = LBound(arr2) To UBound(arr2)
        For j = LBound(arr1) To UBound(arr1)
            If LCase(arr2(i)) = LCase(arr1(j)) Then
                k = k + 1
                Exit For
            End If
        Next j
    Next i
    ' Test if counter is equal to array length and set answer if so
    If k - 1 = UBound(arr2) Then CompareStrings = True
End Function

Public Sub TestComparison()
    Dim string1 As String, string2 As String
    string1 = "Hello"
    string2 = "Hejslclo"
    If CompareStrings(string1:=string1, string2:=string2) Then
        Debug.Print string1; " contains "; string2
    Else
        Debug.Print string1; " does not contain "; string2
    End If
End Sub

Collectives™ on Stack Overflow

Excel VBA. Compare if all letters in a string is in another string

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related