2

I'm working on a cargo calculator that uses different algorithms depending on whether the cargo can be classified as pipes, plates or beams. I'm trying to get it to automatically detect the cargo segment (if it can; the operator will be able to manually select a segment if there's not enough data) based on the item's description and dimensions.

My initial thought was to set lists of keywords as arrays; if a spot of pseudocode can be indulged, I'm thinking something along these lines:

Pipes = {pipe, tube, conduit, duct}
Plates = {plate, sheet, panel}
Beams = {beam, rail, girder}
IF Description CONTAINS Pipes THEN Calc = "Pipes & Tubes"

I know it could be done with lots and lots of IF clauses, but using arrays or similar would make it easier to maintain the list as synonyms crop up - and of course makes the code tidier.

Any thoughts on a nice efficient way of doing this?

EDIT: To clarify, I'm not trying to see if a whole string is found in an array, I'm trying to check if any of the words in an array (or collection of words, however arranged) is found in a descriptive string. For instance, using the arrays above, "Steel sheets" should come back as being in the category "Plates" because the description contains "sheet".

EDIT: @R3uk found a solution that works nicely. Here's the code I ended up using:

In my declarations module: Public aPipe As String ' Pipe synonym array Public aPlate As String ' Plate synonym array Public aBeam As String ' Beam synonym array

In my admin module: aPipe = "pipe/tube/conduit/duct" aPlate = "plate/sheet/panel" aBeam = "beam/rail/girder/truss"

In the main importer module, in the import sub: ImpCalcDetect ' Imported calculator segment detection (experimental)

And the bit itself, essentially unchanged from R3uk's answer but with a minor tweak to make it case-insensitive:

Sub ImpCalcDetect()
' Experimental calculator segment detection
If Contains_Keyword(LCase(wsCalc.Cells(iImportCounter, 2).Value), aPipe) Then wsCalc.Cells(iImportCounter, 3).Value = "Pipes"
If Contains_Keyword(LCase(wsCalc.Cells(iImportCounter, 2).Value), aPlate) Then wsCalc.Cells(iImportCounter, 3).Value = "Plates"
If Contains_Keyword(LCase(wsCalc.Cells(iImportCounter, 2).Value), aBeam) Then wsCalc.Cells(iImportCounter, 3).Value = "Beams"
End Sub

Function Contains_Keyword(Descr As String, KeyWordS As String) As Boolean
Dim A() As String, IsIn As Boolean, i As Integer
A = Split(KeyWordS, "/")
IsIn = False
For i = LBound(A) To UBound(A)
    If InStr(1, Descr, A(i)) Then
        IsIn = True
        Exit For
    Else
    End If
Next i
Contains_Keyword = IsIn
End Function

Many thanks!

Andrew Perry
  • 743
  • 2
  • 11
  • 32
  • 3
    http://stackoverflow.com/questions/11109832/how-to-find-if-an-array-contains-a-string – Raystafarian Dec 15 '15 at 15:09
  • Thanks, but that's not quite what I was looking for - I'm trying to make it check if any of the words in each collection are found in the description. For instance, if the description is "Carbon steel conduits", it should match against "conduit" in the collection "Pipes" – Andrew Perry Dec 16 '15 at 12:21

1 Answers1

1

You could indeed use arrays, here is a version for strings where you just need to separate keywords with a slash / :

Sub Test_AndrewPerry()
Dim Pipes As String, Plates As String, Beams As String
Pipes = "pipe/tube/conduit/duct"
Plates = "plate/sheet/panel"
Beams = "beam/rail/girder"

If Contains_Keyword(Description, Pipes) Then
    Calc = "Pipes & Tubes"
Else
    'Nothing to do?
End If

End Sub

And the function to "decompress" the strings and test each keyword until it find a match :

Function Contains_Keyword(Descr As String, KeyWordS As String) As Boolean
Dim A() As String, IsIn As Boolean, i As Integer
A = Split(KeyWordS, "/")
IsIn = False
For i = LBound(A) To UBound(A)
    If InStr(1, Descr, A(i)) Then
        IsIn = True
        Exit For
    Else
    End If
Next i
Contains_Keyword = IsIn
End Function
R3uK
  • 14,417
  • 7
  • 43
  • 77