amend function in vba

PeterBaileyUk
PeterBaileyUk used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
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

Author

Commented:
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.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
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
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
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.

Author

Commented:
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

Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Great!

/gustav

Author

Commented:
ok so points, maybe you can confirm all is good in my code so i can award
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
Well, you found the solution yourself.
The code looks fine to me, but without test data I can't confirm.

/gustav

Author

Commented:
well you earnt your points thank you. enjoy your weekend.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Thanks! Hope you enjoy it too.

/gustav

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial