'------------------------------------------------------------------------' This enum is used by both InstrWordEx and ReplaceWordEx'' It uses a binary value to determine what separator characters are allowed' bit 0 = allow spaces' bit 1 = allow symbols' bit 2 = allow control chars' bit 3 = allow digits' If all are excluded (ie a value of 0) then this means char must match a ' special separator provided by callerEnum sepType specialSep = 0 'binary 0000 = words are ONLY separated by ' a specified separator spacesOnly = 1 'binary 0001 = words must be separated By ' spaces spacesAndSymbols = 3 'binary 0011 = words must be separated By ' spaces Or symbols spacesSymbolsAndCtrl = 7 'binary 0111 = words are separated by ' spaces, symbols or ctrl chars spacesSymbolsCtrlAndDigits = 15 'binary 1111 = words are separated by ' anything but lettersEnd Enum' Return the next occurrence of a whole word'' Based on InstrWord function from VB2TheMax - www.vb2themax.com'' Changes from VB2TheMax function were made by Peter Stubbs to allow ' specification of' what constitutes a separator between words''---------------------------------------------------------------------------'Contact Peter at [email protected]' http://users.hunterlink.net.au/~dgps'---------------------------------------------------------------------------'' Choices for separator are as described above for the sepType enum'' Examples:' pos = InstrWordEx(1,"This is a string","is",vbTextCompare)' returns 6 in pos since there is a word "is" preceded and followed by a Space'' pos = InstrWordEx(1,"This-is-a-string","is",vbTextCompare)' returns 6 in pos since there is a word "is" preceded and followed by a symbol'' pos = InstrWordEx(1,"This-is-a-string","is",vbTextCompare,spacesOnly)' returns 0 in pos since there are no occurences of "is" preceded and ' followed by a space'' pos = InstrWordEx(1,"This is a list of 310 things","31",vbTextCompare)' returns 19 in pos since the digits 31 are preceded by a space and followed ' by a digit' which is, by default, considered a valid separator'' pos = InstrWordEx(1,"This is a list of 310 ' things","31",vbTextCompare,spacesAndSymbols)' returns 0 in pos since this specifies spaces and symbols (but not digits) ' are valid separators'' pos = InstrWordEx(1,"This/is/a/string","is",vbTextCompare,specialSep,"/")' returns 6 in pos since the word "is" is both preceded and followed by /'Function InstrWordEx(Start As Long, Source As String, Find As String, _ compareMethod As VbCompareMethod, Optional separatorType As sepType = _ spacesSymbolsCtrlAndDigits, Optional Separator As String = vbNullString) As _ Long Dim index As Long Dim charcode As Integer Dim separatorInvalid As Boolean ' assume the search fails InstrWordEx = 0 index = Start - 1 Do ' search the next occurrence, exit if not found index = InStr(index + 1, Source, Find, compareMethod) If index = 0 Then Exit Function If index > 1 Then charcode = Asc(UCase$(Mid$(Source, index - 1, 1))) Else charcode = 32 End If ' check that it is preceded by a valid separator If IsValidChar(charcode, separatorType, Separator) Then ' check that it is followed by a valid separator charcode = Asc(UCase$(Mid$(Source, index + Len(Find), 1)) & " ") If IsValidChar(charcode, separatorType, Separator) Then InstrWordEx = index Exit Function End If End If LoopEnd Function'This function determines if the character value in char is an acceptable ' separator of the'type specified by separatorType'The function is used by both InstrWordEx and ReplaceWordExPrivate Function IsValidChar(char As Integer, separatorType As sepType, _ Separator As String) Dim charType As Integer 'Ctrl are chars in charType 0-31 'Spaces and symbols are chars in charType 32-47, 58-64 and 91-255 'Digits are chars in charType 48-57 If separatorType = specialSep Then IsValidChar = (char = Asc(UCase$(Separator))) Exit Function End If 'Determine charType that char falls in Select Case char Case Is < 32 '0-32 = ctrl charType = 4 '0100 binary Case 32 'space charType = 1 '0001 binary Case Is < 48, Is > 90 '32-48 or 91-255 = symbols (first range) charType = 2 '0010 binary Case Is < 58 '48-57 = digits charType = 8 '1000 binary Case Is < 65 '58-64 = symbols (second range) charType = 2 '0010 binary Case Else 'it's a letter charType = 0 '0000 binary End Select IsValidChar = Not ((charType And separatorType) = 0)End Function


Different Types of Data Models Explained with Examples
In the modern world, data is everything and everywhere. With so much access to technology, data has become a valuable resource for any business. Albeit a complex one. Data is