VM>DT Data Type Code Pg_VM-DT-Code.html
DTc>Bool>
wCBool
wCBool (
iVar As Variant,
Optional oaExc As Boolean ) As Boolean
Returns CBool(iVar), unless this would cause a RTE, in which case it Fails and
oaExc is Seton
Also Fails (and oaExc is Seton) if
1) IsEmpty(iVar) = True (for which CBool() Returns False)
2) IsError(iVar) = True ( " True )
Note: CBool() causes a RTE for all Non-Numeric Inputs EXCEPT cases 1 & 2
egs Returns False for: 0, 0.0 , "0" , False, "false",
or any Numeric Expression that Evaluates to 0
Returns True for: 1, 0.1 , "1" , True, "true" ,
or any Numeric Expression that Evaluates to 1
Also Fails (and oaExc is Seton) if
1) IsEmpty(iVar) = True (for which CBool() Returns False)
2) IsError(iVar) = True ( " True )
Note: CBool() causes a RTE for all Non-Numeric Inputs EXCEPT cases 1 & 2
egs Returns False for: 0, 0.0 , "0" , False, "false",
or any Numeric Expression that Evaluates to 0
Returns True for: 1, 0.1 , "1" , True, "true" ,
or any Numeric Expression that Evaluates to 1
DTc>Bool>
wCBoolOK
wCBoolOK (
iVar As Variant,
Optional oaBool As Boolean ) As Boolean
The same as wCBool(), but in a format suitable for Conditional Testing
If Successful oaBool = CBool(iVar). Fails and Resets oaBool if this would cause a RTE
If Successful oaBool = CBool(iVar). Fails and Resets oaBool if this would cause a RTE
DTc>Long>
wCLng
wCLng (
iVar As Variant,
Optional oaExc As Boolean ) As Long
Returns CLng(iVar), unless this would cause a RTE, in which case it Returns 0
and oaExc is Seton
Fails if iVar is Null or Nothing, but Succeeds if iVar is Empty or the Null String
All Numeric Expressions Succeed
egs &H1 and Exponential Expressions, Dates
Fractions are Rounded; 0.5 Up
Text Expressions only Succeed if CLng(TextExp) Succeeds
egs "" -> 0
" " -> Fails
1.49 -> 1
1.50 -> 2
"$1.50" -> 2
"01-Jan-2001" -> Fails
CDate("01-Jan-2001")
-> 36892
"1-" -> -1
"1-1" -> Fails
Fails if iVar is Null or Nothing, but Succeeds if iVar is Empty or the Null String
All Numeric Expressions Succeed
egs &H1 and Exponential Expressions, Dates
Fractions are Rounded; 0.5 Up
Text Expressions only Succeed if CLng(TextExp) Succeeds
egs "" -> 0
" " -> Fails
1.49 -> 1
1.50 -> 2
"$1.50" -> 2
"01-Jan-2001" -> Fails
CDate("01-Jan-2001")
-> 36892
"1-" -> -1
"1-1" -> Fails
DTc>Long>
wCLngOK
wCLngOK (
iVar As Variant,
Optional oaLong As Long ) As Boolean
The same as as wCLng(), but in a format suitable for Conditional Testing
DTc>Long>
IsValLong
IsValLong (
iVar As Variant, _
Optional iLowerLim As Long = wk_dtILong, _
Optional iUpperLim As Long = wk_dtALong, _
Optional ouLong As Long ) As Boolean The Same as as wCLngOK() except that
1) it Fails if wCLng()'s Return is Not >=iLowerLim and/or <=iUpperLim
2) On Failure, ouLong is Unchanged, whereas wCLngOK()'s oaLong is Zeroed
Optional iLowerLim As Long = wk_dtILong, _
Optional iUpperLim As Long = wk_dtALong, _
Optional ouLong As Long ) As Boolean The Same as as wCLngOK() except that
1) it Fails if wCLng()'s Return is Not >=iLowerLim and/or <=iUpperLim
2) On Failure, ouLong is Unchanged, whereas wCLngOK()'s oaLong is Zeroed
DTc>Long>
GreLong
GreLong (
iLong1 As Long,
iLong2 As Long ) As Long
Returns the Greater of two Long Values
DTc>Long>
LessLong
LessLong (
iLong1 As Long,
iLong2 As Long ) As Long
Returns the Lesser of two Long Values
DTc>Long>
StripLong
StripLong (
ioStr As String,
Optional ocLong As Long, _
Optional iNoClear As Boolean, Optional oaLastDigPos As Long, _
Optional iTrim As Boolean ) As Boolean If Successful, sets ocLong to the Long Value terminated at the First Non-Digit in ioStr (excluding a Leading Minus Sign)
Fails if the First Character of ioStr is a Non-Digit, or a Minus Sign followed by a Non-Digit
If StripLong Fails and iNoClear is Set, ocLong is Unmodified, Not Cleared
oaLastDigPos is the Last Digit's Position in ioStr
If iTrim is Set, ocLong's Characters are Trimmed from ioStr
eg ioStr="23rd" would return ioStr as "rd"
egs Success "0" -> 0
"-0." -> 0
"23rd" -> 23
"2.99" -> 2
Failure "", "-", "The 2nd"
Optional iNoClear As Boolean, Optional oaLastDigPos As Long, _
Optional iTrim As Boolean ) As Boolean If Successful, sets ocLong to the Long Value terminated at the First Non-Digit in ioStr (excluding a Leading Minus Sign)
Fails if the First Character of ioStr is a Non-Digit, or a Minus Sign followed by a Non-Digit
If StripLong Fails and iNoClear is Set, ocLong is Unmodified, Not Cleared
oaLastDigPos is the Last Digit's Position in ioStr
If iTrim is Set, ocLong's Characters are Trimmed from ioStr
eg ioStr="23rd" would return ioStr as "rd"
egs Success "0" -> 0
"-0." -> 0
"23rd" -> 23
"2.99" -> 2
Failure "", "-", "The 2nd"
DTc>Long>
BSL
BSL (
iLong As Long,
Optional ByVal iNOPlaces As Long = 1, _
Optional iWrap As Boolean, Optional oaUnlBit As Boolean ) As Long Returns iLong, Bit Shifted iNOPlaces to the Left
If iWrap is False the MSB(32) is Set to Zero after each Bit Shift
" True " the Unloaded Bit "
oaUnlBit is the Bit Unloaded on the Last Shift
If iNOPlaces is <1 the Return is iLong and oaUnlBit is False
Optional iWrap As Boolean, Optional oaUnlBit As Boolean ) As Long Returns iLong, Bit Shifted iNOPlaces to the Left
If iWrap is False the MSB(32) is Set to Zero after each Bit Shift
" True " the Unloaded Bit "
oaUnlBit is the Bit Unloaded on the Last Shift
If iNOPlaces is <1 the Return is iLong and oaUnlBit is False
DTc>Long>
BSR
BSR (
iLong As Long,
Optional ByVal iNOPlaces As Long = 1, _
Optional iWrap As Boolean, Optional oaUnlBit As Boolean ) As Long Returns iLong, Bit Shifted iNOPlaces to the Right
If iWrap is False the LSB(1) is Set to Zero after each Bit Shift
" True " the Unloaded Bit "
oaUnlBit is the Bit Unloaded on the Last Shift
If iNOPlaces is <1 the Return is iLong and oaUnlBit is False
Optional iWrap As Boolean, Optional oaUnlBit As Boolean ) As Long Returns iLong, Bit Shifted iNOPlaces to the Right
If iWrap is False the LSB(1) is Set to Zero after each Bit Shift
" True " the Unloaded Bit "
oaUnlBit is the Bit Unloaded on the Last Shift
If iNOPlaces is <1 the Return is iLong and oaUnlBit is False
DTc>Long>
CouBits
CouBits (
iTestLong As Long, _
Optional oaAllMats As Long, Optional oaLastMatBitN As Long, _
Optional iMatch0s As Boolean, Optional iRight As Boolean, _
Optional iStartAtBit As Long = 1, Optional iNOBits As Long = 32, _
Optional iStopAfterNMats As Long = 32 ) As Long Counts the NO Bits in iTestLong which Match the Input Criteria
Default is to Count All the 1's in iTestLong, working Left from the LSB (Bit 1)
All Matching Bits in oaAllMats are Seton
oaLastMatBitN is set to the Last Matching Bit's Number (MSB=32, LSB=1)
Setting iMatch0s causes 0's, not 1's, to be Matched
Setting iRight causes CouBits to work Right from iStartAtBit [1]
iNOBits may be used to restrict the NO Bits Tested [32]
iStopAfterNMats may be used to limit the Maximum NO Matchs [32]
Setting iStartAtBit, iNOBits or iStopAfterNMats outside the range of 1 to 32
doesn't seem to cause RTE's, but may cause unpredictable ouputs
Optional oaAllMats As Long, Optional oaLastMatBitN As Long, _
Optional iMatch0s As Boolean, Optional iRight As Boolean, _
Optional iStartAtBit As Long = 1, Optional iNOBits As Long = 32, _
Optional iStopAfterNMats As Long = 32 ) As Long Counts the NO Bits in iTestLong which Match the Input Criteria
Default is to Count All the 1's in iTestLong, working Left from the LSB (Bit 1)
All Matching Bits in oaAllMats are Seton
oaLastMatBitN is set to the Last Matching Bit's Number (MSB=32, LSB=1)
Setting iMatch0s causes 0's, not 1's, to be Matched
Setting iRight causes CouBits to work Right from iStartAtBit [1]
iNOBits may be used to restrict the NO Bits Tested [32]
iStopAfterNMats may be used to limit the Maximum NO Matchs [32]
Setting iStartAtBit, iNOBits or iStopAfterNMats outside the range of 1 to 32
doesn't seem to cause RTE's, but may cause unpredictable ouputs
DTc>Long>
FitsMask
FitsMask (
iTest As Long,
iMask As Long ) As Boolean
Returns True if All the 1 Bits in iMask are also 1's in iTest
egs &H11 & &H10 Succeeds
&H10 & &H11 Fails
* & 0 Succeeds
egs &H11 & &H10 Succeeds
&H10 & &H11 Fails
* & 0 Succeeds
DTc>Single>
wCSng
wCSng (
iVar As Variant,
Optional oaExc As Boolean ) As Single
Returns CSng(iVar), unless this would cause a RTE, in which case it Returns 0.0
and oaExc is Seton
Fails if iVar is Null or Nothing, but Succeeds if iVar is Empty or the Null String
All Numeric Expressions eg &H1, Exponential Expressions and Dates Succeed
Text Expressions only Succeed if CLng(TextExp) Succeeds
egs "" -> 0.0
" " -> Fails
1.49 -> 1.49
"$1.50" -> 1.5
"01-Jan-2001" -> Fails
CDate("01-Jan-2001") -> 36892
"1-" -> -1
"1-1" -> Fails
Fails if iVar is Null or Nothing, but Succeeds if iVar is Empty or the Null String
All Numeric Expressions eg &H1, Exponential Expressions and Dates Succeed
Text Expressions only Succeed if CLng(TextExp) Succeeds
egs "" -> 0.0
" " -> Fails
1.49 -> 1.49
"$1.50" -> 1.5
"01-Jan-2001" -> Fails
CDate("01-Jan-2001") -> 36892
"1-" -> -1
"1-1" -> Fails
DTc>Single>
wCSngOK
wCSngOK (
iVar As Variant,
Optional oaSin As Single ) As Boolean
The same as wCSng(), but in a format suitable for Conditional Testing
DTc>Single>
IsValSin
IsValSin (
iVar As Variant, _
Optional iLowerLim As Single = wk_dtISin, _
Optional iUpperLim As Single = wk_dtASin, _
Optional ouSin As Single ) As Boolean The Same as Function as wCSngOK except that
1) IsValSin Fails if wCSng's Return is Not >=iLowerLim and/or <=iUpperLim
2) On Failure ouSin is Unchanged, whereas wCLngOK's oaSin is Zeroed
Optional iLowerLim As Single = wk_dtISin, _
Optional iUpperLim As Single = wk_dtASin, _
Optional ouSin As Single ) As Boolean The Same as Function as wCSngOK except that
1) IsValSin Fails if wCSng's Return is Not >=iLowerLim and/or <=iUpperLim
2) On Failure ouSin is Unchanged, whereas wCLngOK's oaSin is Zeroed
DTc>Single>
GetNorExp
GetNorExp (
ByVal ivVar As Variant, _
ouNorExp As Long, Optional ouFormStr As String ) As Boolean If Successful, sets ouNorExp to the Exponent of the Normalized Scientific
Representation of ivVar, and ouFormStr to a Standardized Formatting
String with which to Display ivVar
Fails if ivVar is Not a Valid Numeric Expression
egs 2 for 951 (or 9.51E2) and -3 for 0.00951 ( or 9.51E-3)
ouFormStr is WasteNot's Standard Formatting String for the different
ouNorExp Ranges shown below;
Format(ivVar,ouFormStr) then returns;
- the Full Value of any Long ivVar
- a Value rounded to Three Significant Digits for other ivVar Types
ouNorExp Range Format(ivVar,ouFormStr) eg
> 9 Scientific Format to 2 Decimal Places 1.23e10
>= 0 and < 9 Includes 1,000 Separators 1,234,567,890
>=-3 and < 0 Rounded to 3 Significant Digits 0.00123
<-3 Scientific Format to 2 Decimal Places 1.23e-4
ouNorExp As Long, Optional ouFormStr As String ) As Boolean If Successful, sets ouNorExp to the Exponent of the Normalized Scientific
Representation of ivVar, and ouFormStr to a Standardized Formatting
String with which to Display ivVar
Fails if ivVar is Not a Valid Numeric Expression
egs 2 for 951 (or 9.51E2) and -3 for 0.00951 ( or 9.51E-3)
ouFormStr is WasteNot's Standard Formatting String for the different
ouNorExp Ranges shown below;
Format(ivVar,ouFormStr) then returns;
- the Full Value of any Long ivVar
- a Value rounded to Three Significant Digits for other ivVar Types
ouNorExp Range Format(ivVar,ouFormStr) eg
> 9 Scientific Format to 2 Decimal Places 1.23e10
>= 0 and < 9 Includes 1,000 Separators 1,234,567,890
>=-3 and < 0 Rounded to 3 Significant Digits 0.00123
<-3 Scientific Format to 2 Decimal Places 1.23e-4
DTc>String>
wCStr
wCStr (
iVar As Variant,
Optional oaExc As Boolean ) As String
Returns CStr(iVar), unless this would cause a RTE, in which case it Returns the
Null String and oaExc is Seton
Fails if iVar is Null or Nothing, but Succeeds if iVar is Empty or the Null String
Fails if iVar is Null or Nothing, but Succeeds if iVar is Empty or the Null String
DTc>String>
wCStrOK
wCStrOK (
iVar As Variant,
Optional oaStr As String ) As Boolean
The same as wCStr(), but in a format suitable for Conditional Testing
DTc>String>Chars>
wAscW
wAscW (
iChar As String ) As Long
Returns iChar's UTF16 (0-65,535) Character Code, as a Long Value
Similar to VB's AscW, except that VB's AscW
1) Causes a RTE if the Input is a Null String
ie AscW("") causes a RTE
whereas wAscW("") returns 0
Note however that AscW(ChrW(0)) returns 0
2) Returns a Signed Integer
eg AscW(ChrW(65535)) returns -1
whereas wAscW(ChrW(65535)) returns 65535
Uses Left(iChar,1) if Len(iChar)>1
Similar to VB's AscW, except that VB's AscW
1) Causes a RTE if the Input is a Null String
ie AscW("") causes a RTE
whereas wAscW("") returns 0
Note however that AscW(ChrW(0)) returns 0
2) Returns a Signed Integer
eg AscW(ChrW(65535)) returns -1
whereas wAscW(ChrW(65535)) returns 65535
Uses Left(iChar,1) if Len(iChar)>1
DTc>String>Chars>
StrHasAnyChar
StrHasAnyChar (
iChars As String,
iInStr As String, _
Optional oaPos As Long ) As Boolean Succeeds if iInStr contains Any of the Characters in iChars
oaPos is the Position of the first match in iChars, or Len(iChars)+1 on Failure
Succeeds with oaPos=0 if iChar is Null
Optional oaPos As Long ) As Boolean Succeeds if iInStr contains Any of the Characters in iChars
oaPos is the Position of the first match in iChars, or Len(iChars)+1 on Failure
Succeeds with oaPos=0 if iChar is Null
DTc>String>Chars>
GetCharTypes
GetCharTypes (
iStr As String, _
Optional oaU16CC As Long, _
Optional oaChar As String, _
Optional iPos As Long = 1 ) As wCharTE Returns the Character Types (as defined by wCharTE) of oaChar, the Character in Position iPos of iStr
oaU16CC is the UTF-16 Character for oaChar (0-65,535)
Returns we_ctNone=0 (and sets oaChar to Null and oaU16CC to 0) if
iStr is Null
iPos=<1
iPos>Len(iStr)
Optional oaU16CC As Long, _
Optional oaChar As String, _
Optional iPos As Long = 1 ) As wCharTE Returns the Character Types (as defined by wCharTE) of oaChar, the Character in Position iPos of iStr
oaU16CC is the UTF-16 Character for oaChar (0-65,535)
Returns we_ctNone=0 (and sets oaChar to Null and oaU16CC to 0) if
iStr is Null
iPos=<1
iPos>Len(iStr)
DTc>String>Chars>
IsWSpChar
IsWSpChar (
iStr As String,
Optional iPos As Long = 1, _
Optional oaU16CC As Long ) As Boolean Succeeds if the Character at iPos in iStr is a WhiteSpace Character
7 WhiteSpace Chars are Defined;
U16CC Symbol Description
009 (&H09) HT Horizontal Tab
010 (&H0A) LF Line Feed
011 (&H0B) VT Vertical Tab
012 (&H0C) FF Form Feed
013 (&H0D) CR Carriage Return
133 (&H85) … Horizontal ellipsis
160 (&HA0) Non-breaking space
Optional oaU16CC As Long ) As Boolean Succeeds if the Character at iPos in iStr is a WhiteSpace Character
7 WhiteSpace Chars are Defined;
U16CC Symbol Description
009 (&H09) HT Horizontal Tab
010 (&H0A) LF Line Feed
011 (&H0B) VT Vertical Tab
012 (&H0C) FF Form Feed
013 (&H0D) CR Carriage Return
133 (&H85) … Horizontal ellipsis
160 (&HA0) Non-breaking space
DTc>String>Chars>
GetNextCC
GetNextCC (
iChar As String,
ouChar As String,
Optional ouOCC As Long, _
Optional iPrev As Boolean, Optional iWrapE As Boolean, _
Optional oaWrapO As Boolean, _
Optional ByVal ivTarCT As wCharTE ) As Boolean If Successful, Finds the Next, or Previous, Character of the Same Character Type as iChar
ouChar is set to this Character, and ouOCC to it's UTF16 Character Code
Fails if
1. No Such Chararcter Exists
2. ivTarCT is specified and iChar is Not that Character Type
(and is Not Null**)
3. ivTarCT is Invalid
iPrev determines the Search Direction (the Default is Next)
iWrapE Enables Wrapping, and oaWrapO is Seton if Wrapping has Occurred
Togther, iPrev and iWrapE determine which ivTarCT Characters are Search Candidates;
iPrev iWrapE Candidates then
have a Character Code
False False > iChar (the Default)
True False < iChar
Don't Care True Any ivTarCT
ivTarCT may used to specify any wCharTE Character Type
If ivTarCT is Not Specified it is;
Set to the Same Type as iChar if iChar is
Numeric,
a Captital Letter, or
a Lower Case Letter
Otherwise it is set to the Printable Character Set
** Note that you can find the First (or Last) Character of a Character Type by
setting iChar to Null (Set iPrev as well to find the Last)
Refer to (WNXlA_123.XlA > wCharCodes Worksheet for a summary of Character Codes
egs
iChar iPrev iWrapE ivTarCT ouChar ouOCC
Null space 32 1st Printable
Null True ~ 126 Last Printable
A B 66 2nd Capital
a True Fails; Wrap is Off
0 True True 9 57 Last Numeric
= we_ctValFI @ 64 ">" and "?" are Invalid
Null we_ctExtAsc *** 128 1st Extended Ascii
Null True we_ctCtrl *** 127 Last Control
*** = can't be Printed
Optional iPrev As Boolean, Optional iWrapE As Boolean, _
Optional oaWrapO As Boolean, _
Optional ByVal ivTarCT As wCharTE ) As Boolean If Successful, Finds the Next, or Previous, Character of the Same Character Type as iChar
ouChar is set to this Character, and ouOCC to it's UTF16 Character Code
Fails if
1. No Such Chararcter Exists
2. ivTarCT is specified and iChar is Not that Character Type
(and is Not Null**)
3. ivTarCT is Invalid
iPrev determines the Search Direction (the Default is Next)
iWrapE Enables Wrapping, and oaWrapO is Seton if Wrapping has Occurred
Togther, iPrev and iWrapE determine which ivTarCT Characters are Search Candidates;
iPrev iWrapE Candidates then
have a Character Code
False False > iChar (the Default)
True False < iChar
Don't Care True Any ivTarCT
ivTarCT may used to specify any wCharTE Character Type
If ivTarCT is Not Specified it is;
Set to the Same Type as iChar if iChar is
Numeric,
a Captital Letter, or
a Lower Case Letter
Otherwise it is set to the Printable Character Set
** Note that you can find the First (or Last) Character of a Character Type by
setting iChar to Null (Set iPrev as well to find the Last)
Refer to (WNXlA_123.XlA > wCharCodes Worksheet for a summary of Character Codes
egs
iChar iPrev iWrapE ivTarCT ouChar ouOCC
Null space 32 1st Printable
Null True ~ 126 Last Printable
A B 66 2nd Capital
a True Fails; Wrap is Off
0 True True 9 57 Last Numeric
= we_ctValFI @ 64 ">" and "?" are Invalid
Null we_ctExtAsc *** 128 1st Extended Ascii
Null True we_ctCtrl *** 127 Last Control
*** = can't be Printed
DTc>String>Chars>
GetNextSeqStr
GetNextSeqStr (
ByVal ivStr As String,
Optional oStr As String, _
Optional iPrev As Boolean, _
Optional iWrapE As Boolean, Optional oaWrapO As Boolean, _
Optional iTarCT As wCharTE = we_ctAlphaNum ) As Boolean If Successful, sets oStr to the Next, or Previous, string in iTarCT Order from ivStr
Fails if
1. ivStr is Null (unless iTarCT = we_ctCtrl)
2. No Such String Exists
3. iTarCT is Invalid
Uses GetNextCC to Increment (or Decrement) the Right-most Character of
ivStr that is of Type iTarCT, ignoring any Characters that are Not of Type
iTarCT eg "7A8b" -> "7A9b" if iTarCT = we_ctNum
If the Right-most Character is a Limit Value for ivTarCT in the Search Direction,
then the Next Right-Most Character is also incremented/decremented, in a
similar manner to Counting eg "7A9b" -> "8A0b"
iWrapE Enables Wrapping of the Left-Most Character, and oaWrapO is Seton
if iWrapE is Set AND the Left-Most Character Wraps
egs "9A9b" -> "0A0b" if iWrapE is Seton (Succeeds and Setson oaWrapO)
but
"9A9b" -> "9A9b" if iWrapE is Reset (Fails )
and
"7A9b" -> "8A0b" Succeeds and Resets oaWrapO regardless of iWrapE
Optional iPrev As Boolean, _
Optional iWrapE As Boolean, Optional oaWrapO As Boolean, _
Optional iTarCT As wCharTE = we_ctAlphaNum ) As Boolean If Successful, sets oStr to the Next, or Previous, string in iTarCT Order from ivStr
Fails if
1. ivStr is Null (unless iTarCT = we_ctCtrl)
2. No Such String Exists
3. iTarCT is Invalid
Uses GetNextCC to Increment (or Decrement) the Right-most Character of
ivStr that is of Type iTarCT, ignoring any Characters that are Not of Type
iTarCT eg "7A8b" -> "7A9b" if iTarCT = we_ctNum
If the Right-most Character is a Limit Value for ivTarCT in the Search Direction,
then the Next Right-Most Character is also incremented/decremented, in a
similar manner to Counting eg "7A9b" -> "8A0b"
iWrapE Enables Wrapping of the Left-Most Character, and oaWrapO is Seton
if iWrapE is Set AND the Left-Most Character Wraps
egs "9A9b" -> "0A0b" if iWrapE is Seton (Succeeds and Setson oaWrapO)
but
"9A9b" -> "9A9b" if iWrapE is Reset (Fails )
and
"7A9b" -> "8A0b" Succeeds and Resets oaWrapO regardless of iWrapE
DTc>String>Substring>
wLeft
wLeft (
iStr As String,
iLen As Long,
Optional iRem As Boolean ) As String
Returns a Substring of iLen Characters from the Left-hand Side of iStr
Returns Null if iLen is <0 Note: VB's Left() causes a RTE for Len<0
" iStr " >Len(iStr)
Setting iRem returns the Remainder of iStr after removing the Default Return String
egs iStr iLen iRem Return
Null x x Null
abcde 2 ab
abcde 2 True cde
abcde 6 abcde
abcde -1 True abcde
Returns Null if iLen is <0 Note: VB's Left() causes a RTE for Len<0
" iStr " >Len(iStr)
Setting iRem returns the Remainder of iStr after removing the Default Return String
egs iStr iLen iRem Return
Null x x Null
abcde 2 ab
abcde 2 True cde
abcde 6 abcde
abcde -1 True abcde
DTc>String>Substring>
wRight
wRight (
iStr As String,
iLen As Long,
Optional iRem As Boolean ) As String
Returns a Substring of iLen Characters from the Right-hand Side of iStr
Returns Null if iLen is <0 Note: VB's Right() causes a RTE for Len<0
" iStr " >Len(iStr)
Setting iRem returns the Remainder of iStr after removing the Default Return String
egs iStr iLen iRem Return
Null x x Null
abcde 2 de
abcde 2 True abc
abcde 6 abcde
abcde -1 True abcde
Returns Null if iLen is <0 Note: VB's Right() causes a RTE for Len<0
" iStr " >Len(iStr)
Setting iRem returns the Remainder of iStr after removing the Default Return String
egs iStr iLen iRem Return
Null x x Null
abcde 2 de
abcde 2 True abc
abcde 6 abcde
abcde -1 True abcde
DTc>String>Substring>
wMid
wMid (
iStr As String,
iOri As Long,
iNOChars As Long, _
Optional iRev As Boolean, _
Optional iIncOs As wSSCE = we_sscSS, _
Optional oaExc As wMidXE ) As String Returns Substring(s) from iStr
The Default Return is the same as VB's Mid(iStr,iOri,iNOChars)
iOri is the Origin Character Position in iStr:
In the default Forward Direction it is the same as VB Mid's start Parameter
eg iOri=3 is the Third Character
In the Reverse Direction (with iRev Set) it is the iOri'th Character,
counting Left from the Last Character
eg iOri=2 is the second-to-last Character
iNOChars is the No. of Characters to be Returned counting
Right from iOri if iRev is False or
Left " True
If iRev is Set, the Return is StrRev(Mid(StrRev(iStr),iOri,iNOChars));
the Returned string Ends at Len(iStr) - iOri + 1
iIncOs may be used to Include Non-Default Substring combinations in the Return (see wSSCE)
we_sscL Includes the Substring to the Left of the Default Return
we_sscSS Includes the Default Return
we_sscR Includes the Substring to the Right of the Default Return
we_sscAXSS Includes the Left & Right Substrings (All Except Substring)
In All Cases, Concatenating the Left, Default and Right Substrings re-makes
iStr; if an Exception occurs that returns a Null String, then iOri determines
how iStr is split between the Left and Right Substrings (see last egs)
oaExc is Zero unless an Exception occurs; see wMidXE
egs iStr iOri iNOChars iRev iIncOs oaExc Return
Null x x x x Non-0 Null
abcd 3 2 . . 0 cd
abcd 3 2 True . 0 ab
abcd 3 3 . . we_mxOvR cd
abcd 3 3 True . we_mxOvL ab
abcd 2 2 x . 0 bc
abcd 2 2 x we_sscL 0 a
abcd 3 2 True . 0 ab
abcd 3 2 True we_sscR 0 cd
abcd x 0 x we_sscAll Non-0 abcd
abcd 0 1 . we_sscR we_mxOL1 abcd
abcd 2 0 . we_sscR we_mxNL1 bcd
abcd 4 0 True we_sscR we_mxNL1 bcd
Optional iRev As Boolean, _
Optional iIncOs As wSSCE = we_sscSS, _
Optional oaExc As wMidXE ) As String Returns Substring(s) from iStr
The Default Return is the same as VB's Mid(iStr,iOri,iNOChars)
iOri is the Origin Character Position in iStr:
In the default Forward Direction it is the same as VB Mid's start Parameter
eg iOri=3 is the Third Character
In the Reverse Direction (with iRev Set) it is the iOri'th Character,
counting Left from the Last Character
eg iOri=2 is the second-to-last Character
iNOChars is the No. of Characters to be Returned counting
Right from iOri if iRev is False or
Left " True
If iRev is Set, the Return is StrRev(Mid(StrRev(iStr),iOri,iNOChars));
the Returned string Ends at Len(iStr) - iOri + 1
iIncOs may be used to Include Non-Default Substring combinations in the Return (see wSSCE)
we_sscL Includes the Substring to the Left of the Default Return
we_sscSS Includes the Default Return
we_sscR Includes the Substring to the Right of the Default Return
we_sscAXSS Includes the Left & Right Substrings (All Except Substring)
In All Cases, Concatenating the Left, Default and Right Substrings re-makes
iStr; if an Exception occurs that returns a Null String, then iOri determines
how iStr is split between the Left and Right Substrings (see last egs)
oaExc is Zero unless an Exception occurs; see wMidXE
egs iStr iOri iNOChars iRev iIncOs oaExc Return
Null x x x x Non-0 Null
abcd 3 2 . . 0 cd
abcd 3 2 True . 0 ab
abcd 3 3 . . we_mxOvR cd
abcd 3 3 True . we_mxOvL ab
abcd 2 2 x . 0 bc
abcd 2 2 x we_sscL 0 a
abcd 3 2 True . 0 ab
abcd 3 2 True we_sscR 0 cd
abcd x 0 x we_sscAll Non-0 abcd
abcd 0 1 . we_sscR we_mxOL1 abcd
abcd 2 0 . we_sscR we_mxNL1 bcd
abcd 4 0 True we_sscR we_mxNL1 bcd
DTc>String>Substring>
GetSS
GetSS (
iStr As String,
iOri As Long,
iNOChars As Long, _
Optional oaSS As String, Optional oaExc As wMidXE, _
Optional iRev As Boolean, _
Optional iIncOs As wSSCE = we_sscSS ) As Boolean The same as as wMid(), but in a format suitable for Conditional Testing
Optional oaSS As String, Optional oaExc As wMidXE, _
Optional iRev As Boolean, _
Optional iIncOs As wSSCE = we_sscSS ) As Boolean The same as as wMid(), but in a format suitable for Conditional Testing
DTc>String>Substring>
FindSS
FindSS (
iSS As String,
ByVal ivInStr As String, _
Optional ocLeftStr As String, Optional ocRightStr As String, _
Optional ocFouAtPos As Long, Optional iRev As Boolean, _
Optional ByVal ivOri As Long = 1, _
Optional iComp As VbCompareMethod = vbTextCompare, _
Optional iPresOsOnFail As Boolean ) As Boolean If ivInStr contains iSS, Succeeds and Splits ivInStr into ocLeftStr and ocRightStr
The Split is made at ocFouAtPos, which is the Position of the First
(or the Last) Occurance of iSS in ivInStr, (depending on iRev)
iSS IS NOT INCLUDED in either ocLeftStr or ocRightStr (see NOTE 2)
If Successful, ocFouAtPos will be between 1 and Len(ivInStr),
On Failure, ocFouAtPos is set to 0, unless iPresOsOnFail is Set
(see NOTE 1)
If iRev is False ivInStr is searched from Left to Right
" True " Right to Left
If ivOri is (see NOTE 1)
=1 then All of ivInStr is searched (the Default)
>1 And then the Search Starts at that Position
<=Len(InStr) Relative to either;
the Start of ivInStr, if iRev is False,
or " End " True
<1 Or then FindSS Fails
>Len(InStr)
The Default iComp = vbTextCompare = 1 IS NOT CASE SENSITIVE,
Setting iComp = vbBinaryCompare = 0 makes FindSS Case Sensitive
On Failure Outputs are Cleared by Default, or Unmodified if
iPresOsOnFail is Seton
Succeeds if iSS is NULL (unless ivOri causes it to Fail) and in this case
ocFouAtPos = ivOri (see NOTE 2)
Fails if ivInStr is NULL
NOTE 1 ivOri is relative to one End of ivInStr (depending on iRev)
but ocFouAtPos is always relative to the Start of ivInStr
NOTE 2 FindSS can be used to Split ivInStr AFTER ivOri,
by Setting iSS to NULL
egs
----- iRev=False ---- ----- iRev=True -----
iSS ocFouAtPos ocFouAtPos
ivInStr ocLeftStr ocLeftStr
ivOri ocRightStr ocRightStr
NULL NULL x 0 0
NULL abcde 3 3 abc de 3 ab cde
a 1234a 1 5 1234 5 1234
a a2345 2 0 . 1 2345
a a234a67 3 5 a234 67 5 a234 67
abc 123abc7 3 4 123 7 0 .
abc 123abc7 2 4 123 7 4 123 7
. = ivInStr
Optional ocLeftStr As String, Optional ocRightStr As String, _
Optional ocFouAtPos As Long, Optional iRev As Boolean, _
Optional ByVal ivOri As Long = 1, _
Optional iComp As VbCompareMethod = vbTextCompare, _
Optional iPresOsOnFail As Boolean ) As Boolean If ivInStr contains iSS, Succeeds and Splits ivInStr into ocLeftStr and ocRightStr
The Split is made at ocFouAtPos, which is the Position of the First
(or the Last) Occurance of iSS in ivInStr, (depending on iRev)
iSS IS NOT INCLUDED in either ocLeftStr or ocRightStr (see NOTE 2)
If Successful, ocFouAtPos will be between 1 and Len(ivInStr),
On Failure, ocFouAtPos is set to 0, unless iPresOsOnFail is Set
(see NOTE 1)
If iRev is False ivInStr is searched from Left to Right
" True " Right to Left
If ivOri is (see NOTE 1)
=1 then All of ivInStr is searched (the Default)
>1 And then the Search Starts at that Position
<=Len(InStr) Relative to either;
the Start of ivInStr, if iRev is False,
or " End " True
<1 Or then FindSS Fails
>Len(InStr)
The Default iComp = vbTextCompare = 1 IS NOT CASE SENSITIVE,
Setting iComp = vbBinaryCompare = 0 makes FindSS Case Sensitive
On Failure Outputs are Cleared by Default, or Unmodified if
iPresOsOnFail is Seton
Succeeds if iSS is NULL (unless ivOri causes it to Fail) and in this case
ocFouAtPos = ivOri (see NOTE 2)
Fails if ivInStr is NULL
NOTE 1 ivOri is relative to one End of ivInStr (depending on iRev)
but ocFouAtPos is always relative to the Start of ivInStr
NOTE 2 FindSS can be used to Split ivInStr AFTER ivOri,
by Setting iSS to NULL
egs
----- iRev=False ---- ----- iRev=True -----
iSS ocFouAtPos ocFouAtPos
ivInStr ocLeftStr ocLeftStr
ivOri ocRightStr ocRightStr
NULL NULL x 0 0
NULL abcde 3 3 abc de 3 ab cde
a 1234a 1 5 1234 5 1234
a a2345 2 0 . 1 2345
a a234a67 3 5 a234 67 5 a234 67
abc 123abc7 3 4 123 7 0 .
abc 123abc7 2 4 123 7 4 123 7
. = ivInStr
DTc>String>Substring>
FindDSS
FindDSS (
iLDS As String,
iInStr As String,
iRDS As String, _
Optional ocSS As String, Optional ouStr2 As String, _
Optional iSS2IncOs As wSSCE = we_sscRS, _
Optional iRev As Boolean, Optional ByVal ivOri As Long = 1, _
Optional ocFCPos As Long, Optional ocLCPos As Long, _
Optional iComp As VbCompareMethod = vbTextCompare, _
Optional oaSSIsNull As Boolean ) As Boolean Finds the first Substring in iInStr Delimited by Left and Right Delimiting Strings, iLDS & iRDS
If iInStr contains Both iLDS & iRDS
And a Substring (including the Null String) exists Between them
then FindDSS Succeeds and sets ocSS to the Left-Most such
Substring [NOTE 1]
Setting iLDS to the Null String is Valid and has a "Wildcard" effect;
ocSS is then set to the LHS of iInStr, up to (but not including)
the First Character of iRDS. Similarly; [eg2]
Setting iRDS to the Null String returns the RHS of iInStr, after the
Last Character of iLDS [eg3]
A Second Output String, ouStr2, is also returned and it's value
determined by setting iSS2IncOs to any combination of the
Component Substrings defined in Enum wSSCE [eg8]
Refer to the the FindSS Function for a description of the functions of iRev, ivOri & iComp
ocFCPos & ocLCPos are the Positions of the First & Last Characters
of ocSS in iInStr [NOTE 2]
oaSSIsNull is Set whenever ocSS is Null eg FindDSS Fails,
or [eg9] iInStr = iLDS & iRD [NOTE 2]
NOTE 1 If iInStr itself is Null, FindDSS Fails [eg1]
NOTE 2 If FindDSS Succeeds And ocSS is Null,
then ocLCPos=ocFCPos-1 [eg9]
egs iLDS iInStr iRDS Return ocSS Non-Defaults/Variations
1 Null Null Null Fail Null
2 " abc c Suc ab
3 " " Null Suc abc
4 B ABCDabcd B Suc CDa
5 B ABCDabcd B Fail Null iComp=vbBinaryCompare
6 A ABCDabcd D Suc bc iRev=True
7 A ABCDabcd D Suc BC iRev=True &
iComp=vbBinaryCompare
8 A ABCDabcd C Suc B default iSS2IncOs=we_ssRS
returns ouStr2=Dabcd
9 ABCD ABCDabcd abcd Suc Null oaSSIsNull=True,
ocFCPos=5 & ocLCPos=4
Optional ocSS As String, Optional ouStr2 As String, _
Optional iSS2IncOs As wSSCE = we_sscRS, _
Optional iRev As Boolean, Optional ByVal ivOri As Long = 1, _
Optional ocFCPos As Long, Optional ocLCPos As Long, _
Optional iComp As VbCompareMethod = vbTextCompare, _
Optional oaSSIsNull As Boolean ) As Boolean Finds the first Substring in iInStr Delimited by Left and Right Delimiting Strings, iLDS & iRDS
If iInStr contains Both iLDS & iRDS
And a Substring (including the Null String) exists Between them
then FindDSS Succeeds and sets ocSS to the Left-Most such
Substring [NOTE 1]
Setting iLDS to the Null String is Valid and has a "Wildcard" effect;
ocSS is then set to the LHS of iInStr, up to (but not including)
the First Character of iRDS. Similarly; [eg2]
Setting iRDS to the Null String returns the RHS of iInStr, after the
Last Character of iLDS [eg3]
A Second Output String, ouStr2, is also returned and it's value
determined by setting iSS2IncOs to any combination of the
Component Substrings defined in Enum wSSCE [eg8]
Refer to the the FindSS Function for a description of the functions of iRev, ivOri & iComp
ocFCPos & ocLCPos are the Positions of the First & Last Characters
of ocSS in iInStr [NOTE 2]
oaSSIsNull is Set whenever ocSS is Null eg FindDSS Fails,
or [eg9] iInStr = iLDS & iRD [NOTE 2]
NOTE 1 If iInStr itself is Null, FindDSS Fails [eg1]
NOTE 2 If FindDSS Succeeds And ocSS is Null,
then ocLCPos=ocFCPos-1 [eg9]
egs iLDS iInStr iRDS Return ocSS Non-Defaults/Variations
1 Null Null Null Fail Null
2 " abc c Suc ab
3 " " Null Suc abc
4 B ABCDabcd B Suc CDa
5 B ABCDabcd B Fail Null iComp=vbBinaryCompare
6 A ABCDabcd D Suc bc iRev=True
7 A ABCDabcd D Suc BC iRev=True &
iComp=vbBinaryCompare
8 A ABCDabcd C Suc B default iSS2IncOs=we_ssRS
returns ouStr2=Dabcd
9 ABCD ABCDabcd abcd Suc Null oaSSIsNull=True,
ocFCPos=5 & ocLCPos=4
DTc>String>Substring>
TrimBufStr
TrimBufStr (
iStr As String ) As String
Returns the Substring to the Left of the First Null Character in iStr
DTc>String>Substring>
FirLineOfStr
FirLineOfStr (
ByVal ivStr As String ) As String
Returns ivStr up to it's First Carriage Return or Line Feed Character,
Ignoring and Excluding Leading CR and LF Characters
Ignoring and Excluding Leading CR and LF Characters
DTc>String>Substring>
CouSSs
CouSSs (
iSS As String,
iInStr As String, _
Optional iComp As VbCompareMethod = vbTextCompare ) As Long Returns the No. of Occurances of iSS in iInStr
Returns -1 if iSS or iInstr is Null
The Default iComp = vbTextCompare = 1 IS NOT CASE SENSITIVE,
Setting iComp = vbBinaryCompare = 0 makes CouSSs Case Sensitive
Optional iComp As VbCompareMethod = vbTextCompare ) As Long Returns the No. of Occurances of iSS in iInStr
Returns -1 if iSS or iInstr is Null
The Default iComp = vbTextCompare = 1 IS NOT CASE SENSITIVE,
Setting iComp = vbBinaryCompare = 0 makes CouSSs Case Sensitive
DTc>String>Substring>
StrHasAnySS
StrHasAnySS (
iSSs As String,
iDStr As String,
iInStr As String, _
Optional oSS As String, Optional ocPos As Long ) As Boolean If Successful, sets oSS to the First Substring from iSSs (Delimited by iDStr)
found in iInStr, and sets ocPos to iSSs Position in iInStr
Fails if iSSs, iDStr or iInStr is Null, or if iSSs and iDStr are the Same
Optional oSS As String, Optional ocPos As Long ) As Boolean If Successful, sets oSS to the First Substring from iSSs (Delimited by iDStr)
found in iInStr, and sets ocPos to iSSs Position in iInStr
Fails if iSSs, iDStr or iInStr is Null, or if iSSs and iDStr are the Same
DTc>String>Substring>
SplDStr
SplDStr (
ByVal ivStr As String,
ByVal ivDStr As String,
oaStrA( As String, _
Optional iPresDS As Boolean, Optional iIgnDSReps As Boolean, _
Optional ByVal ivCopyToOriCe As Range = Nothing) ) As Long Splits ivStr into an Array of Substrings, and Returns a Count of these Substrings
ivDStr Delimits the Substrings in ivStr and is is dropped from each
Substring, unless iPresDS is Seton
oaStrA is ReDim'd to a 0-Based Array, with
LBound(oaStrA) = 0 and
UBound(oaStrA) = Ret-1, where Ret = SplDStr's Return
egs ivStr="" with ivDStr= * Returns 0
ivStr="123" with ivDStr= "" Returns a 1 Entry Array: 123
ivStr="1,22,,4444" with ivDStr= "," Returns a 4 Entry Array:
1 22 Null 4444
Setting iPresDS Preserves the ivDStr; each oaStrA Entry, except the Last,
will end with ivDStr
Setting iIgnDSReps causes Consecutive Delimiters to be Ignored.
The example above would then Return a 3 Entry Array: 1 22 4444
If ivCopyToOriCe is specified, oaStrA is copied that Row, starting in
ivCopyToOriCe
Optional iPresDS As Boolean, Optional iIgnDSReps As Boolean, _
Optional ByVal ivCopyToOriCe As Range = Nothing) ) As Long Splits ivStr into an Array of Substrings, and Returns a Count of these Substrings
ivDStr Delimits the Substrings in ivStr and is is dropped from each
Substring, unless iPresDS is Seton
oaStrA is ReDim'd to a 0-Based Array, with
LBound(oaStrA) = 0 and
UBound(oaStrA) = Ret-1, where Ret = SplDStr's Return
egs ivStr="" with ivDStr= * Returns 0
ivStr="123" with ivDStr= "" Returns a 1 Entry Array: 123
ivStr="1,22,,4444" with ivDStr= "," Returns a 4 Entry Array:
1 22 Null 4444
Setting iPresDS Preserves the ivDStr; each oaStrA Entry, except the Last,
will end with ivDStr
Setting iIgnDSReps causes Consecutive Delimiters to be Ignored.
The example above would then Return a 3 Entry Array: 1 22 4444
If ivCopyToOriCe is specified, oaStrA is copied that Row, starting in
ivCopyToOriCe
DTc>String>Substring>
SSFns
SSFns (
iFN As String,
ioSSs As wSS_U,
Optional ioVar As Variant,
Optional ioVar2 As Variant ) As Boolean
Split String Funcs
Performs Functions using ioSSs, which is composed of a String (.Str)
with Left and Right Substrings (.L and .R)
"Art" is currently the only Function implimented
iFn =
"Art" Articles are the words "a" and "the" (or "A" and "The")
Copies ioVar to .Str
Sets .L to any Leading Article in ioVar & iOVar2
Sets .R to the Remainder of of ioVar, excluding the Leading Space
Succeeds if a Leading Article is Found
Capitalization is Unchanged
egs ioVar ioVar2 .L .R Ret
"a Word" "" "a" "Word" Suc
"An Idea" "x" "x" "An Idea" Fail
"The Time" " " "The " "Time" Suc
Performs Functions using ioSSs, which is composed of a String (.Str)
with Left and Right Substrings (.L and .R)
"Art" is currently the only Function implimented
iFn =
"Art" Articles are the words "a" and "the" (or "A" and "The")
Copies ioVar to .Str
Sets .L to any Leading Article in ioVar & iOVar2
Sets .R to the Remainder of of ioVar, excluding the Leading Space
Succeeds if a Leading Article is Found
Capitalization is Unchanged
egs ioVar ioVar2 .L .R Ret
"a Word" "" "a" "Word" Suc
"An Idea" "x" "x" "An Idea" Fail
"The Time" " " "The " "Time" Suc
DTc>String>Modify>
wTrim
wTrim (
iFromStr As String,
Optional iTrimStr As String = " ",
Optional iLorROnly As String = "" ) As String
Returns a copy of iFromStr with all Leading and/or Trailing iTrimStr's Removed
iTrimStr defaults to a Space; " "
Setting iLorROnly to "L" or "R" causes Only Leading or Only Trailing iTrimStrs Only to be Removed respectively
iTrimStr will generally be a Single Character; wTrim works with longer strings,
but only entire iTrimStr's are Removed
eg wTrim("aaaBBaa","aa") returns "aBB"
iTrimStr defaults to a Space; " "
Setting iLorROnly to "L" or "R" causes Only Leading or Only Trailing iTrimStrs Only to be Removed respectively
iTrimStr will generally be a Single Character; wTrim works with longer strings,
but only entire iTrimStr's are Removed
eg wTrim("aaaBBaa","aa") returns "aBB"
DTc>String>Modify>
TrimWSp
TrimWSp (
ByVal ivStr As String,
Optional iLorROnly As String = "" ) As String
Returns ivStr with Whitespace Characters Trimmed from it
Setting iLorROnly to "L" or "R" causes Only Leading or Only Trailing Whitespace Only to be Removed respectively
Setting iLorROnly to "L" or "R" causes Only Leading or Only Trailing Whitespace Only to be Removed respectively
DTc>String>Modify>
wPad
wPad (
iStr As String,
iPadStr As String,
ByVal ivNOCopies As Long,
iLorR As String ) As String
Returns ivNOCopies of iPadStr added to the Left or Right of iStr
Setting iLorR to "L" or "R" adds Copies to the Left or Right Side respectively
egs wPad(Null,"a",2,"left") -> "aa"
wPad("a","bcd",1,"R") -> "abcd"
Setting iLorR to "L" or "R" adds Copies to the Left or Right Side respectively
egs wPad(Null,"a",2,"left") -> "aa"
wPad("a","bcd",1,"R") -> "abcd"
DTc>String>Modify>
wReplace
wReplace (
iFindStr As String,
iRepWStr As String,
iInStr As String, _
Optional iStartRepsPos As Long = 1, Optional iCou As Long = -1, Optional iComp As VbCompareMethod = vbTextCompare ) As String Similar to VB Replace() except that;
1) the Parameter Names and Order are Different
2) iStartRepsPos<1 Or iCou<-1 Return iInStr, rather than causing a RTE
eg wReplace("c","3","abc",-1,-2) Returns "abc" whereas
Replace("abc","c","3",-1,-2) causes RTE 5
3) if iStartRepsPos>1, Character's in Positions <iStartRepsPos are simply
not Replaced, rather than being Trimmed
eg wReplace("c","3","abc",2, 1) Returns "cb3" whereas
Replace("abc","c","3",2, 1) Returns "b3"
Note that if iFindStr is Null, wReplace and VB Replace both Return iInStr
(subject to 2) and 3) above)
eg wReplace("","3","abc") and
Replace("","3","abc") both Return "abc"
Optional iStartRepsPos As Long = 1, Optional iCou As Long = -1, Optional iComp As VbCompareMethod = vbTextCompare ) As String Similar to VB Replace() except that;
1) the Parameter Names and Order are Different
2) iStartRepsPos<1 Or iCou<-1 Return iInStr, rather than causing a RTE
eg wReplace("c","3","abc",-1,-2) Returns "abc" whereas
Replace("abc","c","3",-1,-2) causes RTE 5
3) if iStartRepsPos>1, Character's in Positions <iStartRepsPos are simply
not Replaced, rather than being Trimmed
eg wReplace("c","3","abc",2, 1) Returns "cb3" whereas
Replace("abc","c","3",2, 1) Returns "b3"
Note that if iFindStr is Null, wReplace and VB Replace both Return iInStr
(subject to 2) and 3) above)
eg wReplace("","3","abc") and
Replace("","3","abc") both Return "abc"
DTc>String>Modify>
RepStrs
RepStrs (
ByVal ivFindStrs As String,
ByVal ivRepWStrs As String, _
ByVal ivDStr As String, iInStr As String, _
Optional iComp As VbCompareMethod = vbTextCompare ) As String Uses wReplace to Return iInStr with each substring from ivFindStrs Replaced with the Respective Substring from iRepWStr
ivDStr Delimits the Substrings in ivFindStrs and ivRepWStrs
If ivRepStrs has Fewer Substrings than ivFindStrs, the Last ivRepStr
Substring is used for ivFindStrs that don't have a
corresponding ivRetStr value
eg iFind="a,b,c" iRep="x,*" ivDStr="," iInstr= "1A3B5C7"
Returns "1x3*5*7"
with Default iComp
ByVal ivDStr As String, iInStr As String, _
Optional iComp As VbCompareMethod = vbTextCompare ) As String Uses wReplace to Return iInStr with each substring from ivFindStrs Replaced with the Respective Substring from iRepWStr
ivDStr Delimits the Substrings in ivFindStrs and ivRepWStrs
If ivRepStrs has Fewer Substrings than ivFindStrs, the Last ivRepStr
Substring is used for ivFindStrs that don't have a
corresponding ivRetStr value
eg iFind="a,b,c" iRep="x,*" ivDStr="," iInstr= "1A3B5C7"
Returns "1x3*5*7"
with Default iComp
DTc>String>Modify>
CapSen
CapSen (
iSen As String,
Optional ByVal ivXPoss As String ) As String
Returns a Copy of iSen with the First Letter, and the First Letter of Every
Word, Capitalized
ivXPoss is a Comma-Separated String of Exception Positions in iSen for which this Logic is Inverted;
ie Position 1 Is Not Capitalized
First-Letters Are Not "
Other Letters Are "
ivXPoss must be in Ascending Order, values >Len(iSen) have no affect
eg iSen="ie mr mcgee", ivXPoss="1,9" Returns "ie Mr McGee"
ivXPoss is a Comma-Separated String of Exception Positions in iSen for which this Logic is Inverted;
ie Position 1 Is Not Capitalized
First-Letters Are Not "
Other Letters Are "
ivXPoss must be in Ascending Order, values >Len(iSen) have no affect
eg iSen="ie mr mcgee", ivXPoss="1,9" Returns "ie Mr McGee"
DTc>String>Modify>
PrepSpasToCaps
PrepSpasToCaps (
iStr As String,
Optional ByVal ivXPoss As String ) As String
Returns a String with a Space Prepended before Every Capital Letter in iStr,
except iStr's First Character
ivXPoss is a Comma-Separated String of Exception Positions in iStr, for which;
If the Exception Position=1, a Space IS Prepended to the First Character
(regardless of it's Type)
If the Character at the Exception Position;
IS a Capital, a Space IS NOT Prepended to it
IS NOT " IS "
ivXPoss is a Comma-Separated String of Exception Positions in iStr, for which;
If the Exception Position=1, a Space IS Prepended to the First Character
(regardless of it's Type)
If the Character at the Exception Position;
IS a Capital, a Space IS NOT Prepended to it
IS NOT " IS "
DTc>String>Specialized>
NegBStr
NegBStr (
iStr As String ) As String
Returns a Logically Negated copy of a (Binary) String iStr, replacing all 1's
with 0's and vice versa
Leading 0's are Preserved
Other Characters are Unchanged
eg NegBStr("01a0") Returns "00a1"
Leading 0's are Preserved
Other Characters are Unchanged
eg NegBStr("01a0") Returns "00a1"
DTc>String>Specialized>
BNibExDig
BNibExDig (
iDigChar As String,
iOct As Boolean ) As String
Returns any Valid Hex or Octal Digit (iDigChar) as a Binary String (or Nibble)
Other Values Return Null
If iOct is False iDigChar may be any Hex Digit (0-9, A-E or a-e)
" True " Octal Digit (0-7)
If iOct is False the Return is 4 Bits long
" True " 3 "
If Len(iDigChar)>1 BNibExDig uses it's First Character
egs BNibExDig("e") Returns "1110"
("71",True) " "111"
("8",True) " Null
Other Values Return Null
If iOct is False iDigChar may be any Hex Digit (0-9, A-E or a-e)
" True " Octal Digit (0-7)
If iOct is False the Return is 4 Bits long
" True " 3 "
If Len(iDigChar)>1 BNibExDig uses it's First Character
egs BNibExDig("e") Returns "1110"
("71",True) " "111"
("8",True) " Null
DTc>String>Specialized>
SwapFir2Nums
SwapFir2Nums (
iStr As String,
Optional oaStr As String ) As Boolean
If Successful, sets oaStr to iStr with the First and Second Numbers, as
delimited by Non-Digit(s), Swapped
This function is intended for dd-mm <-> mm-dd Date Data Type Conversions
iStr's First Character must be Numeric
The Numbers must be delimited by a Single Non-Numerical Character
Otherwise Fails & Returns iStr if this is not possible
egs "31-1" returns "1-31"
"1/31/12" returns "31/1/12"
"a31-1", "31--1" and "31st March" all Fail
This function is intended for dd-mm <-> mm-dd Date Data Type Conversions
iStr's First Character must be Numeric
The Numbers must be delimited by a Single Non-Numerical Character
Otherwise Fails & Returns iStr if this is not possible
egs "31-1" returns "1-31"
"1/31/12" returns "31/1/12"
"a31-1", "31--1" and "31st March" all Fail
DTc>String>Specialized>
IsValBaseChar
IsValBaseChar (
iChar As String,
iBase As Long, _
Optional oNum As Long, Optional oaExcN As Long ) As Boolean If Successful, sets oNum to iChar's Numerical Value in iBase
On Failure, Returns a Non-0 Exception No. (oaExcN)
Exceptions 1 = iChar isn't a Single Char
2 = iBase is <2 Or >36
3 = iChar is Invalid in Any iBase
4 = iChar is Invalid in iBase
egs "1",10 -> 1, "a",16 -> 10, "E",16 -> 15, "z",36 -> 35
Optional oNum As Long, Optional oaExcN As Long ) As Boolean If Successful, sets oNum to iChar's Numerical Value in iBase
On Failure, Returns a Non-0 Exception No. (oaExcN)
Exceptions 1 = iChar isn't a Single Char
2 = iBase is <2 Or >36
3 = iChar is Invalid in Any iBase
4 = iChar is Invalid in iBase
egs "1",10 -> 1, "a",16 -> 10, "E",16 -> 15, "z",36 -> 35
DTc>Time&Date>
wTimeStr
wTimeStr (
Optional ByVal ivSecs As Double = -1#,
Optional iRouMs As Boolean, _
Optional oaDays As Long ) As String Returns a Seconds Value, ivSecs, as a String of Format hh:mm:ss.mmm
mmm is the Millisecond Component of ivSecs
If ivSecs is Not Specified, or is Negative,
wTimeStr Returns the Current Time Of Day (since Midnight)
This is the same as wTimeStr(Timer)
If iRouMs is Set, the Return is Rounded to the nearest Second and Formatted hh:mm:ss
wTimeStr Always returns a 12 or 8 Character String; values of >= 24 Hours Wrap back to <24 Hours
egs 86,399.499 -> "23:59:59.499" with oaDays =0 if iRouMs is Reset
86,399.499 -> "23:59:59" with oaDays =0 if iRouMs is Set
86,400.501 -> "00:00:01" with oaDays =1 if iRouMs is Set
Optional oaDays As Long ) As String Returns a Seconds Value, ivSecs, as a String of Format hh:mm:ss.mmm
mmm is the Millisecond Component of ivSecs
If ivSecs is Not Specified, or is Negative,
wTimeStr Returns the Current Time Of Day (since Midnight)
This is the same as wTimeStr(Timer)
If iRouMs is Set, the Return is Rounded to the nearest Second and Formatted hh:mm:ss
wTimeStr Always returns a 12 or 8 Character String; values of >= 24 Hours Wrap back to <24 Hours
egs 86,399.499 -> "23:59:59.499" with oaDays =0 if iRouMs is Reset
86,399.499 -> "23:59:59" with oaDays =0 if iRouMs is Set
86,400.501 -> "00:00:01" with oaDays =1 if iRouMs is Set
DTc>Array>
ArrIsAllocd
ArrIsAllocd (
iArr As Variant,
Optional oaIsArr As Boolean ) As Boolean
Succeeds if the Array iArr is an Array, and has been Allocated
oaIsArr is Set if iArr is an Array
Note that Static Arrays are always Allocated
refer to http://www.cpearson.com/excel/ArrIsAllocd.aspx
oaIsArr is Set if iArr is an Array
Note that Static Arrays are always Allocated
refer to http://www.cpearson.com/excel/ArrIsAllocd.aspx
DTc>Variant>
VarTypeStr
VarTypeStr (
iVar As Variant,
Optional oaVarType As VBA.VbVarType, _
Optional oaArrVT As VBA.VbVarType ) As String If Successful, Returns the String Constant Subtype of Variant iVar,
suffixed with "()" if it is an Array eg "vbLong()" for a Long Array
Sets oaVarType to VarType(iVar)
Sets oaArrVT to;
-1 if iVar Is Not an Array Or to
VarType(iVar) if iVar Is an Array eg vbLong (=3) for a Long Array
On Failure, returns Null and sets both oaVarType and oaArrVT to -1
Note that oaVarType and oaArrVT Parameters may be declared as Long
see VarType Function in Code Notes
Optional oaArrVT As VBA.VbVarType ) As String If Successful, Returns the String Constant Subtype of Variant iVar,
suffixed with "()" if it is an Array eg "vbLong()" for a Long Array
Sets oaVarType to VarType(iVar)
Sets oaArrVT to;
-1 if iVar Is Not an Array Or to
VarType(iVar) if iVar Is an Array eg vbLong (=3) for a Long Array
On Failure, returns Null and sets both oaVarType and oaArrVT to -1
Note that oaVarType and oaArrVT Parameters may be declared as Long
see VarType Function in Code Notes
Last Div