ࡱ> 4s+0,*o#%b0CompObj`pi26".( n``Pj9Pj9foJ  Worksheet"&Tools&WindowChart"&Tools&Window(Visual Basic Module"&Tools&Window\pHumberto Barreto Ba= ThisWorkbook=+<n@"1.Times New Roman1.Times New Roman1.Times New Roman1.Times New Roman1.Times New Roman1. Times New Roman1.8Times New Roman1.Times New Roman1Arial1Arial1. Times New Roman1.Times New Roman1 Arial1.Times New Roman1. Times New Roman1.Times New Roman1. Times New Roman1.Times New Roman1. Times New Roman1. Times New Roman1.Times New Roman1Calibri1 Calibri1Calibri1 Calibri1 Calibri1Calibri1Calibri1,>Calibri1>Calibri1>Calibri1>Calibri1 Calibri1Calibri1?Calibri1h>Cambria1Calibri1 Calibri"$"#,##0_);\("$"#,##0\)!"$"#,##0_);[Red]\("$"#,##0\)""$"#,##0.00_);\("$"#,##0.00\)'""$"#,##0.00_);[Red]\("$"#,##0.00\)7*2_("$"* #,##0_);_("$"* \(#,##0\);_("$"* "-"_);_(@_).))_(* #,##0_);_(* \(#,##0\);_(* "-"_);_(@_)?,:_("$"* #,##0.00_);_("$"* \(#,##0.00\);_("$"* "-"??_);_(@_)6+1_(* #,##0.00_);_(* \(#,##0.00\);_(* "-"??_);_(@_)"C"#,##0_);\("C"#,##0\)!"C"#,##0_);[Red]\("C"#,##0\)""C"#,##0.00_);\("C"#,##0.00\)'""C"#,##0.00_);[Red]\("C"#,##0.00\)72_("C"* #,##0_);_("C"* \(#,##0\);_("C"* "-"_);_(@_)?:_("C"* #,##0.00_);_("C"* \(#,##0.00\);_("C"* "-"??_);_(@_)A<_("$"* #,##0.000_);_("$"* \(#,##0.000\);_("$"* "-"??_);_(@_)C>_("$"* #,##0.0000_);_("$"* \(#,##0.0000\);_("$"* "-"??_);_(@_)=8_("$"* #,##0.0_);_("$"* \(#,##0.0\);_("$"* "-"??_);_(@_)94_("$"* #,##0_);_("$"* \(#,##0\);_("$"* "-"??_);_(@_)E@_("$"* #,##0.00000_);_("$"* \(#,##0.00000\);_("$"* "-"??_);_(@_) 0.000000000 0.00000000 0.0000000 0.000000 0.00000 0.0000 0.000 0.0%0.0 0.0000000000 0.000000000000.0000000000000.00000000000000.000000000000000.0000000000000000.00000000000000000.000000000000000000.0000000000000000000.00000000000000000000.00000000000000000000 0.0E+00 0.000E+00 0E+00 #,##0.0 #,##0.000 #,##0.0000 #,##0.00000 #,##0.000000 #,##0.0000000#,##0.00000000                                                                      ff + ) , *      P  P       ! ` "        #   $ % a8 &      " !    *0  *8 (  ( " "  "   (        !         "    *8  *8 "        ,   $@ @  ,@ @  @ @  (@ @  (!@ @  !@ @  $!@ @  !!@ @ (0   (@ @   ,!@ @  (!!@ @  (  @  $@  (@ @  ,!@ @  ,!@ @  (!@ @  ,!!@ @  (@ @  ,!@ @  ,!@ @  (!@ @  ,!!@ @  (   (   (  (@  (  @  (   (   (  (@@   (  @   (@   (   (@   ( @ "8"@@ "8"@ "8 " @ "8"@ "8 " @ *8@ @ *0!@ @ "8@@ "8  @ "8@@ "8  @ *8!@ @  20% - Accent1 20% - Accent2 20% - Accent3 20% - Accent4 20% - Accent5 20% - Accent6 40% - Accent1 40% - Accent2 40% - Accent3 40% - Accent4 40% - Accent5 40% - Accent6 60% - Accent1 60% - Accent2 60% - Accent3 60% - Accent4 60% - Accent5! 60% - Accent6 "Accent1 #Accent2 $Accent3 %Accent4 &Accent5 'Accent6(Bad) Calculation* Check Cell+,-./Explanatory Text0  1Good2 Heading 13 Heading 24 Heading 35 Heading 46 7Input8 Linked Cell 9Neutral:Normal_AutoCorr;Normal_MonteCarlo <Note =Output> ?Title @TotalA Warning Text8 ``i̜̙3f3333f3ffff333ff333f33f33BBB\` @DocU OneVarMCSimI TwoVarMCSimc MainModuleg( Sample NumbersecsAverageSDMaxMin?Only the first 100 repetitions are displayed on this worksheet.Summary Statistics!Results of Monte Carlo SimulationSimulation Stats repetitionsVar1Var2secondsNotesiThis add-in permits Monte Carlo simulation of one or two cells from any Excel workbook with Solver calledat each repetition.`Check to see if RAND or RANDOM are used and display data warning only if a change is to be made.RAdded Howland latest Histogram code to handle displaying cells that do not change.Finally figured out conflict with the RefEdit. The solution was to put the two check box controls OUTSIDE the frame that contained the RefEditTwoCell control.OMicrosoft does have a little documentation on problems with controls and focus.7Updated language ID. Moved Log10 function to Histogram.Uninstall language fixed.FileSearch fixed! ""fcc||rd}A}  \(#,##0ef-"??_);_}A}  \(#,##0ef-"??_);_}A}  \(#,##0ef-"??_);_}A}  \(#,##0ef-"??_);_}A}  \(#,##0ef-"??_);_}A}  \(#,##0ef -"??_);_}A}  \(#,##0L-"??_);_}A}  \(#,##0L-"??_);_}A}  \(#,##0L-"??_);_}A}  \(#,##0L-"??_);_}A}  \(#,##0L-"??_);_}A}  \(#,##0L -"??_);_}A}  \(#,##023-"??_);_}A}  \(#,##023-"??_);_}A}  \(#,##023-"??_);_}A}  \(#,##023-"??_);_}A}   \(#,##023-"??_);_}A}!  \(#,##023 -"??_);_}A}"  \(#,##0-"??_);_}A}#  \(#,##0-"??_);_}A}$  \(#,##0-"??_);_}A}%  \(#,##0-"??_);_}A}&  \(#,##0-"??_);_}A}'  \(#,##0 -"??_);_}A}(  \(#,##0-"??_);_}}) } \(#,##0-"??_);_   }}*  \(#,##0-"??_);_??? ??? ??? ???}-}/  \(#,##0}A}1 a \(#,##0-"??_);_}A}2  \(#,##0-"??_);_}A}3  \(#,##0?-"??_);_}A}4  \(#,##023-"??_);_}-}5  \(#,##0}}7 ??v \(#,##0̙-"??_);_   }A}8 } \(#,##0-"??_);_}A}9 e \(#,##0-"??_);_}x}< \(#,##0-"?  }}= ??? \(#,##0-"???? ???  ??? ???}-}?  \(#,##0}U}@  \(#,##0-"? }-}A  \(#,##0M 20% - Accent1 ef %M" 20% - Accent2 ef %M& 20% - Accent3 ef %M* 20% - Accent4 ef %M. 20% - Accent5 ef %M2 20% - Accent6  ef %M 40% - Accent1 L %M# 40% - Accent2 L湸 %M' 40% - Accent3 L %M+ 40% - Accent4 L %M/ 40% - Accent5 L %M3 40% - Accent6  Lմ %M 60% - Accent1 23 %M$ 60% - Accent2 23ٗ %M( 60% - Accent3 23֚ %M, 60% - Accent4 23 %M0 60% - Accent5 23 %M4 60% - Accent6  23 %AAccent1 O %A!Accent2 PM %A%Accent3 Y %A)Accent4 d %A-Accent5 K %A1Accent6  F %9Bad  % Calculation  }%  Check Cell  %????????? ??? Comma( Comma [0]&Currency. Currency [0]G5Explanatory Text %: Followed Hyperlink;Good  a%G Heading 1 I}%OG Heading 2 I}%?G Heading 3 I}%239 Heading 4 I}%( HyperlinkuInput ̙ ??v% K Linked Cell }%ANeutral  e%"Normalb Note  wOutput  ???%????????? ???$Percent1Title I}%MTotal %OO? Warning Text %XTableStyleMedium9PivotStyleLight16BĘ7ɹq1  =M,QT  dMbP?_*+%;&A Page &PMF Canon iR5570 PCL6<h  odXLetterCanonY Canon iR5570/iR6570 PCL6ddd      d d d d dd@@d  d  d dd d"edd     ddd     d!!d      !"#$ddA     A d     @8o @*/ o @*/ AXXd2\SRGBCO~1.ICM\SRGBCO~1.ICM\SRGBCO~1.ICM CONFIDENTIALCONFIDENTIALHArialDefault Settings@"dXX??U} `=;;;;;;;;; ; ; ; ; ;;;;;;;;;;;;;;;;;;;~ `@ ` `~ `@  `~ `@  ` ~ ` @ ~ `@ ~ `%@ Dl       ;!;";#;$;%;&;';(;);*;+;,;-;.;/;0;1;2;3;4;5;6;7;8;9;:;;;<; !"#$%&'()*+,-./0123456789:;<>f0 >@ 7 Sheet1ggD&   ga|Jl  dMbP?_*+%;MF Canon iR5570 PCL6<h  odXLetterCanonY Canon iR5570/iR6570 PCL6ddd      d d d d dd@@d  d  d dd d"edd     ddd     d!!d      !"#$ddA     A d     @8o @*/ o @*/ AXXd2\SRGBCO~1.ICM\SRGBCO~1.ICM\SRGBCO~1.ICM CONFIDENTIALCONFIDENTIALHArialDefault Settings@"d??U} L}  D} F} F} G} V}  G} G}  @ G}  G}  F} @F} @ F}  F}  F} !* G} +1 F} 2 GEstimated rho in SUsgBBv BHJ BJ BJ B;B;B;BJ B; B; B; B; B; B;B;B;B;B;B;B;B;B;B;B;B;B;B;B;B;B;B;B; EFFFFFFFF !FFF#$FF%&FFFFF*&2FFFFFFFFFFFFFFFFA HIFFk  KKKKKKK(KFFFFFKKKKKKKKK ~ !K "KK#$KK%>&KKKKKFFFFFFFKKKKKKKKKKKKKKKKA~ L?M Ql g !F!?~ "F#F$F!?~ %F&FFFFF*&2FFFFFFFFFFFFFFFFA~ L@M Qm n      O!F!?~ "F@#F$F!?~ %F@&FFFFF*&2FFFFFFFFFFFFFFFFA~ L@M NaN p q|u? P O!F1?~ "F@#F$F!?~ %F@&FFFFF*&2FFFFFFFFFFFFFFFFA~ L@M f d 0n? o} !F!?~ "F @#F$F!?~ %F@&FFFFF*&2FFFFFFFFFFFFFFFFA~ L@MW e c>i@ P~} !F@)?~ "F@#F$F1?~ %F@&FFFFF*&2FFFFFFFFFFFFFFFFA~ L@MW h ilSQ? b !F1?~ "F"@#F$F1?~ %F @&FFFFF*&2FFFFFFFFFFFFFFFFA~ L@MXN_NNNN !F9?~ "F"@#F$F!?~ %F @&FFFFF*&2FFFFFFFFFFFFFFFFA~ L @ M X Q_NNNN  O !F!?~ "F(@ #F $F!?~ %F@ &FFFFF*& 2FFFFFFFFFFFFFFFFA~ L"@ M N_NNNN  O !F ?~ "F @ #F $F@)?~ %F@ &FFFFF*& 2FFFFFFFFFFFFFFFFA~ L$@ M NNNNNN  O !F@)?~ "F@ #F $F@)?~ %F"@ &FFFFF*& 2FFFFFFFFFFFFFFFFA~ L&@ M NNNNNN  !F`?~ "F@ #F $F1?~ %F"@ &FFFFF*& 2FFFFFFFFFFFFFFFFA~ L(@ M S NNNNNN  !F1?~ "F@ #F $F1?~ %F"@ &FFFFF*& 2FFFFFFFFFFFFFFFFA~ L*@MSNNNNNN !F?~ "F@#F$F9?~ %F"@&FFFFF*&2FFFFFFFFFFFFFFFFA~ L,@MSNNNNNN !F9?~ "F@#F$F9?~ %F(@&FFFFF*&2FFFFFFFFFFFFFFFFA~ L.@MNNNNNN !F?~ "F@#F$F!?~ %F(@&FFFFF*&2FFFFFFFFFFFFFFFFA~ L0@MNNNNNN !F!@~ "F@#F$F!?~ %F @&FFFFF*&2FFFFFFFFFFFFFFFFA~ L1@MNNNNNN !Fc@~ "F@#F$F ?~ %F @&FFFFF*&2FFFFFFFFFFFFFFFFA~ L2@MNNNNNN !F @~ "F@#F$F ?~ %F@&FFFFF*&2FFFFFFFFFFFFFFFFA~ L3@MNNNNNN !F0@~ "F@#F$F@)?~ %F@&FFFFF*&2FFFFFFFFFFFFFFFFA~ L4@MNNNNNN !F@)@~ "F?#F$F@)?~ %F@&FFFFF*&2FFFFFFFFFFFFFFFFA~ L5@M!FPk@~ "F#F$F`?~ %F@&FFFFF*&2FFFFFFFFFFFFFFFFA~ L6@M!F`@~ "F#F$F`?~ %F@&FFFFF*&2FFFFFFFFFFFFFFFFA~ L7@M!Fp@~ "F#F$F1?~ %F@&FFFFF*&2FFFFFFFFFFFFFFFFA~ L8@M!F1@~ "F#F$F1?~ %F@&FFFFF*&2FFFFFFFFFFFFFFFFA~ L9@M!Fs @~ "F#F$F?~ %F@&FFFFF*&2FFFFFFFFFFFFFFFFA~ L:@M!F @~ "F#F$F?~ %F@&FFFFF*&2FFFFFFFFFFFFFFFFA~ L;@M!F @~ "F#F$F9?~ %F@&FFFFF*&2FFFFFFFFFFFFFFFFA~ L<@M!F9 @~ "F#F$F9?~ %F@&FFFFF*&2FFFFFFFFFFFFFFFFA~ L=@M!F{ @~ "F#F$F?~ %F@&FFFFF*&2FFFFFFFFFFFFFFFFA~ L>@M!F@~ "F#F$F?~ %F@&FFFFF*&2FFFFFFFFFFFFFFFFADl B;!B;"B;#B;$B;%B;&B;'B;(B;)B;*B;+B;,B;-B;.B;/B;0B;1B;2B;3B;4B;5B;6B;7B;8B;9B;:B;;B;<B;=B;>B;?B;~ L?@ M !F@~ "F #F $F!@~ %F@ &FFFFF*& 2FFFFFFFFFFFFFFFFA~ !L@@!M !!FFF#!$F!@~ !%F@!&FFFFF*&!2FFFFFFFFFFFFFFFFA~ "L@@"M "!FFF#"$Fc@~ "%F@"&FFFFF*&"2FFFFFFFFFFFFFFFFA~ #LA@#M #!FFF##$Fc@~ #%F@#&FFFFF*&#2FFFFFFFFFFFFFFFFA~ $LA@$M $!FFF#$$F @~ $%F@$&FFFFF*&$2FFFFFFFFFFFFFFFFA~ %LB@%M %!FFF#%$F @~ %%F@%&FFFFF*&%2FFFFFFFFFFFFFFFFA~ &LB@&M &!FFF#&$F0@~ &%F@&&FFFFF*&&2FFFFFFFFFFFFFFFFA~ 'LC@'M '!FFF#'$F0@~ '%F?'&FFFFF*&'2FFFFFFFFFFFFFFFFA~ (LC@(M (!FFF#($F@)@~ (%F?(&FFFFF*&(2FFFFFFFFFFFFFFFFA~ )LD@)M )!FFF#)$F@)@~ )%F)&FFFFF*&)2FFFFFFFFFFFFFFFFA~ *LD@*M *!FFF#*$FPk@~ *%F*&FFFFF*&*2FFFFFFFFFFFFFFFFA~ +LE@+M +!FFF#+$FPk@~ +%F+&FFFFF*&+2FFFFFFFFFFFFFFFFA~ ,LE@,M ,!FFF#,$F`@~ ,%F,&FFFFF*&,2FFFFFFFFFFFFFFFFA~ -LF@-M -!FFF#-$F`@~ -%F-&FFFFF*&-2FFFFFFFFFFFFFFFFA~ .LF@.M .!FFF#.$Fp@~ .%F.&FFFFF*&.2FFFFFFFFFFFFFFFFA~ /LG@/M /!FFF#/$Fp@~ /%F/&FFFFF*&/2FFFFFFFFFFFFFFFFA~ 0LG@0M 0!FFF#0$F1@~ 0%F0&FFFFF*&02FFFFFFFFFFFFFFFFA~ 1LH@1M 1!FFF#1$F1@~ 1%F1&FFFFF*&12FFFFFFFFFFFFFFFFA~ 2LH@2M 2!FFF#2$Fs @~ 2%F2&FFFFF*&22FFFFFFFFFFFFFFFFA~ 3LI@3M 3!FFF#3$Fs @~ 3%F3&FFFFF*&32FFFFFFFFFFFFFFFFA~ 4LI@4M 4!FFF#4$F @~ 4%F4&FFFFF*&42FFFFFFFFFFFFFFFFA~ 5LJ@5M 5!FFF#5$F @~ 5%F5&FFFFF*&52FFFFFFFFFFFFFFFFA~ 6LJ@6M 6!FFF#6$F @~ 6%F6&FFFFF*&62FFFFFFFFFFFFFFFFA~ 7LK@7M 7!FFF#7$F @~ 7%F7&FFFFF*&72FFFFFFFFFFFFFFFFA~ 8LK@8M 8!FFF#8$F9 @~ 8%F8&FFFFF*&82FFFFFFFFFFFFFFFFA~ 9LL@9M 9!FFF#9$F9 @~ 9%F9&FFFFF*&92FFFFFFFFFFFFFFFFA~ :LL@:M :!FFF#:$F{ @~ :%F:&FFFFF*&:2FFFFFFFFFFFFFFFFA~ ;LM@;M ;!FFF#;$F{ @~ ;%F;&FFFFF*&;2FFFFFFFFFFFFFFFFA~ <LM@<M <!FFF#<$F@~ <%F<&FFFFF*&<2FFFFFFFFFFFFFFFFA~ =LN@=M =!FFF#=$F@~ =%F=&FFFFF*&=2FFFFFFFFFFFFFFFFA~ >LN@>M >!FFF#>$F@~ >%F>&FFFFF*&>2FFFFFFFFFFFFFFFFA~ ?LO@?M ?!FFF#?$F@~ ?%F?&FFFFF*&?2FFFFFFFFFFFFFFFFADZl@B;AB;BB;CB;DB;EB;FB;GB;HB;IB;JB;KB;LB;MB;NB;OB;PB;QB;RB;SB;TB;UB;VB;WB;XB;YB;ZB;[B;\B;]B;^B;_B;~ @LO@@M@!FFFFFFFFFF*&@2FFFFFFFFFFFFFFFFA~ ALP@AMA!FFFFFFFFFF*&A2FFFFFFFFFFFFFFFFA~ BL@P@BMB!FFFFFFFFFF*&B2FFFFFFFFFFFFFFFFA~ CLP@CMC!FFFFFFFFFF*&C2FFFFFFFFFFFFFFFFA~ DLP@DMD!FFFFFFFFFF*&D2FFFFFFFFFFFFFFFFA~ ELQ@EME!FFFFFFFFFF*&E2FFFFFFFFFFFFFFFFA~ FL@Q@FMF!FFFFFFFFFF*&F2FFFFFFFFFFFFFFFFA~ GLQ@GMG!FFFFFFFFFF*&G2FFFFFFFFFFFFFFFFA~ HLQ@HMH!FFFFFFFFFF*&H2FFFFFFFFFFFFFFFFA~ ILR@IMI!FFFFFFFFFF*&I2FFFFFFFFFFFFFFFFA~ JL@R@JMJ!FFFFFFFFFF*&J2FFFFFFFFFFFFFFFFA~ KLR@KMK!FFFFFFFFFF*&K2FFFFFFFFFFFFFFFFA~ LLR@LML!FFFFFFFFFF*&L2FFFFFFFFFFFFFFFFA~ MLS@MMM!FFFFFFFFFF*&M2FFFFFFFFFFFFFFFFA~ NL@S@NMN!FFFFFFFFFF*&N2FFFFFFFFFFFFFFFFA~ OLS@OMO!FFFFFFFFFF*&O2FFFFFFFFFFFFFFFFA~ PLS@PMP!FFFFFFFFFF*&P2FFFFFFFFFFFFFFFFA~ QLT@QMQ!FFFFFFFFFF*&Q2FFFFFFFFFFFFFFFFA~ RL@T@RMR!FFFFFFFFFF*&R2FFFFFFFFFFFFFFFFA~ SLT@SMS!FFFFFFFFFF*&S2FFFFFFFFFFFFFFFFA~ TLT@TMT!FFFFFFFFFF*&T2FFFFFFFFFFFFFFFFA~ ULU@UMU!FFFFFFFFFF*&U2FFFFFFFFFFFFFFFFA~ VL@U@VMV!FFFFFFFFFF*&V2FFFFFFFFFFFFFFFFA~ WLU@WMW!FFFFFFFFFF*&W2FFFFFFFFFFFFFFFFA~ XLU@XMX!FFFFFFFFFF*&X2FFFFFFFFFFFFFFFFA~ YLV@YMY!FFFFFFFFFF*&Y2FFFFFFFFFFFFFFFFA~ ZL@V@ZMZ!FFFFFFFFFF*&Z2FFFFFFFFFFFFFFFFA~ [LV@[M[!FFFFFFFFFF*&[2FFFFFFFFFFFFFFFFA~ \LV@\M\!FFFFFFFFFF*&\2FFFFFFFFFFFFFFFFA~ ]LW@]M]!FFFFFFFFFF*&]2FFFFFFFFFFFFFFFFA~ ^L@W@^M^!FFFFFFFFFF*&^2FFFFFFFFFFFFFFFFA~ _LW@_M_!FFFFFFFFFF*&_2FFFFFFFFFFFFFFFFADl````````````````````````````````B;aB;bB;cB;dB;eB;fB;~ `LW@`M`!FFFFFFFFFF*&`2FFFFFFFFFFFFFFFFA~ aLX@aMa!FFFFFFFFFF*&a2FFFFFFFFFFFFFFFFA~ bL@X@bMb!FFFFFFFFFF*&b2FFFFFFFFFFFFFFFFA~ cLX@cMc!FFFFFFFFFF*&c2FFFFFFFFFFFFFFFFA~ dLX@dMd!FFFFFFFFFF*&d2FFFFFFFFFFFFFFFFA~ eLY@eMe!FFFFFFFFFF*&e2FFFFFFFFFFFFFFFFA fUx``````P>@   7 Sheet2ggD&B   g[X(~  dMbP?_*+%;MF Canon iR5570 PCL6<h  odXLetterCanonY Canon iR5570/iR6570 PCL6ddd      d d d d dd@@d  d  d dd d"edd     ddd     d!!d      !"#$ddA     A d     @8o @*/ o @*/ AXXd2\SRGBCO~1.ICM\SRGBCO~1.ICM\SRGBCO~1.ICM CONFIDENTIALCONFIDENTIALHArialDefault Settings@"d??U} L} Y} Z} F} G}  G} G}  G}  G}  F} @F} @ F} 5 F} 6 GColumn LgBBv BJ BJ BJ B;B;B;BJ B; B; B; B; B; B;B;B;B;B;B;B;B;B;B;B;B;B;B;B;B;B;B;B; EFFFFFFFF!'6FFFFFFFFFFFFA H [ \ Fk  KKKKKKKjKFFFFFKKFFFFFFFFFFFFFFFFFFFFFFFKKKKKKKKKKKKA~ L? ]^ Ql g !'6FFFFFFFFFFFFA~ L@ ]^ Qm j       O!'6FFFFFFFFFFFFA~ L@ ]^ NaN r sLo?  w xr?}k?   O!'6FFFFFFFFFFFFA~ L@ ]^ r t ??  w yh ?  | !'6FFFFFFFFFFFFA~ L@ ]^B r s*?  w xV?  ~} !'6FFFFFFFFFFFFA~ L@ ]^B u v{n  z { ݿ  !'6FFFFFFFFFFFFA~ L@ ]^CN_NNNN !'6FFFFFFFFFFFFA~ L @ ]^ C Q_NNNN  O !' 6FFFFFFFFFFFFA~ L"@ ]^ N_NNNN  O !' 6FFFFFFFFFFFFA~ L$@ ]^ NNNNNN  O !' 6FFFFFFFFFFFFA~ L&@ ]^ NNNNNN  !' 6FFFFFFFFFFFFA~ L(@ ]^ S NNNNNN  !' 6FFFFFFFFFFFFA~ L*@ ]^SNNNNNN !'6FFFFFFFFFFFFA~ L,@ ]^SNNNNNN !'6FFFFFFFFFFFFA~ L.@ ]^NNNNNN !'6FFFFFFFFFFFFA~ L0@ ]^NNNNNN !'6FFFFFFFFFFFFA~ L1@ ]^~ T?NNNNNN !'6FFFFFFFFFFFFA~ L2@ ]^NNNNNN !'6FFFFFFFFFFFFA~ L3@ ]^NNNNNN !'6FFFFFFFFFFFFA~ L4@ ]^NNNNNN !'6FFFFFFFFFFFFA~ L5@ ]^!'6FFFFFFFFFFFFA~ L6@ ]^R!'6FFFFFFFFFFFFA~ L7@ ]^!'6FFFFFFFFFFFFA~ L8@ ]^!'6FFFFFFFFFFFFA~ L9@ ]^!'6FFFFFFFFFFFFA~ L:@ ]^!'6FFFFFFFFFFFFA~ L;@ ]^!'6FFFFFFFFFFFFA~ L<@ ]^!'6FFFFFFFFFFFFA~ L=@ ]^!'6FFFFFFFFFFFFA~ L>@ ]^!'6FFFFFFFFFFFFAD`ljrvvvlvvvllzlllV`VVVVVVV B;!B;"B;#B;$B;%B;&B;'B;(B;)B;*B;+B;,B;-B;.B;/B;0B;1B;2B;3B;4B;5B;6B;7B;8B;9B;:B;;B;<B;=B;>B;?B;~ L?@ ]^ !' 6FFFFFFFFFFFFA~ !L@@ !]^!!'!6FFFFFFFFFFFFA~ "L@@ "]^"!'"6FFFFFFFFFFFFA~ #LA@ #]^#!'#6FFFFFFFFFFFFA~ $LA@ $]^$!'$6FFFFFFFFFFFFA~ %LB@ %]^%!'%6FFFFFFFFFFFFA~ &LB@ &]^&!'&6FFFFFFFFFFFFA~ 'LC@ ']^'!''6FFFFFFFFFFFFA~ (LC@ (]^(!'(6FFFFFFFFFFFFA~ )LD@ )]^)!')6FFFFFFFFFFFFA~ *LD@ *]^*!'*6FFFFFFFFFFFFA~ +LE@ +]^+!'+6FFFFFFFFFFFFA~ ,LE@ ,]^,!',6FFFFFFFFFFFFA~ -LF@ -]^-R-!'-6FFFFFFFFFFFFA~ .LF@ .]^.!'.6FFFFFFFFFFFFA~ /LG@ /]^/!'/6FFFFFFFFFFFFA~ 0LG@ 0]^0!'06FFFFFFFFFFFFA~ 1LH@ 1]^1!'16FFFFFFFFFFFFA~ 2LH@ 2]^2!'26FFFFFFFFFFFFA~ 3LI@ 3]^3!'36FFFFFFFFFFFFA~ 4LI@ 4]^4!'46FFFFFFFFFFFFA~ 5LJ@ 5]^5!'56FFFFFFFFFFFFA~ 6LJ@ 6]^6!'66FFFFFFFFFFFFA~ 7LK@ 7]^7!'76FFFFFFFFFFFFA~ 8LK@ 8]^8!'86FFFFFFFFFFFFA~ 9LL@ 9]^9!'96FFFFFFFFFFFFA~ :LL@ :]^:!':6FFFFFFFFFFFFA~ ;LM@ ;]^;!';6FFFFFFFFFFFFA~ <LM@ <]^<!'<6FFFFFFFFFFFFA~ =LN@ =]^=!'=6FFFFFFFFFFFFA~ >LN@ >]^>!'>6FFFFFFFFFFFFA~ ?LO@ ?]^?!'?6FFFFFFFFFFFFADJ lVVVVVVVVVVVVV`VVVVVVVVVVVVVVVVV@B;AB;BB;CB;DB;EB;FB;GB;HB;IB;JB;KB;LB;MB;NB;OB;PB;QB;RB;SB;TB;UB;VB;WB;XB;YB;ZB;[B;\B;]B;^B;_B;~ @LO@ @]^@!'@6FFFFFFFFFFFFA~ ALP@ A]^A!'A6FFFFFFFFFFFFA~ BL@P@ B]^B!'B6FFFFFFFFFFFFA~ CLP@ C]^C6FFFFFFFFFFFFA~ DLP@ D]^D6FFFFFFFFFFFFA~ ELQ@ E]^E6FFFFFFFFFFFFA~ FL@Q@ F]^F6FFFFFFFFFFFFA~ GLQ@ G]^G6FFFFFFFFFFFFA~ HLQ@ H]^H6FFFFFFFFFFFFA~ ILR@ I]^I6FFFFFFFFFFFFA~ JL@R@ J]^J6FFFFFFFFFFFFA~ KLR@ K]^K6FFFFFFFFFFFFA~ LLR@ L]^L6FFFFFFFFFFFFA~ MLS@ M]^M6FFFFFFFFFFFFA~ NL@S@ N]^N6FFFFFFFFFFFFA~ OLS@ O]^O6FFFFFFFFFFFFA~ PLS@ P]^P6FFFFFFFFFFFFA~ QLT@ Q]^Q6FFFFFFFFFFFFA~ RL@T@ R]^R6FFFFFFFFFFFFA~ SLT@ S]^S6FFFFFFFFFFFFA~ TLT@ T]^T6FFFFFFFFFFFFA~ ULU@ U]^U6FFFFFFFFFFFFA~ VL@U@ V]^V6FFFFFFFFFFFFA~ WLU@ W]^W6FFFFFFFFFFFFA~ XLU@ X]^X6FFFFFFFFFFFFA~ YLV@ Y]^Y6FFFFFFFFFFFFA~ ZL@V@ Z]^Z6FFFFFFFFFFFFA~ [LV@ []^[6FFFFFFFFFFFFA~ \LV@ \]^\6FFFFFFFFFFFFA~ ]LW@ ]]^]6FFFFFFFFFFFFA~ ^L@W@ ^]^^6FFFFFFFFFFFFA~ _LW@ _]^_6FFFFFFFFFFFFAD lVVV>>>>>>>>>>>>>>>>>>>>>>>>>>>>`B;aB;bB;cB;dB;eB;fB;~ `LW@ `]^`6FFFFFFFFFFFFA~ aLX@ a]^a6FFFFFFFFFFFFA~ bL@X@ b]^b6FFFFFFFFFFFFA~ cLX@ c]^c6FFFFFFFFFFFFA~ dLX@ d]^d6FFFFFFFFFFFFA~ eLY@ e]^e6FFFFFFFFFFFFA fUx>>>>>>P>@"    7 Sheet3ggD&B &A Page &P&ffffff?'ffffff?(?)?"P??d> P MainModule}>N__SRP_6j__SRP_7"ThisWorkbook#5   !"$%&'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWYZ[\]^_abdefghijklmnopqrtuvwxyz{|}~v "*`  0 :  &` "  *"2  0 K8 X ;x  D  $R   ( 8 ,S@ 8p  <n Ls T@   B   8  @ P >vX         0  ( " 0P8 "   <"(HP X <h"2  $(P X $` `(   " ( .0` 0h $ T &8 N` J  ( 0@JH  (   0 J8      (  2 8 DX < & ( *8 hx @ &  *  P`h p: &  * 8HP X 6p6 * ( 0PzX @.(X ` p DOCUMENTATION/C> This add-in does a Monte Carlo simulation of one or two cellsog< from ANY Excel workbook using a non-volatile RNG RANDOMNV()\To4 This enables it to use Solver with each repetition.ion( It was built off the Feb 2005 MCSIM.xla INITIAL DATE: 26 Mar 2005 REVISIONS:- To see complete documentation, see Doc sheet User Input; It is assumed that the user has a workbook with some cells that use random number formulas Written by Humberto BarretoE for Barreto/Howland Introductory Econometrics, CUP, Forthcoming 20057 Idea behind the Progress Bar is due to John Walkenbach3 http://support.microsoft.com in the Knowledge Base( FMRG references in the RNGandSortModule% All other ideas from Barreto/Howland Contact Humberto Barreto Dept of Economics Wabash College Crawfordsville, IN 47933 E -mail: barretoh@wabash.edu phone: (765) 361-6315 FAX: (765) 361-6277- Run the MCSim add-in by executing this macro2 It will bring up a form which enables the user to input necessary information]   +in case the user is in a graph or something load form with where the user is &!( !"($make sure it's A1 style *!, .This add-in will not work with columns as numbers. Please execute Tools: Options, click the General tab, and uncheck the R1C1 reference style option.2Unacceptable0hRecord All Selected Cells (256 Max) 5Times New Roman(/ sla074CommandButton1aO0 ;4CommandButton2a; $#Frame1$#Frame2; ( 0pCheckBox2tto{S\ 1t0CheckBox1tto{Select cells on sheet, then execute Tools: MCSim ;^HxMET@"(Lf6 LLLLLL"LLL` To = JH`k66J`Lxng`Nv MCS`TXAL D d@ R% t`  :` `vcell `cc@ H `(Xp@P*Zjpr0@p @` @\ @T @D @4 @H$ @ @0 @H @` @x @ @ @ @ @ 8"0`%(@` @x @| @l @\ @L @< @n, @ @@x @ t @d @8` @P\ @hX @T x  x HG $*\Rffff*1V49d07e3f$*\Rffff*1U49d07e3f*\R5*#50*\R1*#2ac*\R1*#e8*\R5*#8c*\R5*#4d*\R5*#4e*\R1*#d7*\R0*#f*\R1*#6c*\R5*#4f*\R5*#50*\R5*#30*\R0*#1*\R0*#14$*\Rffff*1T49d07e3f*\R1*#f1z*\G{0002E157-0000-0000-C000-000000000046}#5.3#0#..\..\..\..\..\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB#Microsoft Visual Basic for Applications Extensibility 5.3*#1bz*\G{0002E157-0000-0000-C000-000000000046}#5.3#0#..\..\..\..\..\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB#Microsoft Visual Basic for Applications Extensibility 5.3*#3fz*\G{0002E157-0000-0000-C000-000000000046}#5.3#0#..\..\..\..\..\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB#Microsoft Visual Basic for Applications Extensibility 5.3*#3b,   D B` : .  8 4P  B &  ( L@ > : . ,@ p      $  8 X 4` 8 "  " 6 &@h     2 Reference Style4 86A@0kxAdd Solver ReferenceA@: B@<o8Excel 2003 workaround+must remove solver reference before closingoDELETE THE TEXT BELOW (FROM Public Sub auto_close() to End Sub) IF YOU HAVE MANUALLY ADDED THE SOLVER REFERENCE`Xsolver @!B%D @!B!DB@F @B@Ho) ****************************************4 This macro adds a menu item so the ShowMCSolverForm macro can be run from the menu define variables]]'N$ Make sure the Tools menu is visibleWorksheet Menu Bar$P(Rcheck language] *!V$WThanks to John Talyor Z *%XTools'T=it's not English, try to get the word for Tools from the userdThe language of this installation of Excel does not appear to be EnglishUS. Please enter the word that Excel is using for Tools on the main menu across the top of the screen. For example, in Spanish, it is Herramientas.Get the word for TOOLS Herramientas *%\'TkdTools'Tk TWorksheet Menu Bar$P%^(R1 Check to see if there is a MCSimSolver menu item'N L TWorksheet Menu Bar$P%^!^!` L TWorksheet Menu Bar$P%^%^!bMCSimSolver ...MMeans the MCSimSolver menu item is already there, make MCSimSolverIsHere True'NdX;Means the MCSimSolver menu item is not there, so do nothingk L1 Add the MCSimSolver menu item if it is not there N]( jl TWorksheet Menu Bar$P%^!^%h.d dShowMCSolverForm9lMCSimSolver ...9bq(k * Message to the user when add-in is loaded;You have successfully loaded the MC Sim with Solver add-in. $p $ptIn Excel 2007, click MCSimSolver in the Add-Ins tab. In older versions of Excel, access the the macro by executing  T: MCSim Solver... rMC Sim with Solver$0'noHMCSimSolver ...ToolsWorksheet Menu Bar$P%^%^!lA@0op Check Solver Reference]6 Check all of the references to see if any are missingx v @!B!D!` v @!B%D!zFFk v5 Check all of the references to see if any are Solver| v @!B!D!` v @!B%D!Solver ~ Fk v Run AddSolverReference macroA@| Remove the missing referenceZ Must be done outside of loop so that .Count in Missing loop (last ref checked) is correctF v @!B%D @!B!DB@FxF o ' Attempt to add Solver programmaticallyF* Check to see if this is Excel for Windows *!V$W Add the Solver.xla referenceM 9/2003 addition to catch XP version of Solver that is called simply "Solver" 10/2003 fix End With code errorG 5/2007 workaround to removal of Application.FileSearch from Excel 2007C Must add "Public FileSearch As New CLRFileSearch" to top of moduleFind Solver or Solver.xla C@ *!99 Solver*.*91 this does catch plain "solver" with no extensionC@qX !!`  % @!B!DB@d "expand the search before giving up C@C:\99 Solver*.*9$1 this does catch plain "solver" with no extensionC@5!` 7 @!B!DB@d8  Giving upSolver cannot be found. It must be installed from the Office CD in order for the Comparative Statics Wizard to function properly. 8Fatal Error: Solver Not FoundA@0kh q` kX "Save the add-in with the reference @B@H, Check to see if this is Excel for Macintosh *!V$Me Add the Solver reference> Mac doesn't support directory search so let's take four shotsX First attempt *!:Office:Excel Add-ins:Solver @!B!DB@if successful save and exit sub # Save the add-in with the reference @B@H|kxSecond attempt' *! :Office:Excel Add-ins:Solver.xla @!B!DB@if successful save and exit sub # Save the add-in with the reference @B@H|k Third attempt' *!:Office:Add-ins:Solver.xla @!B!DB@if successful save and exit sub # Save the add-in with the reference @B@H|kFourth attempt0last chance if can't find it, go to ErrorHandlerP *!:Office:Add-ins:Solver @!B!DB@# Save the add-in with the reference @B@H| Neither Win nor MacdoThis machine seems to be neither Windows nor Mac. Macros that rely on Solver may have to be manually adjusted.A@0k |9 Handling the error when the Solver add-in can't be foundThe Solver reference cannot be found. You will have to manually add this reference. Launch the Visual Basic Editor from Excel, then execute Tools: References. Click the Browse button, navigate to the Solver add-in and open it. Make sure it is checked in the available references list.A@0|oǹAttribute VB_Name = "MainModule" ' DOCUMENTATION@This add-in doe MonCarlo simulation of one or two cellsfrom ANY Ex workbook using a non-voCle RNG RANDOMNV()enabl{it to9e Solver with each repeti.6It was builtf the Feb 2005 MCSIM.xlaNITIAL DATE: 26 Mar REVISS:Txee compledoc ument, s Doc sheetUsSInpu' KssdJahՀLuhXamsoԆth randnumb.for8Writtenh byHBarreto /Howl Intrctory Econcs, CUP, Forth>mPIdea@ behin3e Progress :dueAlJohn Wal0kenbk http://supp@.microsoft. kKn'edge seA FMRGzferencnv0S All o@r ri)s ¡L=1CacgQKDepEicBWabash Colleg&CrawVdsvi, IN 47933"#E -mail: bah@w.eduph: (765) 361-6315FAX2773C.Ru?imEby executjtmaWGb braupo@m whi_@hlA/REiaW $ne-saCinaB^ Public FileSear As New CLRc Sub Shhow`oaxKmA}On Err Recxt ' AcasHdJ`a graph!dngload #whDD i 7.RefEditOneC!.ValU= ActivCAddY@ make sure it's A1 styl ?If App ""a SS`xlR1C1 T@hen MsgBox pࢠpt:=")w3nocolHumns }s. Ple"<e Tools:X Op vckGeneral@ tab, |u`nchecr&ei o.", Title`Unacc`a `& Buttons:=vbC@ical EIf'(CCac(4aE GH'C3¥@around4ustmov`5K3be Clo!ϠDELETE TH`EXT BELOW (F0ROM dZUauto_ce()A) IF YOU HAVE MANUALLY ADDEDb SOLVER REFERENCEe q_ C"W.VB`jetct'Ls@B(4"s!" uSav , :' *@- 9FHquP2em soOB(>acapje rpLYGH $P!im"MenuIterC' defivarib DPj PHLoqAIsH ,Bp3eap9`= FalAg M? 6 Ss visi Comm6 fs("q b kr").V@ATrupc8xguaqd mvy S x @FLeft(FOp=A^Sys, 1")"W"F'T hankswTalyor KernaCal(xlNonEnglishFunP=) <> ! QLE @ 'iQcK, tbgecYK#yY?0> BS("T 5qRninstalǨCBd11 appibe  US.T0`fB -C Ю{a }ns vPAt op# HcreenZ0n0yaQ,SpantQ(ramisW"GOOLSV", 2=QCheckRef@erenceF' ˆ Di Asteg ercall r&`o see if any are miDss@V M: For i = 1 To This>book.VBProject.!s@untH If(i).IsBrokenTrueI enOGo"Remove:OrIfNext iqD`dOLVERCFCInSt,#FName[C#µT4pm) > 0Jfou ndJ)8 ' pRun w#&> r /-Exitj "6zL Q! Must be done ouPtsidfop soat #L DT(la cged) is corrT E-`YJU QEe-ȉy#CyAttem`pt toezp@rogramac!wy Orr+ qEAH`le^r:A'sytu C Win@dows @OLeft(App`A.Oper ngSys , 1) p"W" pA `B7@.xla79/ 3icatch XP Ac9!4Psimp@"" 10 fix )Wco@BeEA5!7 warA\rLaIFileSearfrom! BOA@ "Pub"  New CLjR"! tRmoduleF@,e8`5c% ݃. CL = PathJFs !7c*.*" '`--doea2Hpla3"s}"S~noputenAxErv s# .Fa_H4Nc=%F0a(1cpxIEls I'xexp`0be.e givup3 /ep.&"C:\p)q. Qk7Ra  h 1rq?a?%} }u u'Gt4? canno_BT@. It mai8nstB;aOffice CD`or1aNSqL$StpcsOzaro funr& `V`OlycCr`KV@vFa RV:TFD4  4:`]3p6D4%;D `"%p$ p Dc]@p5D CX RD (%'%'%'%'%: %:` % p$ p D`1Xp5D/ CXSlXF%\:`%p$ dp H\  ,  )p\pvnxlXF%\:`%p$ dp H\  , WDA4")p\6D4dsx@lXF%\:`%p$ dp H\  , DA4:]3)p\6D4vpvx xf kv0v%lXF%\:`%p$ dp H\  , W4$)p\64$ l    c D  $ '<'L:N\\@:`D4$:lXF|:l^i,6D4$|l\L<`4,XHTp\D4$|l\L<0 H  0   0   RrU $`$`$`$`$!`$I`n8 ^^#<эA$ҜN^F*e Ex=Zx*e Ex=ZэA$ҜN^MEPSLSS6" <<<N0{00020819-0000-0000-C000-000000000046} @`8|kT `X`T%@ %@0 %JOLVER.L@@\TG $*\Rffff*1T49d07e3f$*\Rffff*1U49d07e3f4" "0 8 " @ 8H    DX$` J  Put the MCSim menu item inA@Jo@@1 Delete the new menu command from the Tools menu.xcheck language] *!V$WThanks to John Talyor Z *%XTools'TCS=it's not English, try to get the word for Tools from the userdThe language of this installation of Excel does not appear to be EnglishUS. Please enter the word that Excel is using for Tools on the main menu across the top of the screen. For example, in Spanish, it is Herramientas.Get the word for TOOLS Herramientas *%\'TkpdhTools'TkPMCSimSolver ... TWorksheet Menu Bar *%P%^%^B@oHAttribute VB_Name = "ThisWorkbook" Bas0{00020P819-0C$0046} |GlobalSpacFalse dCreatablPredeclaIdTru BExposeTemplateDerivBustomizD2Sub _AddinInstall() ' Put the MCSim menu it em inqdd SolverM I End , 0Uni1Dele#3new2comma"fromA@Tools . On Error ResuNext -check languagRD/ByAs Sng If Left(Application.Oper@ngSyBs3, 1)W " The@='Thanks to John Ta0lyorInternal(xlNonEnglishFuncAs) <> Aa{DA5m)AA<'it's not , trygegword fB8Luse&E£ K*putB0ox("8L of]is@zB2Excel doe$appear A#be(US. hPleAeAe )tha1B using.on!B mainCacrosZtop$e scree n. Fh  8 &  0 H X 6x 8 , t & 8 &  8 P ` p          8 HX ` h x  X" @ P` h p   Z  (HX  p "      (H @X @  ( &0Xp        J 8`, &(08 "@"h & >0 ,H x     P0  @ $`  :   (*@ p  8 &  284p (&( @ P h x    . 0  H  X h *p  ^ 8 !>X!! "! ! !$""0"X" `"x""" " " " " ##8#$@#h# tp# d#P$p$0$ $ $ ($ % % 0%H%4h%&% %2%2&8H&8&&&& && ' '('0'8'@'H'P'X'`'x''''''''''''( . ( P( <h( ( (( ( ( )) ) 0)*H)*x)  ))  8)* (*&H*  2p*4*  (*+& +H+`+ x+ + + ++  + .+, ,8,0P, , ,, *,,, ^- 2x->-- ". 8. H.$`.".".  . .//0/ H/`/x// / / / 0 0 (81 `1x1 P20`2 222$22 23(3$03X3 `3x333334 (84 `4 p4 4444&4 52(52`5858566(6 86H6 `6p6x66666666666677 7(70787@7H7h7 Np7 7 <X8 8 "88 8,8 :(::::::;D;<= .=8=@= P=`= `>p>??? ?(? 8?H??? ?  ? @@<P@<@H@ ,A "HApANAAA "B8BBB @B<(C<hC<CHC ,0D"`DDNDDE "(EPEEpFF NF FF G BGXGpG|xG G .H8H HHXH XIhIJJJJ J 0J@JJJ J JK0K HK XK "hK KK KK KK &K L (LHL &PL xLL&LL LL &M 8M HMhM pM M M M M "M M MN "NNN" N HN`XNNNNNNNfN PO"XO`OhO"pO O`OPPP P(P0Pf8P P "PPPPx] ]X]p]]]]](]@]X]p]ge]0]H]`]] ; These are the proposed Widths of the bins in the histogram]]]]](]H]h]]]]] !"!$$!!'run Solver with each repetition, all cells that contain RAND() will be changed to RANDOMNV(), a non-volatile RNG function that enables Solver to be used. Please be advised that your data will be changed. You may want to copy the existing sheet and run the Monte Carlo on the copy in order to not destroy your original data. Instead of F9, use CTRL-ALT-F9 to recalculate and draw new random numbers. Do you want to proceed? Data Alteration Warning$0'   A@r|pkhMake the data changerand()MCSIMSOLVER.XLA!RANDOMNV()     B@k random()  %.  do nothingd Warn user of data changeIn order to run Solver with each repetition, all cells that contain RANDOM() will be changed to RANDOMNV(), a non-volatile RNG function that enables Solver to be used. Please be advised that your data will be changed. You may want to copy the existing sheet and run the Monte Carlo on the copy in order to not destroy your original data. Instead of F9, use CTRL-ALT-F9 to recalculate and draw new random numbers. Do you want to proceed? Data Alteration Warning$0'   A@r|h k` Make the data changerandom()MCSIMSOLVER.XLA!RANDOMNV()     B@k Number of repetitions !!$' P Track the Time elapsed ' Get the calculation setting *!' Set to manual  *( Turn off screen updating *( Show the Progress Bar ! (R9 general variables not used in this histogram application'':handle user's request for more tracked cells, called MCRaw !!$declare array for results]X] Get number of var to be tracked  !&  ! $ '$   $ You have selected more than 256 cells to record. This cannot be accomodated on an Excel sheet. You may have inadvertently selected a large range of cells. Please correct this and try again.2Too Many Cells to Record4 86A@0 A@r|(k add a sheet for MCRaw (B@hCI added 1 to Sheets.Count and used 2 instead of 1 to make the MCRaw1sheet number correspond to the MCSim sheet number  (!`MCRaw (!`  $(!names taken can't use that name $(! (*kname wasn't taken so use itMCRaw (!`  (get the sheetname for later use]* !',7put down cell addresses in first row as labels for data'v  !&  ! %( v ,$(%($ v 'v   size array  $"k Imake sure you are in the original sheet to be able to access Solver model $(B@H Run the Monte CarloDetermine if a One or Two Var  it's a OneVar v  *B@.)draws a new random number from RANDOMNV() run SolverA@04A@2Get the result !"!$ $(%!$ v+ Track Progress v '2 Update the Caption property of the Frame control. 0%$ ! (b Widen the Label control.  ! !  !6(, The DoEvents allows the UserForm to update. v 2  *!8%: GA@j"load array if user requested MCRaw !!$'initialize for next rep  !&  !  ' !$ v +"  kp v(put down results if user requested MCRaw !!$   L $  L$"  L ,$(%($ L k#Get Summary stats and put them down     A@<WBasicFour(data() As Double, MEAN As Double, SD As Double, MAX As Double, MIN As Double)1Copy the MCSimSolver sheet to the user's workbook B OneVarMCSimMCSimSolver.xla$>%(B@@MCSim (!` (get current MCSim sheet name !'Put down first 100 vd  *!8%: v$ v $($  +  +  +  +Make histogram**** Width histrange ^    $D'd    $F'knHistogramMaker FirstCellArray, myReps, 31, xmean, 0, 0, 0, ActiveSheet, ActiveSheet.ChartObjects("EmpHist"), 1^(values, noobs, Nobins, center, manual, minval, maxval, histsheet, histogram, whichhistonsheet !"!$B2$($ !"!$AL1$($ %P( %$.histsheet.Activate (H"make a chart on the existing sheet JB@h P L(N add a series VT  LB@Raa1:ab3 %Z LB@XC one seriesEmpHistChart 1 %\(size it $(%`!EmpHist %^( $(%d!bEmpHist %^(b $(%d!f EmpHist %^(f $(%`!EmpHist %^( L!hB@H p j!l(n L!rB@H jB@ v L%tB@H j!x |9z p9~q j p9 p9 p9q v L%t!B@H L!hB@H  L%t99qH v L%t99q L9 Empirical5!(dq         A Label Output5This is the end of the One Var Monte Carlo simulationdPit's a Two Var  v  *B@. run SolverA@04A@2Get the result !"!$ $(%!$ v+ !!$ $(%!$ v+ Track Progress v '2 Update the Caption property of the Frame control. 0%$ ! (b Widen the Label control.  ! !  !6(, The DoEvents allows the UserForm to update. v 2  *!8%: GA@j"load array if user requested MCRaw !!$'initialize for next rep  !&  !  ' !$ v +"  k8 v(put down results if user requested MCRaw !!$   L $  L$"  L ,$(%($ L kX#Get Summary stats and put them down     A@<     A@<WBasicFour(data() As Double, MEAN As Double, SD As Double, MAX As Double, MIN As Double)+Copy the MCSim sheet to the user's workbook B TwoVarMCSimMCSimSolver.xla$>%(B@@MCSim (!` (get current MCSim sheet name !'Put down first 100 vd  *!8%: v$ v $($ v$ v $($ v  +  +  +  +  +  +  +  +Dim myMin As DoubleDim myMax As DoubleDim myHistWidth As DoublemyMin = Application.WorksheetFunction.Min(Application.WorksheetFunction.Min(FirstCellArray), Application.WorksheetFunction.Min(SecondCellArray))myMax = Application.WorksheetFunction.Max(Application.WorksheetFunction.Max(FirstCellArray), Application.WorksheetFunction.Max(SecondCellArray))"myHistWidth = (myMax - myMin) / 10Make histogram[SuperimHistoMakerScaled FirstCellArray, SecondCellArray, myReps, 31, (xmean + x2mean) / 2, 0, 0, 0, ActiveSheet, ActiveSheet.ChartObjects("EmpHist"), "Var1", "Var2",' myMin, myMax, myHistWidth, 2 get width %P( %$. ^    $D'd8    $F'k ^    $D'd    $F'k Label Output !"!$B2$($ !!$C2$($ !"!$I4$($ !!$K4$($ !"!$AL1$($ !!$AM1$($"make a chart on the existing sheet JB@h P L(N add a series VT  LB@Raa1:ac3 %Z LB@XC two seriesEmpHistChart 1 %\(size it $(%`!EmpHist %^( $(%d!bEmpHist %^(b $(%d!f EmpHist %^(f $(%`!EmpHist %^( L!hB@H p j!l(n L!rB@H jB@ v L%tB@H j!x |9z p9~qx  j p9 p9 p9qP v L%t!B@H L!hB@H  L%t99q v L%t99q L9 Empirical5!(dq               A(values1, values2, noobs, Nobins, Center, manual, minval, maxval, histsheet, histogram, myname1, myname2, histbegin, histend, histwidth, histno)5This is the end of the Two Var Monte Carlo simulationk`do the existing sheet option !!$]Please enter the number of the existing MCSim sheet to which the results of this simulation will be written. Simulation results on the existing MCSim sheet will be overwritten and lost. Click Cancel if you are unsure.Existing Sheet Name Requested MCSim Number *%\' fYou have canceled writing output to an existing sheet. No changes to an existing sheet have been made. rNo Writing to Existing SheetA@08k0] MCRaw 'MCSim 'I5 $(%!$AverageI6 $(%!$SDI7 $(%!$MaxI8 $(%!$MinThe Average, SD, Max, and Min results are written in cells J5:J8, with labels in cells I5:I8. Cells I5:I8 of the chosen existing sheet do not contain this information so this may not be an MCSim sheet. Thus, no changes to the existing sheet have been made. 80Existing Sheet May Not be MCSim Sheet CompatibleA@0k(check to make sure existing sheet exists $(B@H  The sheet name you entered does not exist. Please enter the name of the existing sheet on which you want to overwrite the MCSim results with the results of this simulation.Existing Sheet Name Requested MCSimNumber *%\' fYou have canceled writing output to an existing sheet. No changes to an existing sheet have been made. rNo Writing to Existing SheetA@0kk' $(B@H  SThe existing sheet cannot be found. No changes to an existing sheet have been made. rNo Writing to Existing SheetA@0(k B@  it's a OneVarb3:b102 $(%!$b3:b102 $(%($f3:f4 $(%!$f3:f4 $(%($j5:j8 $(%!$j5:j8 $(%($ AH1:AL10000 $(%!$ AH1:AL10000 $(%($&update the histogram on existing sheetEmpHist $(%^B@ L%!'  $AL$2:$AL$ '?&the 10 characters in the search string  ,'     $'EmpHist $(%^B@=SERIES( !!$AL$1, ! !$AK$2:$AK$ , ! !$AL$2:$AL$ ,1) L%(K15 %B@Hd=b3:c102 $(%!$b3:c102 $(%($f3:f4 $(%!$f3:f4 $(%($j5:j8 $(%!$j5:j8 $(%($l5:l8 $(%!$l5:l8 $(%($ AH1:AM10000 $(%!$ AH1:AM10000 $(%($&update the histogram on existing sheetEmpHist $(%^B@ L%!'  $AL$2:$AL$ '?&the 10 characters in the search string  ,'     $'EmpHist $(%^B@=SERIES( !!$AL$1, ! !$AK$2:$AK$ , ! !$AL$2:$AL$ ,1) L%(=SERIES( !!$AM$1, ! !$AK$2:$AK$ , ! !$AM$2:$AM$ ,2) L%(K15 %B@Hkp9Hdelete new MCSim sheet since the user wanted output on an existing sheet *( $(B@ *(<output to MCRaw sheet if user selected record selected cells !!$]8CDo you want to write the output to the existing MCRaw sheet called ? MCRaw Output Option$0' (check to make sure existing sheet exists $(B@H  The sheet name you entered does not exist. Please enter the name of the existing sheet on which you want to overwrite the MCRaw results with the results of this simulation.Existing Sheet Name Requested MCRawNumber *%\' fYou have canceled writing output to an existing sheet. No changes to an existing sheet have been made. rNo Writing to Existing SheetA@05k5k5' $(B@H  SThe existing sheet cannot be found. No changes to an existing sheet have been made. rNo Writing to Existing SheetA@005k(5 B@   L $  L$"  L $(%($ L delete original MCRaw sheet *( ,$(B@ *( $(B@HMCRaw overwrite end to IFk4record selected cells end to IFk3existing sheet end to IFk3 Find Time elapsed and report it 'K15 %B@H   Q$($ $($run link checker I1$($ Return the calculation setting  *(One last calculationA@Put away the form A@r Turn off screen updating *(o02P Put away the form A@ro11] !"!$$%('  Your entry, !"!$2 , is not a valid cell address. Please try again.2A@0'|81d01'| 1k1 Your entry, !"!$ , has caused error,   . Please try again.2A@0o00]( !!$$%('  Your entry, !!$2 , is not a valid cell address. Please try again.2A@0'|/d/'|/k/ Your entry, !!$ , has caused error,   . Please try again.2A@0oX/@ !"B@o8/0/aAttribute VB_Name = "MCSolverForm" Bas0{6D280F7A-B63F-4017-927036CA0D049E}{A01E345F-256J1A5-BAA2-EF3FDA033B43} dGlobaBlSpacoFalse Cre atablPr@edeclaI"dTru BEx0pose0TemplateDerivCustomizDOption 0licit Pub CellV alidSngTest As Boolean9= Sub CommandButton1_C"k()Dim iLong First1 1 Array()Do$ESecondQGmyreps!PctdoneASinglDmystartti@=VarianGA yourseng xmGA,, xSDmaxinC@Tx22L22mViewT{mA0AllA@shistRangCC$myen$dTBw1G/, w2G ' T he@ the proWid@ths ofbpins @/ ogramjjJilHjkkI@nteger#a !(CMCRaw myOriginalSheetb()UmyC@urrentimRSLpPosip myEnd = B().RefEditOnef.`zue).Pq2nt.!ta'Get user-1px@d 2'u c 'make sure it's a v A|If= E' 'Unlo8ad AExit`# Ifexact ploca")dKA ZaasT`L  can bead!Pa' Obje with allual PWertie @ccessi`  &@//Addr (exter`L:=`'U/error check &In@*(1,;f).ula, "="`, vbT@ pA;) < 1*M sgBoxpmpt:="s seNl`@=B%donHot `tara f sou>ue wi`#łha. P }@try ag. s:=vbCr\cal, TPitleFKl E`_?3_'/itTwo/yP""D$Gc E|..!.P @jt/ _/rpPR/9nd/J Acvt`H.)@J).))p (/(/(he=o(De(s} (( ((l((ti(( ooSub$EIu U''< to kS4c3dxmy=In2a!6a5'sÒ(D`=NotCpWarnh'72 '$^cP  "M &I (s(k`hCalcEt`1R~)slMxjP= 3'("Or@OS8 & Kdidd M w0,Ps w@ere# Hd. iss+mayeapQ'ːTrMo C@arlo a_ysis. Do  w@A"@ceed?pvbYesNo0 esn't  "c*1ee=@bNouOwGQ,H"dH'/'Tv#`F#-%#'myO.al05 &|).&E:4/ &Or ? :? /*88'*6*s!ez*" "0+,, ,a040J,,,up1nt,?,,$1,angeWarning = vbNo Then  ' Unload MCSolverForm Exit Sub,End If ! If .CheckBox1@.ValueTFrSet-RawRActiveWindow.SeleopcM for too many repsvmy > 65535uMsg@ prompt:="You cannot have more than,#etitions we Record All Ied Cel ls op in becau s'eMsult-!i7fon'sheet. Plealowejhe Numbof8pC beO.", TitleeTMR Butt[:=vbCr0cal Aqim C[SB"[(@"A1").7 X _PEaJ_8(C Dim FoundVolatile0 As Da8taC@XVariant %K=V.F@("@rand()@7, xlulas)} Is NothA>'do _A4lseDJ' 1r 2d9( cB(O,= ("In k@o run CA with eacFhDn, a~c‰t contain RANDL() bedBOMNV()#@.n-vURN`G fun@at enable2s to5d.E advisUAyour wRg. cmay w2!copyUexist*VV 1tUMe Carlo D[@f$A_destroy ori ginal. `Inste0F9, CTRL-ALT-F9!rjlc>tedraw newB ACom nfs9iDoA F~ceed?GvbYesNo, "a;yter`UĤ"@JFa1@Ȩ% o%k'Ma^k#P.|lace WhacfbmenIMSOLVER.pXLA!LI LookAxlPart,archOrd@Y_^ :=xlByRows, MatchC :=Fabgwvmv`vE7vvvevOMwaww w@}w;;I>;;l;;;`;n;z;;r;f!.5; ;;aP;P;{Kr1;;q`;;C ;;q}3's( oh.Textp@ s ReirstArray(x1 T˜U TrackdTime elap istHtA={w ' GqCb_ setAh  1d1pplic\.CaSbA1J qpuqA=`' Tur~ff screen uppppSU #RhowRProgress Bar .Frame.Visi }`1genjl v~ud :s hy!am a ViewTqFG Lo"W `d`X'squzRR,llMCp2 declAa"2?PQRUDofucmyCPtѥIntege"`!t 0 Ea.Arзc6C1a.;5 ]=+ 1#N2 C ra30> 256ʑussпĥ.Paccomo+an Ex0 "a.F@d`tly7`a 0gb`2Жːt.ѡtry agp ۢ13ModEZ!kY%Y%add a %b#A1s.Add0I5pudP 1!s.wc% r5256rm;%'ud'sponFK@10Ic(ii8"R""" & p%`#(ii).N C|#D'n0s t n@:n't use that name ActiveSheetD.Nd = 0s(ii)D& 2^GoTo continue3.End If Next ' wastaken so~io"MCRaw" &y.Coun t + 1Lgebte sYfor laterS Dim myBAs String := G 9put down cell addresses in first row asB bels Hda`ta iZFPEach a In MRange.AreasņCa.C5 ̈́ n)(1, Value;(C.AS(ernal:=True)7A(iViAl CCn 0?size arra`y ReAW0(1 ~myreps, BVarl@ Qm sure yo ueAPthe `origi(Cu to bbl@ o acc[ Solvxmodel BOz).Selec51 RunM@e CarloDemin5"fgOjTwo 1 If 4SedcT"" Th'it's Ao{EYApplication.Cal$cuAFu'drawAnew random nHumb8frRANDOMNV(QA1'r0ApOkMrfi0nish~C 'GРXul$ FXA!=(i)y,1W(MCm.RefEdit(5.Q) Track Prog!h Pctdo /= i /`r!JC' Upi !7pa) property of:Fra`5trol.%@1B=umat(, "0%"%. Wid=!TLa@{  widths@(e* k%-8 10$M D@oEvent El loFUser;a.!eu#D Vi 8Mod() PWorkFun@RMin(50,>0C\vloa"dC ifJ requestedÙbe-CheckBox1#@`!  kk0 'a`Stial!ndex`epi?r b7 B\eACi, kkCTobi"h7_#6.Z#: iR!j&qFv?Br, j9j'RzjT`iY\`SDSummary sta))mr BasicFour E, xmean`SDdaxQinp&(s() `{Doup`, MEANSDAXIC@ Copy`CxSimcKfA5r`]w@2boo U2s(F.xla"@fN0`/LimPRBe,e:= S} 1curr`?5 my~CB2*Puc100jAOpA0A C$20$"27$c#A#50066VSD57axE.8G"0Mhistdam*s`1[thpugĭQӊ < 86&2HAw10(x max -*in)SSqr(318Els?0(10 * Log10TA H O2myR3`/3pZ#,ChartObj)EmpA") (vnoobA`Nobinpc `&er@anualpinxr3qRc, , whichn!("B2p0U? *bKfAL1RuPSPi0'1840)' .CUWiw.DisplayGridl`spFaB!Q"'a ca oex3<3r>s9Ra0TypAxlXYSУPLNoMarker'`ie$ Lo WherHxlIAsC", a:^=/EdS\ceDurcKN.#"aa1:ab3") 'q 2K.Shape`* "+'ȱ: .`.LeftpBsP8R olumnps(9)i(A ).ToptX).RPqo/ !/Heigh-O n@.* n1_N@.5 ·ets(myCurrentMCSimSheet).Columns(9).width * 6 ActiveChart.PlotArea.Se0lect@ion.InteriororIndex = xlNoneLegeTndJD`t4Axes(xlValue) ;WiBorderu.WeightGHairliILStyle XEnd ,$1.MajorTickMark !/MinLabelPositD DGrid=ps7Category)H<Has+Falsl==#Vwz H'[TitBTru6%.acs.Tex"Empirical" Call HistogramMaker1Var(FirstCellArray, myreps, w1, xminAax, #H, hRange, Vie wTablAllLong, 1Al ' `OutpuvT  is the" p ofBOnDe  Mo`y Carlo simulaaRA Eb<'it's a Two ReDim Second(1 To"For i@$Appl% .$ceFull'run SolvIzOkC!N# userfinish:=!g7'Getresul$+/(i) +OriginalNam.(`m.RefEdit'.-# : `. ' Track Pr`Less Pctd@i /2C' Upd`}$Cap1 property%DFVe "tObj@Cмa""pD1"p2p_ A`  a2#,4 2 B SC0FA0 .1 58 401 < 862dm1w1prxxmin) / Sqr(myre@ps) ElseH4w1 = (xmax - (10 * Log10vx nd If|If  < 862 Then2X:2Y2$5'Label Output Range("B2")@.ValueFMCSolverForm.RefEditOneCe,llCTwoI46K6AL127AM  A'make a char  ^   !"#$%&'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\_aocdefghijklmn`pqrstuvwxyz{|}~t on the existing` shee]ACAs.AddActiveB.BTypcxlXYScatterLinesNoMa rkers 'addserieSILoi!Where:=xlEAsObjec@t, Nam@myCurrent1imS*SetSourceData :=C .Daa1:ac3") 'tw o 'C Shapes("< \#A"EmpHM"D9size iNBC2E ).Lefts@7).Column8s(9#  ).Top\ Row   ).HeiLgh" 10* 13_ E width""* 6) "..PlotArea.SelHGcM.InUior,orIndexxlNon- Legen*dT D tAx) Gxl–)jWigBorder#!o.W-xlHpairljlStyloE`! Ck MajorpTicktlMin´Positw= )' (CK""Grids*$29P Cat`egoryHLasK Fa.akp+C=h-? ?d SmTitR!Tru(2,[ac/`s.Tex "Aagep" OrS61SD"HngbW7MaxA\8[in"!7["0 d VD, ,,z 0+U.in @-ls `J5:J8W1l s I5:I8. [s S8q(hosp#;RQ A Hnot cKa@trM% s!J = FaliThen MsgBox "Yhave cancelZ:outpu9ean . No changv )bee@n madefvbInformo{n W) u" GoTo finish E nd Ifrr@ = 0 ls"( u).SdctE9 {@4@Abe fou$nd8 "8rr.C@r CrSecondCe$ll=""2 'it's a OneVaB UCRj("b3:b102").Value Currs“). \ fp3:f4 jp5:j8+  AH1:AL10 F%!8'updahogrxam ˈA1ChartObjects("EmpH` Activ! my8Strj= be"@briesColl  (1).FqASt Pos&i@CIn(1, w$AL$2:") + p10 '!@rac@s in"sear s" 3@y myq,"〚Mid  -@ B.,r`*"=SERIES(" &d3">."& "!*1,K"/K$A?!Y6Q1)$zK15L%E@JUɆcm >uK Fe4cF>f'-R"Ftl5:lL'-"*/g2IML ~eaLLx .?5LdLrtLLtLoE "6L!MLLndLL SӯL= Lg QL[Lt`$!om߯LbbL heRLNGNLLtDL,LB#2o pYM$Y_h[ UM"]M7/]`!q2Yp("7|Y$B:dnew Dsi byuser tn Applic#DisplayAler@;>9KDoiTruEMCRawD if sdPcord Plsolpףa@Feck 1֮KdDmybO!As Ige:("Do G ֝ cad {s 3+?pvbYeXsNoBOO>pqݲ = 6 'chQmak0ur P ?> 5r "In("a///e /eѳ/- +the results of this simulation.", "Existing Sheet Name RequestedMCRawNumber, 2) If my~H= False Then MsgBox "You have canceled wriwoutput t@o an es. No ch`anges Rbeen madevbInform߀ W41v" GoTo finish @End Ifrr = 0 lDs(v).SfctE9| knot be foundvsrr.Clear CFor ii<1 @Emyreps Aj„VarC/>AICells(+ 1, j).Value@Next Hj ii&'d@Yte ori@ginal B  ApplicDisplayAlerĢp)A).DpTrueЀB 'C%overe eIF@ record *s@c ls @ KĖ FiTi@elaps a 8ort vi4\:@v=kw@ctive .Ram("K1 5"'c74, 66`a-@ startt) * 8640f3eJ un link|eckeP,I1`CL Ch T'turnslcę *@ng9Ca= y`ourseOne last@r e>`P0away A Unload M  Toff scraupdQ-SUd@_JcCSubPriva] Comm`8Button2$_C`\k(' a8CSolOumG  `pStrTFirsta4a On R`Hao Dim A! As # )= 6MH.RefEdit+! A}).Adds(|erx:=A #Ƀ1004prompt:=r@`try &r`" a valid"n a. P`֠ agaiAPida6t S!7E ޙC=Ahs͟hasXuAerrorC$q& " rH"U !S&"SJnd1?"e ?"P?"?"rmTwo?"t?"v?"?"O?"?Bh, ?"l?"?"05?"?"=?"%?"?"07it0?""?"z8"=J{>_En@"@SetFocuP' (SLSS<N0{00020820-0000-0000-C000-000000000046}@(%H@0G xAttribute VB_Name = "She@et2" Bast0{00020820- C$0046} |Global!SpacFalse dCreatablPre declaIdTru BExposeTemplateDeriv$Bustom izD2rU~|  CxrU 8 `9Qi////X * @ P ` " h p  &  *8@ H X "h       (  H X hx     (  (08@*H 6x D (8HP`p& 6   0@ H  X h  p    {^GxME (<<6 <x `@mmI `I I tineI ob o` to `bins``xval`3 E`vnval`ogra`~ S ` )nge,ong, num I  I @I `I I I I I I `v`L h `:` % @lue d'( x ring xx `` `is`h`(n`B@` `! W`st b X9% x!%` ``  %` don` yy` ``This x%    yy i%` Mon`` `.`0G `11): sim]P )<PX   8I> xI@ IB ID 'IF 'IH  IJ 8 IL X I x I   IN  'IP  (I   WI  `v T`L x ]`   ]@ `:I G`ls--  unne%`RDi TP s Stll Retri8  V va(1)p  X   Z 4 Tfor zero ``'` `\`^ ```b`d`f`h`j`$` ``'` l  `n`p+ %`r   @ ```j OSe%  0P`vge.C`.!Co`0 = m `'' )08% L$ )pxL ' X)& Friate st I inglI ngreI  liI 8If)pxWhmit  X) 0 Thenan `mit `v `L |`@I ` I  ` F` I  `8I Xxx)I o co`t = `v `I  mulI ( I H I h I  I  ` `..I  EnI @ limI me f`less`(xx)`alra`imit`e S`B@`off h% @ G $*\Rffff*1k49d07e97   $0 "X 2 & . :0p  $  H `  6 2         0  H P  X  h p x           "     8 @ H P X ` h p x       P $ $@ h       :   ( 0 8 @ X ` p      2 P  p   4 > "0<X   V 8 :X ( ""( P`hp ,       0 P p   $   " &0 X x $    & ( 8 X x  $   " & @ ` $x    *  $( @P 0  T `p   & HP$X $   (08@H P @p , " F *P   ( " 4 2 HP F P P0   ( @ H ` " h p H( *p   (08@ H`hp4x( 40 h p (   T0          !!!!0! :8!x!!!!!!! !!!! !""" 8" P"  `"p"x"""""" """##0#P#X# `# p###### # # ## $0$4P$ $ $$P$>%"X%<%% %% %&6 & X&x&&(& && &"'"(' P' &`' 0' 8' '( (0( H(h( 2( (.(.)00) `) p)6)) 4) >* <X* * * * * *++6 +X+ `++"+ + ++ + ,(,0,"H, p, , ,",J, -$0- X- x-- ------- .. 8.TH. ..@/ `/p//////// ///0 0 & 0H0P06X000 000000 0111 1 (1 $H1 "p1 "1 $1 1 2 2(202 82X2 `2 x222222222 2 22 33 3(30383H3P3X3`3 h3p33 3 3 3 " 3 `3 04 B84 4444(4 4 4&5 85  H5h555 5 5555 5 $606H6X6p66666 7 707 P7" X7`7h7 p77777B 7 77 " 777  8 8 808H8 X8h8 p88 " 88 8 f8  9 89H9 P9 0p9 9 09 999 .: @: n`: 0: n; 8p; ; 8; ;<<" << <(<0<8<@<H< P< `< p<<<<<<<<<= = 0= @= P= `=p= == == =======> >8> H> HistogramA1  22 October 2002 Routines modified 8 Jan 2004 This revision: 22 Feb 2004. Contains NewLimit routine, which does a better job of finding binwidthks to and starting bins for histogram' Sub Limits(minval, maxval, Width, xx):3 Establish adjusted minval and maxval for histogram~ Sub HistogramMaker1Var(values() As Double, noobs, Width, minval, maxval, histsheet,' histRange, ViewTable, AllLong, number) values(): data array La noobs: number of observations Width: binwidth minval: minimum value maxval: maximum value/ histsheet: the sheet where the output is to go+ histRange: the Range where the output goesexisti# ViewTable: include Frequency table ad AllLong number: This routine determinesB@ Width: this is binwidth xx: this is the starting valuesi]]%] pH]8 ' absolute value of minimumEmpHd''     !f Bring xx back above absmin    !  'isH  'h xx = 9 * yyl(n 'v   B@ v 'vL v '! We're done: xx is just below yy   e9   P  '! ''v  t    v 'v v '( v  '! We're done: xx is just below yykkd''k   'This is the difference0 Next step it to adjust width so that it is even, ' the default;isto   1, m '6we're going to have to lower xx by 10*yy if minval < 0d of   Mon   'at   'do   'sh  'x  G    'jer/ Next step is to get the Width to be reasonablees Start with original width]Ph'     '  '' e @''dX ''k80 'd    '  '' e @''d ''k 'kp   'd@ 'k(k o Written by Frank Howland] ]8]P The bins]]]]]]H]]]]]](]@`]I Get the variable labels--this routine may be unnecessary if public worksDim Varlabel(1 To 2) As StringCall RetrieveLabels(Varlabel)varname = Varlabel(1)]]   ']]0]H`4 This code checks for zero width (SD = 0) histograms ' 'd   Ak   '     A      ] 8$$     $$A  %!' No. Obs. , v  v$  G v+j v $ to  v$ v  , v$ v  , v  % v  %$. !`B@ !'   G 'j vOSet myboxRange = Range(histRange.Cells(17, 3), histRange.Cells(18 + Nobins, 4))Call PutInBox(myboxRange)4 Is this right or should it be histRange.Columns(3)?!Columns(3).ColumnWidth = maxwidthk8& %' L  L$ L % ,e used L$ L & , will L. You ]P '  G 'je & Figure out appropriate starting pointroy Dim limit As Singled of FDim mult As LongreDim oldy As Single limit = 10mult = 0 pIf limit > Abs(xx) Thenn While Abs(xx) < limit  oldy = limitp If mult = 0 Thenan limit = limit / 2R.XL mult = 1 ElseIf mult = 1 Then  limit = limit / 2.5do mult = 2 user ElseIn o limit = limit / 2ion, mult = 0NDOM() End IfRAND Wendvo If xx < 0 Then limit = oldyr Else. Plea While limit < Abs(xx) oldy = limit to co If mult = 0 Thenth limit = limit * 2stro mult = 1ead of ElseIf mult = 1 Thenaw new limit = limit * 2.5ed mult = 2Warnin Else  limit = limit * 2`  mult = 0 End If WendR.# If xx > 0 Then limit = oldyB@End If If xx < 0 Then limit = -limit!: do same for valrange if it's less than abs(limit)-abs(xx)*Then add the valrange units to lower limitHistogram Title SEmpHist %^B@ P L(Noff % %  & %$Z "  LB@X9 g L!$B@Hes  L%thistog99q`  v L%tle use99q0 L(&, call L!hB@H p j!l(n L!hB@Har jB@(sults ActiveChart.Axes(xlValue).Deletear L9 Histogram of 5!(d!qx  L%t  9* $9, .MajorUnit = tickwidthd moq v L%tB@H Thi j!xacco |9z p9~q j p9 p9 p9qDim chartName As String c9 **** Need to check for other charts named Histogram ****&chartName = "Histogram" '& Varlabel(1) ActiveChart.Name = chartName(B@h@ Count the number of chart objects and then just assign the laste $Dim rowH As Variant, colW As Variantond to!leftC = histRange.Columns(3).LefttopC = histRange.Rows(1).Top.rowH = histRange.Rows(15).Top '.RowHeight * 14,colW = histRange.Columns(10).Left 'Width * 6 wasn'Ahistsheet.ChartObjects(histsheet.ChartObjects.Count).Left = leftC?histsheet.ChartObjects(histsheet.ChartObjects.Count).Top = topC cIhistsheet.ChartObjects(histsheet.ChartObjects.Count).Height = rowH - topCIhistsheet.ChartObjects(histsheet.ChartObjects.Count).Width = colW - leftC %B@]h !2'.d8'.k( !4'0d'0k  !2 . 96 Solve !4 0 98B@Hq %B@HoCall HistogramMaker2VarRev(values1, values2, publicnobs1, publicnobs2, w1, w2, MINvalues1, MAXvalues1, MINvalues2, MAXvalues2, histsheet, histRange, ViewTable, 1)' 21 Oct 2002A In this revised version I use the methodology from HIstogram1Var$ for setting limits on the histogram2 U] ]0 ]H  The bins] ] ] Dim Nobins As Long] Dim histsheet As Worksheet] ] ]X ] ] Dim noobs As Long] ] ]0 ]H ` ) keep track for scaling purposes"lo]x   uest] ]  ] ] 8 in]P h ex] ]  ] - Find overall max and min for the 2 variables F'MsgBox histRange.Address J F G J'j H' L H G L'j! Call RetrieveLabels(Varlabel) myname1 = Varlabel(1) myname2 = Varlabel(2)M Same as HistogramMaker except adjusted to accept two series on the same plot] First variable B'B H'`d0 F B `Ak D'D L'bd J D bAk4 Choose the larger Width as the class interval Width B D  ******* B'n'dP D'n'k8 F H D'n'k J L B'n'k1 This gives us parameters for the first histogram ` b  `'pd b `  n'r b r n 'p p ` G p n 'pjk(d   b'pd ` b  n'r ` r n 'p p b G p n 'pjkk n    'nd`'nkPkH H p '  n '^ ^8 ^X ^ Tx ^ V$$$: > ^ n p$T$VA ]  v ^ v$  G v+j>avoid rounding problem v $ to  v$ v  , v$ v  , v  % v  %$. !`B@ !'   G 'j v $`( % ^  %$. Call PutInBox(myboxRange)k v ^ v$ v  , v ^$'f L ^ L$T L % , L$V L & , L reset bins for second histogram*Call Limits(minval2, maxval2, Width2, xx2)2 This gives us parameters for the second histogram L p '  n '\ \ \( \ XH \ Zh$$ < @ \ n p$X$ZA  No. Obs. in $l , No. Obs. in $l , % %$(t \ ^  why is this column 4? % \  %$.  v ^ \ v$  G v+j v $ to  v$ v  , v  % v  %$. !`B@ !'   G 'j v  , v $`(d % ^  %$. kCall PutInBox(myboxRange) v \ v$ v  , v ^ \ v \ ^ v  , vk v \ v$ v  , vkx L \ L$X L % , L$Z > @ L ' ,?* (Width1 / Width2) L \$ f G \$'fj Figure out chart scalingDim tickwidth As Single]p ^ \ ^'vd@ \'vk0& %'h' %'j Put in the chartEmpHist %^B@ P L(N% % v ' %$Z "  LB@X ActiveChart.SetSourceData Source:=Range(histRange.Cells(1, 37), histRange.Cells(Nobins * 2 + 3, 38)), PlotBy' :=xlColumns VT ! LB@R L!$B@H  L%t99q` v L%t99q0 L(& L!hB@H p j!l(n L!hB@H jB@( ActiveChart.Axes(xlValue).Delete L9 Histogram of h And  j5!(dqh L%B@H j!x9n x9z z9~q  L%B@H j!x9n x9z z9~q If Nobins2 < 30 Then With Selection.Border .ColorIndex = 57 .Weight = xlThin .LineStyle = xlDot End With End If  L%t p9* f9,.MajorUnit = tickwidthq v L%tB@H j!x |9z p9~q`  j p9 p9 p9q8  %B@< %B@] !2'.d '.k !4'0d '0k   !2 . 96 !4 0 98qX  %B@Ho8 Y Usage Call MakeBins(binleft(), bincount(), values(), noobs, Nobins, Width, xx, x(), y())< This routine determines the binleft() and bincount() arrays]]  A|   *!8%~+Dxx v $  v v+ v$  *!8%~ v+ v Put data into bins L 'v v$ L$b v GFj v 'v0 ( v$ v+ L Get coordinates for histogram$++ v  v 'L v $ L+ v$ L+ v$ L + v$ L + v $  +  +o]'    'h $F 'oH $$@$'Fi]x]0'  0.' v  0' vk (ox]Dim statRange As Range`Set statRange = Range(histRange.Cells(6, 1 + whichseries), histRange.Cells(12, 1 + whichseries))statRange.Select ' If AllLong = True Then) Call FormatSumStats(statRange, 0) Else) Call FormatSumStats(statRange, 1) End Ifd   '( Call FindPrecision(width, precision) If AllLong = True Thenh Set statRange = Range(histRange.Cells(8, 1 + whichseries), histRange.Cells(12, 1 + whichseries))) Call FormatSumStats(statRange, 0)g Set statRange = Range(histRange.Cells(6, 1 + whichseries), histRange.Cells(7, 1 + whichseries))1 Call FormatSumStats(statRange, precision) Else1 Call FormatSumStats(statRange, precision) End Ifko]`]x]]]]]  ''dx'kh $F' $F  '?$F'@$F'@$F'!@$F' '          'dx 'k`dX @'k8d0 'kk   '  ' G   'jo{Attribute VB_Name = "Histogram" 0 ' 8422 October 2002&Routines modified 8 Jan>4fThis revision: Feb 0 Thenh<' Brw!backoveC%Whily >sG= * 1`!apWend"/j'B9 y`b%nig<i -%B iL A' We'`done: `AA!low Else**  #'" / /4A$+$A_bM%F(-)j+)EIf%-#bI[k=T ?` 'C`8ydiff@nce!NextҸep C ‰h so@a@evvAfyPAp" efault; =B>c02wKgok`haj@Kby a2* if= < $xcA+*;8-RH @*'PF4 &2= --$xy' Ds 0get0etbeason`SVth originalۥQWmQSyPX{ 2= 1C<]=*1A / Ж5tWYCe.5[T(2 t ,4%3g#S# <Q1  e*lq 101 1pa   U0 > 1}ନ 4>+'QP@-"dTi@є@V,erHWrin NFrank How~lຑ >(%jW%left}'4c8ounUMin!Maxaom my GWorkGXgyo!choiceQ colOPWgPvarn@ S0ng4Nosp\co`Rble, xup As Doudown D im yy Dabsmi, myboxRangeV ' This code checks for zero width (SD = 0) 'tograms 8 IfAen 1= 1 xx9maxval ElseCall NewLimits( ,Px),nd If rX= # - xx !DriveFormat ting(>, Nobins, AllLong, ",a|, 12 \Releft(0 To#couPnt(1X2 * + 2 !y?TaOAmWaH@BZDMakeB>(2x), ,Bue@FnoobJMxx, X( Xy(),F=M.Cells(18,5.Column'BView0@}TrueB6 H4) "No. Obs."Æs| iHXAa@AbsF7i)) < 1[10 ^ \-6AnLThen q  Next :ic'Ȍ17, 3M18@2-R4P'}PutInBox( ' Is t` right should i0t beWVs(3)?CX! "O _Ernamne8!Xj` T2Ma *j27aQX(jd Y8AyF0jtickC7 S@l =Cbv- > 5ga ' Figure out appropriat@e star PpoinN'AlAmult@oldB#@ w'@̀@SC> axx) Whil@< 'aB =a]'' $ / 2)g g .5S )s' 5 ?|U,rWend z< P+><DAH!%s*eO=  /P >dyoc-R!d8o sQIa ifO's lesQQan (2)-2]addSef un`o lower3HiBs TitEActSheet.ChP@ObjeBc"Emp").2`BOTypTxlXYSc`erLinesNoMarkn just assign0lastBDim rowH As{iant, colW&AleftChRange.Columns(3).L&top! "Rows.Top5= D5) '.Height * <14E 9109 'Width 6,sheet.O( _#= o!o~ ==c- D=Cr-E6 ADnelle, 1).ate subLongBIfp@t>1 Then  @ = 0` ElsJ1@@If vFWiWindow%H .ScrxollGE3-` )S -[ME:$N(q/Sel`uaS,ubA FMaker2`(values1()3Double, 2noobs1, 2, BBminmax"A" i,>, All!C E`Vie8wTaCallRev%, picn+` wwMIN@ , MAXga`(1)' 21 Oc@t 2002@w Iis revised version I useBmethodology f@rom HID1@4afor setting limi !K-miEmGjObini,"I"o!U' Fa  !:2pUriMS cg1e'MsgBox8qAdd8res@n2 < rG}D=A DQcY >TbQ AHWReeveLqs( JYm$!=82U(v2Sp$] excepdASto ac"two0Qries ŴPs!plop^is1&r va3‚10CFA3NewLY(qp !xA3 iC3~2c!pF2wq.=O= 0  1 $*-Xth  _\ ar }gsQ`| paepsXfi&y7$" b-_ eg &OTp;In1 phll D0  ,E&Y@s 83>  (]K- minval) / 2 aElseHhoverallWidth = 1nd If E .ranDgeLmax1H - ~xxDN@obins1@Ihnt(^/%e) +f/lReDim 9left@(0 To H) couW1! 'x1C2 *G82) As DoXubl~;YC MakeBn(T),YIv ues1(noobs1,>, xx, UA)umyƆW4, wЉ yb8oxRȀ  If ViewTasTrue ThenHFor| iIPCAbs7i)) < 1@Q10 ^ L-6ii)0 'avoid rfding (promhistB'.Cells(i8, 4 Si1) & " to&m5ci4Sxet CB @,(9$, .Columns.AutoFitG+q~=H D xG @^ >gzmG=  N`ext iD (|4);a!mɇF= s!17a3 187s, 5!V!,'cPutInBox( %ŇeO{.2, 24E0*E0 IIC jۉ[2 j37@ujDe^8azej'0 resRs f`qseco 0ograf)Limits(C Fxx2`' ris gives us pa!`eter thde / @ 2븃'2%'6?%#t% x2-56 #`+"D!92!Y2xm\1""No. Ob s. inbVarlabel(1 MA6!MWF/bOO>).WrapT!FK0d  >ue' w(hy )t cbQ 4?Q H bHcH4HE 1a=Gtq1P ononincn7 mpmIia?j?jQ rso#q1jjqgjn߼j jxja1Р00w]q?nQ˭'''geo6'Ymo4t)OG="-P.822{1nȳwws7t(1`Q| |2(|pT9|;(3Pԣa) '* (11@W A0e?Pl"3P=u ' Figure out chart scalPtick;6SṕnbsLong231f[y"=\e/mynameA] 38/SA_ 39P `1o Act Sheet.Ca Objec"EmpH").2ValuentinuouV+2+5 .a$If Nobins2 < 30 The@f)@olGV5`*+DotHg+bIf !"[CMinimumScab9overallxx .MabxImax#'#Uni!Etickwidth"ep$oTc-.(HairA!L>g+k "TMarkvɎ3 'oraLabelPosi<= ARange.Cell s(1, !yhow(`: Dim rowsub As Long, col[w 1[A=` 0 EϦ1`M l l h !d1cCW8indAB.ScrollRow -G4v g$-~Sub1 MakeBPD(2left()Double, @counT vys{noob&sI, 5, xx, Xy@~' Usage< C<`, i, W<x(@PHas Bu_e denm‹the $a #AarrayPb'q+preciskiAjr F`"P( ! 0)0# Appli.WorkFunon.R d(9G 'PC iP`3oDbyiqH+S`iO , NA' Put data intoq$y j BO  Doi`x <=5"jT1@sD GoTo veloop:i໗ @1LP  S25+ j)Get<in@Qs for ;CX(B 0 ycpOO412 -ahX(jYiy G @$1Dy(]2L[K5h';X!9SPinglX% Wh#X >S/tX / 12W f,-Log10(X)ytpc 0pmc_3Bp/(10#`XU#Y" ҿSps(sPw #p tr vH!$= "0" @0 > 5!.! &vS *&CxF .Numbe'P 1Dro` p[g%S!,P Allhrш,4dwhichsǴ$[ ' 7RS:= u(}v6@0!y,j histRange.Cells(12, 1 + whichseries)) B 'staSelect If width = 0 ThenRNobins(01 'tNAl lLong(TrueX8 Call FormatSumShs(n, 0+ElseS 616nd pIf >9Int(valrt /g)]LindPrecision(y, p2rSet j( 8,.B%6%7 EGAyNT{}Sub  NewLimits(xmin,C~,grt,Dim lAs Doubl@Grem a b C d neg@egefrB؁' < ؂= - @ǸO?3= Log10ò!|c$= I - a@VC1.75`$b2.2C4.d8#*= 10 ^ fa''@bTICDd\**aa*%5.E 2.HEKg`rt@0*g>BG E* `FNcG-1b{ -cei  '__SRP_b|RNGandSortModule"bH__SRP_8__SRP_9 `(i9Q!9k/////////x `@Qi)Aa9kk//////////////\ `$i!khk////kk,! `i//(I `!/,q `//@ `i!!A//////0 ` aiy///nrU~| ) d '\ 5\'\ ϳ5\'\ ϳ5\#gD jDttDvDdzDԁD428G $*\Rffff*1W49d07e40*\R0*#15' : &@ ,h " F \ Xh * 2 6( 4` <  ` bHD ( "8 @P X p 0@P`p<}t^Z0xME(> < <ꀚ@P @ꀞ$@☢C%4 o P`(>k''' D0 the`>n yo ` N+2`  b `  l `  u -76h `  8` [[)@Hduleoject (w (i h worist bibook``renc`or d``v`+` x i ii +` Pipi +x `  i(i``  `  $) ii````v  ` 6`v  `eded`8 `he r`: it + `  Aint bien B  ` H Xice      ` $$e) mplyin a cel ixls ` *  '2is functp   ion WILL  8 B@ @ `vr te`Leede  ` )   A@0 i0 6 MHiC G4`ds` `v' ( `    `v6**+ ` h i nd pielow  ` ..  h` ` )6 T a macro iQ Tiarraiewheile 1`` to 1`  be 8`  NN) ii(i` `v```T`X```` ` x##A iTiXiiii8iXixi ` d) i8iXi xi ii``  "` H y) hiviLi` `$ &`  (pꀖ@ꀘ@ꀜ(@ꀠx(@x 8  (  P h(8Px8@l @\ x ,@H`p " x Z   0 @ >P < < >( 8h     B J . P ` h p Jx R B  ( 8 @ H BP "         & &@ h " p  x   $        < ` Fx  "  ` ` <x X $ `8  d   "8 &`  n  *( $X f   ( "0 VX . h "`   .      @ "`      (8 P `p   " @HPpx    . B   V ^H .  "( P 6pB   V ^ .h    08 B  @H X` hx X  *8  @` "  X08 *P  "  "  @ `p B  x  >  48 Fp $ , 6 H X Np 4 . B  ( B0 x    "   !!! "  ! ((!P!X! h!p!! !!!!!!!""0"H" h" " " 4""," (# 88# "p# # # # #$ "  $ $($8$ H$P$ *h$$  $*$ $%" %%(%@%"X% %% B  % >% >% @ &`& p&& && &&$&& & ' D ' " h'p'' l'(( ( (0(X( <`( ( ( ( ,( T) @p) 0) V) T8* V* * F+ BH+ P+ B+ 2(, :`, , ,, , X- ,X- "  -------"- .8.X.`. B  h.p.x..... B. / :(/ *h/ / / /// /&0 80 @0P0 X0h0p0x0 0800 @0 $1 81 @1 P1 h1 1 1 1 *1 1 2 2 (282@2H2P2X2`2h2 p2 2222222 2 B2 3 (3 03 $83 `3 x333 333 63 .4 404 h4p4"  x444 24 4 4  4 445@5>H55 65 $5 66 6 6406h6Bp66  6 6664787@7H7P7 X7"  `7h7p7x77 77 77 77 7 8 8(808 88 @8 "  H8P8X8`8x88888 " 8 8 T8P9h99&999 99 ::0:,H:x:: : :4 This module contains three general purposes macros:1) FMRG random number generator%2) Normally distributed random number3) qsort sorting proceduresob o?The module has an auto_open macro that initializes the FMRG RNGVTo use the module, copy all of it and paste it into a module in your "active" workbookograRor, on a PC, from the Project Explorer, drag this module into the "active" project#For questions and comments, contact,Humberto Barreto Frank Howland/barretoh@wabash.edu howlandf@wabash.edu-765.361.6315 765.361.63176****************************************************** RNG macrosZDeclarations to enable the FMRG to be used by other modules in the SAME project (workbook)[To enable use by other workbooks, a REFERENCE in VB must be made to this project (workbook)+] ] 6 see sheet CurrencyDoc in Random.xls for documentation] ] ] (] P] ]x,  Initialize FMRGA@4 CRandomize runs on open; no need to use Randomize again in any macro '$> 'MCSiLoad B values from p. 147 of Deng and Lin, "Random Number Generation for the New Century," The American Statistician, May 2000, vol. 54, no. 2$]#g+ j+  Microsoft Forms 2.0 FormEmbedded Object9qj$VERSION 5.00 Begin {C62A69F0-16DC-11CE-9E98-00AA00574A4F} MCSolverForm Caption = "Monte Carlo Simulation with Solver" ClientHeight = 4800 ClientLeft = 45 ClientTop = 345 ClientWidth = 3735 StartUpPosition = 1 'CenterOwner TypeInfoVer = 128 End tt+(v+dz+gԁ+„+)+ +U +A + + ++++++٥+++++f++%Pick a B, each with equal probabilityrstC]8  'heet $'ecoPTTo use FMRG in VBA code: FMRG runs the rng, myFMRG is the actual random number drawnlArrayTo get a VBA Rnd, just use Rndh*To get an FMRG value, TWO steps are needed FMRG8 x = myFMRG to get the random number and put it into x5If you RESET VB at any time, you clear the value of B6The If statement below runs auto_open again when B = 0 GA@j8This calculates the next "random" number in the sequence           '  ' ' 'o8C To use Random function on a sheet, simply type =random() in a cellg, num( See sheet1 of Random.xls for an example *B@A@ '2iCThis function will NOT recalculate with F9 or Application.CalculateLThis function WILL recalculate with CTRL-ALT-F9 or Application.CalculateFull *B@A@ 'i;The two macros below remain for testing, but are not needed]@BP ' v@BA@  v+   A@0$A@0 PowerPC G3; 266 MHz -> 9 seconds PowerPC G4; 500 MHz -> 4 secondso v A@ ' ffffff? H}8g?   v 'v  v+A@  v+(nko6******************************************************Oct 2004 version@Copy and paste from *** above to *** below into RNGandSortModule of every workbookNormal distribution macrosZ Numerical Recipes in Fortran 77: The Art of Scientific Computing, 2nd ed.; vol. 1; p. 279 Box-Muller Method6 To use, call from a macro with FOUR parameter choicesQ The first choice requires an array so it has to be DIMmed somewhere in the macro Example 1: Explicit ChoosingZ Dim result(1 to 100) as double MUST be DIMmed as double to be compatible with NormalRNG NormalRNG result, 0, 0, 1] These two lines create a result vector that is filled with Normally distributed values based on VBA's Rnd Example 2: Default Choosing Dim result(1 to 100) as double NormalRNG resulth Since the default choices are VBA's Rnd, mean zero, and SD = 1, this gives the same result as Example 1 Example 3:$ Dim myOutput(1 to 100000) as double NormalRNG myOutput, 1, 20, 5_ These two lines create a myOutput vector that is filled with Normally distributed values based on the FMRG routine]]]]] The Optional parameters areP TypeofRand, which is 0 or False if not set--this means the macro uses VBA's Rnd' Getting the number of values requested] $'a Because we generate TWO deviates at once, we divide number in two (with MOD handling odd number) We waste one deviate if odd ' Use VBA's Rnd to generate random draws v    '  '   '  Gj $ $D'  '     v+'d8    v+'k vd " Use FMRG to generate random draws v  A@  'A@  '   '  Gj $ $D'  '     v+'d    v+'k vkh o` 'This is the old version of normalrandomDefault was Rnd8O To use NormalRandom function on a sheet, simply type =normalrandom() in a cellW The 3 parameters can be set, eg., =normalrandom(1, 100, 50) uses FMRG, mean 100, SD 50( See sheet1 of Random.xls for an example *B@]    A@$'iThis is the Feb 2004 versionIt forces use of FMRG/Also forces explicit declaration of mean and SDO To use NormalRandom function on a sheet, simply type =normalrandom() in a cellW The 3 parameters can be set, eg., =normalrandom(1, 100, 50) uses FMRG, mean 100, SD 50( See sheet1 of Random.xls for an example *B@]  'Error: SD must be >= 0dSD OK   A@$'ki *B@]H]`  'Error!dh      $D '   '$draw a random number on 0,1 intervalA@map to computed interval      'kxipx]8]P]h$'      $D '   '] v $draw a random number on 0,1 intervalA@map to computed interval      v+ vo]]'$ A](@Xp$    :A<HiA@0o *B@A@      'i@Barreto Mar 20027myTInv takes a random draw from the t-distribution withdf degrees of freedom-This is based on the definition of the t-dist?t=z/Sqr(y/df) where z is standard normal rv, y is chi-square rv with df degrees of freedom%See, e.g., Freund and Walpole, p. 270/Many Net references cite Knuth, Vol. 2, p. 130.USAGE:Two lines requiredHDim myArray(1 To 10) As Double CREATE AN ARRAY WHERE T DRAWS ARE PLACED.TRNG myArray, 100 CALL TRNG WITH ARRAY AND DF(Example provided in TRNGtest macro below <Used to draw a single t distributed variate in a single cellUser provides df *B@]h  DF must be greater than 0'd  A@$'ki "Get number of t deviates requested]@ $']X   A@]   ]  X ]` ]x v  v+ A@ L  L$ L+ v$ L$ v+ L v-create t distributed deviate and put in array v  v$ v$ $D v+ v1Uncomment and go to Test sheet, then run TRNGTestFor I = 1 To UBound(tRand())Cells(I, 1) = myzRand(I)Cells(I, 2) = myyRand(I)Cells(I, 3) = tRand(I)Nexto ]P h $'$'] v  $draw a random number on 0,1 intervalA@map to computed interval $    v+ vo ]'  $A v' v$ v  , voh( 8 This is a quasi-Exponential random variable in which we7 take lambda = 1 in the standard defn of an exponential9 and adjust the mean and then scale to get the desired SD *B@  'Error: SD must be >= 0"dPSD OKA@ $    'kiEnd Oct 2004 version=************************************************************* ]@B 'eNormalRNG NormRand:=result, TypeofRand:=1, mean:=0, SD:=1 ', is an alternative way to pass arguments A@   A@0 $A@0!PowerPC G4; 500 MHz -> 17 secondso6******************************************************Sorting algorithm Thanks to David Maharry Aug 2000& quick sort of array x which is x(l:r)M l stands for left and r stands for right, but it's the same as top to bottom: sorts array x[l..r,n] using the mth column for comparison* n is the number of columns in the array xO m is column that will be used for comparison (that is, m is the sorted column)M Barreto made it sort decimal arrays by declaring several variables as DoubleO If you make the temp variable in the swap sub an integer, you can see what the code is doing.? To use, call qsort with the FIVE parameter values as described< 1) The array to be sorted. This must be DIMmed as a Double.I 2) l, stands for Left, or the first row number in the array; usually a 1< 3) r, stands for Right, or the last row number in the array+ 4) n is the number of columns in the array3 5) m is the column that is sorted from low to high Example%Dim myArray(1 To 4, 1 To 3) As Doubleqsort myArray, 1, 4, 3, 2Q This sorts the values in myArray, which is a 4x3 matrix, using the second column%Changed declarations to Long Mar-2002 ] ]8]P    $'   $'        A     A     Akoh]H]`]x]]] < found is a check to see that there are at least two unequal entries in the list4 ptemp stores p in case median of three method fails$ Incorporates median of three method Reference is Sedgewick Help from Dave Maharry FH' 'v  v   v $ v $' v 'd v 'vk v' v  v $ v $ G v 'jk09 ptemp is needed in case we have three equal pivot values in the median of three method      $'T  $'X   '  $' T X       A  T X T  X  'd 'k d 'k X  e T  'd` 'kP dF 'k8 k0 < If check = 1 we assigned p already in the Check for smaller  routined ' If x(p, M) < x(p - 1, M) Then p = p - 1 End Ifkp dh 'kP G 'j/ check2 comes from CheckforSmaller and tells us( that all three x1, x2, and x3 are equal.If all three are equal just use the original p 'i 8'', This checks to see which element is smaller June 2002 FH T X T  '% pick x1 because it is larger than x3d '0 pick x3 because it may be bigger than x1 and x2 T G'j0 This says that all three elements are equal, so we need to discard the resultk T e T X '% pick x1 because it is bigger than x2d '4 pick x2 because we know it is bigger than x1 and x3k@ X e X T ' pick x2d '& pick x1 because we know that x1 <> x2kd'ko]] ' '     $   '  $ e  ' d    A"  '   'k ' o]]   v $'$ L $ v + $ L +0o( read in the dataM the array must be DIMmed as a Double to be compatible with the qsort routine]X v L v L$!$ v L+(80 use qsort (A@ Output results v L v L$( v L $($xoph1Attribute VB_Name = "RNGandSortModule" ' This m< contains three general purposeLacros:l1) FMRG rom number`torB2) Normally disd 1'3) qs ing procedures Thehas an auto_open mat initializ=t2Nx!o Xuse%,upy N of it+d pasinto a:in your "active" workbookNor, oH PC, fr=Project Exploredrag?1*{WFor questioWmments\ct>HAUBarre  Ffk Howl bC h@wabash.X hC f@ 765.361.631n57.'~*2mv@"DeclaraC@:enabltzbexd by joVros@~SXAMEBf(p)To \a REFERENCEVB must maZd!ovp IPublic myA)As Do@e Int Curncy ' ssheet DocR@som.xls fဉdocuAFOlag1) 222BbLongfISETege[|dGECVt p@2147 48364DSuby(0 IFxbL C@z 'Gru@b`n; no need @7ag%any9cI 0"= Rnd * 2 ^ 242`'Load B valsc}p. 3!De3 Lin,b4 NG¨aW"6~New Cyury,"@e Ameri2cSt@9 cia@ May 2000, vol. 54,@. 2 Dim BArray(1 @XH25). )aa/6403F= 27149F @981 403022 = P313363(236f 7C98G8B4601B9B6098F10c181`(667136 84h"3709KEC787hc961 408540p4217@Ee 4(245g 24319G'2436j924 2A445302a4567h4633` 'PickB, each with equq{robab ility"myX1";f04 +z 0b # 9EP2:Ya;L;P'A7#MPrVBApjde::.rng,Pp!t ۃ@rwnXgpOaA1:, jVg9 Xnr9, TWO P|preAedSQ xX`CR 3;``Px'If`OP`pEtime,clearwfAB@1s =eZclow L׌#KwhB 00BnRrcalculatDnext "]S"E s0!encgfP@(bPcpQ2 -tR)mp@(/ p@## ?OV' qj(FunP`_(;Og'P) f#a sAu, simptypPa cell"S%x1qcvwZexa0App0~x.Voi(Trues'= U  will NOT re6=F9 {`)CE &6VWILLCTRL`-ALT-Fu1lNVa?FalsNV&g woE1e.m{`-t, Lbu 9@cond@'4; P%4'/AegraphqWh&i < 10C X % PDX >@ 0.7 A\X300F [Ѽi`C 0s(i, kX3Blu If xWen0W -.*op"O@4` vers C>o Cۢנ abovbeQV ] A=ey`C'*A w(=G,X OpwTEofQwBooa09MePD&SDL$l Rpecip^J+t!. 77:#rt1 Scientific Computing, 2nd ed.; vol. 1; p. 279 ' Box-Muller Method XTo use, call from a macro with FOUR parametrchoicesphe f irst  requires an array so it has to be DIMmedmewhere pin tFoSExample 1: Explic>ChoosDimfsult(1S100) !/doub pMUST4 @c1atinNormalRNG.1, 01These tw o linqcrpeatexG @vectorfat isd+ly dist ributvalu#base' on VBA`'s Rn2: Defa1'Q3Sinc1@\.dske, mepzero,sd SD = 1,@76giv.msJao 3:AmmyOutFn0 o G` ,2a5a aFMRG roe/V1 As DB B2MRadiusMFACi 6 Optionalr ' TypeofRand, which0 @FalSif not set--th4!8on@=y`|s)LaG et:e numb{of $Sy|es@Uc( $= UBound(c7())A&eca we genereTWO` devi``t ^c` divi.d@m(tMOD`ndog o ddd ' WeX wa e ! Ifh'נX%In! U@'nr@-`drawF-ia` CB ISET0  rep : F= 2 *Az - V2:J= 2^ + ` /@>t Or  GoC"AaR= Sqr(-Log(q) /,!Gsc* c=i)@`b`@+a+$`ETq *A0 E IfNex`c`b 9v8oDHq8$  Ae!:my"2;T=Y7 _>/()2 ????? ?? ߻? t?iY Sub1 'TNNhold_rsT PLn}S<"5tBvFuncV >Adv(FX'WZPBool@q,GZM1rP 0f)y^`\3–: f a sheet, s"ipy t`_ = ()Aa ceLll/G 3 cc{`, eg.,P|@, 5_2a* 5 ' See1]om.xlP@e Ap`on.Vol(TrueDqr%Vq I1DlNG,oq,B yH0!#~#Feb 02004#@It for`<'Alexdecl౲$p'$ G&ORMALRANDOM(#"4"d i""Q/"e""al"" "n "Ќ""Q"9Tqw"Po< wDNh ""L'Error:  mu/`SqF6i'OKD$(u(?y+(1(Al("Uni%m!DD2OH#aGq8b  T!vb(1r,+ 1) !k- 4`a 30)70Pia- p]Qtї!֒ 4{p1PP谪:8mapinx* (b0a) + afU gE@(dS,'aLong formRand()) b = (2 * Mean + Sqr(<) ^ 2 h- 4|(<3)SD:) /` 2 a- b Dim i As Lon@g For= 1 To number 'draw a rom on 0,1 interval aFMRG 'map to computed .HUnii)myAbja)wa Next i End Su[  Test hl10)zDouble CalDl NG( ), 10, 2.g, Max+, Min3BasicFo= gs2@OK()~&&&>Qu2u#awOct4 vers0`*-VcN(",resulth,00Z rtthimeT``''g!P:=s, Typeof3@* :=-SD:=`1 ', Palnative wayYpasrgu1[sdf{?zdǢ`< (9 'PowerPC G4; 500 MHz -> 17 second@!SU'_!Sort algor@`'anksDa0 Mahoo' 8AugQ16ic8k s02 x`5Px(l:X l P (lefnrYri ghnp it'Ӱsaaqbottb" x[l..r,0n] u7 mth column formparison ' n is the number ofs in`array xXmXthat will be used (B0is, lcsort(A) SB_eto ma@de it # decimal {s bylng severvables as Dou OIf youNketemp +iswap sub antePger,cspee wnY(coLs doin$g.ZToy, caqYwith8FIVE ametvaluRdescribed*1) T;p #ed. mustDIMmqarA' 2) l, standsLeft, firrowr;.u-y a 1"3) ?Righla4)eA 5)ǃ -U a = X(p CaMyPartiG pv, k, n$!k - 1U( E0If @+a1Func-&,fou 4Boolea A/i,OmidHdlex1e,h x2h3e checb:I"Fpb$' ^a ks3re`t `}twneqsEen_listEsto rp!case ithr hoailsEIncorpporat@EReferenc`s SedgewickHelpDavemh2yFH#9= Fals@!Vi@`lWhi`5* A?i < rC@cX(iYc@ +Wmda Truapi! EFA!^AWen!1= i @w( >P_he\5J@rmgqAЃdpnj := 0\1 >CN3_5r2o+1,o '=3/A1O6 Q:  O ?p&'A'1 =assign>p already<. s.' routinO Gq1M)Wxҁ)'p bl}-0P2?F 'Cu2m`lҫ,@`d tells 'ђR%Bм 7ayw's jp¨origin@pя wP P1U< `J(@ '*#wNNJ@O0 =rAelem0v''June C' A `0_7#C@SPl, Pn beca1p oPnPC&#E3@܍Fmbig1!xq1F 7%25'\ sa~y7"pe$,%@ so ' we need to discarPhe re0sult(End If ElseIf x1 = x3 Then@(> x2(pRl ' pick8because it is bigger than;ld @r@2 @!knowH1 rax3O_2_2 _1_>(PqPnt}=w - Call swap(X,'@N$n>?S(HmWendCkAHrr qFX, j[|, jhrClt8emp C{For A [o vb= X(@*kh`X(j, K@ Nex ABT Dorttesting( '`ad in}e data  'barray must`{ DIMmas aZabe compati\ wi$thBqs rDou e  myA (4, 3d !i@{Cg$jeE #j)`Cells( 17 SB.Valua) ! cd , 3, 2%' Outputs `5) *4a*6c„D)D DU DA D D DDDDDD٥DDDDDfDD'\ p>5\k>D @P8D\0 P  0   0   0   0   0   0 8  0   0   0 x  0   0   0    0   0  0  0 (   0  0   0 h  0 8$  0   0   0        prU 4$`$`$` $`$!`$I`8q`)Iik0` Ii ,`i ++, `i ++4 ` ik++$9 `,a ` ++( `&0`k&4` ik++$`,)`i ++$Q`<y`!1AQak((((8`!1Aak(((P`$q1A((((((&&(H `!1A Qak((+(((81`!)Qk((($Y`nf ^|SDModuleLinkCheckerModule$ __SRP_cT__SRP_dg   '2$SD Corrected two-pass formula. 2$D' The SD is the sample SD.% The Population SD is Sqr(n/(n-1))*SDopx]8]P]h]] find n$,$,  ' First pass to get the mean. L$,$, . L$, '. . ' Second pass to get the SD'. L$,$, L$,  '. . 0 '0 . . 2 '2 2 0 0    '2$SD Corrected two-pass formula. 2$D' The SD is the sample SD.% The Population SD is Sqr(n/(n-1))*SDoH]+(@B+(](]@ (,  A@4  $p 8A@0oX]' v'nd p  v+(h]] (,  A@4 A@0.o |Attribute VB_Name = "SDModule" ' Source:(http://www.ulib.org/webRoot/Books/Numerical_Recipes/b.cpdf.htmlA clever way to minimize roundoff erro@r, espCally6for large samples, is?use the corrected two-pass algorithm BGiven a rr{of data(),%?tinCeturns it Tage, Mea4n,d$@ standard deviatioQSD.6IMo k outNskewneInd kurtos9cul froBmcode2TyjSDr9/'b PopSqr(n/(n-@1))*SD!BatoFeb 2001OPublic Sub BasicFx(C As Do=SD?axin)ATo ]A@0macroagetI @G Min you must1) DIjM9f @tnZMEAN, AXIN a" @xB aNling!; no numb@ers ne@besignA)hese variab@2#*load a vthat h@ EA 3) C! likisB$SB:=myNA@37:=8:=9:@INEx foll`ow be@lDim nAgLong jsnepMVarC' fi`Rn n`(UBpAA) - L + 1@`B!s!?dAXc M6`zfirst-lu YMax Kk C=pni F z waMArmaS8 F`7j `V+ ss +EjIf a<{%lnBDQ>nj@Nex]ab / +A%Seco`-PROJECTwm1|PROJECT2PROJECTlkSummaryInformation(4au03{j4 `@>= ep =f*" #E(i!- *)` / (n>u'C m= `Òr3_JE#S󡓡SD'Wj@?@ee # _e_e X)?W.WMBfM22&&&&(&&^|&a`& dg/r-r& 'W(A$\(0D01 =)@>`uP= -10Xa,*3+%.bEcp.aPqcsgBox & Chr(1i&L4$ 2? `9) 3HNi0 2 iyRnP#'! mg 3 ayC< M " @X` pxME(6 << <+`$$`>Sqr(`Fp<<C^ZxME( <x@--),x `I > fI the I I: `s it`L`.rd d`0? I`2ss a 4),n SD*SD ii``L-76`.`0`2 6X  ( `t (w`  : (``xXXG v  H :X D L &0 FX  ,   "  @ JX L . $( JP   "  & H`  0x$$08 Hhp N( 8 ,X "  "  " @X` p NP ` , "  ( H "P X h          Source:duB http://www.ulib.org/webRoot/Books/Numerical_Recipes/bookcpdf.html4 A clever way to minimize roundoff error, especially> for large samples, is to use the corrected two-pass algorithmF Given an array of data(), this routine returns its average, Mean, and sample standard deviation, SD.? I took out the skewness and kurtosis calculation from the code The SD is the sample SD.% The Population SD is Sqr(n/(n-1))*SD Barreto Feb 20019 To use this macro to get Mean, SD, Max, and Min you mustD 1) DIM the four arguments MEAN, SD, MAX, MIN as Double in the macroF calling this macro; no numbers need be assigned to these variables' 2) DIM and load a vector that has data 3) Call this macro like this:C BasicFour data:=myArray, MEAN:=MEAN, SD:=SD, MAX:=MAX, MIN:=MIN Examples follow below]]]0]H]` find n$,$,  ' set MAX and MIN to first value$,$,'$,$,':) First pass to get the mean, max, and min L$,$, . L$, '.  L$, G L$,'j : L$, G L$,':j . ' Second pass to get the SD'. L$,$, L$,  '. . 0 '0 . . 2 '2( 2 0 0 %PxpG $*\Rffff*1l49d07e97$B   P0 ^ \ &@ Dh (   .@ 2P    &  &8 @P 11 Nov 2004p://wwBarretoRoIthis function checks to see if there are any links in the active workbookWto this add-in and if so, it returns a message string that can be used to warn the userUto use it, drag this LinkCheckerModule into the add-in, then insert this line of codeRange("I1").Value = LinkChecker>at the end of the macro that runs from the Form for the add-in!Change the cell address if neededget this add-ins name]@ @!'>(get all the links in the active workbook B%D'@,check to see if any links are to this add-in]X @$H v @ v$@ >'F F ?Warning! This workbook is using formulas available only in the >{ add-in. If you move the workbook, uninstall the add-in, or otherwise break the link, the formulas will show a #NAME error.'z@d8 there are no links so do nothingk vd there are no links so do nothingkiHAttribute VB_Name = "LinkCheckerModule" Function p() As Sng '11 Nov 2004Barretothis f~cs to see if there a!ny l{s $in arve workbookKoNadd"-$andBso , it ourns a message s2at can b@e used}wardr -$, drag_4M]Z A, en sertae of c@ode 'Range("I1").Valu߁->P*ehamacrw rBukfrom6 Form forgB Ch1 chellOrn`eededrgFeSs n} Dim my$Gtv= TtWd.Oa.[qaAs@Awources()ȊaFa#0Pos.Intege@vIft IsEmpty()1eng S i@#1 To UBoundE Apmy= IBnG(1, (i),M, 1.A > 0HO<"Wing!@ @5E  us|mulavailabl1@nlyB}" "&Kp& ". @"you moAhE, uninLst%7B, Korwise breakb ;"^wi>shown#NAME erpror.Ex t*Elsdd'no #Iso do n ' eIfNext i   < E rU~|  krU $ `nrU   9 a @T CLRFileSearchU__SRP_4H__SRP_5(_VBA_PROJECT].----------------------------------------------------------------- Object model:r Methods:D Execute - actually run search (returns Boolean which is always trueeededB unless deletes were requested which failed). If deletesC were requested, the list returned from the actual search has no members. NewSearch - clear it Properties:+ Lookin (string) - directory to search from0 Sort (boolean) - whether or not to sort resultsJ IncludeDirs (boolean) - include directories with results (not just files)& FoundFiles (collection) - the results& SearchSubFolders (boolean) - recurse?: DeleteFiles (boolean) - delete files as search progresses> DeleteFolders (boolean) - delete folders as search progressesJ -------------------------------------------------------------------------B The directory to look in. Set using the procedures. CLR, 13/5/99.]@HB The directory to copy to. Set using the procedures. CLR, 13/5/99.]@p2 Whether or not to sort the results. CLR, 13/5/99.] D Whether or not to include directories in the results. CLR, 17/5/99.] 5 The filename (well, spec) to look for. CLR, 17/5/99.] (? Whether or not to search through subdirectories. CLR, 17/5/99.] P The list of results.] 1 Whether or not to delete the files. CLR, 5/7/99.] x7 Whether or not to delete the directories. CLR, 5/7/99.] - Whether everything was in fact deleted okay.]@( Set the directory to look in. Tidies up trailing slashes. CLR, 5/7/99.< Giving a base directory with a trailing slash doesn't work.eps arB This poses a little of a problem because you can't pass somethingB like "C:\" to it. So we just chop the slash. This, however, meansB that you can't give it "C:" to mean the current working directory@ on drive C - tough. You shouldn't write code like that anyways. i \$^\ \ \ $'\ (Cutting trailing slash on directory nameA@`k0 \'Lm X+ Get the directory to look in. CLR, 5/7/99.g, num L'm( Set the directory to copy to. Tidies up trailing slashes. CLR, 5/7/99.ll \$^\ \ \ $'\(Cutting trailing slash on directory nameA@`ork \'Nm+ Get the directory to copy to. CLR, 5/7/99.;Th N'bm7 The routine to display debugging information. 24/5/99.x J fG[(CLRFileSearch) L f ->  djA@0 J fG[(CLRFileSearch) L f ->  dj-> 4 s J fG[(CLRFileSearch) L f ->  dj8g? @o84 Run when an instance of the class is started - just(n( runs the clear procedure. CLR, 14/5/99.**New class instanceA@`A@o Clear the search. CLR, 17/5/99.ab8c:\'o RN'P'R'Cleared search criteriaA@`io'V'X'bo6 Run when the instance of the class is closed. I'm not ed. 100% sure about this part.Methodx Kill off our results list. param.!Class terminated, memory releasedA@`o& The main run procedure. CLR, 13/ > a> ^\xa3IB: *=h8+3q&:Er`x&:Er`xa3IB: JME PSS S0T6"<<<N0{FCFB3D2A-A0FA-1068-A738-08002B3371B5}jP4 @jP8,@j<4 @jVDH @% %bZP @`Z AXh 8XAi\ O,!h \Qi s Bo Abh ` Vi\. If,!bxh Xdbi  `` p hidifecto h8` tt x` h z j` x,`  l i  - d l`X| inogre p------------ r----he direc`the p`v/99.`xirec`z usi`|LR, `~ 2 W`t th`/99.`er o` ecto  @%bLpH@bNL@jR(6$ @*x@<@jXFP @HXxPxx(p8@ H`&G *\R0*#1c$*\Rffff*1V49d07e3fTAQ  B h (  : P P h p  J H J  p     2 6 PH , , @ D8 P H H h 8p J <H FP  8 >H 4P "   . & B H8 H H F X p 6    B 2  ( "  0 .8 &h   6    B  2 X ` >h "  @ B @ B @` Bp   : . " 8  @ ` h &p "     &    < "P" x "0 ,B  ( Hh p < 8"   4H Pp  ,x  8 : Xp 8x  <   8 &@h "p   <  (   $Hpx  0 , ( @ H h     4 4@ 2x   >   ( 08@H  :P 8 &> 8 >H > : 0,8 h x   H 88P X `x $ (0 <8 Jx L H J` H   6 H:X"  @0 @8 x N  N 08 P@    0 6 "0 X "`    :      ( 0 08 h  "    @ D! P!p!x! ! !! D! @" @P" &""("" "##&#8#@#H#P#X# `#p# Code from Chris Rae< Source: http://chrisrae.com/vba/routines/clrfilesearch.html May 9, 2007! By Chris Rae, 13/5/99 to 6/7/99.P3 Are we debugging - 3=full, 2=some 1=a little, 0=no JJ --------5/99. E" Start recursing from the top dir.Executing searchA@`e 'Z LA@lo be c Z'ip6 The recursive bit. Stolen from various other programses c1 I wrote with similar ends in mind. CLR, 13/5/99.. All of the files which match in the directoryle 2]x And the directories resul]& The count of how many files there areh S] And the directories Rnd, ]2 The string each filename is temporarily stored in]3 The loop to go through each entry and perform what is necessaryutpu]01 The loop to recurse through each directory entry]H5 The flag to say whether any actual changes were madee FM during the bubblesort]` The sort loop T]x The temporary swapping variablean] Whether or not the file isans th a directory Rnd]5 If a copy failed then don't bother trying the delete in case we lose stuff. d] Searching: " n"A@` Find the directories in hereste 'v n\*.* $~'x xBA's x.. x.s ) This trap will catch if the file doesn't & exist at all (occasional problem with  NetWare volumes)  Check if it's a directory n\ x$'   Adding dir: xA@` v 'v v@rv+ x v+rk   - File wouldn't read - in this case it doesn'tv - really matter because we're just finding the  + directories. However, make sure it doesn't  think it's a directory.'Skipping (error): " n\ x"A@`    k ~'x 4 Go ahead and read all of the filenames matching the1 given spec into the array. Similar code to above but there ain't much we can do.'t n\       $~'xdo xl7 Check if it's a directory. Need to force the result of 58 the GetAttr to a boolean because otherwise it isn't ands 4 the "Not" function gets all confused. Don't ask how ) *!&"^%^! long this took me to work out. R n\ x$ X,of F t 'tAl t@p decla x t+p akX ~'xHB Sort the names into alphabetical order. Using a bubblesort, which1 seems to be fast enough at least for the moment. t  Pses _'~  t $p  $p  These two need to be swapped $p'  $p +p 0   +p'~k $ ~k5 Create any directories necessary. This bit has to goD before the file-handling section because, if directories need to be F created, they need to be created before we start trying to copy files ranB into them. Note the big lack of error-handling - the usual reasonD for directories not being created is because they're already there.hB What really matters is the file copies - if they fail, we have to be careful. / If we're copying stuff then make the directory N$dr Creating dir N n L $A@`to com` N n L $A@v+0k( z t9 Presume pleasantly that the copy (if one happens) worked': If we're copying the files then do that before the delete N * Writing file N n\ z$p L $A@`  n\ z$p N n\ z$p L $A@eeFailed copy to N n\ z$p L $A@`'`XPkH) If we're deleting them all then go ahead V d Removing file n\ z$pA@` KClearing attributesA@`E: n\ z$pA@1 To 1DeletingA@`N n\ z$pA@80Failed delete on n\ z$pA@`'Zd * As we're not wiping the whole thing, just  add the files to the list n\ z$p B@hkH z: Okay, here's the recursive bit. We now have an array full= of the directory names from this particular path and we must cycle through these.  | v n\ |$rA@l |k8> If we're deleting stuff then zap the directory. Remember that: some files in it may have failed copies but that's okay -: if they failed the copy then the file hasn't been deleted so the rmdir won't work anyway. XDeleting directory nA@` nA@Failed remove on nA@`'ZkosAttribute VB_Name = "CLRFileSear ch" Bas0{FCFB3D2A-A0FA-1068-A738-08002B3371B5} |Global!SpacFalse dCreatablPre declaIdCExposeTemplat@eDerivCustomiz E' Code from Chris Ra`' Source: http://c rae.com/vba/routines/clr fs.html ' May 9, 2007 ' By1, 13/5/99 to 6/7.Option xlicit ' Are we debugging - 3=full0=so1=a little, 0=no #Const SHOWDEBUGV0C-?C:Objectd m@Ql:M@ethodsExec- actually run CL (returns Boolean which @balways truBd unlessCletes@FGrequest faed). If &weI , the@P@L#?-,Khas no members@p' N,ew@ic@4r q' PropertieFLoo@kin (snDg) dirTo@P C#E02?S_= 3QDFDZDQ(' Rn ipancCe of the class is started - just ' runsear procedure. CLR, 14/5/99. Private Sub C_Initialize () SDebug "Ne wnrnce"J#Sich EndN lCls7ublicCxLookIn = "c:\"ortFalseIncludeDirs Hl FoldeTr>ujPO criterialDeleteFi 0 h35= 0F1= I!p\*.*vbTJWh'45<>0 #1P`.." A K"?+:Q1!strap wAqcSOCs>essexr at aS(ocj0/%;@[MNetW`K volumes 1Opnrr&GoC`w.uNu' Check if it's''= GetAttr\u`(,iB%%?0IA+E0%%, ~3;Q#XR!+ 1RIm PYertpsrd1 UL 6}gZkIf;SkipFNT y:A1le would&!G?0,Res3 NexpqT| e) !CWS' Go ahqy21?%scA1\givspecQprpray 1codPy bovqbRT `muЇ^caa(3D >>,P82 66@80~40$4OW!̥'_Hi@A. c1e SQ'eIQa0o/"pwip0,iqXd-"pR" fge!conf04`Duask *!&"^У%^!0tn@Aok wor>k@uQ PQYOr  (CQ(V/le)iT)TQ ReDim Preserve FilesHere(1 TopCount) > B =` This XEnd IfBXDir.Wend ' Sort the names into alphabetical order. Using a bubb5, whichHseems =be fast enough at lefor4momentB.If (k > 1) AcJ0ThenDoAnyChangZ= FalseF7BV&= - 1 *GFˇ)L +XS#' two needGswapp.eb SFHm", ,98= % bTruLbA( Nex t YLoop Until N|otEzC' Create any dire ctori@necessary. bit hagĒ' be*e¥f-@dlEAsioncause, ifLs!c!d,y{e we sdtanry! @fcopy s prm.A big lack of@ error-1"usuvso9g' n"*be3is"'r%@)ldyrea$u' Wh`ylly matter)a .-A%@ aile.t. carefuld owe"astuffn k y`priC (<> ""nSDebug "D  " & & Mid(B as Hy, Len(@QkInb)`, 2WOn E0sume YMk_ N Go 0h#AddItem ~#'Dplant8tG: 5!7y ( \on5ns) worka,F@:_.'@ k7 bZ7doH(ndel*Hk %C`=r!Yributes", 3  S ܈SetAttrO,O4Gp y p Kill{ V-- -`-G 0~|+Okay+a* ?, :,'( AsXn0~wip AXwhols, jusXj' adT|QTliFd! s.Hi7ŸLS' , h 'Åyursi# . Wow3an arr}l vfXromstdole h%^*\G{00020430-;C 0046}#2.0#0#C:\WINDOWS\sys@tem32\e2.tlb#OLE Automation0MSForms>SFFrms3 MPd 8l 8bUformat(Randomwidth Excel+ VBAWin16~Win32MacVBA6# MCSimSolverystdole`MSFormsC RefEditOfficeu MainModule  _Evaluate (FileSearchD CLRFileSearch"(ShowMCSolverForm˞ MCSolverFormRefEditOneCell;ValueK ActiveCellAddress Application*ReferenceStyle9xlR1C1pMsgBoxRpromptmTitle~Buttons] vbCritical+}(CheckSolverReference/Show (auto_closew ThisWorkbook| VBProjectOh ReferencesRemoveϱSave(AddMCSimSolverMenuItemjaMCSimSolverIsHereg^ CommandBars VisiblemyTools='OperatingSystem[ InternationalxlNonEnglishFunctionsInputBox ControlsKCount0vCaptionxnewItemICommandBarControlPAddrmsoControlButtonnOnActionresponseChrK~ vbInformationn(test4i`Missing|IsBrokenԣSOLVER vbTextCompare Qfound+(AddSolverReference ErrorHandlerO NewSearchfLookInPPath2SearchSubFoldersFileNamejExecuteY FoundFiles AddFromFileuErroWorkbook_AddinInstallWorkbook_AddinUninstallDeletex Sheet1 Sheet3CellValidStringTestCommandButton1_Click FirstCelluFirstCellArray SecondCell$SecondCellArray>myrepsPctdone mystarttime yoursettingFxmeanxSD!xmaxxmin;x2mean-x2SD@\x2maxHx2minf ViewTableAllLong histRangeO&Range  myendTimew1t_w2u_ii]jj]kk]aXCZ MCRawRangeExmyOriginalSheetName$myCurrentMCSimSheetmyCurrentStringmyStartPosition* myEndPositionSParentexternalwRefEditTwoCell~ ActiveSheet%N CheckBox1 ActiveWindow+RangeSelection7~ FoundVolatile*DataChangeWarning.MainModuleMainModuleThisWorkbookThisWorkbookSheet1Sheet1MCSolverFormMCSolverFormSheet2Sheet2Sheet3Sheet3RNGandSortModuleRNGandSortModuleSDModuleSDModuleHistogramHistogramLinkCheckerModuleLinkCheckerModuleCLRFileSearchCLRFileSearchID="{AB8AEE19-FD9C-11D2-9CEA-20F807C10627}" Module=MainModule Document=ThisWorkbook/&H00000000 Document=Sheet1/&H00000000 PaCellsFindn xlFormulasZvbYesNovbNo%ReplacefWhatC ReplacementLookAtOxlPart< SearchOrderxlByRows MatchCase TextBoxRepsKoNow% CalculationhxlCalculationManualScreenUpdating!  FrameProgressMCRawhmyMCRawVarCounteAreasKSheets  continue3rmyMCRawSheetName\ CalculateFull|SolverOkC SolverSolveQ userfinishs LabelProgress}WorksheetFunction&Min (BasicFour` WorkbooksBCopyƿBeforeSqr((Log107HDisplayGridlinesCharts ActiveChart  ChartType?xlXYScatterLinesNoMarkersvLocation-Where֍xlLocationAsObject SetSourceDataaSourceGShapes< ChartObjectsHColumnsp9Top5RowsUHeight|PlotArea SelectionZInterior; ColorIndexxlNoneLegendPAxesbxlValue0x`HͺJA+BNf!èf1 OF} k>=FlrN7MPE)4!ouY#%XIdtOIC(IOL6YR@-tBw)Y{K`}Cٝr=hK/)I2y״7J-nrHխH ^XeTickLabelPosition~MajorGridlinesW xlCategoryZHasMajorGridlinesHasMinorGridlines)HasTitle{ ChartTitle_ Charactersrg(HistogramMaker1Var(HistogramMaker2VarC CheckBox2myExistingSheet0finishXmyExistingRawSheetClearActivate|SeriesCollection;:FormulaqY DisplayAlertsmyMCRawOverwrite ( LinkChecker CalculateCommandButton2_ClickCellStringTestFirstCell TestString{CellStringTestSecondCell`RefEditOneCell_EnterSetFocus Sheet2 Histogram''(LimitsPminvalpmaxvalxx_oldy$yys_absminxrem$adjustymultoldwidthvaluesnoobsr histsheetnumber-binleft;~bincount!MaxȲmysheetb WorksheetXoylchoice{colHvalrangecvarnameYcNobinsjmincompgmaxcomptLargeyRangexRangeWleftCVtopC. precision statRangeAxupwxdown myboxRange (NewLimitsl(DriveFormatting TableRange7myWidthoomaxwidth(MakeBins ColumnWidthAutoFit&  tickwidthPlotBy$ xlColumnsh ChartArea HasLegendB ClearFormats MinimumScale MaximumScale=rowsubmcolsub `rowColumni ScrollRow" ScrollColumnLHvalues1uvalues2unoobs1rOnoobs2sOWidth1yWidth2yminval1rmaxval1minval2smaxval2 AllLongs1 AllLongs2binwidthYx1_Y1O_x2_Y2P_Nobins2Nobins1xx1xx2xxmin5maxbin/myname1myname2Varlabel<@ overallWidth overallxxSRisubwWrapText}nbsAxlThinw xlContinuous[ (FindPrecisionRound leaveloopcLogd(FormatSumStatsv NumberFormatR whichseriesIstartlwidth` lwidthrem_d[neg RNGandSortModule(myFMRG (myFMRGInt6& (myFMRGIntlag1  (myFMRGIntlag2!(ISETu(GSETIpg (auto_openV RndRBArraymyX(FMRG*Volatile(RandomNV"_(FMRGTestfrank starttimeLTimerX (FMRGgraphF (NormalRNGUNormRandYV TypeofRand47Mean@SD_V1t_V2u_RadiusVFACDrepeattrepeat27(NormalRandomAdvbNormalRandomValueh (NORMALRANDOM(Uniform?O (UniformRNG  UniformRand76(TestUniformRandv(UniformBetweenT(TDraweDF\TRand (TRNGmyzRandDmyyRand myyRandTmpt(ExponentialRNGOExponentialRand0lowert(TestExponentialRNG3? myTestVectorB(Expo{ (NormalRNGTestresultur(qsort#hlcrinemdkbpv^ (pivotValue (MyPartition/.middleXx3_checkocheck2ptempe(CheckforSmallerlo ^hi](swap=tempR (sorttestingxNmyArray SDModuledata;sjep]Var(MeanSD(Exampleh"SDPop(Example2i LinkCheckerModule# myLinkStringaLinksActiveWorkbook LinkSourceskmyPosѰIsEmpty  SHOWDEBUGQ priLookIn priCopyTo %Sort IncludeDirs6 CollectionW DeleteFiles  DeleteFolders~ DeletedOkay5ToDir2Right SDebugCopyTo[DBInfo DebugLevelClass_InitializenClass_TerminatePRunDownu BaseDirectoryE FilesHere1DirsHere FileCount=DirCount WThisFileY)AddItem  RecurseDirsx* AnyChanges BubbleSortCSwapFH7ItIsDir CopyFailed vbDirectoryg FileNotThereõGetAttrSkipFNTMkDir CopyFailedErr#FileCopy@SkipCopyFailedFileNotDeletedd*SetAttrCKillSkipFileNotDeleted DirNotDeletedRmDirSkipDirNotDeletedRWorkbookk AddinInstallAddinUninstallClass InitializeN TerminateyLItemz _B_var_LeftQ _B_var_Chr\;_B_var_response A   + !=  $B`@ @`!H@P$HA dir4V__SRP_0__SRP_1JMCSolverForm!P99ED452EE1-E08F-101A-8-02608C4 D0BB4EFM20.DLL#Microsoft : ^ Object Libr@ary/;D1?u00}#0#kh0 AAA9352D3-EAFB-4BF5-8201-FCBFCE5BFA7C6DOCUME~1\user\LOCALS@Temp\VBE\S.exd ; .E .`M AC1RefE(ditgR@fE@i3wH24517HЉ1ʼnProgram Files\dOffice\12\REFEXDITm! " Control?iž707o8EDEB28A4-77g4F94-8197-A0620FA88E8B}#8i4O`J @EFC2aw#cwOf icbw``a22DF8D04C-Hf@B-BDE5@4AjAv4`2Jk2C ommon4Sh@ared\o411\mso.dllLc 9.0j@"  ^ MainModuleGainMduU,26 TH1y",bH!" +ThisWorkbookG TiW rkbjo2o m^"e=Sheet1*G Se=t1¸2 T- 3 3e 3 3 YZҠoAہc.XMC`v`r?c.c.oF}o h]c(3/2-!j2)!2-!2?!O HistrQhGH 0yogr1mjN2ob{G / RNGa@ndSort8 QNGn`_Sr0t9le2:o  t) Z P SDc5 SD< 92?e LinkCheckerEUA$LBKk+h+c=`BeKLPO2 l!]D|  CLRvSearchGE$P LR5iUUS a c F_P)Q//eP!rEghg>2q6rU~~~y    !   Sort IncludeDirsFileNameSearchSubFolders FoundFiles i( DeleteFiles DeleteFoldersToDirDBInfo Deto be EnglishUS. Please enter the word that Excel is using for Tools on the main menu across the top of the screen. For example, in Spanish, it is Herramientas. ,Get the word for TOOLS vYou have successfully loaded the MC Sim with Solver add-in.< In Excel 2007, click MCSimSolver in the Add-Ins tab. In older versions of Excel, access the the macro by executing ": MCSim Solver... $MC Sim with Solver8VBE6.DLLCheckforSmaller i H ` h S  auto_openFMRGRandomRandomNVFMRGTest FMRGgraph NormalRNGNormalRandomAdv NORMALRANDOMUniform UniformRNGTestUniformRandUniformBetweenTDrawTRNGExponentialRNGTestExponentialRNGExpo NormalRNGTestqsort pivotValue 4 R  Q 8 solver`FzFLimitsHistogramMaker1VarHistogramMaker2VarMakeBins FindPrecisionLog10FormatSumStatsDriveFormatting NewLimits  LinkChecker RK[| .C:\DOCUME~1\user\LOCALS~1\Temp\VBE\MSForms.exd 9YaEF6C:\Program Files\Microsoft Office\Office12\REFEDIT.DLLRefEditShowMCSolverForm !!I(wObbugLevel BaseDirectoryNormRand TypeofRandMeanSD UniformRandabDFTRandExponentialRandXlrnmx1x2x3middlecheckcheck2ppvkijminvalmaxvalwidthxxvaluesnoobs histsheet histRange ViewTableAllLongnumbervalues1values2noobs1noobs2Width1Width2minval1maxval1minval2maxval2 AllLongs1 AllLongs2binleftbincountNobinsy precision statRangevalrange whichseriesxminstart$  1}!R KQDBTimes New Roman8KEFҋB `EFx, #FrameProgresK*y    rU~~~~~~~~~~~~~~~~~ ~~~~~. 5~Z3Bd?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopquvwxyz{~7o+-wCompObjupCompObj*/aVBFrameA      !"#$%&')*+,-./012356789:;<=>?@ABCDEFGHIKLMNOPQRSTUVWXYZ[\^_adeg~iklmnpqrstvxy{|} n``Microsoft Forms 2.0 FrameEmbedded ObjectForms.Frame.19q"1Tahoma<H   }&"Progress BarR KQDBTimes New Roman8h0 20LabelProgress (ProceedqNqTahomad(CanceldENqTahomadHFt. ] n``Microsoft Forms 2.0 FrameEmbedded ObjectForms.Frame.19q ( Select a cellq 5Times New Roman@H,{1000 5Times New Roman0(Enter the Number of Repetitions 5Times New RomanH${ 5Times New Roman8H  .}& RequiredR MainModule=88, 116, 953, 365, C ThisWorkbook=0, 0, 865, 249, C Sheet1=0, 0, 0, 0, C MCSolverForm=0, 0, 0, 0, C, 22, 29, 1046, 667, C Sheet2=0, 0, 0, 0, C Sheet3=0, 0, 0, 0, C RNGandSortModule=22, 29, 887, 278, C SDModule=0, 0, 0, 0, C Histogram=0, 0, 0, 0, C LinkCheckerModule=0, 0, 0, 0, C CLRFileSearch=44, 58, 909, 307, C `V78`V78EFOh+'0X hp  , 8 DP$Monte Carlo Simulation with SolverBarreto/Howland0Monte Carlo Simulation with Solver 10 May 2007Humberto Barreto@b%13@AL@9՜.+,0HP X`hp x Root Entry F2}7v9mWorkbook5_VBA_PROJECT_CUR"30`V78v9VBA`V78P9KQDBTimes New Roman(2HLabel3L DTextBoxRepsoqEnter the number of repetitions(2XLabel5,.@RefEditOneCelln{ n``Microsoft Forms 2.0 FrameEmbedded ObjectForms.Frame.19q$(Select a second cell  5Times New RomanH${ 5Times New Roman8H  /}&OptionalR KQDBTimes New Roman`(2LLabel4,/@RefEditTwoCelln{{0hOutput to Existing MCSim Sheet 5Times New RomanLF#{DocumentSummaryInformation8CompObjm Doc OneVarMCSim TwoVarMCSim  Worksheets F!Microsoft Office Excel WorksheetBiff8Excel.Sheet.89q