ࡱ> z  ab*!"#$%&'()+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`ihdefgxulmnopqrstcvwy|}~Root Entry F8zTヱT@;Workbook _VBA_PROJECT_CUR"|TヱTVBA |TT  !"#$&'()*+,-./012345689:;<=>?@ABCDEFGHJKLMNOPQRSTUVWXYZ\]^_`abcdefghijklnopqrstuvwxyz{|}~ \p Stephen Allen Ba= ThisWorkbook=x-B%<X@"1Arial1Arial1Arial1Arial1Arial""#,##0;\-""#,##0""#,##0;[Red]\-""#,##0""#,##0.00;\-""#,##0.00#""#,##0.00;[Red]\-""#,##0.005*0_-""* #,##0_-;\-""* #,##0_-;_-""* "-"_-;_-@_-,)'_-* #,##0_-;\-* #,##0_-;_-* "-"_-;_-@_-=,8_-""* #,##0.00_-;\-""* #,##0.00_-;_-""* "-"??_-;_-@_-4+/_-* #,##0.00_-;\-* #,##0.00_-;_-* "-"??_-;_-@_-                + ) , *  `Sheet1Sheet2Sheet3,"cc    dMbP?_*+%"??U>@7 Sheet1    dMbP?_*+%"??U>@7 Sheet2    dMbP?_*+%"??U>@7 Sheet3 DrK%ׁ#5#1vHF!){F 1J՟[^xThisWorkbook %YSheet17QSheet2IQSheet3[QME (SLSS<N0{00020819-0000-0000-C000-000000000046}(%Hh % %8p@@TT8J xAttribute VB_Name = "ThisWorkbook" Bas0{00020P819-0C$0046} |GlobalSpacFalse dCreatablPredeclaIdTru BExposeTemplateDerivBustomizD2DrK%#TN2Ae F' xmĵLv#xME (SLSS<N0{00020820-0000-0000-C000-000000000046}(%Hh % %8p@@HH8J xAttribute VB_Name = "She@et1" Bast0{00020820- C$0046} |Global!SpacFalse dCreatablPre declaIdTru BExposeTemplateDeriv$Bustom izD2DrK%#Xϙ)FJwA: FSCjzOxME (SLSS<N0{00020820-0000-0000-C000-000000000046}(%Hh % %8p@@HH8J xAttribute VB_Name = "She@et2" Bast0{00020820- C$0046} |Global!SpacFalse dCreatablPre declaIdTru BExposeTemplateDeriv$Bustom izD2DrK%z_#4!@{|UM FӬ̈́?G㦅+xME (SLSS<N0{00020820-0000-0000-C000-000000000046}(%Hh % %8p@@HH8J xAttribute VB_Name = "She@et3" Bast0{00020820- C$0046} |Global!SpacFalse dCreatablPre declaIdTru BExposeTemplateDeriv$Bustom izD2<j%xDateManagementm8__SRP_2T__SRP_3pStringManipulation& ,ME(6 << <+&`P@@i( `*xt`,r %0xx9:@(` P `J $*\Rffff*0]4aba1719x B   4! 4 0@!P`h 6 p0]`]x ('* *$.',w ,b * '*w *$.',w *'&i-Find out the Weekday the test date represents.If it isn't a Monday keep adding 1 until it is0Remember to format your worksheet cell as a datedAttribute VB_Name = "DateManagement" Option Explicit FuncPNextMon(DtRecvd As@ Long) Dim TestDt &MyWe ekDayIn`tegerZ+= R 'Find out the 5d5 tW d reprDess Y=-(U)LIf it isn't a ]&keep adding 1 until_o While +<> 2  V+ 1 ALoop O~'Rmber to format your worksheet celpl as]|E rU~| krU (`a(nrU~|  >rU ,`y/&(`y0,` 8% *$ 4% @& .H&  &$ 8 % >X%  8Ph &$ &  :x& .& 6'(0 @'8@H 2X'P`x  8 P h ' ('   >' D( J`(       8 P h          B( @( 88) 0 "H  p             B     0 8 Hp) ,) F) L0*  H  X h p     $   8 P X x                 ( 0Hh           0H Xh px    B  08@ HP+ F* >+ .H  x     (`, (8, " H$`   "  :* >+ .+  *, 0@ HXhp  x &, (        (@P`h p     N, k%IxMEP6 <<<< <<< p 00%+>` @xFFi: i<`Dj `@t`Zl`Fh`Lp@`Xf0H`+`~~i: @ ``(``  +8`8QQiXI ```````F`l+`zxrri ````````F(`l```+`ZZiI ``````````F`l`n`p``2``+`==i0iPi`` ` `  ` ??i0 iP i```  `l IIi i8 i ```8 + `x ;;i  i i:`D` `"`$`X `&+( `  33i0 iP i:`D` `"`$` `&`*8 8 ` X ` 8  J $*\Rffff*0^4aba1719h B Hx 4P  8 , : & $    (  (@ 4h . $ 4 $H $8 )(xGh   J L0 2   4   `  $ 8HP (X( 0 >    B p x   ( 8 HPX  ` &-hp x B  , ((-   ( 0 P 6P- N- .- hx  :. :.  Dx/ /    HP. .    @ X h    p x B. >8/        @ Xh p           B   (08 H/ (0 L@0 0  @P  X h x       0 PX `px        B  (08 F0 60 B01 ,x1 @ ` p      0 @`hp x     (08 @ P ` B  @@2 >1    (0 8 X h     ( 8X`h 21 22  2 p      ( 0 @ Ppx  B  D2 203   Dh3 *3 , B3 3 @4 8 X ` h p 2X4x   44 64 J5     "  !  ! 0!8!*@! p! ! :P5 25 5 !!!!! !!!! B """" "("0"8" B5  @",P" """""" "#   ##0# P# p# # ##*# "# # $ $($0$ H$P$ h$ p$x$$$ 86xi@]]kz\ :X '@ing8.First convert MyString into a string data type%Check whether this is an error numberan e#Error'>.BFind out how long it is @'D!Test each character in the stringfor whether it's a number!but exclude full stops and commas-because they can form parts of number strings'and exclude spaces because they confusethe presentation of the result F D]  @ F$'Luse ASCII codes for precisionot Ak L$V]@ar X 'Xot < Xchar L'Zy ! L$T  L$T. L$T, L$T L$T FCheck whether PosN - the position of the character under considerationon1\ +Matches Pos - If so then that's your answer}{\s10]k-We don't want to loop any more than necessarysbasxCheck whether we got an answer Z Z'>d8"Tell the user something went wrong#Error'>k @Error #*G\j wor DPos is the Nth position of the string character required by the user.also "" is also often deemed to represent zerodepending on context in Excel(Excel delivers the error numberthis would confuse the results3because when you convert an error value to a string7In this case the string looked looked at an error valuelX`]]](]@]X]p :'  ##/##/####+ ##/#/####+ #/##/####+#/#/####+##/##/##+##/#/##+#/##/##+#/#/##+##/## +##/# +#/## +#/# + $  $'  DateErroryd 'k January+February+March+April+May+June+July+August+ September +October +November +December + $  $'  Jan+Feb+Mar+Apr+Jun+Jul+Aug+Sept+Sep +Oct +Nov +Dec + $  $' '     $ Gyj   Empty String'k0(d  Empty String'k 'z#Error'i]x]]]]  ' ' ' F    F'  $'   $' F F    e  F $'Y  F $'   F $'dH  F $'A  F $''k   $  ' 'y d DateError' k kx p F    b l F    l     l $'|  l $'   l $'d  l $'S  l $''k   $m  ' 'y0 d( DateError'P k k l 'F  'i 8]]]]]]](]@X]p]]'Break the string into relevant elements ' l  l'F   l$' $VGyj X'  Z /'    $'w   W   $$V/  'd    $'kk X'     '  e#  'X  e#  '0k(d  /'$  /'%      $' $V  'd X'3(kx   $' $V  '8k0     '  e +  '  e+  'k ', B- X'- l .  'PkHk@k80 'i ]8]P]h0]]]]](@XpD2] '2 '3 ' F     F $'  5  X F$'  F D6  X $'   $'7    $'  'd  X $'   $'^:    $'  'k0k(  F    b l F     l $'    X l$'  l  X $'   $'    $'  'd0  X $'   $'    $'  'kk l 'F 'i]p]]]  '  e# ' '   $'  ' d ' kd## ' '    $'   $X ' d@  ' k( k# ' ' 5   $'    ' dP ' k@k80 'i ]p ] ] ##  ####' '   $  'k#  ####' '   $  '8k0##  ##' '   $  'k#  ##' '   $  'hk`## ' '   $  'k# ' '   $  'k 'i ]X ]p ]  ##, ####' '   $  'k  #, ####' '   $  'k  ##, ##' '   $  '@k8  #, ##' '   $  'k  ####' '   $  'xkp  ##' '   $  'k  #' '   $  'k 'i ]@ ]X ]p ] ] ] :'D   D   Gj $   : $$  $'yk Gj  '& "  b $ & : $$  : $  $ $'  " '" $ '&yk : $$  $ : $  $ $'  " '" "  Gjkxp 'z`X#Error'i@ ]p ] ] ] ] ] ] :'D   D   D Gj $  D : $$  $ 'ypkh`  D Gj '* "  b $ * D : $$  :  $  $'  " '" $ '*yk : $$  $ D :  D   $'  " '" "  Gjk * D Gxj "  Gj '(z#Error'(i Loop around each of the patterns1Create first array for testing.0$both long and very heavily branched.0 12This tests (and deletes) the longest strings first8to avoid missing a date variable by deleting part of it.02There shouldn't be any errors, but this process is-It is difficult to predict what can go wrong.-19This provides both a safety net and specific notification'to the user if some thing does go wrong04Call the process for deleting numeric date patterns.00 deleting them as you go.e(If the check process identifies an error 0go back to the known correct value of the string0otherwise move on.+Now test for the presence of month strings.-1.Loop around each month0."deleting string segments as found.7Do the same for shortened versions of the month strings->NB May is ommitted because there is no change in string length0.DThere are two shortened wersions of September. This will get either.0 0;Strings that are or look empty tend to confuse the reviewer00 :The substitutes empty string with the value "Empty String"2NB The return value does not include any numerics.BThis process takes the match criteria and tests whether it equates&to any portion of the original string..0@If a match is found, the matching potion is immediately deleted.1FThe matching process continues until the end of the string is reached.00?comprising numbers and slashes only, meets a series of criteria 4Apply the standard test for maximum days and months.f crit8relating to the maximum combinations of days and months..AThis process tests whether the numeric elements of a date string,8Not intended to be able to distnguish the number of days.'associated with different month values..#This is a generalised control only.00 !Treat as day month vatiants only.!Consider a 2 number value string. Consider a 3 number value string.HKeep 4 digit years within the Microsoft date process validation criteriao Any test value above zero fails.s"Considers text based date formats./If the process finds a text match for the monthi'Hthen looks to see where the first related numeric value lies in relationd(to the month value - before or after it.l 3if before then look for appropriate string patterns 2. Blook for string pattern where both day and year numeric fall after the month string ;The Do While ... Loop ensures your look as the whole string83. 3PatLen - determines how long the relevant string is0.07and do not simply jump out after you've found one match1 >We use both StartPos and PatLen togther to remove the relevant0. portion from the string..AValidates the string position from which the start of the patternwill be validated.EEssentially is there a relevant numeric that determines the day value00before the month string..?In this process we look for a pattern that matches a date value00starting with the longest possible version first0<If the evaluation finds a match we return the pattern length 0.&and jump out of the evaluation process 07both day and year, are presented after the month string a+We also gone one step further and evaluatedse a safter the month textnly on9We use a similar process for dates where all the figures,+those patterns where only one value appears1 1>This process returns 1 or more whole words before the position1.$relation to the length of the string0 0,of a numeric value "NumPos" within a string. 0.>This tests the validity of the variables NumPos and WordNum in 0;This For ... Next loop works backwards to find the position 11of the first space.1.0:This represents the end of the word(s) we are looking for.+If no spaces there is an error so junp out..00-Work backwards until you find a complete word.00as determined by the position of the next space. CContinue the Backwards loop until all the required words are found. 11 3If you got to the beginning of the string and still0 - +have not found the required number of words0 0 return an error value0.<Very similar in process and structure to TextBeforeNum above.00$ Attribute VB_Nam@e = "SngManipulation" Op Explicit@Bas e 1 FuncJNthNonNum(My As Variant, PosInteger) % Dim` Test5 Char 1Answe9Len+i-^iN  'First convert ainto a s data type|Y= C <|)'Check whethASthis an error @numbers5Au0gust]s5Se8pte5OctosNy%6DP?6h/}segms 96n.v#!SZ#2Tq)Pa׶t(CurrString, TestVar)  Next 'Do the same for shortened versions ofDmonth ssrNB May i@mmittdbecaHuse\reno chang n: length@The%a(twoxwxSeptember. Thjwill get eir.ReDimArray(12y) = "Jan" 2 Feb 3 (Mar 4 Ap 5 Ju16 l %7 Aug n8 ~ 9 10 Oct 1BNovNC?Dec@ FEachS InUƏACǗVDelTD`atePa՞EMyLen @H@Csat $look empty d tonf revie@Ô substitutefs  w value "EwB?'@tHurn do@not include any numericsIf C3> 0,enCARq@81 "@ n Exit @ ACNc6aEnd If1Else J9am 1 A'i <"-ø o i -0GS i/  / . + i$ sF@ p2 "7  "4vO#@LikeCLpNumElements(auY_ 0sm &$7e)c-$ aSUo0K[q`"O Wx+QGo ileaveVq1y? scУWhil <7)68r i!q88r.arlO%I%r. }%r?/22tp/f g0wO O O t8O F 2@V2wd2RQ33K +v_3133D33R4Q_494K 4 grp#hLoop :047 anq<_2LAq,O"yM}h~1pzDza2WmYea!PMy/mmg =m Slash1Pos 20*qŽ ca dPT,0crisssonly, medetp sAf ՠrelatamaximum combinations of days and months.  'Break the string into relevant mentsVMyLen = (DateS(tr). For r61 To ( iBrDayM1? & Mid\, r, 1bHIf IsNu`meric$F)GFalse ThExit HŐ9Ii= C Nexth4v< 6)'Cider a 2 numbvaluU'T0t as vǀnly*Slash1PosbIn(1, , "/"=2+ P5- 28/ Yc2) ^3Test['E#Go@EscapeFuncEs t@2KEIfCF-ʈYa'ApplyDard t: fmAD'Notende o be able @distnguish+'associ@d with differ?ƘA dThis @a generalis`cprol oQ >% 1! > 12 A`%"l  ebI e)< 3xEV(T A|=%= "# " &HRKPEstO]YGteRY/Yt=b3ˉxHju\- 3,"._" Cp.(V, 2)) > 31ofIf!q 1K{3IAGo{*Hy?&P?&tL=& ` 2! /' /'$'4 J2 &?[ X%<+[ 6 >[_ubxW, Ma$'W!( V\*YExtrac?H#Wp2XMy1 'In NpRprocess wesaaRm!RaPtrWR3YwiaTlong@]sibpversp fi|rs[  Yl uaC_@s a RaretuaD_ lena wGE&/#"(F >,% LѬ;g,='b:MyJp( Q :O O O O P)O eO J 3 [ /( nV > v P  ݵStartPos, PatLen) Like Myptern T@hen E xtrac =C`GoTobJump End If = "## " & MonthSptr t(*)8If Mid(&ingVa8l, fRMy: FindˀgthDbMFunct ioriva(te !aMZC As Integer,j;bbrdlDim H zLJ@A'We use a similar process for d3s wher@ll the figures, 'boDthy ayTea1a@ pea5d af@B m st4 " ##,##"O ,+? i>?,,+_+falso gone astep fur`drbevaluhd etholpat%jonlyBl4pp@hsftextfgx=&#####Z9yy?1c_)_)_)o}`}hhahdg"VhT#Be]eNum(0 !egWords`:MySbA)f`QcCxurr!C0ountN-os^ A3Thisflreturns 1 lmoiwhole w s bRjposis|'ofpnumeric 9"S" withinrl.1t= Е`{z'tests"idit@?friables ObCa i'relaA tolen xt@$<Or>r1 q ErrorCPheckF... NP# loopkackward oP 1kfirst space3reDe4 q(s) wPkч!= 6- P0 V-17#>D5+,4!, 1)="Rf%pTyCExit `嵡P< )# 901;@a1GZ + 1G -":q = wa*2*%TR۱1PNotb۟A0301E(?Q?m;' 'g dIbeginnq;IL stillhave n#"'V%pWberA*%\47Y; '7_"O&>LO sDX_BT"#EPrror" End Function X TextAft@erNum( Pos As Integer, Word, &MyS`tring() Dim ,8Len*ICurrqVal 1h Count4My aartK3 'Very similar in pro@cess astructure toBeforeaboveSx= {()` If F< 1 Or>O I& ThG oTo ECheckor :=#+*? F 9MidB, , 1) = "@ StDR=GExit0 ForCIf8 N6>=40 3b!IDo WhileC< @(D/F 1 Then)HusAHa>pied aiof*#.$'mu3qu ickeranF :Athe #tselfL=D$Ė!D your w@through both @dimenss D%( AOr!@3o UB6d( , 1) s 2 C A = (r,< s [Not ""DY '@m: fin[  add i@oB^total tid(CStr(cl)886"= i i +a|iEI5y` @r Else]"QCH8EBmisRtentialgRO uR(1.`4m!<> n'bb1!`P!M  w @+X!ŧRS___A_____EaB "|ShX i>'`ir]EP,PRPPyP=5p:P Pr?PP @PRm_?V==3?` rPCkASoAo4w0ppPx01005  0p 00680X4 20@0 63@ @ 10HrU~| f 00 FPrU $`$`n%,1,x@ j$ @첀0* pHd VBAProject4@j = r J J< rstdole>stdole h%^*\G{00020430-C 0046}#2.0#0#C:\WINDOWS\syst em32\e2.__SRP_7_VBA_PROJECT{0dir__SRP_0ultjxlcverrZ_B_var_xlcverr] XlCVError IsErrorUtrueThenv ActSheetNm ActiveSheet%N ActCellAdd٠ ActiveCellAddress sctsheetnm@_B_var_sctsheetnmaUsedRangeArrayNumArrayFormula_WBGv _B_var_ubounddulRang  _B_var_On6 NumFormula_W9 (NumFormula_WS|Count0v(DelDate_frm_String TestVarArrayX TestVar. CurrStringZqh ErrorCheck9aMyLen ErrT  %xME@@<$<"