Avatar of PeterBaileyUk
PeterBaileyUk
 asked on

amend function in vba

I have this function but it needs a slight amendment.

It fails on these strings:

strcurin= "a3 110"
strtestfieldin=a3

cur in=A3 S LINE BLACK EDITION (138)
testfield=A3 S LINE BLACK EDITION

each of the five words appears in cur

this one will fail

cur=A3 S LINE BLACK EDITION QUATTRO (170
testfield=A3 S LINE BLACK EDITION QUATTRO (

the first 6 words are in but the seventh word"(" is not a word in cur, it is a substring of (170" but i dont want that comparison.


it should pass as a3 in test field is a word in curin

I remmed out the piece that tests elements on both sides are there as the strtestfieldin could have or be equal too the elements of strcurin.

so cur will never be less than the test string

here i want to test if a3 exists as a word in strtestfieldin but not as an instring word for word.

the test fields are having things extracted from them. the remaining words should appear in cur somewhere, unless an extraction routine left a part word.

Function word4wordCheck(StrCURIn As String, StrTESTFieldin As String) As Boolean

'    Dim ExtractWordsPhrase1() As String
'    Dim ExtractWordsPhrase2() As String
'    Dim WordCountPhrase1, WordCountPhrase2 As Integer
'    Dim intLoop As Integer
'
'    ExtractWordsPhrase1 = Split(Trim(StrCURIn), " ")
'    ExtractWordsPhrase2 = Split(Trim(StrTESTFieldin), " ")
'
''    'If element count of arrays differ return false
''    If UBound(ExtractWordsPhrase1) <> UBound(ExtractWordsPhrase2) Then
''        word4wordCheck = False
''        Exit Function
''    End If
'
'    'Check each element of Array
'    For intLoop = LBound(ExtractWordsPhrase1) To UBound(ExtractWordsPhrase1)
'        If ExtractWordsPhrase1(intLoop) <> ExtractWordsPhrase2(intLoop) Then
'            word4wordCheck = False
'            Exit Function
'        End If
'    Next
'
'    word4wordCheck = True

End Function

Open in new window

Microsoft Access

Avatar of undefined
Last Comment
Gustav Brock

8/22/2022 - Mon
Gustav Brock

Couldn't you just apply a replace for left-right paranthesis with a Space on the string before the comparison?

strToCompare = Replace(Replace("A3 S LINE BLACK EDITION QUATTRO (170", "(", " "), ")", " ")

/gustav
PeterBaileyUk

ASKER
here is my concept:
CUR                    BHP Out      Litres Out      Valves Out      C02 Out          Door Out
A3 1.8 SE           A3 1.8 SE        A3 SE              A3 1.8 SE            A3 1.8 SE         A3 1.8 SE

I have some regualr expression rules on the five columns from bhp to door, if you look at litres out the 1.8 has beein removed. the others remain the same because there was nothing to extract so the original string is cur.

if i now say which of the outputs is not equal to the [cur] field i can deduce that the litres removed something (hopefully litres), to validate the litres string I need to see if each word (a3 then se) are in the original string. if they are its a valid string.

It could be that one of the other tests bleeds over
ie like the bracket it took the 170 but left the bracket

so i can now determine that the string is not valid as it had a complete word that was not in the original string.

The overall goal is to remove technical elements from the string which can only leave the actual descriptive element of the vehicle.  so the original string A3 1.8 SE      will become A3 SE.
Gustav Brock

Oops, I can't get my head around this a quiet Saturday morning ...
Perhaps it would be easier to turn it upside down and extract valid words like A3 and SE?

/gustav
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
PeterBaileyUk

ASKER
too many of them whereas we know the footprints of things like litres bhp doors.

as i said i only need to get the words checked off.
PeterBaileyUk

ASKER
I think ive done it just by changing over the for loop



Function word4wordCheck(StrCURIn As String, StrTESTFieldin As String) As Boolean

    Dim ExtractWordsPhrase1() As String
    Dim ExtractWordsPhrase2() As String
    Dim WordCountPhrase1, WordCountPhrase2 As Integer
    Dim intLoop As Integer

    ExtractWordsPhrase1 = Split(Trim(StrCURIn), " ")
    ExtractWordsPhrase2 = Split(Trim(StrTESTFieldin), " ")



    'Check each element of Array
    For intLoop = LBound(ExtractWordsPhrase2) To UBound(ExtractWordsPhrase2)
        If ExtractWordsPhrase1(intLoop) <> ExtractWordsPhrase2(intLoop) Then
            word4wordCheck = False
            Exit Function
        End If
    Next

    word4wordCheck = True

End Function

Open in new window

Gustav Brock

Great!

/gustav
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
PeterBaileyUk

ASKER
ok so points, maybe you can confirm all is good in my code so i can award
ASKER CERTIFIED SOLUTION
Gustav Brock

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
PeterBaileyUk

ASKER
well you earnt your points thank you. enjoy your weekend.
Gustav Brock

Thanks! Hope you enjoy it too.

/gustav
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy