ࡱ>    no( !"#$%&')*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmuqrst~wxyz{|}pRoot Entry F!p7y0 F2Workbook+_VBA_PROJECT_CUR"p0 FVBA pp<  !"#$%&'()*+,-./0123456789:;<>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\^_`abcdefhjklmnopqrstuvwxyz|}~  \pAndelin Ba= ThisWorkbook=h\: <X@"1Arial1Arial1Arial1Arial1Arial1Arial1Arial1 Arial1 Arial1 Arial18Arial""#,##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_-;_-* "-"??_-;_-@_- d\-mmm\-yy                + ) , *        #        `uSheet1,`iJ!HOUSEGROUP - Household Forecasts)Missing values in the headship rate table Last saved: >Headship rates sum for any popgroup/age/sex must not exceed 1.5Checks the values provided in the HHInRates workbook )The validation program for headship ratesNo value may be more than 1.xla version made 13/11/2000  cc   .5   dMbP?_*+%"??ldU} I}  .h;;        ~ `U@2Tv?         :t.      !"#$%&'()*+,- !"#$%&'()*+,-  >@7 Sheet1 D8``#<mt-xE/4jFV] 7K$=xV]ThisWorkbook =__SRP_2]P__SRP_3 gySheet1i~ 7K$=mt-xE/4jMEP[LSS6"L<<N0{00020819-0000-0000-C000-000000000046} `$k@Ti `ori % % %`@Z q86O8qi@l GS@h @0X -4@T 5F@HD EB@`4 cr@$ x1@XT= $*\Rffff*0R49bd0d2b*\R0*#12*\R1*#dc4" AA(H X Zc6$,1_8GA d6$,cumo0()Attribute VB_Name = "ThisWorkbook" Bas0{00020P819-0C$0046} |GlobalSpacFalse dCreatablPredeclaIdTru BExposeTemplateDerivBustomi6zD2P Sub w_beforesave(ByVal asui As Boolean, cancel ) Sheets(1).Range("c6")jD3dTim{EndG rU pa! #4!aI ` A:D%l lT(X%h! h dT)hT5lA:D%l lT(X%h! h dT)hT5l X$ hTl4XrU @,`T#n<jx``#<QEс@kG pH F#E/ꎀLx#E/ꎀLQEс@kG pHME(SLSS6"N0{00020820-0000-0000-C000-000000000046}(%H` %" %`h8@HH0= $*\Rffff*0S49bd0d2b4xAttribute VB_Name = "She@et1" Bast0{00020820- C$0046} |Global!SpacFalse dCreatablPre declaIdTru BExposeTemplateDeriv$Bustom izD2rU y4 A `rU @n__SRP_4{__SRP_5 BModule1Ь__SRP_6rrU~| \rU 0` ///(`9/(`Q/D`i ///////n   L $h  &      8 *X      (    8@X@h    & 08 @PX xR  &*  \H"&   20 (HDp   ^ 8 XX  D L Zh F @(<hB 04HD \ 0(  ~X 8 V h x  4    * @  P `h p   H   0Ph((  0HXp* .8hp         r >H\4  $@  $h  D   DDP T BN8   2P!B!.!D" H" BX" " " x# "# # \#($60$6h$D$ $ "$  % 0% % "& (&H&(P&  x&  &  &$& & 8& ' ( (   j ( ( \( ) j) 4) )) @) $* $@* h* $x*4** &* 4 +X+ >h+  H+$+$",$40, h, ,p, B, ",- -   -  B@-D- R- @(. Jh. .  `X/ ##"/("/,\0 Ph0 (,0,\0 ,(81(F@1,J1,"1 2 b2 "p2 R22 <2  83 P3$8p3$3#03 33  8484 "P4 x4 64 *4{``xME (<0<6 < &p `@I( `SI* ѽI,  .% %`2`4 eco`6`8the `:ade `<he h`>8 `@om t`Bts r`D`F`H`J-4`L5F`NEB`Pcr`Rx1`T`VSi`XC Zp% \% ^%`` `bew dh finf`hin d@j `l n8A PA NAME===  pp==REAS driv X r====`tf th`v may`xtory`z ent `|irec`~ame ` the` its`@` sto s] :\ (a pa% H'X%  P `h  P X ZE= Z (` \il` 1 `  `Tj``  @ h  ` nam`ook `from  `e co` G T+ `  I arra`m in+h `(  I l va ` age/% ` ]]I<  MaleI  +I  !I2 ( alidI8 H any I h  of I  d    alid%`G `e an@ of t``  Zp a21%`>B@`@`D$` 6,`Ne ad@   % h p ups $p %h H ( = $*\Rffff*0T49bd0d2b h   N ` ,h   "   T V( > b J(x"2"2$@ThDLXX* 4"H@( R4(@@h$&RBP,..H( .p L * &8 V` : 2 40 &h   "    6 J8 & 6 H  0 @  X h   T0 4 55  6686 "P6 x6 66 *60 6 77    8 8 8 $08 X8 h8 8 :8 8 8 $8 9   J(9  x9 99  9 F9 : P :p: x: : : : :: ::: : &; 0; 8;P; `; ; ;   J; ;<< 0<@< X<h<<  < <<< << ==0=    @=P= 8h=<=4= >8>8?P?X?20@ h@ p@ *x@ @.@ @@ A  8A HAXA pAA A A AMA VAB (B "@BhBpB BBBBBB  VBB C 2CPCXC`C(pCC  \C" D DDB D&hD"D&D DD@EPE &pE EEE 8EF"0F XF 2hF 2F F F*F*(G TXG JG H(H @H PH `H pHxHH  H  (HH(HHII I 8I JHI :I  I:I  (J D8J JJ J2J*JK (KTHKKKKKKKL *L @L XL $hL LL .L L   L MG==================LFratesVALIDATION_program ===========================R&=============================15/1/2002 PArguments are:zN1 srce_book is name of book containing the unvalidated economic activity ratesP2 calltype is the type of call being made - =INRATES if from the hr book itself,8 =SCENARIO if from the household forecasts routine[3 totyears is the number of years to be validated, only set if coming from forecast routineppp]16 to store the validated srce-book, original srce_book,p@]] path for LGsysfilesP`]#number of sheets on the source filep](number of pop groups@pp]@#Maximum number of years to validate]Xthe usual definitions!]p0Hbcountersp]`6Number of activity rates filled in on all groups sheet]x08value picked up from and sheets]Jcounter of number of value filled cells across the population group sheets]?temporary store- Dim typ As Integer 'HH/Family type counter] counts fatal errors]89row in the "Notes" sheet to put error/warning messages in]Pi.e. male and femaleL Dim hh_type(8) As String 'descriptions of the household/family types used] Descriptions of age groups used]sum of numbers - temporary]"+row numbers of number and percent not in hh]the diagnostics array]@increments to be applied]xCto store each group's activity rates in before dumping to its sheet]3-to count where we're up to filling array_vals]to store name of workbook, etc] True if in multiplicative mode] annual increment to be appliedha](@+3maximum value for activity rates, individual or sum'& factor to bring sum back down to 100%]X]p*Factual name of validated workbook (incl path), formed from source book]?labels for pop groups} ]0&for range copyingh]PG TO RECORD THE NUMBER OF SHEETS PASSING THE SIMPLE, ALL DATA THERE TEST] ,rates array values read in from input sheets]$$increments read in from input sheets]%initial values per group, per age/sex]0to hold an array read in]H On Error GoTo errorzoneMale+dFemale+d ($. ! ! $-validated.xls'Dclose any previously validated version of this file that may be open $G $B@Bj ! ! $-validated.xls' $G A@j=(erase any validated version of this file B@notes$B@H B@a21$B@Ha21$B@notes$B@HAM_Mode$G'zj6,AM_MODE is set by the addition choice button  !'6 6 '88number of population groups (excluding all groups sheet) 8x 8 B 8a5 B $% B+ B $! B+ B,j1$%'2notes$B@H"program_source$!'4# out_code = Range("out_Code").Value*]e6$%!' ( ! Set grps = Workbooks(groupbook)$B@Ha7:a50$.Z Z !%'<P st'N > <eds th N 'Nf N$! >+f&get descriptions of age groups > B@8get number of years to validatenotes$B@Hn_years$.Z Z$ |2':d Z!': k a27:a5000$B@ d22:m61$B@'bCto be incremented with each note put out to the notes sheet of Base  ====Open template for the output] 4LFinrates-val_temp.xls B@'0V Sub makeLFrates_val(nAge, age_gp, BaseYear, organ, ng, popgroup_long, popgroup_short)' < f  2 8  A16-1LFinrates-val_temp.xls$..T+if forecast years required, do it from thatG , G ,':j 8 < : 8 <82group 0 is the default sheet's 8 <`WSIMPLE CASE IS ALL VALUES PROVIDED FOR EACH GROUP and no annual increment to be appliedSO ONLY NEED TO CHECK THATRNON-NEGATIVE VALUES ARE PROVIDED AND SUM OF THEM DOES NOT EXCEED 100%, AS PROVIDEDBY THE BUILT IN SUMMER.>IF CHECK PASSED COPY THE WHOLE DATA SET TO THE -VALIDATED BOOKsFCHECK EACH SHEET IN TURN AND COUNT THE ONES THAT HAVE PASSED THIS TESTTIF NUMBER EQUALS NUMBER OF SHEETS THEN END THE PROGRAM, ELSE PERFORM THE TEST AT THEB@H@MAIN PART OF THE PROGRAM FOR THOSE THAT HAVE NOT PASSED THE TEST 8 8 n#dimensioned to the number of groups].counts number of sheets with all data provided0 ]+-number of rows and columns of data to pick up 0 <' :' B 8 one v B@_temp B $B@Hay$    $$.ZT$  $$.\@annual incrementVRange must be filled with numbers that are less than or = to 1 and greater than or = 0F*and no annual increments are to be applied  $ Z !% Z !% Z !%   \ !%' 1copy the values straight over to -validated sheet+Copying data to -validated file for group B $! ( .B@ B $B@H B@+$    $$.^ ^ ZB@ B@ < B+n B$n :  B+n+ B+no  'ardk B  B@ $  8Gj < :r 6!dimensioned to size of sheet data <p8 ]@ X  < 'get all the values in  B 8+ B $B@Hnu'N$ 6$$.Z Z'the rates data$ $$.Z Z'the increments#no Fwhat t H <  N 'N$ N$' $ $ , B N+7put blanks to 999 +d8  B N+ $k J :  N J$' $ $$ B N J+6put blanks to 999d  B N J+kp Jot stu H F Bk======================CHECK FOR MISSING/Negative VALUES IN THE base year activity RATES ==================='P /activity rates for base year are in this column B 8 group B$Conly do this if not simple case of all values provided for district''N$ < 6$$.Zj F sex counter$ H < age counter> For hh = 1 To nHH_all 'household/family type counterunt N 'Nda Sheets(g + 2).Select= Set myrange = Range(Cells(irow, icol), Cells(irow, icol)) B N$:$no value provided at the group levelge2_N Sheets(2).Select 'check to see if there's a value in the all groups sheet< Set myrange = Range(Cells(irow, icol), Cells(irow, icol))ate N$>-no value here either - therefore, fatal error b 'b  %ERROR: Base year activity rate for < B $!,  F$d,  H$f > has not been set bnotes$%( ` '`2add 1 to count of fatal errors B$n B+n:add 1 to diagnostics tabled!& rate provided in the all groups sheet N$ @$check to make sure it's not negative ` '` B$n B+n>add 1 to diagnostics table b 'b$$%ERROR: Base year activity rate for < B $!,  F$d,  H$f% > is negative (on the default sheet) bnotes$%(d B$n B+ne N$ B N+V Sheets(g + 2).Cells(irow, icol).Value = myrange.Value 'assign value from all groupskk .end of if no entry provided on the group sheetd-there is an entry provided at the group level B N$ <$check to make sure it's not negative ` '` B$n B+n* b 'b$$%ERROR: Base year activity rate for < B $!,  F$d,  H$f# > is negative (on the group sheet) bnotes$%(Lu*d B$n B+near B N$ B N+kk final end of the first if loop! Next hh H Fkattached to if passed(g) = 0 B ` &do not go on if there are fatal errors    You have `D error(s) in the base year rates. Validated rates book not produced $ $0Please correct before re-running the validation. $LABGROUP - activity rates validationA@ .B@Bdd===============ALLOCATE activity RATES TO POPULATION GROUPS - YEAR 2 ONWARDS======================== B 8 B$"Donly do this if not simple case of all values provided for district' B@<Validating data and building activity rates file for group B %! ( P :(icol is, in effect, the year P   ''N2row to get activity rate from at the age/sex level F sex counter H <' age counter N 'Ne Sheets(g + 2).Select P*"do the things that need doing once B N$ N+rfill first column of array_vals.see if increment to be applied to this hh typeda N+p B N$C" no increment given on group sheet N$H+ an increment is given on all groups sheet N$ N+p B$n B+nlind*+no value provided on either so none appliedkd%#there is a value on the group sheet B N$ N+pL$take value assigned from group sheet B$n B+netekk Sheets(g + 2).Select; Set myrange = Range(Cells(irow, icol), Cells(irow, icol)) B N P$F$no value provided at the group levelK Sheets(2).Select 'check to see if there's a value in the all groups sheet: Set myrange = Range(Cells(irow, icol), Cells(irow, icol))C Set myrange1 = Range(Cells(irow, icol - 1), Cells(irow, icol - 1))$ N P$ N P $ N P $ Q4values in two succeeding years, non-zero in previousZ If IsEmpty(myrange) = False And IsEmpty(myrange1) = False And myrange1.Value > 0 Then B$n B+n z9multiply rates option00 N P $r N P$ N P $ N P+r,Imultiply last year's value by the "trend" value from the all groups sheetd1it's the addition mode00 N P $r N P$ N P $ N P+rkd->all groups value for this year is empty and no entry for group0 N P $r N P+rKso take last year's value B$n B+n6k d%Z value provided in the group sheet, so don't need to do anything because the value's there B$n B+n B N P$ N P+rT+this myrange SHOULD be from the group sheetk 5============apply fixed increment if present========= N$p z1multiply by 1+incrt ( N P$r N$p  N P+rd) add incrt ( N P$r N$p N P+rk k 2check to see if gone below zero, if so put to zero N P$r B $n B +n b 'bDd/ temp_x = Sheets(2 + g).Cells(irow, icol).Valuer N P$rd !%'(("(*(0(WARNING: activity rate for < B $!,  F$d,  H$f,   > has gone negative ( %) - put to zero bnotes$%( N P+rk /check to see if gone above 100%, if so put to 1 N P$r B $n B +n b 'b/ temp_x = Sheets(2 + g).Cells(irow, icol).Value N P$rd !%'(("(*(0(WARNING: activity rate for < B $!,  F$d,  H$f,   > has gone above 100% ( %) - put to 100% bnotes$%( N P+rk H F P Dump the array into the table .B@ B $B@H B@$ < : $$.Z r Z( B@kattached to if passed(g) = 0 B @comes to here if just a simple all values provided set of tables B@put out diagnostics tablenotes$B@H b/There are no errors or warnings from validationa28$(dList of errors ( `) and warnings from validationa28$(kA28$B@H ! 999q@notes$B@H'N B 8 N 'N B $! N$('P > P 'P B >$n N P$( > BCcopy error messages and diagnostics table back to the original file B@notes$B@Ha28:a500$. .B@notes$B@H B@a21:a493$.  B@ copy notes B@notes$B@Ha2:b16$. .B@notes$B@H B@a2:b16$.  B@ B@notes$B@Hk22:l61$.%for sum of increments usedi22:i61$.%for sum of default rates used  !%  !%  zmode set at multiply AYou have chosen the Multiplication mode which applies the annual Vincrement and the change in the Default sheet as proportional changes to the previous year's activity rate.a21$(Multiplicationnotes .%%(d ;You have chosen the Addition mode which applies the annual Rincrement and the change in the Default sheet as absolute changes to the previous year's activity rate.a21$(Additionnotes .%%(k?d?notes .%%(kP? :n_yearsnotes .%%( B@  .B@ .B@Bxk /end of "if there are fatal errors in first year B@notes$B@H B@a23$B@H (|P>H>o`=O+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++p &fname is path with filenameReturns true if file exists] $~' 'd<'kp<ih<O+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ ,Returns TRUE if the workbook is already open]H ; $. G'cG'ji;U-------------------------------------------------------------------------------------h ] ] ]  -.maxrow, maxcol are bounds of the sheet created] ( %temporary storage]@ sum of two HH types]X temporary range storageMale+dFemale+d]x    ?for use as indices in FOR loops]  Row counterLFinrates-val_temp.xls$. B@Default$B@Ha7:c300$B@'N )row counter for putting values into sheet > sexes @ < Age groups N 'N >$d NDefault %%( @$ NDefault %%( @ > d6Default %%( 2j1Default %%(?c:c$(%2set width of column a to widest HH type group namexCnow format and put validation and remove lock from data entry areas < '$ 6$$B@H !Arial9!@99q60.0% ( (.!calculate bounds of the worksheet6'$ $$B@H  %ens 9q6d7$B@H ("]  5for copying info from Default to newly created sheetsLA3Create population group workbooks based on this onef6 B@f6$  $Default$%.LS 8 Ac > 8+for each of the population groups specified $B@& >$'E  (1.$  $ $%.EW.  B@generate the new sheetJ> >$'  $%(?c:c$(12set width of column a to widest HH type group name+b:b$(a:a$(Ld:d$B@HA 9(ffffff@9tq3$8$$(d7$B@H ("CON > $, $B@*_t >lskx2$Default$%($Default$(o 2"An unforseen error has occurred - $ $ $DPlease contact John Andelin on 01274 225750 or johnandelin@gmail,com $ $Abandoning this procedureA@2rAttribute VB_Name = "Module1" '=LFratesVALIDATION_program V"215/1/2002+ Option Expl@icit Sub _valid(srce_book, calltype, totyears)MArguments are:1 3 isD n~of ! containing the u"n3d e omic activityD(2<'B )H be'made - =INRATES if from 3hr>itse(lf,) =SCENARIO ousehold forecasmroutine 3~FnumberGI F6, only setAc8@?& Fa&Dim basea As WorkaI 'Ast*B;Ta- origi8nalfAEor(ganSng sys_pxath. LGfilesG nsheeK@, IntegE 'HDqsou@v HgApPop g@Jp_of_C]H'Maximum PfEG1Age&usbuKdefs! iH , jJ /, kj_C+s ayh/!, irowicol 'cou/r$ARJNVkm!1led in@V }- 6- m@x(u&Double, subl 'picRk up#r< >@ and <&;># h ;amg; CHacell@c0rossa ulaGN myraJnC@R, $1 &K2F 'temporar@{ao}#'@;!wfXHH/Fami)n_fatM .s erro!*:ote_+A '' 7W"Ns"CCput /war¯mess0ages=se0x(2)a# 'i.e. malre"5feA%h$h_A(8descrisA5Ah/fG!s Fd(20j D `RsumfxAR 'DsO`6i_nj_p6)cLbMUpent no! n hh diags(q@nosticarray(ncret[s ivbpplierb#to `eatch'!1iu1>beP\e dumpyo r3# aB),~G- where we'P`?p9A;8>q_b '0C ~wpetc mult_ Boolel' TrI 0ic0@ve modP|1X:At`.n_R XmaxP ,:cp@0bh)p'nddor= Tm ?'&3br1.back down p100%1SMlp id_shor,㋹lo'cOQ d u R(0l т)orm0`bdSQZBZ_10   'lab>e`_!p3/&rng\\ 'a3`0copy@ PA0SSED= TO RECORD THE NUMBER OF S@HEETS AINGSIMPLE, ALL AARE TW31(6u1Zreazinϑ]S+s_+ ?start1FMitik`1 Q3, Z/d g x_(2VariaT'0,@;% MT DO q GoTo#nzoq2Am1)plסAn@F2mdSR=fs@/3= Left(.,n)h4) & "-!2.xls'closQvy pr8evir versېPth`եUeatyyƐopen Iff hIsO`(b )` Then .CsavechQs:=FalsCX2BFull\%ed Exiש 1 Kill.J'er \yHP3 ]A!#S"("}es").SpelecQe.Unt ("a210aSelect  Range("a21").ClearCont0ents|Sheets("notes If AM_Mode") = False Then mult_inc*True '(ODE is set by the addition choice buttornnsB.Counng - 2 'number of @populaUgroups (e@xcludiaXll /)5R0eDim _long(1 To ng), short For~ F C&gg + 2).5"B5Name2Next gorgan(35j@| D"Rwsys_path@(program_sourc{.Valu!' out_c ACB Ybase_y As Integer, _@cessjCG = /G6e6G% ApplicB.ScreenUpd A' Sgr= Workbooks(Bqi  @qŁmyrׁGCa7:a50xnAge@I'aFuncAûA() / 2CIirow@6iQ+ 1aiSCells(F,S) 'g@"d@escripsAl@hQic5.AvatU" t=s to vali@2+S=+n"I @IsEmpty'Aa!n_ofc%5z0Ed0YE`nd If%y2B=0=dd22:m6q1a_;28 '$be remed wiseach A putp)/a5Bm '''=Optempe fa`x Cav4_n AvStrtc d.f& "LFinr?s-A! .xls" ' Sub makeLF_(!c, L_gp, AY, b,ȱDCv , ? up_b$ Pid@H=& (5$P'ifA-ecast"s required, do i@t froma#D[totf> ~0jY +a`rray(am, 6B * 214 's_read?e1)Bw CP~A$defa rM'#star6tsg :'SIMPLE CASE IS ALL VALUES PROVIDED FOR EACH GROUP$ a9no`nu\al3!24aaaed'SO ON LY NEpTO CHECK THATNON-NEGATIVEAREAND SUM OF` EM DO NO T EXCA10`0%, A BY BUILT IN1MER. IFPASS COPWHODATA SET THE - I@BOOKD SHE` TURN 1 COUNON ! HA@THTES NUMBEQUALS. pS N E GRAM, ELPER@M1 At MA PARTDTHO & E  #&욣diags(Jn@'9P+di`Us`UV+wj+P3EБss 'cs w2/sZH na vid+'n`^sznc|ol-0umnx` pic0k upc= =TPf>s?1J0Nl|Dzt?sQn7,l 56Pb ,l 4 )d?l1M4p))an;?3!'@WP@fill`that are l`Wng*1BEgPtth0r= ('oG"{s"0GQ 0^/#`&Maxg<=0\ApN_/*Tin'> / ?/)G$r)* ߤ+/ $#1mk 6pSwue@traighvo qdgotatusBar"Copy4h"#R@ " &014251). 5!er.UnpDt@2p 7 , 4), Cells(6 + nrows, 3Pcols))  myrange.Copy 02 DActiveSheet.P@rotect @diags(g, 1) = nAge * D#4# 1* (n_of_years -D ]PASSED4(g71  all_passedB+ ElseEnd IfNext gAbase.a"tIf 5ng Then GoTo @_by "ReDim ar@ray_va1> ) 'dimensionfto size of sA data/increts(D| "max@ As Integer, x_temp3Varxianx Gu6!C'getB the @5ues inFor g@a06TAB@2).Sel i%= 0 DR(7, 5F)@4ɷ5 E'*rrsPԅP3 c3GAHs BjaA +DJ7ηc2#3(aAt ZIsEmpty() Or IsNumeric= FW>e]a'_r8ead!x999 'put blanks @[@W Ku  / } /0y0s --y̭%_.r2a&_. Z_"-, y?.e;. . .D běas.?bȉs1a # '=CHECK FOR MISSING/Nega VALUES IN THEJ  aity RAT ei@@D5 '3"* f re#@is um̪û IP`Wi'only doif notdmple cpmel[providfdist?~!< YAYYiD?-Zq7Q 'sexPujr/+eZSPtaco 'Bhh HH_'household/fami0typ [ 3 b/[ rwݖa" Sqq1, &r(/I0$=wcQDB'no%" "at&e group le8velG s(pcheck`se`/fre's ain}2sSOb eUrL0'he0(s)3;(a@ > hasKbePset" |n_3@`Wdd 1a&AOe~ 3!u@nosticaLblhƯ v '{4.B?)3c-< `6make suQft6! nno 0omI= //-@-? /U+272/3~3>g3_3eH(a) & " > is negative (on the default sheet)" !ElseJdiags(g, 2) = + 1>start_valBirowErates_array(0, P, 1)M'SnG +G.Cells(icol).ValueSmyrange'assign Cfrom all groups:End If 'eof if no entry provided Ђ7('rean&at{AClevelWIf [q[ < 0 Then 'check to make sut'otEn_fatal_E"F3ʗ@note_"D"s")Ē7_?A"ERROR: Base year Dac@ity` hfor]"KNHame, & dsex(s,ageO a7170V0yts'p finStfirstuloop! @Nex@t hh ? a.Hattasdrp@ed(g,0bgg 1eKeq>}do t go #a~AwerrorHMsgBox "You ha!X aQib_BH. id`d book @duced"^& Chr(1)!Lc"Plea correct be@le re-running⤀ion.", vbCritical,m"LABG@ROUP -+wsdaid.(Clo~s $chas:=FaN5'lc'= AL@LOCATE'RS TO POPULATION b Sp YEAR 2 ONWARDS #FIgT1 T!,%@$PASSED'Dc only $th`$S simple cAp1v"wspAPdistrpict's5+.A!S"Appl0tAStatusBU= "%&a aPxbuildWs@ PPSVpXbZ  @2B_of_1_s;is{ff),(k/_03ce-= + 1 w O Q 0 'sgetK/ ce/g ߆7 s%2 'counte^rQ!a%nAgeAQPo2GTbi Sel@u 2M=7" '1e`(s`@nedo)oncr±EvrO i8'fipnum|n q9se 0ncrem0hbe a8 `#?hh typ  iE xlj !_reaqđ999dJ3`f g n Ȑy 3U) <> IS OsS_8a2qm:'Pв_Zeiц s!n$c~'a{ĩ) 't1S&};7 End If  J>r '`Sheets(g + 2).Select 3 QSet myrange = R(Cells(irow, icol), ):If rates_arra8y(g$'499@9 Thenjno value provided at the group level6' ,check to see if*re's a8in3all5s s!6M- 21d - 1_efAg0g<> gA@ qJ B: D _L: > 0Ņs iwpucceeding years, non-zeroAprevio\us! IsEmpty()`False6 A/ 1D1.VYe#diag, 5+ 1? B?mult_in c-'iplyv option  }_6*'Cbr #N* X) /O\ é )last"U"Y by"trend"Dfrom ?EAY'itaaddia= mode? ) >{>+>o >->{/?iS$foRr"is% peJ aZtentry 1s ?O has gone negBa{e (It emp_xQ%"*) - put to zero"array_vaiicol)0End If['check)see ifAabove@ 100%,so21[If 1> 1 ThenAdiags(0g, 9?I+ ?EN!' D{=2gֈd2Applicon.WorksFun@@@Round(M*a,@ڟs(d X a_ H!k_ui=`_cJOKNext a;bsp'Dump the intablekbaseid.A!΀ K1qSelect+e.UntNSet myPrangR(7, 4), %6 nA`* 2, n_of_as3)) xPr5A2'attad passed(gl1g !_by:qcomesAher2^jus simDpl@ll `gu0vided sof H&& q '1oout q[nostickJ=Gd)\28d "al280"fa0no errors warns from1idRY8Els\*_e"Li`P"n_fatal1) ay?>:tA }With CipdFon1.Siz.1ls .Bold Tru|.I ic  +:sj2HFg0 o n,* b{s:q;74H 2[4[aie9XN@LQ'filC;i<''copy+ 0 Or g2:m_P rjmo4detaiplyW 'lM0"YoupPchosenM3 mode which applies the annual " _ & "incrementXdhcha@nge inDefault sheeFs proportionVo,pre viousqyear's activity rate."base_valid.Ss("notes").Cells(18, 2).Value P= "MUicaP*Else5Ry("a21,&You have chose0Addi-iabsolutCSSZEB YEnd IfX]b1vLXDyn_Ezn_ofA"eB.PrctG As _name_long ClGMC2H 'e@Poffrfat@errorsfirst 2!.biC-fSele&a3 a*j.StatusHBar@nFa&j Exit Sub zone:#MsgBox "An un:f@ejms occurredD - & E() & Chr(13)& "Ple cont@ John Andelon 01274 225750 or ja@gmail,com Q Aboning3iced8ure T!8 '+?' F un@R@fileA-sts(fN)" OBoo n 'B ath "" Then߃aA T G H`I``*%?!h*WorkbookIsOpen(wb +h'`TRUE `'!w.already o c) On B[ Resume NexdS`-s%`.*0b. ~_q@>- Sub makeLF~s1}(nAge, age_gp, B;Y,9gan, ng, popgroup2Ush )CSsex(1 To,!RqUmaxrowInteger, xcol(, 'SaPYboundpKZ4 Xd$tem0aKaS5 'orary stpgԉ hh_al ;" atwo HH typesh yrb\*B)#"Ma.lAL1 1` Then :FEi92 T}geach of thevs specified$&W.Adnametemp8_short(i\$e.N =O2 O rO  .C'gen|ermFC#long #F::,v5, 1).ValueEColumns(0"c:cWidth 0.1 'sw@u aAest HH typeij#-Xb:bN18Q a:a 7 d:d Select Wi5!7.Orient1t= 0+%7.1End !//t5t v56))H5%36 Ö0("d78"dWindow.FreezePanes}?TZ.PMove After:=i +QkNext ig%If'h\ke1DFi  Sub __SRP_7 B_VBA_PROJECTv=dir2__SRP_0.0* pHd VBAProject4@j = r = J< rstdole>stdole h%^*\G{00020430-C 0046}#2.0#0#C:\Windows\syst em32\e2.tlb#OLE Automation`EOffDicEOficEE2DF8D04C-5BFA-101B-BDE5EAAC42Egram Files\CommonMicrosoft Shared\OFFICE11\MSO.0DLL#M 9 .0 Ob LibraryJ"`ThisWorkbookG TisWrkbo 2 HB1xeetsAddr OrientationwMove>yAfterX BeforeSave _B_var_Datev_Defaultj _B_var_Time$ Worksheet`#% 6B tion&*\G{2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}#2.3#0#C:\Program Files\Common Files\Microsof@sB,!`"B+B|ShePet1GS@#eAXt1H2CNModule;dugB2O"{O`!C9raryK*yrU~~~~~~~~~~[ F”&L-pn  a ia   a ) ) VBAProject ThisWorkbookSheet1Module1F /C:\PROGRA~1\COMMON~1\MICROS~1\VBA\VBA6\VBE6.DLLVBA A i0F4C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXEExcel 9a@0FC:\Windows\system32\stdole2.tlbstdole IqPL-[DR?C:\Program Files\Common Files\Microsoft Shared\OFFICE11\MSO.DLLOffice a`Fmt-xE/4jFV] 7K$=DFWorkbookworkbook_beforesave @ c6F Range d6VBE6.DLL Ab  Ad FQEс@kG pH F#E/ꎀLDF Worksheetrates_validate fileExistsWorkbookIsOpenmakeLFrates_val =irow_nZirow_p\diagsR incremerU~~{    !saveasuicancel   srce_book__SRP_1lPROJECTwmVPROJECTSummaryInformation(ay  *\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  = `ThisWorkbook0R49bd0d2bThisWorkbook` Sheet10S49bd0d2b# Sheet1`Module10T49bd0d2b$Module1`0{P0.QLYW}ԼIiku \HU_[0: base>dir Excel+ VBAWin16~Win32MacVBA6# VBAProjectstdole`Officeu ThisWorkbook| _Evaluateworkbook_beforesave lsaveasuicancelSheets Range Time Sheet1 Module1b(rates_validateE srce_book calltypetotyears base_valid+WorkbookkorgangQsys_pathUnsheets}ngb^ n_of_years)nAgei`jag^kbsjaXylhh]irowqicol_!n_ARp mainvaluesubvalue:ic]myrange-myrange12myrange23n_fatal*note_rowmaxrow+x_temp3h IsNumeric$*MsgBoxRChrK~ vbCritical+}Round SelectionZFontUSizeBoldItalic!SumSaveAsf; errorzone)ErrofnameRxowbnameqage_gpyBaseYearmaxcol5nametemp;longnamexhh_alljlcColumnsp9 ColumnWidth ColorIndex NumberFormatRLocked BordersC" xlEdgeLeftL LineStylew xlContinuous[ ActiveWindow+ FreezePanesMz WorkshcalltypetotyearsfnamewbnamenAgeage_gpBaseYearorganng popgroup_longpopgroup_short:ThisWorkbookThisWorkbookSheet1Sheet1Module1Module1ID="{BD442D99-2738-11D4-BEA1-40A650C10000}" Document=ThisWorkbook/&H00000000 Document=Sheet1/&H00000000 Module=Module1 Name="VBAProject" HelpContextID="0" VersionCompatible32="393222000" CMG="ACAE318C3F5743574357435743" DPB="7371EEC9B4CAB4CAB4" GC="3A38A7126F136F1390" [Host Extender Info] &H00000001={3832D640-CF90-11CF-8E43-00A0C911005A};VBE;&H00000000 [Workspace] ThisWorkbook=220, 220, 1020, 668, Sheet1=0, 0, 0, 0, C Module1=0, 0, 800, 448, Z Oh+'0@H\l  J AndelinAndelinMicrosoft Excel@?.%W@v9՜.+,0 PX|  DELL Computer Corporation' Sheet1  Worksheets F!Microsoft Office Excel WorksheetBiff8Excel.Sheet.89qDocumentSummaryInformation8 CompObjm