ࡱ>  H   !"#$%&'()*+,-./0123456789:;<=>?@ABCDEFGIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~Root Entry FQZxY Workbook _VBA_PROJECT_CUR" px Y VBApx pJ  s \pAndelin Ba= ThisWorkbook=ZK$<X@"1Arial1Arial1Arial1Arial1 Arial1 Arial1Arial1Arial1 Arial1Arial1Arial""#,##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_-;_-* "-"??_-;_-@_-                + ) , *        #           ` Sheet1,`is "Copyright ANDELIN ASSOCIATES, 1999 Amended: Saved as add-in:  as val_book.xlaXThis workbook contains .xls version of the validation procedures for FERT, MORT and MIGNCorrected error of reporting back a selection error on sched but giving wrong group; calculating rather than reading number of years and groupskchanged style of lines in the chart part, to allow (Again!) charts to be produced to max of 40 groups only Qchanged message from deaths to deal with deaths/migrants, put onError messages in_Apply standard rates only when group value is blank; allow entry of total births/deaths as zero   cc s  * m  dMbP?_*+%MTHP DeskJet 400 Series`~ od,,  HP DeskJet 400 SeriesLPT1: "d??U} $ *h,     ;   ~ @ ~ @  ~ @  ~ @   ~  @  ~  @   ~ @  ~ @                BX  * *&***& !"#$%&'()  ! " # $ % & '()L >@7 Sheet1 ThisWorkbookSheet1val_routines]_VBA_PROJECT: !"#$%&'(*,-./01235678:;<=?#xME (SLSS<N0{00020819-0000-0000-C000-000000000046}8(%HxAttribute VB_Name = "ThisWorkbook" Bas0{00020P819-0C$0046} |GlobalSpacFalse dCreatablPredeclaIdTru BExposeTemplateDerivBustomizD2#xME (SLSS<N0{00020820-0000-0000-C000-000000000046}8(%HxAttribute VB_Name = "She@et1" Bast0{00020820- C$0046} |Global!SpacFalse dCreatablPre declaIdTru BExposeTemplateDeriv$Bustom izD2 V0 b  J0` 08@ dH  "< HX hx  "  6TP      *0 `<x    L P0T  x 4p  z X h p x $(xME(<:<< <(`h``CGH&`& (HI* hI, I.  0 2`4`6 8`oc%`< `>`@`B`Doc`FK`H`J`L`N 13`PK`R`T`V`Xv_`Z`\U`^```bH p   I*  I,  pX ```@```````` `````<`` 8x% % %``@  8 x P  SCHE`es  ` ==I$  I"   I<  hI, %`\ ASM`Zor m`\`^```bup s`BRs p`L`@`4osen   !!IB 0  I4 P  - FId %`>$`f` h ,,I*  alesI.  < 88 b% 0p X  2 $ `<2No`l led `n roup`p `N or g`brd A`r\`t<`6ptio`vJ`x prov  I*  p JX % s`emal  H"0 ----h ---- 8"`$ ` 8   CEG   *&H*p<  "  2 @ 6   @ 8X    x         X h  p x @ 8 V  P ` p @P X ` hx 08@ H   VX ,  (HX x  8    .(D0 x  8        ^   & (H p @@ H h@@  B(p . *R8PX`p(8... L0   p x    hpxP              X h  p x   &  ,  Z!.`!!D! ! """ """"#### #  #   R# .@$ p$,$$N$  @%(X%,%%.% %&&&(&&&  & '' '''' '   '  2'( ,0( 2`( 4( .()D) `) p))H* X*`*x**X+h+p+x+ +  +  R++, , V@,, R - x- ---.... . . .../// / / `/ 00 \H0 00 20 >1.H1x11 1 1 1112222 2 2    ^3 `3,x33F3  @4(@4,h4 4.4 4445555  5 55~6666 6   6  66 ,6 2 7 4X7 .77d7 @8 P8`89 (9(09X9h9x9: :(:0: 8:  @: P: L`:: `: (; DH;; ;(; ; `< p< << < < < < <.<(=.0= `= h=  x= =.==.= = > > f(> f> B>  @?X?h? x??.??.?@  00@HP    A(A8A  @AHA `A.pAA.AA @ @0(@X@`@h@p@@0@@@@ BCC C C  8C.PC @@0A@@ABA0(BAXB`B CCC CC   D.(D XDhD:pDDDD DDE,EHEPEXE `EpEE EE .E EE:E0F8F@F BHFF,FFFF FFG GG .(G XGhG<pGGGG :GH,H@HHHPH XHhHxH HHHH H $H I I .0I `IpI<xIIII .IJ.J8J@JHJ PJ`JpJ xJJ .J JJ<J K(K0K 68KpK.xKKKK KKK KKL L 88L pL xL L &LM MN  NN NN O PO " @P :HP P PP PPPPQQ0QHQXQ `Q hQxQ   QQ "QQTQ:PR R R R JR0S8S@S HS  6PS :S  S <S<T HXT@T NT  N0U,U XU0V 28VpV xV V V V V V V VVV(WW W W W W W W X  X(X0XX  X6XYY Y  (Y:HY &Y@Y 0Y  Z (Z 8Z HZ  VXZ8ZHZ ,0[ `[x[ [ @[ [ \ \$(\P\ 4h\ \  :\](]>@] ] ] ] ] ]]   4]@^X^ ^`^ ^ ^ ^ ^&_@_P_,`_&_&_$_$`,0```  ````````a0a Ha `a a a a   aa a a b  b (b l0b  b b b b b "c 0c @Hcc (c c cc cc cd dd  d  (d @dPd`dhd pd  xd d  dd dde le " (f P0f Vf .fggg(g8g  Xg&pgTgbgXh>hh Jh 4h 0iHiXihi i  j j b(j pj k >k  &PkxkkHlXl `l  &hlll`mpm xm  m Nm mnn  n n nn Vn" (o0o @oPo 0`o,ooo  xo*Hpxpp  p p (pxp:Xqqqq qq<q  r ^r" prxr rrrrrr rrs d s  ssss "s<s t0t @tPt *Xt tt6tTt @u  P`u  Zuv@(v hv   bxvvvwR w xwwnw 4x@xHxPx  hx xxdx 4yHyPyXy  py>yy y 2y ( z Hz `zpz&zzX{h{ p{ x{{{ { J{ (|8|@| H|`|Pp||D|(}@}X} h} }}}x~~ ~ ~ ~~z~@P X` XhZ @ P hx0@ H P Xhx  ( 0   b@    .,n8XF X.h Є x     ^ ,0``b( 8 @ HX hpx     RȈ  .0 `xX ` h p  0 8@ H  VX,ȋ R.P Ȍ، hx      \  ,8h,x .B؏, TP  .  ؑ  ؒ    \ p, RГ.(Xp ` px( 8@H P  X  \h Ȗؖ2 ( 8,P2 .    ( 8HX(8 @ HP X  \h ș, d((F( .8 hxP` h p x 08 @ H  ^X ,НF  @X(, . 0@PXh(  0 8H~Xؠ     0 ,H 2x 4 .d0 p (Уhx        L  `( D    B 8P` p .  hBxB0BBBBBB0CBCC Ȧئ    .  0@:H 4ا,  (8H  PXp  $ Ш  . 0@<H :. ( 0@P X`x    (  h      . ( 8 bP  4     . P XX   `p x  p Px " Ȱ :а   0 8H(`     4 :X   6в Vp Px @ȳ , N8  6  <д . @ (` $ &:ص ,@H (   ȶ  Vض80 h     @ط 8 H X` 4x  :и((P   4`@ ^ع 8 P ` p&Ⱥ,غ&&0$X$,$ػ   0@P`p  ȼ    @    P` h p        Ⱦ  "   @8x (   ȿؿ     0@PX ` h x    `Option Explicit]@Number of population groups]@Number of years in model]-for diagnostics tables]8]`]@)(names of population groups, 1 is all gps,book is the name of the book being validated3Obooktype indicates whether a mortality of migration book = "mort" for mortality[fromauto is 1 if called by FILLIN, 0 if called from one of the workbooks direct by a punter?? On Error GoTo errorzone1]3!6 marker to show that a total has been given for a year]3D!marker to show ages given]]0]H]hP]@]p](]@]X]p]]p]@]]]0\ - data, shown in grey, for deselected option will be ignored when scenario is run - WARNING'bp *$dB@f h(j'"'$get rid of any charts in it l!n %get rid of any charts in the workbookv- h(p-e r!lB@t9 h(pin knotes$vB@H xB@ze23:n62$:B@|a23:a190$v%:B@|eseA@~$vB@Hc6:bb6$:.8 8 h!%n'7 number of years in model v!n '< number of population groups, excluding the all groups total4 ,mortKT hrchS honn  '<row index for error messages @  @$v! @ +&'gname(1) is overall name @.57FCHECK THAT THERE IS A FULL SET OF STANDARD ASMRs FOR MALES AND FEMALES Asched$vB@Hc13:c104$: h!%n'HC&No. of ASMR values filled in for males c109:c200$: h!%n'JD(No. of ASMR values filled in for femalesSc H\ < '< SCHED: Only H# standard rates provided for males  - FATAL ERROR <$v%( $ '$ start counting the fatal errorsdPkH J\ < '< SCHED: Only J% standard rates provided for females  - FATAL ERROR <$v%( $ '$dk+ @  @ $h @ $$: h!%n'H0No. of ASMR values filled in for males for group @ $a @+ 1marker for group specific standard ASMRs provided H\ < '<SCHED: Option chosen but only H# standard rates provided for males  in population group < @ $&> on Sched sheet - FATAL ERROR <$v%( $ '$dpkhd` H < '<SCHED: H# standard rates provided for males  in population group < @ $& b <$v%( " '"dkkm @ $ @ $$: h!%n'J2No. of ASMR values filled in for females for group @ $a&1marker for group specific standard ASMRs provided J\ < '<SCHED: Option chosen but only J% standard rates provided for females  in population group < @ $&> on Sched sheet - FATAL ERROR <$v%( $ '$dkd J < '<SCHED: J% standard rates provided for females  in population group < @ $& b <$v%( " '"dkk @O-------------------------------------------------------------------------------&CHECK TOTAL GIVEN OPTION on each sheet @  @ $vB@H B   B $!'L  B+0  B $!'Ntotal given in row 122 totgive says if there's a numeric value in row 12 N$'Pd'P @ $ @ +k B $!'6'!PROVIDE option indicator in row 7/ 6a @G'Rj 2 record that total given in y for all groups total P= B+0d < '<For < @$& > in year  L 'provide total' option chosen "but no data provided - FATAL ERROR <$v%(W $ '$k0 d( P] < '<For < @$& > in year  L# 'provide total' option NOT chosen  b <$v%(p " '"dh k` kX BwX CHECK FOR AGE-SEX VALUES THAT ARE SUPPOSED TO HAVE BEEN ENTERED (OR NOT SUPPOSED TO BE)decide how many should be there ,mortG'TcG'Tj B  B $!'L TKTmort B $ B $$: h!%n'V# B $5 B $$: h!%n'Xn of female cells filledKTmig B $ B $$: h!%n'V$ B $3 B $$: h!%n'Xn of female cells filledn B $!'Z)chosen to provide age sex info? Za @ $ @ + ,mig%check for profiling B $a B $aGprofiling chosen B @ \A \KT $ '$ < '< For < @$& > in year  L7 provide AND trend total have been chosen - FATAL ERROR <$v%(KT $ '$ < '< For < @$& > in year  LP profiling of total chosen but no (or incomplete) profile provided - FATAL ERROR <$v%(KT < '< For < @$& > in year  LA profile chosen by providing total AND age/sex migrants - WARNING <$v%( " '" @ $ @ + @ $ @ + @ $ @ +Fcheck if profile provided for this year, and, if so, full set is thereP V V T < '< $ '$$$For < @$& > in year  L- you opted to enter age/sex values, but only  V" filled in for males - FATAL ERROR <$v%(k X X T  < '< $ '$$$For < @$& > in year  L- you opted to enter age/sex values, but only  X$ filled in for females - FATAL ERROR <$v%(dkn$Fgo beyond these other checks, which look for full set of age/sex belowk(k  V T  < '< $ '$For < @$& > in year  L- you opted to enter age/sex values, but only  V" filled in for males - FATAL ERROR <$v%(k0 X T  < '< $ '$For < @$& > in year  L- you opted to enter age/sex values, but only  X$ filled in for females - FATAL ERROR <$v%(d@k80d( V  < '<For < @$& > in year  L/ the option to enter age/sex values NOT chosen  b <$v%( " '"d`kX X  < '<For < @$& > in year  L+ option to enter age/sex values NOT chosen  b <$v%( " '"dkk BCHECK TREND TOTAL OPTION CHOSEN B  B @ $v%!'L B @ $v%!a>!trend total option chosen if ="a" @ $ @ + D B D$0Gj:#check for a previous total PROVIDED D < '<For < @$& > in year  L trend total option chosen )but no previous value given - FATAL ERROR <$v%( $ '$0 B$0 < '<For < @$&> year  L trend total option chosen +AND a value has been provided for this year b <$v%( " '"d@k8d0k( BL----------------------------------------------------------------------------(CHECK THE DIFFERENTIALS OPTIONS AND DATA B  B @ $v%!'L B+0B B @ $v%!'N4total diff'l given in row 66:totgive indicates a numeric (possibly zero total provided) N$G'PcG'Pj< B @ $v%!'6 6a @ $  @ +  P B+0d < '<For < @$&> year  L" total differential option chosen 2but not provided in the data section - FATAL ERROR <$v%( $ '$kd P < '<For < @$&> year  L& total differential option NOT chosen  b <$v%( " '"d@k8k0 B,CHECK TREND TOTAL DIFFERENTIAL OPTION CHOSEN B  B @ $v%!'L= B @ $v%!a -trend option chosen if row 61 year value ="a" @ $  @ +  D B D$0Gj:#check for a previous total PROVIDED D < '<For < @$& > in year  L7 the option was chosen to trend the total differential )but no previous value given - FATAL ERROR <$v%( $ '$ B$0 < '<For < @$& > in year  L7 the option was chosen to trend the total differential +AND a value has been provided for this year b <$v%( " '"dkdkx BK CHECK THAT THERE ARE SOME VALUES GIVEN FOR AGE SEX DIFFERENTIALS IF CHOSEN B  @ $vB@H B $!'L> B $!'6'4 option chosen to give values for age/sex diffs ="a" D B $V B $$: h!%nY B $k B $$: h!%n '^ Lentries gives number of non-blank entries made for this year/age combination 6a ^ < '<For < @$& > in year  L6 the option was chosen to enter age-sex differentials <BUT no values have been provided for this year - FATAL ERROR <$v%( $ '$dh @ $  @ + k0d( ^ < '<For < @$& > in year  L: the option to enter age-sex differentials was NOT chosen  b <$v%( " '"dPkHk@ BZ ======== CHECK THAT IF TREND AGE/SEX VALUES CHOSEN THERE ARE SOME VALUES BEFOREHAND ===== B > B  @ $v%! B+2A#provide age/sex diffs option values B B ? B  @ $v%!a 7compare with option to provide given in a previous year @ $  @ + '` F B F$2aG'`j F ` < '<For < @$& > in year  L6 the option to trend age/sex differentials was chosen BBUT no values have been provided for a previous year - FATAL ERROR <$v%( $ '$d( k d k BW=========================== DO CHECK ON DIRECT PROVISION OF SMR ====================== B  B @ $v%!'L B+0w B @ $v%!'N5smr given in row 119:totgive indicates a numeric (possibly zero total provided) N$G'PcG'Pjs B @ $v%!'6 6a @ $  @ +  P B+0d < '<For < @$&> year  L provide SMR option chosen 2but not provided in the data section - FATAL ERROR <$v%( $ '$k( d P < '<For < @$&> year  L provide SMR option NOT chosen  b <$v%( " '"dh k` kX BCHECK TREND SMR OPTION B  B @ $v%!'Lt B @ $v%!a .trend option chosen if row 116 year value ="a" @ $  @ +  D B s D @ $v%!aGjV#check for a previous total PROVIDED D < '<For < @$& > in year  L( the option was chosen to trend the SMR )but no previous value given - FATAL ERROR <$v%( $ '$w B @ $v%'N N$ < '<For < @$& > in year  L7 the option was chosen to trend the total differential  b <$v%( " '"dkdk B @F ================DO FINAL CHECK ON COMBINATION OF OPTIONS CHOSEN====== $ " < ,AY ============================ CHECKING COMPLETE - GIVE NUMBER OF FATAL ERRORS =========== $$v%( .G|j$,don't leave any messages if called by FILLIN "$v, $'LTthere are fatal errorsVChecks now complete - correct sources of the fatal errors listed and rerun validation Data ValidationA@$vB@Ha1$:B@H|pKT fill in diagnostics tablenotes$vB@H xB@z ,mortKT $  $$:B@HS $  $$:B@Hn  ( ,mortKT$  $$:B@HS8$  $$:B@Hn  (a1$:B@H`======================Produce a chart of the age/sex values if there are no errors==============`=============and if the call has not been made by fillin ======================================= <REDO formatting, just in case it's been messed up by copying 4  4$vB@H 4$vB@zage/sex values ,mort$   $$:B@Hd($  $$:B@Hk? !C@t  l  0C@999 Data entry9 @9(Value must be greater than or equal to 0999q@ !ie bC@t99999 !Arial99q>#,##0 ( ,mort#$5  $$:B@HdX>$$3  $$:B@Hk >  l  0C@ Data entry910(Value must be greater than or equal to 099q !e = C@t  l  0C@999 Data entry9999 !Arial99q<#,##0 ( total events $   $$:B@H(Value must be greater than or equal to 099q !Arial99q@;#,##0 (age/sex differentialsD$V  $$:B@H !C@t  l  010C@999= .ErrorTitle = "Data entry (age/sex diff'ls)"9#The value must lie between 0 and 10999q9 !Arial99q9#,##0.00 (Y$k  $$:B@H !C@t  l  010C@999< .ErrorTitle = "Data entry (age/sex diff'ls)"9#The value must lie between 0 and 10999qP8 !Arial99q 8#,##0.00 (B$B  $$:B@H !C@t  l  0200C@999 4 .ErrorTitle = "Data entry (total diff'ls)"9$The value must lie between 0 and 200999q6 !Arial99q6#,##0.00 (a1$:B@H 4$vB@ 4 now format the schedule sheetsched$vB@Hsched$vB@z $h  $$:B@H !C@t  l  01000C@999 ( .ErrorTitle = "Data entry - ASMR"9%The value must lie between 0 and 1000999q4 !Arial99q4#,##0.0 (m$  $$:B@H !C@t  l  01000C@999/ .ErrorTitle = "Data entry - ASMR"9%The value must lie between 0 and 1000999qp3 !Arial99q@3#,##0.0 (a1$:B@Hsched$vB@ * ,A8#call the routine below that does itn2 MaleRates$vB@H "   _Checks now complete. No fatal errors were found, but please read warnings on the notes sheets. $ $'Charts of schedules have been produced. $ $0Save this workbook with a name of your choosing. Data ValidationA@dX1   0Checks now complete. No fatal errors were found $ $'Charts of schedules have been produced. $ $0Save this workbook with a name of your choosing. Data ValidationA@kX0 h(j|@080/Mort_Valid Module Error - VBA Error message:- $ $ $mPlease contact John Andelin on 01274 225750 or johnandelin@gmail.com to deal with this unexpected problem. POPGROUPA@o@/J##########################################################################3produce a chart of asmr's for each population group h(j]@x]](]@X]p]]]D0H]D`]]- *$dB@fnotes$vB@z v!n'  ' \0\ XB$v%!'+.the overall differential applied ot all groups c60$v%:aG'jcheck that option chosen @   7set the start row for picking up asmr data from "Sched" 'd+l'k+ \newborn and 0 to 90 inclusive    @ $v%! @  + @ @ $v! @ notes$v%( @ $v!+ @ notes$v%(B @ $v%!'=the adjuster for this group c60 @ $v%:aG'js @ $v%aG'jHdirect entry of smrGdoes this group require its values to be taken from the standard rates? @ Sched$v%!' $G  $ @  +j_set to standard if not set @  $   @  + ,mort/set age groups for mortality LT'LT'VLT   'W\LT'n8(5 (from the schedule and 1st year differentials only; 8counts and SMR will take effect when used in a forecast)'dset up age groups for migration LT'LT'LLT   'M\LT'n'1 (from the schedule 1st year differentials only; :counts and SMigR will take effect when used in a forecast)'kp& C  '/!set row for age sex differentialsd &X  'k& $v%!' c62$v%:aG'j  @ $v%!' c62 @ $v%:aG'j @  $   @  +k%   @P=========================PUT THE DATA INTO THE NOTES SHEET AND MAKE CHARTS======$v%!'*e.g. Bradford MDC$v%! $v%!'&rate type is migration , mortality etc  \ @  :put values into temp_array for fast download into workbook @  $  @+ @  G'<cG,'<jnotes$vB@H <$ <[   $$:.8  8(# drop the data < $ <[   $$:.sched$vB@Hb13:b104$:.' age labels +set up variables to put into chart headings MaleRates'Males'd! FemaleRates'Females'kP!.SET UP FOR CHOICE OF COLOUR OR BLACK AND WHITE](`((OALLOW UP TO 10 LINES] Wltype is line marker type, strength is size of marker, col is colour of line and marker   +  +% no marker +)Triangular markers + + +"Square markers with an X +' Circular markers +$Short bar markers +%Long bar markers +'circular markers  +%Square markers with a +  +& no markers++++++++yellow2 + off green/ +grey  (  $ +  $ +  $ +  lB@  9  C@ $ (&C@"9*9,  $  $ Age schedule of rates per 1,000   5.!0(d 5.!(True5.!(254!6(8 7:(< @ B7>(*Age @ B7>!D!0(d F B7>(*Rate per 1,000 population F B7>!D!0(dq  set bolder lines  LT H' LT J'LT L'(LT N'n  %:B@H !P $98 9R V9Tq  !XB@H |x6Schedule charting Module Error - VBA Error message:- $ $ $mPlease contact John Andelin on 01274 225750 or johnandelin@gmail.com to deal with this unexpected problem. POPGROUPA@oxf +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Ithis routine checks to make sure that only one option chosen for any yearOexcept for migration profiling, in which case provide total AND provide age-sex'allowed for any group, incl. all groups] ] ] ( ]@ X ]p    B  B $v%!'L B $v%!aG'\cG'\j[ Give total B $v%!aG'^cG'^j[Give (Age)/sex breakdown \ ^ 'Z B $v%!aG Z 'Zj Z ,mig1( sum of options chosen must be 1 or less .check to see that conditions met for profiling if a migrants file $ '$ < '<For <$& > in year  L3 more than one option has been chosen - FATAL ERROR <$v%(k0 @  @ '4 B 4$v%!aG'`cG'`j^Give total for pop group B 4$v%!aG'bcG'bj^%Give (Age)/sex breakdown for popgroup ` b 'Z B 4$v%!aG Z 'Zj \ ` b < '<For < @ $& > in year  L. values given BUT already given for all groups) ALL GROUPS VALUES will be used - WARNING <$v%( " '"dk ^ b ` < '<For < @ $& > in year  L. values given BUT already given for all groups) ALL GROUPS VALUES will be used - WARNING <$v%( " '"dk Z ,mig H sum of options chosen must be 1 or less unless migration profile wanted $ '$ < '<For < @ $& > in year  L3 more than one option has been chosen - FATAL ERROR <$v%(k @ Bo`O=============================================================================== ]p  B '> 4$vB@H >$a >$a'dprovide and trend in same year|hk` >$ >$$: h!%n$ >$3 >$$: h!%n'dOK profile there so return|k B f > "check for profile in previous year f$ f$$: h!%n$ f$3 f$$: h!%n'd,OK profile there in previous year so return| d k fkh 'd -if gets to here then failed to find a profileo X++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ j ] ]3@ ]3x ] ]    ]( ]@ X p 6ticks]  another tick!] \ - data, shown in grey, for deselected option will be ignored when scenario is run - WARNING'b h(j *$dB@f'$'"get rid of any charts in it l!n %get rid of any charts in the workbook h(p r!lB@t h(pk '<row index for error messages$vB@Hc6:bb6$:.8 8 h!%n'7 number of years in model v!n '< number of population groups, excluding the all groups total   a23:a500$v%:B@|.(CLEAR THE AREA TO HOLD THE ERROR LISTINGS ============================== READ IN THE GROUP NAMES =========================== @  @ $v! @+&'gname(1) is the overall name @[============================ CHECK THE SCHED SHEET ========================================$vB@Hc14:c48$:.8 8 h!%|'z 8 h!%n'~=6how many valid entries are there for the standard asfr ~# < '< SCHED: Only ~ standard rates provided  - FATAL ERROR <$v%( $ '$ start counting the fatal errorsdk+2$v%!'  < '<CSCHED: No standard boys per 1,000 girls rate provided - FATAL ERROR <$v%( $ '$ start counting the fatal errorsdk+$vB@Hd8$:a"check that mixed parentages add up   8 '   $G +j $ ,$$:.8 8 h!%|'  < '<8  $v%!'@DSCHED: Mixed parentage births sum of distribution factors for group $& is   IT MUST BE 1 - FATAL ERROR <$v%( $ '$dk d check for grey datac57$:!!8 < '<#SCHED: mixed parentage not selected b <$v%( " '"kk @  @ ' @ '<value used for group name (i.e. group name 1 is for overall)$vB@H $!'r#( does group g have its own set of ASFRs? $0 $$:.8 8 h!%n' ra @+ # < '<SCHED: For group < $&#> you have said you want to put in a set of ASFRs. You have only  0 values entered. There must be 35 - FATAL ERROR <$v%( $ '$dkd  < '<SCHED: For group < $&> you have entered a set of ASFRs b <$v%( " '"dkk @ $v%!'t/+ does group g have its own boy/girls ratio?2 @ $v%!'l0.the value given for the group's boy:girl value l$G'lj ta @+ l < '<SCHED: For group < $&> you opted to put in <its own boy/girls ratio. You have not done so - FATAL ERROR <$v%( $ '$d}k}d} l < '<SCHED: For group < $&> you have entered a boys per  thousand girls rate b <$v%( " '"d}k}k} @\==================== NOW MOVE ON TO CHECKING THE ANNUAL DIFFERENCES SHEETS =============== @  'CHECK TOTAL, or SEX TOTALS GIVEN OPTION B  B @ $v%!'L B @ $v%!'62< the "provide a total" option chosen indicator = 1 if chosen B+0  B @ $v%!'N3&tot is the value of the total provided N$G'PcG'PjB See note in mort above 6a @ $ @ + P B+0dpz < '<For < @$&> year  L total births option chosen 2but not provided in the data section - FATAL ERROR <$v%( $ '$kydy P < '<For < @$&> year  L total births option NOT chosen 0BUT there's a value provided in the data section b <$v%( " '"dxkxkx BX CHECK FOR AGE-SEX VALUES THAT ARE SUPPOSED TO HAVE BEEN ENTERED (OR NOT SUPPOSED TO BE) B  B @ $v%!'L  B @ $v%!'n4-totb is the value of the total males provided B @ $v%!'p4/totg is the value of the total females provided n$'ndv nh㈵>'nkvkv p$'pdv ph㈵>'pkpvkhv B @ $v%!'v7Option to provide births by sex va @ $ @ + n p < '< $ '$For < @$&> year  L, you opted to enter sex values, but males =  n and females =  p - FATAL ERROR <$v%(dtktdt n p  < '<For < @$&> year  L* option to enter age/sex values NOT chosen b <$v%( " '"dsksks BO==================== CHECK TREND TOTAL OPTION CHOSEN ========================== B  B @ $v%!'L B @ $v%!a?trend option chosen if = 1 @ $ @ + D B D$0Gj D < '<For < @$&> year  L trend total option chosen )but no previous value given - FATAL ERROR <$v%( $ '$q B$0 < '<For @$& year  L trend total option chosen +AND a value has been provided for this year b <$v%( " '"dpkpdpkp BU ============================ CHECK THE DIFFERENTIALS OPTIONS AND DATA ============== PROVIDE TOTAL B  B @ $v%!'L B+0 B @ $v%!'N( The value for total difftl in this year N$G'PcG'PjCsee above elsewhere B @ $v%!'6M Option to put in a total diffntl chosen if =a, direct TFR now done elsewhere 6a @ $ @ + P B+0d n < '<For < @$&> year  L" total differential option chosen 2but not provided in the data section - FATAL ERROR <$v%( $ '$k@md8m P < '<For < @$&> year  L& total differential option NOT chosen 0BUT there's a value provided in the data section b <$v%( " '"d@lk8lk0l BU ==================== CHECK TREND TOTAL DIFFERENTIAL OPTION CHOSEN ================== TREND TOTAL B  B @ $v%!'L B @ $v%!a@trend total option chosen @ $ @ + D B D$0Gj D < '<For < @$&> year  L( trend total differential option chosen )but no previous value given - FATAL ERROR <$v%( $ '$i B$0 < '<For @$& year  L( trend total differential option chosen  b <$v%( " '"dhkhdhkh BV ======== CHECK THAT IF TREND AGE VALUES CHOSEN THERE ARE SOME VALUES BEFOREHAND ===== TREND AGE B  B  @ $v%! B+2 B B  B @ $v%!'L B  @ $v%!a @ $ @ +'` F B F$2aG'`j F ` < '<For < @$&> year  L) 'trend age differentials' option chosen BBUT no values have been provided for a previous year - FATAL ERROR <$v%( $ '$dekedeke BV ======= CHECK THAT THERE ARE SOME VALUES GIVEN FOR AGE DIFFERENTIALS IF CHOSEN ======PROVIDE AGE VALUES B  B @ $v%!'L B @ $v%!'641Option to provide age diffntl values chosen if =1 @ $vB@H B 'D D$$ D$$:.8 8 h!%n'^?)number of entries provided for age groups 6a @ $ @ + ^ < '<For < @$&> year  L) 'enter age differentials' option chosen <AND no values have been provided for this year - FATAL ERROR <$v%( $ '$dbkbdb ^ < '<For @$& year  L- 'enter age differentials' option NOT chosen  b <$v%( " '"dakaka BW=========================== DO CHECK ON DIRECT PROVISION OF TFR ====================== B  B @ $v%!'L B+0/ B @ $v%!'N4tfr given in row 47:totgive indicates a numeric (possibly zero total provided) N$G'PcG'Pj+ B @ $v%!'6 6a @ $ @ + P B+0d_ < '<For < @$&> year  L provide TFR option chosen 2but not provided in the data section - FATAL ERROR <$v%( $ '$k_d^ P < '<For < @$&> year  L provide TFR option NOT chosen  b <$v%( " '"d@^k8^k0^ BCHECK TREND TFR OPTION B  B @ $v%!'L, B @ $v%!a -trend option chosen if row 44 year value ="a" @ $ @ + D B + D @ $v%!aGjU#check for a previous total PROVIDED D < '<For < @$& > in year  L( the option was chosen to trend the TFR )but no previous value given - FATAL ERROR <$v%( $ '$[/ B @ $v%'x x$ < '<For < @$& > in year  L7 the option was chosen to trend the total differential  b <$v%( " '"dZkZdZkZ B @F ================DO FINAL CHECK ON COMBINATION OF OPTIONS CHOSEN====== $ " <fertAY ============================ CHECKING COMPLETE - GIVE NUMBER OF FATAL ERRORS =========== $$v%( .G|j$,don't leave any messages if called by FILLIN<REDO formatting, just in case it's been messed up by copying 4  4$vB@H 4$vB@zage/sex values $  $$:B@H !Arial99qPW#,##0 ($$  $$:B@H !C@t  l  020C@999 . .ErrorTitle = "Data entry (diff'ls)"9#The value must lie between 0 and 20999qV !Arial99qU#,##0.00 ( 4$vB@a1$:B@H 4 now format the schedule sheetsched$vB@Hsched$vB@z$0  $$:B@H !C@t  l  01000C@9994 .ErrorTitle = "Data entry - fert rates"9%The value must lie between 0 and 1000999qT !Arial99qS#,##0.0 (a1$:B@HS "$v%( $'LTthere are fatal errorsVChecks now complete - correct sources of the fatal errors listed and rerun validation Data ValidationA@notes$vB@Hb19$:B@He23:o62$:B@||RKT put diagnostics outnotes$vB@H$  $$:.8  8(b19$:B@H\======================Produce a chart of the age values if there are no errors============== *A+#call the routine below that does itn@QRates$vB@H "   _Checks now complete. No fatal errors were found, but please read warnings on the notes sheets. $ $/A chart of the age schedules has been produced. $ $0Save this workbook with a name of your choosing. Data ValidationA@dO   0Checks now complete. No fatal errors were found $ $/A chart of the age schedules has been produced. $ $0Save this workbook with a name of your choosing. Data ValidationA@kN h(j|NjN/Fert_Valid Module Error - VBA Error message:- $ $ $mPlease contact John Andelin on 01274 225750 or johnandelin@gmail.com to deal with this unexpected problem. POPGROUPA@oMJ########################################################################## 3produce a chart of asfr's for each population group h(j]@x] *$dB@fsched$vB@Hb14:b48$:." age labelsnotes$vB@z v!n'  ' ## $v%!'.the overall differential applied to all groups $v%aG'jbut only if not greyed out @ /check if standard rates to be applied for g > 0 @  @ sched$v%!'=#set to a if group's values providedkJ @ $v%!'1#the overall adjuster for this group  @ $v%aG'j #actual ages 15 to 49   ',*the age group for picking up differentials   @ $v%! @ + @ @ $v! @ notes$v%( aG $ @ +jset to standard if not set @ $   @ +multiply by overall adjusters  $v%!' $v%aG'j   @ $v%!'  @ $v%aG'j @ $   @ +kG  @P=========================PUT THE DATA INTO THE NOTES SHEET AND MAKE CHARTS======$v%!'*e.g. Bradford MDC$v%!'rate type is fertility # @  :put values into temp_array for fast download into workbook @ $  @+ @ '<notes$vB@H <$ <"   $$:.8  8(# drop the data < $ <"   $$:.sched$vB@Hb14:b48$:.& age labelsRates'.SET UP FOR CHOICE OF COLOUR OR BLACK AND WHITE]((P(9ALLOW UP TO 40 LINESWltype is line marker type, strength is size of marker, col is colour of line and marker   +  +% no marker +)Triangular markers + + +"Square markers with an X +' Circular markers +$Short bar markers +%Long bar markers +'circular markers  +%Square markers with a +  +)picture markers++++++++yellow2 + off green/ +grey  (  $ +  $ +  $ +  lB@  9  C@ $ (&C@"9*9,   $  $AAge schedule of rates per 1,000 women (from the schedule and 1st Qyear differentials only; counts and TFR will take effect when used in a forecast)5.!0(d 5.!(True5.!(254!6(8 7:(< @ B7>(*Age @ B7>!D!0(d F B7>(*Rate per 1,000 population F B7>!D!0(dq>  set bolder lines  LT H' LT J'LT L'(LT N'n >  %:B@H !P $98 9R V9Tq=  !XB@Ho= !C@t  l  0C@999 Data entry99(Value must be greater than or equal to 0999q<>շAttribute VB_Name = "val_routines" 'Option Expl@icit Privang As Integer 'Numbof populargps  fy f 4years in model Dim diags1(*)3,2  'fornostics tablewEn_war`ningsG`n`fatal Tgn(41>S%w' s q, 1 is all gx Subgrt_id(@book, type, fromauto)  the 22i be^ "dG"dic s wher aA9ality"m8igr%mort" A @G#!2f c2ed by FILLIN, 0 iC one)w(orksarect a pu@r@ O@rr@i@GoTo ez51I jtot_given(5]Ɩ markto show that: @ohas been& 6a EageH{Et  ssh opnG myrangeRG ir@.G, ico˞ , @2DF, jH`asmrM AF= iA%2+VarianA-u_opRn_req2m_pres`cMAS_6! 8, prof! sult en'x* okRmsg_gre/ Ci - dz,BSnA@aeXselaiocwi@be ign orvn scen,oorun WARNING.Wkb).ActbAp|.Scr@aUp a= False= 0d#'get ridzany chartaIf "C.Co{ >Tho w!%! DisplayAle BUe .D`+t# Tru$End IfSheets("notq ).S5c,e".Un@Lt9C"{("e23:n62").ClCont΁#Q 2).da`a190Calc!* 3S5E= d c6:bb6"8= i-E(sFunpo n(t) ' n|Ay"gЂc T - 3kN, excluda) r Ea b Caser1b"k1A1LReadT0 fnvgy4aEf1!15#)"(2( %c S`= 23 '``indexjp messh)F@D3 + 3K 3gB2)(g).8(tᐉover!!Next gU'CHECK THAT@ERE IS A FULL SET OF STANDARD ASMRs FOR MALE`ND FE/"sched.QiM )`9)-13:c104"))o.Ju ^fU`UabFk0t(09:c20:femR3< 92 aRs b+ A1t0CCell s(`,).ValurS`D:@ly " & 3&standa8rd @PKvid0_7a- FATAL `ERROR\5D=& 'prt c`=d,$Pw63uWF  b=cs(FP" AJ)Tu?1s?C]o,e,C$ 13, gC),R 04%)Q'NO5O5srKݷ 7,`+ gQI"a"/a YA;'dQ+2 specific'G.G%M%3&o8owo8a8chos`0я999[ (Lu  SWPTa= nfatal = @+ 1 0Else TEnd If &JIf asmrM > 0 Then `firow}{Sheets(2).Cells(), 1). Value:"SCHED: " &m& standard rates provided for mal " _GI!in population group <2gname(g W):msg_grey&n_warningsWw9F/@ApplicY. WorksFuncb.Count(Range(AS109, 23), 200))G'No. of ASMR vA_s fill@UJVfeVK7, 3Og)@,"a" 'markerh[specific ssGmF < 92C*֛Op…chosen but PonlyF@"-EK)">0 Sch@c| - FATAL ERROR"KJ%a?RA F27, Ab?,_0^00b?F*iNe0xt g>*'-G?' 'CK TOMGIVEN OPTIONReach Q+Fg ?1 To nB>Select5AyFyiyearcx6, tot_given(y!0*12, 2 yΧ'Bf 12( @ says if there's a num? ?de<NOT  & OLix4]"ke fFOR AGE-SEX VALUES THAT ARE SUPPOSEDPh HAVE BE`hENTER@(YBE's0dphmany koulxd baZs#b@ooktyprmortn_reRqQG9 a 6 jCjfjo CaseQr19 @ !m_pr = o4s3_ptA1fn#oo(3553 'n c 16]pi oo n (1 3 ooo35@yonE~nd Select AS_mark = Cells(9, y + 2).Value 'chosen to provide age sex@ info?If "a" Then<diags1(g - `1, 4)v+ 1 4Wbooktype/"migZeck forfiling :s>8ځ"iOOry7 [ v'v) ,.CXallC_e(y, g,J_result) Case?7 nfatalr pirow  Sheets(@UA , 1= "Fd<" & gname(g) _4"& ">@@ year iBFAND Htre@to8have beĨ- FATAL @ERROR"Q0$?QMY?QRQofy'&but no (6incomplete)QilR/d`v7+Y2+BM= ?N?N?N%%e$ bydPd'aQ/ rants $WARNINGxOn_warn st>!5刺@ |+3/@@`ifM!this, and, so, fu@set there m`es > 0 A<> nqԏE cU_VG?  Yr& Vyou opted- enXterA~-vas, QBonlyYg &  %Glinzma^lZEpIfp f{ 0 Then irowoi1 Sheets(2).Cells(;, 1). ValueL"For <" & gname(g)"> in year " _*Q& i&option to enter age/sex v6s NOT chosen!+Ec9CNext  'CHECK TREND TOTAL OPTION CHOS,ENGATyV2 pTo nLJ=!eg + f6, yBfGr 8, 2 yHsa" 'tretotal e^if ="a"KAdiags1@(g - 1@)0<2 1A<yO1@$_given(y2A  GoJmark8 'check fa @`vious2PROVIDEDCk2O@+fAfakeW4n`lbut no"MBk "*.FARE0RROR<@nfa( 0:&P7g7/A~,߁,,ؑ#>-+ݐ A@}a h as be@pr@ovidedTthisB!7hƁq5!`-?$GHE DIFFE@TIALSS +D@V!>bڶ,V0c  `(XzUTrTdiff'l6rx66''19`tdicatP}a numeric (possibly zeroMU%)G7IsE`mpty(7Fa@^= 1 '"a= HopnO60cjc"dq5a2b3 .UD #`_%cQ?/IoIowO_G/JI%e{tiz` PJ#at0aI)the Bd^ sec2- abHM P,!es_P/  0B ߯d/7do_?sU_>_R`F ^᭏?T_1>D+R2_S /?dhU?:n79??? +? i1= b ?9?9?9UpPw$· the total diffe renti`" _ & "but no previous value given - FATAL ERRODR"nfa=" + 1 $ @mark9::I f_g(y) = 1 Then)irow"dSheets(2).Cells(, 1).Vi= "For <" & gname(g)(> in year i option was chosto td-6A*AND ah *be)prov@ided f]thisGFmsg _greyn_warningsMOElseEnd IfN Q Next * ' CHEC@K THATERE ASOME VALUES GIVEN FOR AGE SEX DIFF@ NTIALS IFOSENwy@;o nąg + Select&Cu= Ì6, y   opn@ 2 q' DGuspage/sexf"arieAlApplicaA.WorksFuncBCount(Range("8",8&2)\+ 89 107E))$ aY'as number ofn-blank %madeK[/$ combinG '`1' = 0B= bd}o(#-%Gs3E BUT!s have ?x% ӊdi@ags2(g` 1, 5N21 @>@@`@@D @A9?!8NOTUe& ctOcd13 =a' =`]TREp̀_/_aCH^bbBEbEHat" CaT 5y``g6^'y ]_6ox2 Tj! +?s(63Wa"M 'Spare withi$ aYnaW @j=y:4: !okm`r0qj2zy?? Wjq^ l!"ja2@u///O@on&W`@4@laa6 aCaC ?EGs U'4Fo== DOGONRECT PROVISIOF SMRVJ%F<ťt#$4< Hh43Ps(112 Ky 'smrtP@W'`1A AdAaQeric (possibly zerDd`6IsE`mpty(;FalseB7A?q 3 5, W q>ҹB$S{1 ./M04fiO0-Iets(2).Cells(irow, 1).Value = "For <" & gname(g)("> year " _ (& iP&Tprovide SMR option chosenF=but not Dd in the data secN- FATAL ERROR"nf5l+ 1End If ElseIf totgiv1 ThenȀT#SSheNOTgsmsg_greyV=n_warningsi@\qM@&'Nex$t * A'CHECK TRENDOPTION Ay,2 To nL7= ig + 6, yBCt@ T11@ 2@ ya"P'trehif @ @Cv9}"afadiags2(g - 1,Nr5B$2^o >5 'F Go/mark9a 'check faeviousoal PROVIDEDo=2/>s6<``۫ɪd;waspto >!Ufg$cAn<[^ 2:(k A[9<)bIsEmpty( )@Fa`>5!N߿5W+`*>Ԅ855@Tdiff2ewti& ߩߩ7ToT 6UgU = DO FIN|CWON 0COMB!W OF WS CHOSENcCall0@ops(C*$, ge, 8booktype`&?=p_INGq PLETE1GIVE NUMBEvR! 2S c =&2~3`.[s S.f romau"=  Exit Sub'don' t leaany messag(es ^c@ed by`LLITj>UQ4SelЕ Case C\100WF0re a0#erprors1MsgBox "CYsPw com pletecorrsourcp op list aPmrerun Ki!20, vbCriti , "DA?c4,o Range("a1"t). W0q['fi*p^asnos iabl,e"es Ave.Unt %/ "mortO¡+9), 0yngm3)?DBC%-10y?14?aG? 3!Vas\~/|ct/ s/ 5 8 F9" ҃_03^'SProduc BaYart = K/hsex+Between, ula1:="0j"21^IgnoreBlaDnkTrudInDropdownputTitle"'7.ErrorData entry (age/sex diff0'ls)}A.InMess I B The value must lie bCQ 0 and aLShowFalsK  U>EOwAHFoFn6NamXArialUISiz 8O&GD66IAtt#ettt)htat2r.?t a?t}C gFo Gt8ttotalss! ss%t .t/tAt"t= t" t_D !"a1"Sheets(sh) .ProtNe`xt sh' now fcthe schedu`sa k"2o # =Unp i134g"AXF.tHFFTFnF_n4F[GF- ASMRm_x_F@(_TF10CoF 0 ThenvMsgBox "Checks now complete. No fatal errors were found, but 'ase read doneJ s." & Chr(13)- _&Carts of sc hedulhave been produced/'.'Sthi\orkbook with a name3your choos.", vbInformation, 3"D@ Valid"`Else?J3?J?J JIfI}DApplicW.Scr@3UpZng = TruXExit Sub zone1:A^Mort_j BM@le E - VBAmessage:-+{(@ɨɭ"Plecontact John Andelin @01274 225750 Por ja@gmail.ـ?to de@¥BU unexp|ed!^blemRCrit #liR, "POPGROUP@Q"!,d#'#?'"! Srch t_m`,mig(kktype)x '$um"!ma smr'sr each popul< groufp7?FaflDim () As S@xle, temp_Arr ay'tit2tr nshInteger, gsex`irow_diffG Mgp3dj_cHallst Kard_y!Variant, aas_ orga Y)ɢ#, _T:"L& &idGmyrangJeR!, @|a_.JBSgekr#`re_w0idthcOnaĀGoTo#_+b\ Wds]).Activ`c%noUn_tCL= .CDb- 3S Re.p{o ng, 1 92>261T/kngt#U(3).Cells(66 , Sue 'thqver#,pnti`JaYIot ;s 0 Or1("c60") <> "a"* 1S'FckӐlopAxys5CFU`TCS:=2p a 'set24r t =bJpick1B upBF2* from "2"q1HA[1k08 Os E92 'newborn AP`f90 inclusivBU&@, 6)w.1#H + 3 + g).2$?g?67O"57#,19b9A[+ 24G g{ -NP%2E-a82?+ /8#8uster(r7C88 7]o  s(1<15QDp > 'dir`Q entry_Q'W`qu s valuo`.be tak’:ӁLq r?E2tQ> 7%$??.A2aIsEmpty(5nTPP (l780|g8Jo u i@YPL"or*Xg_Q Q% Ac@ "a" T!1s $bs`#g_, Gb * a@smr(g,,E)@;o* #gT?$ANext"@_#gc '=PUT THE DAT@A INTONOTES SHEET AND MAKE CHAR TS  organ@61e61, 8).L%'e.g. Brad d MDCRate_Type(3, oP" &(v4, 4t#'0ke t 1is7l , mortality0 etcSFor">= i@g bn "'put vq6syto temp_arrayEfy downl oa!{workbookc qFAѐ, go&ex){1 W2R 1B 200 3007"notes").mPmyrB@7HaH, 99@#2png) $$=c 'dropdataYQSP_CQp3) @t g#"b13:b104R'$labxelss  ]variables& !$inchart head8inghnam4"Male6s gender lJFemo g3K 'SFUP FORpFOICE OF COLOUR 0BLACKAHWHITEDim l?(40) AsAegerXt`gthcol< 'A`LLOW P10 LINES[a le_l`PStr' Gline mark1 Gsize of, pa`our#SAGiGV1X9siA5{W?icP= xlMR StyleNoP 'no[ 1 T*ng'ul3 (*2Squars3~DiaPmond4X, '3 sth9 XCirc  R6 othf *b d*7ash 'Long8~ c B9PlusOiA +=1""H.(B#1 `3 col(3) = 4 *4L5 L5L30U P6(7 &7&8 &8&6 'yePllow 950 'off green 10 47 'y For i11` To 4ltype(iD - *strength% + 2(Next i5Charts.Add With Active . TkzxlLine Location where :=AsNewSheet, NamcnSetSourceData :=d_range, PlotByAColumnsLHasTitle@)Tru(@Legend8;acters.TV= or`gan _F& Chr(& `Rate_BI "Age schedu+of r@s per 1,000 " & A*& titHz>H/Font.SizA1LDrB9 BolC"K"[Area.InBior.]orIndexPSeriesleon(1).XValues B8x, Axes(xlCgory, xlPrimary).99Axi@9A/d wB_0.0C"F?populi}EVt('l@,ng 'set b;Gl`udSe4 Case lB 1 C$A _wid =6Medium 2 , ThiH2ck3 M HairAk A1c(..cl)`0kC `.BorderK!pl)WeightBSty q`inuoulFOGbH'iORD=RseAExit Sub error@zone_SR:MsgBox ."2Sdi`/Mo TEa0rVBA messageX:- UB(P)?PV[ t\8:"P4leq2cpa J`ohn AP0 n01274 2257Pb ja@gmail.com@to @deal wvthis unexpRed problem.", vbCritich, "POPGROUP0A(3q ' +Sub ckops(nfyl, n_warn0s, irow,Gok ap)'routHs mak@pure thatly@e opchosen fany yea0'exceptRmig`s1ifigi0ch cQvidotAND -B''a+0g pcl. oup0Dim!f_result As qgU:sum Ctotg_a, sub?!upghgjS2Qyi S1fy6fX)s= "sн.C0s(6, y1).ruSKIf7`"a" Th)= 1 ElPkKGP0%P8 9u)vK%u(Л)/# bakdowXk=6+  8  r%=+ 1wJ4>J8 <> "P3'6 7s97musЃe7lS'? se;Qd0Kons m4&9if a ;nts P;LSE@ 1aF b(2)(, ("!-><Ъ& "> inHc/Q` moKnKh0as b@- FATAL ERxROR1]If  g "ng'sh37|6sh660E626XCR (sh).Cells(9, y + 2).Value = "a" Then subgive_pgT1 Else L0 'G@ (Age)/sex breakdown for popgroup  sumoptRtot_+X -If Sheet:s8\e+ 1OX@allp And (J= 1 Ory1)Ë%nirow"?:|c, 1F<" & gname(1g) _5 "> in year i&vs n B UT aldyOxBfs" ALL GROUPS VALUES wiJbe used - WARNING"'n_warnin|gsDRFA E@bIfKŧo o toοooo7= 7se377Bu>lobooktype <>` "mig 'A`qm of ions chosA must S1 hless uFnrat@ profile wanted At/nfataWA??j& 6more than oned& has@%&d'- FATAL ERROR`y !-9Next g#cby aSub '=?' `ScrepenUp0MF^as`#sB$).A`vat0= D'2 ridanyxartsiqNChAcf`W0#  w`j բ e.D0  TruiB23 ' A6 m@1$#d3).YSet U8= W"c6`:bb6"$0=N A {t(t) ' number1modteln1 4-'mjulðgroups, excluding the all  tota0l ReDim diags1(0 To ng, 111) RSheets(2).Range("a23:a500").ClearContents 'CLEAR THE AREA TO HOLDERROR LISTIDNGd' = !ED INAGROUP NAMES6-For g =k+ 1 gname(g)ng + p"N '1) isover"Next g'e CH(ECKcSD SHEET(1V1).Select et myrf4hc14:Pc48"sa@Application.Works!uFuncBSuLm(D n_ueredCountF 'how many valid ries areS@f@gstandar@d asfrC3If < 35 0Thenniry=  uG@qC`ells(A)@.Value0"^: Only h" &&"rat+provid?" _GA  - FATAL B"nfa@@) '@rt cJ errorsGElseCEnd IfE4@IJv'C50, 3D*?})))No F'boys per 1,000 girls ) )?'?'al ?'''2}*'##g'7j%Didh`"a"BQ 'checJk Yanix@Dp! [ntaga\dd up i«n$ai56@i`56G!AX56, 2 ip) <>7i7< S 3), 44)~}as@ esar w灶mO$+ y - M 6 births sum of*stribu! fact}@s b逄L& Cƀ4s`"@IT MUST BE7_ D_= g`A1B{ji+ exy dJ'c5 7}F`}.ColorIndexP>1XY owD: mA|not sBnedmsg_gr ey n_warnU!+%gg3q38141 'm`eus?" (i.e. 1?A6,} innd:@@g\ doFC)g have iown@t,ASFRs&? ?Ae(14, 5A48( @G%T S!YѧY1D.'t''HNE'B <&gQ(g<> ydou3saЌwant put inAua. YousB}$s1pmust beCusCniC91_> tUuoDqDsEOjM/12PDw?c87`7`~F>'?>6>`/ioh>vxP$(ᠢLհ'ѷ'@venBn12E's :!!ua` IsEm0pty(a;TrpPA 0@@2*%\1o'to'o'Go'o'@;opt^to_?<'X?ndone so =+= Else  End If LIf xval <> 0 0Then `ir ow =  + 1@Sheets(2).Cells(), 1). Value:"SCHED: For group <" & gna@me(g1) "> you have entered a boys per " _w& thousawgirl@s rate/msg_greyn_warnin|gsNnN`ext g9'=^NOW MOVE ON TO CKING THE ANNUAL DIFFERENCES SHEETS %l E CZ71 To ng J6 'TALD, @eSEXCS GIVEN OPTIONnyTiyear6, yBGopn P7, 2 y' the "pr ovidetotal" option chosen indicat2= 1 if@ _given(y)@0J"12"@ isB$̀of (dIsEm0pty(Fa;@ @A$1 0 ' Se@e note6 mort abov#)"a"wdiags1(g - 1, 5aN^` )S!CI+haً_TSA & CX"K bir8thsLLy@bu:t5 @6!Wda@ta secU- FA n ERR8OR"DhnflC3-9d111 11NOT~<2BUTsre'su3[Ƭs157A?讧\c FOR AGE-\VALU@cTHAT ARE SU@PPOSED`^ $HA@gBE`^ENTeD (YBE)s__ _ Cb-d$*c13OWtb_W@aPPWmale07aWg?14?tg?6fe_p5^bUTraR1g":/]B.0V]# g AP ?h  O w#sex_ E9 'O#toEZ^byZ~xg@uQ;t6?q.n[pOr~*o qQobw ZowZlиsZ_ @! RWs, q#4=&bA Qf5_FRgRtV_5%'l-re{+Qr _ 2nt age/@- gtooY/oc/Pom mpf/==dmTqksɀlN8k2%2+l`"{:8\e,dGtreTUf??;: D4E  For y2 = 1 To y - 1 8If tot_given(y2)dhen GoArmark8rN8ext0ir,owT+Sheets(2).@Cells(), 1).Valu e:"<" & gname(g) "> yea`r " _sl& i&trendsal option chosrbut no previous vGFATAL ERROR"@nfa2t7= } :ɒ3 ~=XAC#YXU"E^Xi"AND aWhas beeprovided fthis'msg_greyBn_warningsEPElseE@IfN Q K' = CHECK THE DIFFERENTIALS OPTIONS MD@Lc 'PROVIDE TO`Tc⁛`"n'd= Dg + &q6, Bq# 6(S0* 28, 2 y{g' The $@=ogdifftl in ?G dIsEmpty(`aFa`3 |ott;0 'see a@bove e@wher(@opn*2OctutmAanif =a, direct TFRAw donr "a"B 0`diags1(g, 9#N6b@*(@3n A Eǡ t!m!a$$Kş$ *P Dnti, ftYe d` s@A- " >OI_5o o /how~~g?i 8NOT BUTH0'svjinOjOjk/9]ihiehgDdhhPOSENm='T7 i2iiiEp+%Jl23{[]T`! %+T8_U /uw37U2U#m9 2oVL99r 99z99 FȔ?Tj #!>f1irowB[g)."f<" & gname(g) _g'& ">  'trend y diffetials' option chosen "BUT no v~s have be provided fa previous"- FATAL ERROR"/nfatal;LElsH 6A0Sra7 @+Ҽ=78NOT_#gmsg_greSbn_warning9oT Z =[@[CV_ DO\ONPZ RECT rXSIOF TFRE_ 3?Yt {CtotdyAC#!*[47,1`+ yu|frB Ҋ in 4v77'@aPdRSa Peric (possibly zero>  wQhW/IsEmpty(0Q :FeKQ= |1  h43 G X"q;Y7q .(0 tb9/Z?.BBowok0A/& r1Ϡ zbut]ted)the dМ sO_]o/Cb $ "+!,Eqr ]`:N^& ^__O61o?a1[aԻRE^OPT^\~/]/].]"ϛ[[= OLo7]4b ~= q5O8P   For y2 = 1 To y - 1 $If Sheets(g + 2).Cells(43, 2 y" Value"a" Then Gomark9b 'check fa previous total PROVIDEDzNextirow{}+,0 1).\<" & gname(g)"> in year " _;& i&the option was chosto trend TFR%!but( novgiv#- FATAL ERROR"Fnfa= i :b_PRz7Dz)OIsEmpty()@FalseBO AMBj= l 1mVir,m6@differtiփ.& msg_0grey?n_warningsKBESOE@IfN Q Cmg(E' = DO FINSCHECK ON COMB TIA`OF OP!S CHOSENa Calle@oHps(W, ",D, "/t"dP| !` `INGaPLETEGIVE NU`MBER hjSCMP2acCafromau}bExit Su` 'don't leave any messages if c@ ed by(0LLINC 'RE-mattG@, justcanit's bNe upa copy OBTshO3nA3+ sh).S electZUnPprot'@/sex Bs/@Range(12, 3), 4ny) With C8idaAO.D teAdd Type:=xl ! eDecimal, Aler`tStylDop, Operat 4A GreaterEqu 6mula1:="0пIgnor@eBlank ;T$ru5InDropdown4.InputT8itl~r.EPrrorVDh entryRM30"xx{ma/be PV!uhan e / .Show@bjr2 p?!1U$L&Fon3 .N8"ArPe/CSiz8__ Q10Numb$er&ata #,<##);28);36/;ߦO6; /;Q4./;t/;/;/;Ha;Betw0X, ;2<2)C.<_P9<>R"< wH"no W /b19/ e23:o62ClearC Np "Exit hSub +0'@t diagnos$ulY `at myr!IMB3, 5),  + ng, 15)) .Gas1k% '=Produc J chaA1"s iCNno$N Call Schedu ` _(boo,k)XDlNroPutinlTth`rdoRia-c zoA@/R t!IfHm > 0 nd. N o+bwnfound, but iAvread y LonZ s€." & Chr(13)(Lh& " A0s(s has p5d NC Savei@s work/ w@ a n!=Fy`oos`.avbIn`,{}x{E+aI _+  !1o"ur` Pg- IfY AppFl`TtScr}UrpWngDy;M `zone2$iF_X M>p}- VBA m:- P*B(P)+"PpWkcWact John Andelin101274 22575kpor j@gmail.ito dealth+unexpjh`blem)f)@gPOPGROUPfEC_0P##Oe}ONtCN'6Ua@sfr's 5 each poppbugPp3   CDim q(@) As S@ "a"l 'ionlya{ grey`vy] DD'@gck%" 0 bp !,gQu up)4(A1l-@K()7(113)P0ON#+ Sheets("notes").Cells(199, g + 2).Valu0e = *3).Name  *If standard_yes <> "a" Then asfr(g, age)h0L'set toQ if 0;C* adj_all g!'multiply by overusters 7s829gp,> #$0 Or'4`A;1SgpLj3@+gŒ..J/ k/О̀CEs_gpeGEnd IfK NextFĘ '=PUT THE DATA INTOBNOTES SHEET AND MAKE CHARTSorgan41}, 8| 'e.g. Bradford MDCRate_Typh&3, `R$'r tis fertility# !ForA" 1 To 35gn q@'put vAs in`temp_array  fast do@wnload#workbook bAa(m ƑDBK6'irow`200gmSelect(myrang/R(# y, `e3@d2ng))DF 'drop the dataC[S_ x- 1A `sched ϭ  x&C"b14:b48") ',@ labelc`hartn "Us"cD''SfUP FORfOICE OF COLOUR `BLACKk WHITEDim l\(40),rengthcol As Integer`kALLOW `  w40 LINE*S ' eline mark   size of, our $ 4i61(st i/x8-.iW0xlMR StyleNoP 'noj1"1Trip-le 'ular (2Squars3~DiaPmond 4X '35 witTh TX5Circ g"6DothoPrt b &7ash 'Lolng8~ c 9PlusOB +=1"@Picturp3/G.(#h~1"!3{4;-30A67b8;"6 d'yowB50 'off g0reenS47 'q!W;};4;.b;X0_qq 2 7Lb@e2 OA0 CqYs.Adx7W"Activne .2Ԇx lL Q Location whe0re:=@AsHNewe, a:.=va< fSPSceJD0o :='n, PlotByColumn)b @HasTit>=P Tru.L_` 4$ac1.T= Ú_ 1& Chdr(#& 7 ى"Avu cs p f1,0@00 wom(pfromRd10" / "yePJdif0entials only;iuntsS`d TFRSt ake ectAqn useQ aecast).{-yFont.S!q= 1)3$CrBoldo" \.Area.zrior.orIndeSeries P( I.Xsc&Axes(xlCgory, xlPri0Oy)?"o$1 r Ax6i(/$ A{ .Axes(xlValue, xlPrimary).HasTitle = Tr`ue Axin.Characters.Thext_ "Rate per 1,000 population",End With For l[1 To ng 'set boldRlin es*oSelect Case l  %10)_width9xlMediuVm#$2$ $Thin#2#3#Tck$3$4#$Hair(bP@Active|t.SeriesColZ@r(l)7 t @ .B\ormd.@o@rIndex\c olP Weig.hAVP LStyxlCont`inuouLNAI>?Area; EBQub  A ChartType?xlLineWPLocation-where֍xlLocationAsNewSheetM SetSourceDataaSourceGPlotBy$ xlColumnshHasTitle{ HasLegendB ChartTitle_ CharactersrgBoldPlotAreaInterior; ColorIndexSeriesCollection;:XValuesbAxesb xlCategoryZ xlPrimaryu AxisTitlexlValuestd@olerh%^*\G{00020430-;C 0046}#2.0#0#C:\Windows\sys@tem32\e2.tlb#OLE Automat8ion0EOfficEOficEE2DF8D04C-5BFA-101B-BDE5EAAC42EProgram Files\CommonMicrosoft Shared\OFFICE11\MSO.DLL#M 9.0 Ob LibraryJThisWorkbookGTh@isWrPkbo 2 HB1x%B,!"B +B|Sheet1GS@#eXt1H2N_ro@utines@vDa_r>uрin s@ G@ $(@!@qThisWorkbookThisWorkbookSheet1Sheet1val_routinesval_routinesBActivate| ApplicaID="{00000000-0000-0000-0000-000000000000}" Document=ThisWorkbook/&H00000000 Document=Sheet1/&H00000000 Module=val_routines HelpFile="" Name="valid_project" HelpContextID="0" VersionCompatible32="393222000" CMG="DDDF71F1916F956F956B996B99" DPB="5755FB7B0F8F94AC94AC6B5495AC094B79E4BCF48DB7BDBAEA35CCB130352D2A86ay  *\G{000204EF-0000-0000-C000-000000000046}#4.0#9#C:\PROGRA~1\COMMON~1\MICROS~1\VBA\VBA6\VBE6.DLL#Visual Basic For Applications*\G{00020813-0000-0000-C000-000000000046}#1.5#0#C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE#Microsoft Excel 11.0 Object Library*\G{00020430-0000-0000-C000-000000000046}#2.0#0#C:\Windows\system32\stdole2.tlb#OLE Automation&*\G{2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}#2.3#0#C:\Program Files\Common Files\Microsoft Shared\OFFICE11\MSO.DLL#Microsoft Office 9.0 Object Library  CEGThisWorkbook0E4bf154bdThisWorkbook% Sheet10F4bf154bd Sheet1%val_routines0G4bf154bdval_routines0(P0>`J]bc~HSxCIE=rSbC=O0BExcel+VBAWin16~Win32MacVBA6# valid_project.stdole`Officeu ThisWorkbook| _EvaluateSheet1 val_routinesngb^nyp^diags1`Ldiags2aL n_warningsZ-nfatalgname  mort_validfbookbooktypefromautoM tot_givenr age_givensh_opnmyrange-Range irowqicol_!g^yly2P_jaasmrM>asmrF>iyearA,tot9totgive tot_oplNn_reqm_prespf_presAS_mark| prof_resultJBentriesok^msg_greyx WorkbooksBActivate| Application*ScreenUpdating! ChartsCount0v DisplayAlertsActiveWorkbookDeletexSheets  ActiveSheet%N Unprotect? ClearContents{ CalculateWorksheetFunction&CellsValueKIsEmpty  check_profile, jump_pastmark8AImark9BImark9acheckopsMsgBoxR vbCritical+} SelectionZ Validation AddrxlValidateDecimalG AlertStyle7xlValidAlertStopOperatork xlBetweenkFormula1Formula2 IgnoreBlankRInCellDropdown9 InputTitleA InputMessageR\ ErrorMessagef ShowInput ShowErrorFontUSize NumberFormatRProtectfSchedulechart_mortmigChrK~ vbInformationn errorzone1Erroasmr=I temp_Arraytit2nshsexr irow_schedu irow_diff agegsagegpadj_gadj_allN$ standard_yesb adj_as_allL adj_as_gpoorgangQ Rate_Typei`lc data_rangerangex chartname1gender@ line_widtherrorzone_SchedŴltypesstrength colH title_lenyxlMarkerStyleNonegxlMarkerStyleTrianglexlMarkerStyleSquarexlMarkerStyleDiamondYxlMarkerStyleX{xlMarkerStyleCircle~xlMarkerStyleDot/xlMarkerStyleDash;dxlMarkerStylePlus ActiveChart BCCAB37E8F63" GC="D1D37D7E7E7E7E81" [Host Extender Info] &H00000001={3832D640-CF90-11CF-8E43-00A0C911005A};VBE;&H00000000 [Workspace] ThisWorkbook=110, 110, 910, 558, Sheet1=44, 44, 844, 492, val_routines=66, 66, 592, 336, Z Oh+'0HPx  Validate population components J AndelinAndelinMicrosoft Excel@Ŏ@f ՜.+,0 PXt | Andelin Associates' Sheet1  Worksheets F!Microsoft Office Excel WorksheetBiff8Excel.Sheet.89qDocumentSummaryInformation89CompObj>m