ࡱ>  c ~{b  !"#$%&'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`aefgpijklmnodqrstuvwxyz{|}~Root Entry Fd5Workbookh|XCBTK_VBA_PROJECT_CUR"ROd5d5'FRI - general utilities Ke1 pComplex LookupL'C:\Program Files\Microsoft Office\Office\LIBRARY\fpagenrl.xla'!lookstuffsub((ʦ """)))UUUMMMBBB999|PP3f3333f333ff3fffff3f3f̙f3333f3333333333f3333333f3f33ff3f3f3f3333f3333333f3̙33333f333ff3ffffff3f33f3ff3f3f3ffff3fffffffff3fffffff3f̙ffff3ff333f3ff33fff33f3ff̙3f3f3333f333ff3fffff̙̙3̙f̙̙̙3f̙3f3f3333f333ff3fffff3f3f̙3ffffffffff!___wwwp(   pSet Display to USERN'C:\Program Files\Microsoft Office\Office\LIBRARY\fpagenrl.xla'!SetEditDisplay((ʦ """)))UUUMMMBBB999|PP3f3333f333ff3fffff3f3f̙f3333f3333333333f3333333f3f33ff3f3f3f3333f3333333f3̙33333f333ff3ffffff3f33f3ff3f3f3ffff3fffffffff3fffffff3f̙ffff3ff333f3ff33fff33f3ff̙3f3f3333f333ff3fffff̙̙3̙f̙̙̙3f̙3f3f3333f333ff3fffff3f3f̙3ffffffffff!___wwwp(??G pClear StatusbarN'C:\Program Files\Microsoft Office\Office\LIBRARY\fpagenrl.xla'!ClearStatusBar((ʦ """)))UUUMMMBBB999|PP3f3333f333ff3fffff3f3f̙f3333f3333333333f3333333f3f33ff3f3f3f3333f3333333f3̙33333f333ff3ffffff3f33f3ff3f3f3ffff3fffffffff3fffffff3f̙ffff3ff333f3ff33fff33f3ff̙3f3f3333f333ff3fffff̙̙3̙f̙̙̙3f̙3f3f3333f333ff3fffff3f3f̙3ffffffffff!___wwwp(wo~7v p"Set Screen to match selected cellsT'C:\Program Files\Microsoft Office\Office\LIBRARY\fpagenrl.xla'!FitSelectionToScreen((ʦ """)))UUUMMMBBB999|PP3f3333f333ff3fffff3f3f̙f3333f3333333333f3333333f3f33ff3f3f3f3333f3333333f3̙33333f333ff3ffffff3f33f3ff3f3f3ffff3fffffffff3fffffff3f̙ffff3ff333f3ff33fff33f3ff̙3f3f3333f333ff3fffff̙̙3̙f̙̙̙3f̙3f3f3333f333ff3fffff3f3f̙3ffffffffff!___wwwp(p`@3CCC p!Go to last REAL cell in the sheetP'C:\Program Files\Microsoft Office\Office\LIBRARY\fpagenrl.xla'!GoToLastRealCell((ʦ """)))UUUMMMBBB999|PP3f3333f333ff3fffff3f3f̙f3333f3333333333f3333333f3f33ff3f3f3f3333f3333333f3̙33333f333ff3ffffff3f33f3ff3f3f3ffff3fffffffff3fffffff3f̙ffff3ff333f3ff33fff33f3ff̙3f3f3333f333ff3fffff̙̙3̙f̙̙̙3f̙3f3f3333f333ff3fffff3f3f̙3ffffffffff!___wwwp(!9!y "Move activecell to Top Left cornerL'C:\Program Files\Microsoft Office\Office\LIBRARY\fpagenrl.xla'!MoveTopRight((ʦ """)))UUUMMMBBB999|PP3f3333f333ff3fffff3f3f̙f3333f3333333333f3333333f3f33ff3f3f3f3333f3333333f3̙33333f333ff3ffffff3f33f3ff3f3f3ffff3fffffffff3fffffff3f̙ffff3ff333f3ff33fff33f3ff̙3f3f3333f333ff3fffff̙̙3̙f̙̙̙3f̙3f3f3333f333ff3fffff3f3f̙3ffffffffff!___wwwp( pFill to adjacent lengthV'C:\Program Files\Microsoft Office\Office\LIBRARY\fpagenrl.xla'!FillFromAdjacentColumn((ʦ """)))UUUMMMBBB999|PP3f3333f333ff3fffff3f3f̙f3333f3333333333f3333333f3f33ff3f3f3f3333f3333333f3̙33333f333ff3ffffff3f33f3ff3f3f3ffff3fffffffff3fffffff3f̙ffff3ff333f3ff33fff33f3ff̙3f3f3333f333ff3fffff̙̙3̙f̙̙̙3f̙3f3f3333f333ff3fffff3f3f̙3ffffffffff!___wwwp(?______ pFill Down if blankL'C:\Program Files\Microsoft Office\Office\LIBRARY\fpagenrl.xla'!Changeblanks((ʦ """)))UUUMMMBBB999|PP3f3333f333ff3fffff3f3f̙f3333f3333333333f3333333f3f33ff3f3f3f3333f3333333f3̙33333f333ff3ffffff3f33f3ff3f3f3ffff3fffffffff3fffffff3f̙ffff3ff333f3ff33fff33f3ff̙3f3f3333f333ff3fffff̙̙3̙f̙̙̙3f̙3f3f3333f333ff3fffff3f3f̙3ffffffffff!___wwwp(??????#?? mFind next VALUE downQ'C:\Program Files\Microsoft Office\Office\LIBRARY\fpagenrl.xla'!FindNextValueDown((ʦ """)))UUUMMMBBB999|PP3f3333f333ff3fffff3f3f̙f3333f3333333333f3333333f3f33ff3f3f3f3333f3333333f3̙33333f333ff3ffffff3f33f3ff3f3f3ffff3fffffffff3fffffff3f̙ffff3ff333f3ff33fff33f3ff̙3f3f3333f333ff3fffff̙̙3̙f̙̙̙3f̙3f3f3333f333ff3fffff3f3f̙3ffffffffff!___wwwp(? pFind a string in ALL worksheetsS'C:\Program Files\Microsoft Office\Office\LIBRARY\fpagenrl.xla'!FindInAllWorksheets((ʦ """)))UUUMMMBBB999|PP3f3333f333ff3fffff3f3f̙f3333f3333333333f3333333f3f33ff3f3f3f3333f3333333f3̙33333f333ff3ffffff3f33f3ff3f3f3ffff3fffffffff3fffffff3f̙ffff3ff333f3ff33fff33f3ff̙3f3f3333f333ff3fffff̙̙3̙f̙̙̙3f̙3f3f3333f333ff3fffff3f3f̙3ffffffffff!___wwwp(   List ALL names in the workbookI'C:\Program Files\Microsoft Office\Office\LIBRARY\fpagenrl.xla'!ListNames((ʦ """)))UUUMMMBBB999|PP3f3333f333ff3fffff3f3f̙f3333f3333333333f3333333f3f33ff3f3f3f3333f3333333f3̙33333f333ff3ffffff3f33f3ff3f3f3ffff3fffffffff3fffffff3f̙ffff3ff333f3ff33fff33f3ff̙3f3f3333f333ff3fffff̙̙3̙f̙̙̙3f̙3f3f3333f333ff3fffff3f3f̙3ffffffffff!___wwwp( p$Locate the nth highest cell in rangeI'C:\Program Files\Microsoft Office\Office\LIBRARY\fpagenrl.xla'!findlarge((ʦ """)))UUUMMMBBB999|PP3f3333f333ff3fffff3f3f̙f3333f3333333333f3333333f3f33ff3f3f3f3333f3333333f3̙33333f333ff3ffffff3f33f3ff3f3f3ffff3fffffffff3fffffff3f̙ffff3ff333f3ff33fff33f3ff̙3f3f3333f333ff3fffff̙̙3̙f̙̙̙3f̙3f3f3333f333ff3fffff3f3f̙3ffffffffff!___wwwp(aaaaaaaaaa Paste selection with VALUESK'C:\Program Files\Microsoft Office\Office\LIBRARY\fpagenrl.xla'!PasteValues((ʦ """)))UUUMMMBBB999|PP3f3333f333ff3fffff3f3f̙f3333f3333333333f3333333f3f33ff3f3f3f3333f3333333f3̙33333f333ff3ffffff3f33f3ff3f3f3ffff3fffffffff3fffffff3f̙ffff3ff333f3ff33fff33f3ff̙3f3f3333f333ff3fffff̙̙3̙f̙̙̙3f̙3f3f3333f333ff3fffff3f3f̙3ffffffffff!___wwwp( m One decimalJ'C:\Program Files\Microsoft Office\Office\LIBRARY\fpagenrl.xla'!OneDecimal((ʦ """)))UUUMMMBBB999|PP3f3333f333ff3fffff3f3f̙f3333f3333333333f3333333f3f33ff3f3f3f3333f3333333f3̙33333f333ff3ffffff3f33f3ff3f3f3ffff3fffffffff3fffffff3f̙ffff3ff333f3ff33fff33f3ff̙3f3f3333f333ff3fffff̙̙3̙f̙̙̙3f̙3f3f3333f333ff3fffff3f3f̙3ffffffffff!___wwwp(gccg pText to NumberE'C:\Program Files\Microsoft Office\Office\LIBRARY\fpagenrl.xla'!psadd((ʦ """)))UUUMMMBBB999|PP3f3333f333ff3fffff3f3f̙f3333f3333333333f3333333f3f33ff3f3f3f3333f3333333f3̙33333f333ff3ffffff3f33f3ff3f3f3ffff3fffffffff3fffffff3f̙ffff3ff333f3ff33fff33f3ff̙3f3f3333f333ff3fffff̙̙3̙f̙̙̙3f̙3f3f3333f333ff3fffff3f3f̙3ffffffffff!___wwwp(???????}111 #Multiply all CONSTANTS in selectionQ'C:\Program Files\Microsoft Office\Office\LIBRARY\fpagenrl.xla'!MultiplySelection((ʦ """)))UUUMMMBBB999|PP3f3333f333ff3fffff3f3f̙f3333f3333333333f3333333f3f33ff3f3f3f3333f3333333f3̙33333f333ff3ffffff3f33f3ff3f3f3ffff3fffffffff3fffffff3f̙ffff3ff333f3ff33fff33f3ff̙3f3f3333f333ff3fffff̙̙3̙f̙̙̙3f̙3f3f3333f333ff3fffff3f3f̙3ffffffffff!___wwwp(qcGGcq ;Add Header to Current sheetCLabels the workbook with authors name and date and workbook detailsW'C:\Program Files\Microsoft Office\Office\LIBRARY\fpagenrl.xla'!label_sheet.label_sheet T&Datestamp WorksheetO'C:\Program Files\Microsoft Office\Office\LIBRARY\fpagenrl.xla'!DateStamp_sheet(ww"!p(?‡?; Wrap Text in the selected cellsN'C:\Program Files\Microsoft Office\Office\LIBRARY\fpagenrl.xla'!WrapTextToggle((ʦ """)))UUUMMMBBB999|PP3f3333f333ff3fffff3f3f̙f3333f3333333333f3333333f3f33ff3f3f3f3333f3333333f3̙33333f333ff3ffffff3f33f3ff3f3f3ffff3fffffffff3fffffff3f̙ffff3ff333f3ff33fff33f3ff̙3f3f3333f333ff3fffff̙̙3̙f̙̙̙3f̙3f3f3333f333ff3fffff3f3f̙3ffffffffff!___www p(-P T 4  Print Pivot tablesR'C:\Program Files\Microsoft Office\Office\LIBRARY\fpagenrl.xla'!PrintAllPivotPages((ʦ """)))UUUMMMBBB999|PP3f3333f333ff3fffff3f3f̙f3333f3333333333f3333333f3f33ff3f3f3f3333f3333333f3̙33333f333ff3ffffff3f33f3ff3f3f3ffff3fffffffff3fffffff3f̙ffff3ff333f3ff33fff33f3ff̙3f3f3333f333ff3fffff̙̙3̙f̙̙̙3f̙3f3f3333f333ff3fffff3f3f̙3ffffffffff!___wwwp(~1~5    Switch PanesP'C:\Program Files\Microsoft Office\Office\LIBRARY\fpagenrl.xla'!CycleWindowPanes((ʦ """)))UUUMMMBBB999|PP3f3333f333ff3fffff3f3f̙f3333f3333333333f3333333f3f33ff3f3f3f3333f3333333f3̙33333f333ff3ffffff3f33f3ff3f3f3ffff3fffffffff3fffffff3f̙ffff3ff333f3ff33fff33f3ff̙3f3f3333f333ff3fffff̙̙3̙f̙̙̙3f̙3f3f3333f333ff3fffff3f3f̙3ffffffffff!___wwwp(  Create DatabaseN'C:\Program Files\Microsoft Office\Office\LIBRARY\fpagenrl.xla'!createDataBase   Hide text in cellsH'C:\Program Files\Microsoft Office\Office\LIBRARY\fpagenrl.xla'!HideText((ʦ """)))UUUMMMBBB999|PP3f3333f333ff3fffff3f3f̙f3333f3333333333f3333333f3f33ff3f3f3f3333f3333333f3̙33333f333ff3ffffff3f33f3ff3f3f3ffff3fffffffff3fffffff3f̙ffff3ff333f3ff33fff33f3ff̙3f3f3333f333ff3fffff̙̙3̙f̙̙̙3f̙3f3f3333f333ff3fffff3f3f̙3ffffffffff!___wwwp(   Hide Numbers in cellsR'C:\Program Files\Microsoft Office\Office\LIBRARY\fpagenrl.xla'!HideNumbersInCells((ʦ """)))UUUMMMBBB999|PP3f3333f333ff3fffff3f3f̙f3333f3333333333f3333333f3f33ff3f3f3f3333f3333333f3̙33333f333ff3ffffff3f33f3ff3f3f3ffff3fffffffff3fffffff3f̙ffff3ff333f3ff33fff33f3ff̙3f3f3333f333ff3fffff̙̙3̙f̙̙̙3f̙3f3f3333f333ff3fffff3f3f̙3ffffffffff!___wwwp( Save and Close active workbookW'C:\Program Files\Microsoft Office\Office\LIBRARY\fpagenrl.xla'!SaveCloseActiveWorkbook((ʦ """)))UUUMMMBBB999|PP3f3333f333ff3fffff3f3f̙f3333f3333333333f3333333f3f33ff3f3f3f3333f3333333f3̙33333f333ff3ffffff3f33f3ff3f3f3ffff3fffffffff3fffffff3f̙ffff3ff333f3ff33fff33f3ff̙3f3f3333f333ff3fffff̙̙3̙f̙̙̙3f̙3f3f3333f333ff3fffff3f3f̙3ffffffffff!___wwwp(;w yAChange Current DirectoryV'C:\Program Files\Microsoft Office\Office\LIBRARY\fpagenrl.xla'!ChangeCurrentDirectory((ʦ """)))UUUMMMBBB999|PP3f3333f333ff3fffff3f3f̙f3333f3333333333f3333333f3f33ff3f3f3f3333f3333333f3̙33333f333ff3ffffff3f33f3ff3f3f3ffff3fffffffff3fffffff3f̙ffff3ff333f3ff33fff33f3ff̙3f3f3333f333ff3fffff̙̙3̙f̙̙̙3f̙3f3f3333f333ff3fffff3f3f̙3ffffffffff!___wwwp(@C_@C_?pCw@C_@C_? "Close active workbook - DON'T SAVEY'C:\Program Files\Microsoft Office\Office\LIBRARY\fpagenrl.xla'!NOSaveCloseActiveWorkbook((ʦ """)))UUUMMMBBB999|PP3f3333f333ff3fffff3f3f̙f3333f3333333333f3333333f3f33ff3f3f3f3333f3333333f3̙33333f333ff3ffffff3f33f3ff3f3f3ffff3fffffffff3fffffff3f̙ffff3ff333f3ff33fff33f3ff̙3f3f3333f333ff3fffff̙̙3̙f̙̙̙3f̙3f3f3333f333ff3fffff3f3f̙3ffffffffff!___wwwp(;w CK'C:\Program Files\Microsoft Office\Office\LIBRARY\fpagenrl.xla'!togglemerge(ww"" """ "" """ "" """ "" """ p( sPaste labels in XY plotN'C:\Program Files\Microsoft Office\Office\LIBRARY\fpagenrl.xla'!setlabelvalues((ʦ """)))UUUMMMBBB999|PP3f3333f333ff3fffff3f3f̙f3333f3333333333f3333333f3f33ff3f3f3f3333f3333333f3̙33333f333ff3ffffff3f33f3ff3f3f3ffff3fffffffff3fffffff3f̙ffff3ff333f3ff33fff33f3ff̙3f3f3333f333ff3fffff̙̙3̙f̙̙̙3f̙3f3f3333f333ff3fffff3f3f̙3ffffffffff!___wwwp(_[}x} k&Paste labels in ALL seriesW'C:\Program Files\Microsoft Office\Office\LIBRARY\fpagenrl.xla'!setlabelvaluesallSeries( " "" ""p(dgAO~~ sCreate XY (Scatter) ChartH'C:\Program Files\Microsoft Office\Office\LIBRARY\fpagenrl.xla'!createXY jSwap X & Y axesH'C:\Program Files\Microsoft Office\Office\LIBRARY\fpagenrl.xla'!swapaxes((ʦ """)))UUUMMMBBB999|PP3f3333f333ff3fffff3f3f̙f3333f3333333333f3333333f3f33ff3f3f3f3333f3333333f3̙33333f333ff3ffffff3f33f3ff3f3f3ffff3fffffffff3fffffff3f̙ffff3ff333f3ff33fff33f3ff̙3f3f3333f333ff3fffff̙̙3̙f̙̙̙3f̙3f3f3333f333ff3fffff3f3f̙3ffffffffff!___wwwp(?x>8<8>88X?>?? }&Save active chart as GIF fileR'C:\Program Files\Microsoft Office\Office\LIBRARY\fpagenrl.xla'!SaveThisChartAsGIF(wwwwD@D@DD@D@Dp@Dtrqqr''rqpr''qqtDp@@D@Dp(I$I$x$Hx'y'? t&Fill Down,Up,Left & RightQ'C:\Program Files\Microsoft Office\Office\LIBRARY\fpagenrl.xla'!FillAllDirections M'C:\Program Files\Microsoft Office\Office\LIBRARY\fpagenrl.xla'!Button1_Click sDisable Chart Point DetailsQ'C:\Program Files\Microsoft Office\Office\LIBRARY\fpagenrl.xla'!ChartPointDetails(  "!"!"!"!p(p Xxxhxx xx   '&statistica - create regression formulaa'C:\Program Files\Microsoft Office\Office\LIBRARY\fpagenrl.xla'!CreateFormulaFromStatisticaOutput(www"" " "3003333p(# 0 0`  Create List from TableV'C:\Program Files\Microsoft Office\Office\LIBRARY\fpagenrl.xla'!CreateColumnsFromTable(p(^RI^RI^@I B &Select Visible CellsR'C:\Program Files\Microsoft Office\Office\LIBRARY\fpagenrl.xla'!SelectVisibleCells(p(_SX_DB_CURQd5d5VBAP"d5 {d5Text to Number9=lookupstuff2$  !"#%&'(*+,./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{}~  (  , *                                                     ` $    $    $   (  Comma [0]_APIComma [0]_ASCIIoutDComma [0]_ASCIIoutMComma [0]_Constant Data Comma [0]_DCComma [0]_Demo_Of_LEDBarComma [0]_ExampeVBACodeComma [0]_File FunctionsComma [0]_fredtestComma [0]_LEDBar Comma_APIComma_ASCIIoutDComma_ASCIIoutMComma_Constant Data Comma_DC!Comma_Demo_Of_LEDBar"Comma_ExampeVBACode#Comma_File Functions$Comma_fredtest% Comma_LEDBar&  Worksheet"&Tools&WindowChart"&Tools&Window(Visual Basic Module"&Tools&Window\p Fred Cumming Ba=H  !"$%&'()* ThisWorkbookSFRI=u&t8X@"1Arial1Arial1Arial1Arial1Arial1.Times New Roman1* MS Sans Serif1Arial1Tahoma"$"#,##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\);_(* "-"??_);_(@_)"$"#,##0;\-"$"#,##0"$"#,##0;[Red]\-"$"#,##0"$"#,##0.00;\-"$"#,##0.00#"$"#,##0.00;[Red]\-"$"#,##0.0050_-"$"* #,##0_-;\-"$"* #,##0_-;_-"$"* "-"_-;_-@_-,'_-* #,##0_-;\-* #,##0_-;_-* "-"_-;_-@_-=8_-"$"* #,##0.00_-;\-"$"* #,##0.00_-;_-"$"* "-"??_-;_-@_-4/_-* #,##0.00_-;\-* #,##0.00_-;_-* "-"??_-;_-@_-[Red][<5]0;General[Red][<5]0;[Black]General\ 0.0 0.00000 0.0000 0.000#,, #,,"000,000" 0.000000 "";Generald/m/yy\ h:mm:ss 0\ % 0\ "%"d/m#,##0\ "DM";\-#,##0\ "DM"##,##0\ "DM";[Red]\-#,##0\ "DM"$#,##0.00\ "DM";\-#,##0.00\ "DM")$#,##0.00\ "DM";[Red]\-#,##0.00\ "DM">9_-* #,##0\ "DM"_-;\-* #,##0\ "DM"_-;_-* "-"\ "DM"_-;_-@_->9_-* #,##0\ _D_M_-;\-* #,##0\ _D_M_-;_-* "-"\ _D_M_-;_-@_-FA_-* #,##0.00\ "DM"_-;\-* #,##0.00\ "DM"_-;_-* "-"??\ "DM"_-;_-@_-FA_-* #,##0.00\ _D_M_-;\-* #,##0.00\ _D_M_-;_-* "-"??\ _D_M_-;_-@_-#,##0"DM"_);\(#,##0"DM"\)##,##0"DM"_);[Red]\(#,##0"DM"\)$#,##0.00"DM"_);\(#,##0.00"DM"\))$#,##0.00"DM"_);[Red]\(#,##0.00"DM"\)@;_ * #,##0_)"DM"_ ;_ * \(#,##0\)"DM"_ ;_ * "-"_)"DM"_ ;_ @_ @;_ * #,##0_)_D_M_ ;_ * \(#,##0\)_D_M_ ;_ * "-"_)_D_M_ ;_ @_ HC_ * #,##0.00_)"DM"_ ;_ * \(#,##0.00\)"DM"_ ;_ * "-"??_)"DM"_ ;_ @_ HC_ * #,##0.00_)_D_M_ ;_ * \(#,##0.00\)_D_M_ ;_ * "-"??_)_D_M_ ;_ @_  General_)\$#,##0\ ;\(\$#,##0\)\$#,##0\ ;[Red]\(\$#,##0\) \$#,##0.00\ ;\(\$#,##0.00\)% \$#,##0.00\ ;[Red]\(\$#,##0.00\)m/d #\,##0.00 \$#.00 %#.00 #.00 #\,##0. \$#. 0.000_) 0.0_) 0.00000_) 0.00_)0.0000000;"DM"0_) 0.0000_) 0.000000_) 0.0000000_) 0.00000000_) 0.000000000_) 0.0000000                + )    &     &     (    '(Currency [0]_API)Currency [0]_ASCIIoutD*Currency [0]_ASCIIoutM+Currency [0]_Constant Data,Currency [0]_DC -Currency [0]_Demo_Of_LEDBar.Currency [0]_ExampeVBACode /Currency [0]_File Functions0Currency [0]_fredtest1Currency [0]_LEDBar2 Currency_API3Currency_ASCIIoutD4Currency_ASCIIoutM5Currency_Constant Data6 Currency_DC7Currency_Demo_Of_LEDBar8Currency_ExampeVBACode9Currency_File Functions:Currency_fredtest;Currency_LEDBar<Normal_betweendates=Normal_CalculateColumnName>Normal_Change Blanks'?"Normal_Colour Cells Based on Value@Normal_Constant DataANormal_DIRectory retrievalBNormal_Fix Other SubCNormal_fredtestDNormal_GoToLast Real Cell"ENormal_Import from text filesFNormal_lookupstuffGNormal_Module1HNormal_Module1 (2)INormal_Module1 (3)JNormal_MsgBox for chartsKNormal_MsgBox for charts_1LNormal_On Entry Do StuffMNormal_Proper CaseNNormal_Run DOSONormal_Shift_controlPNormal_Text to NumberQRStandard_ASCIIausDSStandard_ASCIIausMTStandard_TabelleUStandard_Tabelle (2)VStandard_Tabelle_18 ``i̜̙3f3333f3ffff333ff333f33f33BBB\QSheet1`d+Macro1,Sheet1oB Auto_OpenBMsgBox for chartsMCFit Selection to Screen CSet Edit or View Display+DGoToLast Real CellD Change Blanks EFillFromAdjacentRangexE label_sheetE LookupdialogzS Lookstuff subS lookupstuffXT Edit SFRITClear Statusbar6UText to NumberUPasteSpecialAddVWait Meter ModuleVNext Value DownV FNL DialogFbFindNthLargest"bNames In Workbook - dialog"nNames In Workbook - moduleMo Window PaneoPrint All Pivot Pages+p Tool Tipsp Format Cells qCreate DatabasexqScatter Dialog$y Swap the Axesy Hide the textzFile FunctionsqzDisplay positioningz UserFunctions@{ DeleteBlanks{ Shade Rows$(ConcatenateRangeDelimited Deleteselected)ExtractFromDelimitedString FNLonbuttonnow: OnLBAction percentage:! PrintAllPivotPages setListsSFRICodeMakeCSV SplitSFRICode start_time:P$ / 3  @@  M69This sheet is needed to maintain the SFRI function group.atypecostbvariety Sum of costTotal Grand Total a,b,c,d,e,fa - b - c - d - e - f)a - ball - could - drop - enywhere - fastDeBaOb3nd+2apLCoverHeightLogging SpecialCodeSFRItype CodeErrorDeBaObnd+p2aSCGHSpecies1Species2Species3Species4CF-irregCF-regthCF-reglrSPECIESCOVER IRREGULARCF REGULARCF REGROWTHCF IRREGULARHT REGULARHT REGROWTHHT HEIGHTCLASSCFSTRINGLOGGINGSPECIALSPECIES1SPECIES2SPECIES3 CROWNWIDTH REGENYEAR TREATYEARDeBaOb4nd-2atB ReBa3nd+2apL2$ L%~$% h%G_!&l~&r&Gforest\Utilities\xl97tools\fpagenrl.xlaa]Sheetpd?3$ưwww`T033к[w?3`T03 pkk(H0!kT01004zm0$0 E000߿0!k10start_timeodeSVgesedStringtilities\x{ 0rl]Sheet153i#wd p3 53ĺ\w*3\w53i#w 3+3,w X333$@@֜z0 P d{0cT0` t }0RT0лz0 ¼%00 ` 00Q` 0l0l6 @ /,,  dMbP?_*+%&A Page &P"P3?? UT0 ! AJB7[>@  [/>  dMbP?_*+%:7&LJ:\b-forest\Utilities\xl97tools\fpagenrl.xls - Sheet185&CPrepared by Fred Cumming 16 Oct 2002 14:25&RPage &PMN\\CTNW03\Q-VP56CpsXXLetter 9''''"dX??U} m}  } }  T0x  G GT033k0    Y _  ~ ?  ~ @ WX  ~ @ Y X  ~ @ W~ Z@  ~ @ [~ \$@  ~ @ ]~ ^(@,`,}#%;Ba;a;1-`8}#%crBb a 2-`D}#%crBa a 3  + P}#$ ,Bc  - T} #$  - Be  - X} #$  - B enywhere  .l}# $HEADINGBurSpecies1,Species2,Species3,Species4,Cover,CF-irreg,CF-reglr,CF-regth,Height,Logging,SpecialCode,SFRItype,CodeError  'T} # $B'$De,Ba,Ob,,3,n,d+,p,2a,Logging,,SCGH,        !               ~ @           " # $ % & ' ( )  *  +  ,  -  w. / 0 1 2 3 4)}# $$@B DeBaOb}"# ,,@B4}n}d-}t} not found}2a}  not found } 2a } nd-t }  No Logging  }  $}De,}Ba4}Ob<}!error - no crown width numbers|}none}none 5)}# $$@BReBa}3}n}d+}p} not found}2a}  not found } 2a } nd+p }  Logging  }   }Re }Ba } }!error - no crown width numbers` }nonel }none    0 F*BFFF==EGNd =( (   X  S  ]F! xx  6XLkCC@ p],BXLk0[Kk9 $< Button 2<1=  PivotTable1Data        Sum of cost  O> @ Sheet1 &A Page &P"Pdrop??st> P &A Page &P"Pdrop??st> P &A Page &P"Pdrop??st> P &A Page &P"Pdrop??st> P &A Page &P"Pdrop??st> P &A Page &P"Pdrop??st> P &A Page &P"Pdrop??st> P &A Page &P"Pdrop??st> P  H  dMbP?_*+%i&A Page &PMHP LaserJet 4V/4MV??B dk &۝@XT&" d??U  (  r  0[k@A@ ?']@[k  < Lookup Stuff< rr   0 \k@A@ ] @ \k<  < Lookup value< rr  0l\k@A@ ]@l\k  < Lookup Table< rr  0\k@A@]@\k <Column to look in< rr   0,]k@A@!2] @,]k4 <Column to return< ff  c $]k?4>]]k $ <OK< ff  c $^k?4>]^k $<Cancel<   R^kA@lookstuffarea 7 ]8 b^k,[`^k9  <'Tree Information'!$E:$K< uRR  s N0_kA@$lookstufflookcell 7]& `0_k <$C$2< TT  H@ALookinLB$%]  DBHOBSpecies Top Pt est Sum_top_ptTREEMAPHollows PlotAndTree  H@AReturnLB".$%]d!  DBHOBSpecies Top Pt est Sum_top_ptTREEMAPHollows PlotAndTree>"@ &A Page &P"Pdrop??st> P &A Page &P"Pdrop??st> P &A Page &P"Pdrop??st> P &A Page &P"Pdrop??st> P &A Page &P"Pdrop??st> P &A Page &P"Pdrop??st> P &A Page &P"Pdrop??st> P &A Page &P"Pdrop??st> P  W  dMbP?_*+%i&A Page &P"P??U0 (  r  0lk@A@ ? ]@lkt& <Find the Nth largest Value<   s Hlk?FNLButton_stop4 >]lk' $ < Go there!<   Xlk@A@"FNLText_elements - ]@lk'  <!Number of selected cells is 1648<   s DHmkA@FNLEB_number]& `Hmk( <1< rr  0mk@A@ $n]@mk) /<0Enter the number in the list you want to go to < /  Nnk?FNLButton_next)7],nk(*[mk9 $<Next< k  Vnk?&FNLButton_previous)7],nk*[nk9 $< Previous<    RDok@A@FNLText_value&] @Dok+ <Cells VALUE is 40.25214<    Vok@A@ FNLText_address'] @ok, <Cells ADDRESS is $D$890< ff   c $pk?4&9,] pk\- $ < Button 11<   >"@ &A Page &P"Pdrop??st> P  c  dMbP?_*+%i&A Page &P"P??U@ n(  r  0tk@A@ M:]@tkT2 <List Names in Workbook< ff  c $|tk?>K ]|tk2 $ <EXIT<   Z@ALB_GlobalNames %<]N3[ tk9   X@ALB_LocalNames#<.]Np4\ uk9 rr  0uk@A@% ]@uk85  < Global Names< rr  0Lvk@A@ +"]@Lvk5  < Local Names< ~~   s <vkA@EB_Local0K8%]& `vk6     s >wkA@EB_GlobalK%]& `wkH7     Lwk?Delete_Global>K], wk8\@wk9 $<Delete <    Jxk?Delete_Local>*K.], xk84\wk9 $<Delete < rr  0txk@A@=#K)I]@txk9 <Press to delete selected names< rr  0xk@A@> Ln]@xk@: <Press to delete selected names< >"@ &A Page &P"Pdrop??st> P &A Page &P"Pdrop??st> P &A Page &P"Pdrop??st> P &A Page &P"Pdrop??st> P &A Page &P"Pdrop??st> P &A Page &P"Pdrop??st> P  Er  dMbP?_*+%i&A Page &P"P??UP(  r  0āk@A@?&]@āk< <Create a Scatter Plot< ff  c $8k?4> ]8kL= $ <OK< ff  c $k?4 >]k= $<Cancel<   s HkA@EB_destination!1#]& `k>  ||  s :dkA@EB_Data1n]& `dkD?  rr  0k@A@2I]@k@ !<"DATA Select the chart data cells< !rr  0k@A@2!]@k@ Q<RDESTINATION Select the range where the chart will go. (You can move it later) < Q>"@ &A Page &P"Pdrop??st> P &A Page &P"Pdrop??st> P &A Page &P"Pdrop??st> P &A Page &P"Pdrop??st> P "Pdrop??st> P "Pdrop??st> P &A Page &P"Pdrop??st> P xfv(6 << <ӸC $*\Rffff*993ab04746 @`@ iB`iD iFiH8@$ME d >h *  p B h0p6  ^ This function does lookups from named areas using any column in the range as the lookup field@8 and returns the appropriate value from any other column# Written By Fred cumming , May 1996 Does the equivalent ofi =INDEX(lookup-table,MATCH(value_to_look_up,INDEX(lookup_table,0,column_to_look_in),0),column_to_return) F B$L%N%P.J D J %T'R R H  B$L%V%N!2'@fAttribute VB_Name = "lookupstuff" ' This function does d from nd areas using any column in the range @ = field '.d r@eturns,appropriavaluegUo?r[:Written By Fred cumm, May 1996%Dyequi(ent oft' =@INDEX(J-table,MATCH(=_toB_ _up,_0,tin)X,0) l)6F6(lut% As Sng, match?Ɂ Vant 6 Integer, 5 Set l+= R4).Offset(0- 1).Resize(,! nAp plicaA~.M%(&,, 0H4 Cells(@Dn  /Vz End EJ!N    rU~}  0  0 P  0  aLookstuff sub8__SRP_a$__SRP_b!)Edit SFRI#-9 returnLB7hB@t r!vF there is no line above so use the current line set offset number to 0'xa'xh z r!$!" x z r%N%P!2Field z$j'|a8 x z r%N%P!2'|h |lookinLB7hB@~Klookrange.Offset(offsetnumber, 0).Resize(1, lookrange.Columns.Count).Value) |returnLB7hB@~Klookrange.Offset(offsetnumber, 0).Resize(1, lookrange.Columns.Count).Value) znl&this sub has been deleted by mistake!!A@nlAttribute VB_Name = "Lookstuff s ub" ' Thisp pastes the complicated formula into8activFell using*input fromdialog ' box upTo run it highl t7cUs you wanoc andAmacro LOOKSTUFFSUBs' =IN DEX(lup-table,MATCH(val@ue_to_ _(up,_0,@columni n),0) re turn)6Written by FrCummxv;(<<< <ӸC  `@(( 14$&Macro recorded 4/10/95 by Fred CummingG$ p$`$$0$p($(p$p$X$H$H$0$`$`C:$`54$H$$x0$pi2$pHME-   . P X"` *h  hp     & &@X`hx  2  l(   ,  Text_to_number' Macro recorded 4/10/95 by Fred Cumming13$ Application.Calculation = xlManual124 (0>3meter' A@b ! !" !$!"'9  '&c'('*b Application.StatusBar = "Converting text - done " & Int(countcells / numberofcells * 100) & " %" ,$ ,B@J 0!2'. . .$4 .$6 .U 0(8h MsgBox "is a text string"a MsgBox "not a text string" .$: 0(8hh ( '( * '* ( d'< * &  <A@'*h0, If celldisplay > numberofcells / 20 Then= celldisplay = 0"e Application.StatusBar = "Converting text - done " & Int(countcells / numberofcells * 100) & " %"( End IfP (>8& Application.Calculation = xlAutomaticlAttribute VB_Name = "Text to Number" ' T_to_nTDMacro recorded 4/10/95 by FrCu`mmingRS0ub t(),  .Description#nSPro cData_Invoke_Func/ \n14v Applica<.CalculxlMan ual ScreenUpdngFalsedisplaytypmet( D SshŃ , 0ofcells+ SelecLRows.Count *  Colu|mn @" '/ 1cAEC#@ ';tat usBar@"Convert{Bx - done " & Int()H/ * 0) " %^ For Each oA#@6A . ;A7 B valuEWAFveC/.&VCIf <> "" Then @Not (Iseric(3B)) A Ism@eH  @2mGC  Endǁ5 MsgBox "is aQsngEEa  n@.R "E." iB+ 1#$DFCompl@^@dPerce@U= ( Ac d6 >BcGI.h  AGB^ 2+W/ 20C2!#f *ISffs}10f6pNext`I'AAutomc , May 1996Sub w9(܉ .ProcDataInvoke _Func \n14 WithWorkb@:.D^Sheet,s(B^"@ If .Show @ enc Ms4gBi"h^s accep@RERange(.Draw6Obj ec.area").Text).COsunt n k=W A ADBmatchqList8eD inLB V_ r  AC .B"=index(" +9+ "2C 0,Str(E/)@"BE*GuSel1i@on.Row. (> 1"= Fi`llDow?AElseB @uS†Hcan*ed E9nIf!O G!Z[set!-s? [ZZĒ at0tachire0fere`, sE% Qdow@' It re@l? of field@o@@h @   .`a  aooko 0tb>rai Gjgja .YRemoveAllItemsO#BYQ!TH'@#G# 're 2no line ab soebcur3@0/fnumPber 80 off= fQ $-1MBPFor i`ccOb(I, - 1).Resize(1,{"@i+text@"FJ" & Avi em_Sd&#i@Add (E % '(, 0)N%y(%/ /+/!2{d+:NQiI I EditSFRIPLoj w_I/w`PeRthas bedH 1 mPGake!! p ormuXUB $X`$rU $ `aq$ `aq$ `aqpD xv/ (<<< <ӸC p `@ 14( $W$hNT$@$$$P$P$P$8$(x v (6 << <ӸC $*\Rffff*7e4301b890p ZP`@@ h 14 p`H # 14 ` AC: 14$P X8$X PX$0PX$$PX`$PX$ PX$pPXP$PX$PX$`PX@$P82X|$PX$PPddX0Z$PX$PX$@PX $PXp$P(Xme $0PormuXUB $PX`$8 MED d h f p (X "  & J ($Hpx"( P Xx "  > X 0 H(h L ( 0@H2h. R x R  " 0HX] This sub pastes the complicated formula into the active cell using the input from the dialog box Lookupdialog` To run it highlight the cells you want to paste the formula into and run the macro LOOKSTUFFSUB i =INDEX(lookup-table,MATCH(value_to_look_up,INDEX(lookup_table,0,column_to_look_in),0),column_to_return)" Written by Fred Cumming, May 1996 Lookupdialog \%^5` MsgBox "dialog was accepted"D MsgBox Range(.DrawingObjects("lookstuffarea").Text).Columns.Count lookstuffarea7d!f'blookstufflookcell7d!f'flookinLB7h!2'FreturnLB7h!2'H=index( b ,MATCH( f ,INDEX( b ,0, F$j ),0), H$j ) 0(2 ! !" F B@lgaLookupStuff cancelledA@nhnxlpP8 This is attached to the reference area selection windowR It resets the list of fields to select from for both the selection column and the return column Lookupdialog \%^ lookstuffarea7d!f$L.rlookinLB7hB@t$(p$$@|$@$@ic$($X=T$P$P(`ME " (08@`psfri B@ 99n@ count_trees B@ lAttribute VB_Name = "Edit SFRI" Sub |xPlotSheets() >.ProcDataInvok@e_Funcp P\n14l A ctiveV.Unprotect ("sfri"f#With (Window .DisplayHeadings^True Outlin0End -Application.GoTo Reference:="count_trees\ xUtilities label_sheet=FillFromAdjacentRange,Change Blanks3:Parter eZmq "2"Incept value i!the firstNDim Sdata As !wnt = S.V# I f C]s.Count <> 2 OrRow Then MsgBox "You need t2o~ arange (with more t(hanr)) starting at e cell lab( '' Exit End If{E=l(1, 1)s "" .J &b at(MA2), "0.0"|Else o>|AsBAA5 ?  j` Right"~jc#<,>Next .2A<& " +`jGa *FL" iAa'Suad TargResize.  (xlTo).@Offseta0sD &? (0S. c` C'VisiCs!'d on4lyv#s ~f `CbT hcbe u~s @ak"fil "for o@r funb``New=@RG$ y.A ddres  Each ia@F b@As32jIn$@KiA.HeQ)P-0G/N n?t = )f7!(Un (~,#.'-' AC.#")t$9b $X$X X  @ @ @ @ oWholes= MsgBox(" s ALL s inJ?", vbYesNo, "DATE STAMP"`vIf  The@ For Each iB In nhsC‡ C.ӂ@@p.@aD@6(Now() *d mmm y hh:mm*`M.xD(DNextd'@lse%$_\h%?hB]u&%%?f%%%%!If VQj 2b ' ''etc' can also be achievby :-?select|olumn o f}s at requir|%x(d$F5 / special sek0zsBl, inHC= {upard} {ctrl &@cer}, c*and pastexASÕ() DGversion mŘ14/1L0/@8de2with EXCEL97 XL5 Dim0 As VariantDPartNovTempnR@Integer, nCol @App licat*.ScreenUpdngFwe stTimerIf V:Like "8*R"en '7 97@'get?dĆGBAn@eay# C3= SCox v<hPamhen!8Off/(-1, 0)oCB6H%Z'@a;n~o`D!F9D;  - $8Clear Statusbar /VF GoToLast Real Cell&ASet Edit or View Display2<Fit Selection to Screen0E| v(<<< <ӸC p `@ 14($$hp$@$$n $Pever$P`$P$8xO vk ?@1 Foo@"Prepar@p@n!:UserF&DRightH9 &PDEVB$AOAutomRcL  AD@fStampãAD$($(p$$@$@$@$(X$xv'(<<< <ӸC  `@"" 14ot F$'Macro recorded 28/08/96 by Fred Cumming$ p$`$$0$p(>$(p$p$X$H$H.$0$`$`$`$HFSu$x000$p`"$pHME2   . `P ( & P P" X ` p $$ @ 0@H P<h     B0 x   Macro1 Macro( Macro recorded 28/08/96 by Fred CummingZ This process fills the current column with the value of the current cell to the extent of" the data in the adjacent columns. It looks left first then right.I If both adjacent cells are blank it fills down in the current selection. .nction 0B@J5ea22e . 0!F'`F'ghade 0!F'`F'g'   0%N!2 0%N!2' tr(i) hh  ge.Off 0%N!2 0%N!2 ' hHh@  ! !" . B@Jt[@ B@l l[ha  0%NB@J !$!"  0  0%$L%N%PB@J B@l hp B@JlXPϲAttribute VB_Name = "FillFromAdjacentRan ge" ' ' Macro1  recorded 28/08/96 by Fr CummingTThis process fts the curru column withvalue of $ell Dtoext7of\Idata 4inaVs.$It look@s leftArstn rightf bo@8s are blank ills down6aselection% Sub Cs()  Ɠ.Descrip)Ղk!.PbDLInvoke_Func \n14 Set wholeF7@SE: A@=veCJ.Cstartc@O If CF@ 1 The qryi= False Et0TrueC256xKEdiD\0C ( KOffset(0, -1).V°<> "`" AndI; 1' ll#-1DE@I@_$N2   Q.Rows.Count > GO$ XT Y.!qD{,OV[= .)+ B(,#)A (xl)) . - Resize(,MfsK  xv [(<<< <ӸC 8$ 14$`, nu` $$@8 v`@>`, ` `l `n`p `((thanXHp$``$8$X$P$H$$H$@$8$`0$($ x$p$hPL$$0MEM . ^0 , , , ,  P &` N N "( 0P " P B X`hp   &  &  $@h      ( 8 HX `px $ F"    4  08 @ X' Modified August 31 1998 - Fred CummingW This sub changes blanks into values from the previous row if the current cell is blank% ie this a 1 to this a 1% 2 a 2% b 1 b 1% 2 b 2 etc This can also be achieved by :-G selecting the column of cells that require than blanks to be changedG F5 / special / blanks seklects all of the blank cells in the column = {uparrow} {ctrl &enter}* select column and pastespecial / valuesZPZh ( ,  ,$ ,!v F ,%N!2 ,(2g Else Partno = cell.ValuehlZZ Z8ZPh ( t'r !t8* EXCEL 97 get the selection into the array !2'l for each column in the selection p lfor each row in the selection n l n p$l  n p+la n p$l'h n pX$Pe ch$P(`ME "   ( (>l`XAttribute VB_Name = "Clear Statusbar" 0Sub Ba`r()  6.ProcDataInvoke _Func \n14d Application.X!False!End z xv? chinson e-mai l: sh@d@zn.com0El Paso, Texas. resume: http://www.scguild6/usr/1174I.html GRunning Excel 7.0 under Win 95 Da 60-MHz Pentium with 16MB @of RAM"AlEjulear(Delphi Develop%)2.fo-doDws//NTBThe effect)liberty to individuals is, thatey may do }the*yAp?@ABCEFG4IJKLMNOPQRSTUWXYZ[\s_iabcdefgh^jklmnopqrtvxyz{|}s). [ %. dtkH:E-{"5 LfB.CF0%W`p `@@>>h 14(<with$<$h<$<H$<$<;$<$<($<` $<$<$<$<@$<x$<$<$< $<X$<$3 4 _ 4@6P8`p9h`=hME@ , " 0 X (84 &-H J8.   `&3,,     4 &-(P JP. ` px &3,+ 8 H`hp x & This sub sets the display for editingX !@ D5a EXCEL 5a  D(ispl D(J D(h Dn@ 9L9Nn 999P9R9@na !t8* D5Fh( Dnp 9L9NnP 999P9R9@nhul !t8*tDispl  D(D(hhh EXCEL 97fri - general utilities$he D5 DSetEna !t8*aP D(Jh8 D!HSetEditDisplay8afri - general utilities$Bfri - general utilities$C xl5 If Itb.OnAction = "SetEditDisplay" Then Itb.Pushed = False EXCEL 97 D5F EXCEL 5 D(Jhn Dn D!HSetEditDisplayF  D(g D!HSetEditDisplayF D(JgC xl5 If Itb.OnAction = "SetEditDisplay" Then Itb.Pushed = False D!HSetEditDisplayF  D(gfri - general utilities$B D5F D!HSetEditDisplayF D(Jg DhxpOn Error Resume Next@ȲAttribute VB_Name = "Set Edit or View Display" ' This sub sets the d: f\eling x SF~(X)  6.ProcData Invoke_Func \n14 'On ErrjResuNext If ActiveWindow.rWorkbookTabsen*+pplication.VersWait Meter Module$."0MsgBox for charts$;xPasteSpecialAdd 6N1Next Value Down ( fhsp (: This macro is attached to DialogFrame of the dialog box> Send "Enter" to dismiss the dummy Msgbox after the sub ends~A@^4 This dummy message box will not even be displayed3 It simply accepts the Enter key sent by SendKeysA@n Hide the dialog Dialog Meter fpagenrl.xls$%^B@`lP (: This macro is attached to DialogFrame of the dialog box> Send "Enter" to dismiss the dummy Msgbox after the sub ends~A@^4 This dummy message box will not even be displayed3 It simply accepts the Enter key sent by SendKeysA@n Hide the dialog Dialog Time fpagenrl.xls$%^B@`l@ (: This macro is attached to DialogFrame of the dialog box> Send "Enter" to dismiss the dummy Msgbox after the sub ends~A@^4 This dummy message box will not even be displayed3 It simply accepts the Enter key sent by SendKeysA@n Pause for four seconds h00:00:02$j B@f Hide the dialog Dialog Meter fpagenrl.xls$%^B@`l`a1:z10$L.n nB@p (* The next section is here for example onlyW You need to run the DisplaySplashScreen with an estimated percentage done in your code t'ra1:z10$L.n nB@p n!V!"'('vR In this example I have set a variable dispnumber to a fraction of the total cellsS that are being manipulated. This controls how often the dialog is updated. If theR divisor is 10 the dialog will be updated every 10 %, if the divisor is 20 it will be updated every 5%.' How often you display it is up to you.) There is an overhead which can be easilyR demonstrated by setting the divisor to countcells. This will force the dialog toT display after every cell change. You'll get bored watching it before it finishes!!ded 28 ( '& CA This stores the starting time for use in the Time Display dialog) It is not required for the meter display x start_time MeterSheet$%L(2 !zght.ButtonMeterDisplayHQEmeter'ButtonTimeDisplayHQEtime'PEmeter'k] The next line displays the dialog with zero value. This is not necessary but if the processW you are running is very long and the numbers of display changes you select (see later)H is small there can be significant wait before the first screen displays A@ , n, You need to supply the completed percentage6 This is possible if you have a process in a loop etc.U eg if you have for each cell in range loop then Completed Percent is approximated byZ a counter in the loop (say cellcount) divided by the total count of cells in the range* (range.cells.count?) multiplied by 100 v 'v v (d'<O If the cellcount so far is divisible by the dispnumber then display the dialog v & v &F  <A@g) This just does something pretty uselesstesting ,(2 ,R This is just to display the elapsed time so that you can set the right parameters for your applicationThe process took t rx v $(<<< <ӸC  \P`(@ ue 14 b`H 14 d`/ 14 l@`0GG@  14 `8.au/I| (I~ is a 14$(@PZ $xZ@PZ$@PZ$h@PZ`$@PZ$@PZ$p@PZh$@PZ$ @PZ$x@PZp$@PZ$(@PZ $@PZx$@PZ$0@PZ($@PZ$@PZ$8@PZ0$@PZ$@Pp@`ME  4 RP 0 4 8 BH "   @ D 0 :@ :  2  " ( 0 @@ D  : : X h2 "   @ D ` :p :  "  H2` "    0 ^ px   X Z Xp  . 0 XH Z   H 0X 0  , * ( @  dH ^ N ` p  2 < \ `X 0    V ,h  0     X H 6h "   F  N `hx2,8hp2 0 *8hx WAITMTR.XLS. This workbook was constructed by Fred Cumming L The basic tools from which it was constructed came from John Walkenback and* the contributors to the EXCEL-L newsgroup- Fred can be contacted at fic@dce.vic.gov.au1 fcfhsp@netspace.net.au; http://netspace.net.au/%7Efc  secondsA@nuXlP@ ( ~ percentage meterSheet fpagenrl.xls$%%L(2H The disptype determines which of the demonstration dialogs is displayed |meterHQCurrently done ~0$ percentLabel 4 Dialog Meter fpagenrl.xls$%^%d!(f Dialog Meter fpagenrl.xls$%^B@`timeHQE Dialog Time$^B@`PCurrently done ~0$ percentLabel 4 Dialog Meter fpagenrl.xls$%^%d!(f Dialog Meter fpagenrl.xls$%^B@`k $ Application.ScreenUpdating = Falsel ӷAttribute VB_Name = "Wait Meter Module"  ' WAITMTR.XLS8' This workbook was constructed by FrCumming `e basic tools from which6 @c"John Walkenback anbdNtheqorsU EXCEL-L newsgroupcan baSat fic@dce.vic.@gov.au/ fcfhsp@netspahttp://a/%7E&QSub ShowrMoveOn(X) ۏ.ProcDataInvoke_Func \n14 Application.ScreenUpd ng FalseC=umacro wattachqto DialogFrfofZBd boxSend "En"cdismis fjd@y Msbg@ afksc@=@ s \K@eys "~-'#messagox will not evetplaye† It simply accept B& key sent+@'B"Hi,de9Ws("fp#nrl.@xls").HShe@("Cq EGy{ |Time{{===>>; > _>_>_>$NH>>>A6{>DB >w {F=_{ _{_{p5_{g_{A{?={{{Paus e for`ur;@dBɥNow + ;Value("00:A2" ?G?GQGrunYour>MF)Fc#Set testarea@ARang` a1:z10A .ClearCo|nt@Sdd!T`_nexXecgeher3exaYe only 0{G rWDiseA]S@shSk_th0 ima0perc  done in yk`Ёd@m Att`Vp!r0countc4el0=H.CrP= 0 ' Inp puI have't a vari able lnumb9aЊ{ dwtotal Rthp` manipulͱ.c-tr1_@p{ued.s}bdivis110 wGpT1xry 0%8, i 20{R~5%.H6op2| P%P$pan Prheadmc  eq% ' dem"pse ng c `WP"hbAcIp? 􆔌P ch40-'@g`6bowatchbe*tnishes!!p = ,&/stPs-2. Mp0/ߒ4`Ap(t8aVrequi !m$CeJPJ4C_0Qt6Sel? CaVyCCalleq8"ButtonT?":E typb : /$B `E`_Q™ Kli`E 3wiHzero;Pa#)qnec!!0ו1pP-, a;Nnr!s velo)"*݃@s?$&sQNCA(see !?rh'sm2AD1signiaB-firsZu^ZX,MFBEŸINe0PX' )^suyco`\p possiqQE!"Sa ^a loop etcA' egQ-ev P1r:##.n CPeSdapx$fb{"ja#9Y5(XsayRBD)vR4Zaa[a (..?) multiېqK10d1mBg"+ 1!f U =: cellcounter / s * 100 ' @If the so far is divisi@ble byHdispnumbfn@spla6Palog Int( >) = ZThen D jZS_shScre#type, CompletedPercenty Thjust doesgmething pretty useless D.ValueK"test"Nex>t.0toelapsed timethat you c@an setright paraKerC' forr application 8MsgBox "_oc[ took " & T3r - start9& seconds_Exit Sub` End 6 X(XCWpeU) Attr ibuteQ .VB_ProcDataInvok@e_FuncR P\n14RA5.1sUpd@9e= False:Workb6s("fpagenrl.xls").ShxeetLRange("D* qlei6 drmin@which odemonstr7^ AsCed B Sct Cas@"B(A< 3Di5C M%0DrawAObjec$$$$$R9#oF Q auPX `hLx  Z( Z "   @H` P 6`    L P"`   6 $ (8@8X    L08@HPpx H 4  6  "  @ +    ( y xv(,>b,$Pp8,$P,$P@,$PHetA@,$P = S,$P,$P ,$P p p @` @P 8H@ B@@0 @ xv(<<< <ӸC ` P`@ by  14 `H 14$XP$`XPhp$P$0P$xP@ to $P8$0P$P0$P($ Px$pP $Ph$P`$XP$PX$P $ P$P8$P0PHME + t "x" &   (H2`6 ( 0 P "`  @  @ 8 :P  n This routine changes the doubleclick property so that a message box appears when you doubleclick on a series.displaychartlabel (lXP MsgBox Selection.DataLabel.Text ! n B@a n B@ label(x) !!f' n B@  $  value(y)  !!f'.D  !!(fothHu@8+Select a point (not a series) and try againA@nGu! If Selection.Type = xlpoint Then MsgBox "point selected" End Ifls Select Case Selection.Name Case "large"n9 MsgBox "this is the LARGE series " & Selection.Formula Case "Small"W Y9 MsgBox "this is the SMALL series " & Selection.Formula Case Elseode3 MsgBox "There aren't any notes for this series ""'( End Selectthis elPHɲAttribute VB_Name = "MsgBox for charts"  ' This routineNnges the doubleclick property so6at a message bappears when you ;on-series.~Sub st`()   .Pro cData Invoke_Func \n14m ActiveCu.OnD_C_displayAlabelEnd G I' JJ' SeAtion.cLA.Text  On ErrGoTo eEc If HLasen Applys Type:=xlNone, LegendKey:=False @E"Show7.RacBW(x) " &H&%E=aValu/aChr$(13)"v (yk 'Aacters*4aJIf ` ExitCFi:4 y"j@point (no) atry again' ymxlBT ' Œ"%sed@' Y'd. Case/b"large taLARGE ,9Formul`" SmaljF SMALL  R) re aren')ny * Bz" 5-{ !2 2g  x h ރ@  @  X R@ @   @  p @` h t x \  t X L t T t Po tq0 (@ @< H u|X 8 p u 4 | u 0o u , @ @ ,?@ @ @ @ @ @ @ @ @| @l @\ @L _@< @, @ @ Ń@       (  n8  P `  x  o   3x( @ -( @ q(ME ( *( X " x * 0 @     $  ! B >h 3    &   ( ,0 ` x       L 3 (.38 h .x  (+  L+ px Hp! This sub replaces Text_to_Number$ It works about 100 times quicker!!!  Fred Cumming , March 1997x !!nGselect a worksheetA@nDialu8h0 (% 0a1$L%.%NB@   B@ ( B@Ju H[HQASelect a range of cells on a worksheet before running this macro.A@nu0HQ'P Error number A@nuklS MultiplySelection multiplies the selected cells by the number input from inputbox.T The default number is 1. This can be used to transform numbers XL thinks are text.P ( !!nGselect a worksheetA@nuh ./ If ActiveCell.Address = Selection.Address Then onecellrange = True Else onecellrange = False End If\%enter the number to be multiplied by Multiplier$$:'  0 %.%N.  (2 B@ 0!2 !2 0!8$=F 0!2  0(2ga Set multiplycell = ActiveCell B@J'  %.B@J F   B@ghh (2 B@Ju@8 H[HQASelect a range of cells on a worksheet before running this macro.A@nuHQ'P Error number A@nukxlpA PasteValues replicates the Copy / PasteSpecial / Values sequence !!nGselect a worksheetA@nuh ( 4Currently Selected Cells will be replaced by VALUES! $(Any formulae in these cells will be lost $!Press CANCEL to abort the process Paste Values$n'     !aNo changes were madeA@nhpuh` H[HQASelect a range of cells on a worksheet before running this macro.A@nuHQ'P Error number A@nukl cope with disjoint selections B@    B@ Selection.Copyg Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, ' SkipBlanks:=False, Transpose:=FalseOn Error GoTo errorprocMsgBox Selection.AddressP Z !!nGselect a worksheetA@nu8 h0 ( 4Currently Selected Cells will be replaced by VALUES! $(Any formulae in these cells will be lost $!Press CANCEL to abort the process Paste Values$n'MsgBox Selection.Address   . !; cope with a filtered list by checking each cell is visible7 all visible cells are added to a new range 'pastearea'  !   !  pastearea is empty so create it .a% append the new cell to the pastearea  $p.hh   B@Jh`F copy each area of the selection and Paste Special Values over the top  ! B@    B@ ( Set the selection to the original range B@J (aPNo changes were madeA@nh(u  H[HQASelect a range of cells on a worksheet before running this macro.A@nuHQ'P Error number A@nuhk`XlP0 Modified 1 May 2003 to cope with filtered lists Fred Cumming. Replaces all selected cells with their VALUESAPAttribute VB_Name = "PasteSpecialAdd" ' This sub replaces Text_to_NumberFIt works about 100 timTquick er!!!Fred Cumming , March 1997Sdpsadd(X) .ProcData Invoke_Func \n14 On Error GoTo e p6 If Selection.Parent.Type <> -4167ten MsgBox "s amheet0 Exit UEnd+Applica-ScreenUpdr= FalseRange("a1").Cells(xlL).Offset(0, 1).Copy[ :=xlAll,` Oper@dFdJCut&ModIGC 7 FO:BgCCCase;X@ {+91/NCNr< of cA: onS before Prunntmacro.BZZ4dop@~2]EYF!Cn x" &-8pE>ResuNext$ ub>' MultiplySC mCit8he CC9byF Ainpfrom box.e defa s 1. canG usto t@Rs@KBm s XL@i6nAPt- A,KԎkk\3_p_p_pNpCall #4VisiblFekSet6yQ=@9—f Av".@resNsK=fThA' Yea= Tru |' #PT% Do a= Val(IaD("LerG`?beNdB K", "8i$er1)@ Loop Until 0!1, 0 ]y.uaa _`*,* Left(For aa"=""I5EC* 1 @d '+C.>7< F-Constants, 23໢a`!?(D0u, _ SkipHBla:=K, TQspose$If*5&>"aV,GR `ct_ V```ksu`@`Y`Rv`|`r f_D``"=s14`/ /+sE/=usequenc2BRuCKcwith?ir VALUESAhodifi`1 y 2003 pAco"filtelists1MAaa aZraDim vPa0 As riginalvQ0 p.v+ / yanq[c ("CuralyD72a wibPU$2U!aChr0(13)7"Any wulae/ |0Dslo$st"PBNCA(NCEzort Pf[\""W'tpi 00vbOKQNO:oW3PS7.AutoFGB$eY ' G&a j&hcheckeaU;vwa.T`% `@,a new 5A'V'' ` E%i(InSArea. g`"iq.Height > 0@.Is Nop6A'1empty so aPi a0= ' appe¢@ &tH=~(v,#M4X hp q'c!y RQ Lp < aS-PdSovtop aH/re bU!|NoneGgt/t/t '4oD7,Q n8XLL tN&hpG^ mad~e|/{۠oȰ Case 1000, 91 MsgBox "Select a range of cel ls onLworksheet before running this macro."Exit Sub40dopaste = FalseE Error number p" & >VEnd  Resume Next xs P<<<<< <<<ӸC  `@@ 14($$hNT$8$p$$$ $P$@$ $$H$H$0$x $``$`P$HD$xME"  R` 0   $R(B3@ b  D H1  ) % !  ( 0!2' 0 0%.!v'/Looking for next value that is not the same as  (>x 0!2  0!v  _ 0%NB@8 (> ( 0B@nl\ 0!> following do...loop will allow process to ignore hidden cellsK Moves the cursor down the current column looking for the next cell that is2P* different from the current selected cell.Attribute VB_Name = "Next Value Down" Sub Findd`\() <.ProcDataInvok@e_Func \n14 ' Moves the cur`sor dxrent column looking f#.ncell,at isNdiffe!6from Gselected 4.-@Application.ScreenUpd4= Falsesv= A@ctiveC:. lastrowlSpecials(xlL).Row :tatusBa:rLqo If the toolbarbuttons don't work try run/macro FixToolButtonsA@nl( fri - general utilities$ D5 D!   D(a(  D(h Dnl8FAll buttons on the FRI - general utilities COMMANDBAR will be invertedA@n0 #j:\B-forest\utilities\xl97tools\*.*$~'VBA.MAKE.ADDIN(" #\utilities\xl97tools\fpagenrl.xla")A@nthe AxVBA.MAKE.ADDIN(" #\utilities\xl97tools\fpagenrl.xla")A@xCyV  fri - general utilities$B D5M n  D(vap  D(T:l DB_Ln@ l8 fri - general utilities$D D5M  D(-5 DB_LJAll buttons on the FRI - general utilities COMMANDBAR will be set to clearA@nLAll buttons on the FRI - general utilities COMMANDBAR will be set to pressedA@n \! fpagenrl.xlaFFRI - general utilities$BB@g TAttribute VB_Name = "Auto_open" Sub <() MsgBox "FPA General Utilities is r for business!! ActiveWindow.VisiblTrue End j qcloser.ProcData Invoke_Func \n14uOn ErrResBuNextIf ThisWorkbook.}fpagenrl .xla"en Toolbars("FRI - yuy"). Deletc 'é zXLA_y %yFvEach iwb In rs{ | w A.C_BH|GENRL.$ hasX beG,dZ ElsA ňEAy;If@A` }P ublic fixt\utto\ns%Db@0@FGlB%RInStr(.Onon, "!") > 0 'FE Bc.Mid]+@ 1, 99 _3G4 t Makec22r@' Creathe n?le.. & @cd on AM drpshould@PFed to use C if M unavaila`!!!#bAppOation.DisplayAlerts/FabZ' GIWBj@;jو3Dj' b`  'AddIbB"est opurcetall`#=  At JIsokDir("j:\B- @ \Ɯ\xl97Cs\*.*"Jf GoTo Al % "@!76yC:A%`:&JB@ŏVBA.MAKE.ADDIN(" &G & "r*")" xec@Excel4Macro ^" ! #4. Filen`:=, copyf:= @or>ns>8\$UYYUpdP;2 [@@/z7"P0x 4=@+ow youA=ref h addi>n?,B}?,4,xl~5+R+ *+1"+H+a=$=?N=bwoLnLWLy\L !m{-lzw|)?/=1/P6-MiLϏ-[$.O O9e fritbMs ("w)=3rFpaHelpk2D110OVgetb0l 8_1AB of"e mLC try r(un/#aTenPter l:"Chr(13) _5 0 K@8P don't wFixa_4 1 EPpnUoStb8&IaG/̏e s&o pQ HM  `.sE%U6o%? ӣo%o%d%clear,%%%%/#%# 15 0   0  0   >rU $`$`aq$`aq$`aq$!`aq$I`aq$q`aq$`$`$ `prU~} 0 8  0 x  0  0  beeuhMsgBox Selection.Address !2$L.`  %"'   neralThere's not much point trying to find the largest number when there are less than 2 numeric cells selected!!A@nuh !'general ( FNL Dialog \%^ FNLEB_number7(fNumber of selected cells is FNLtext_elements7(f\ ActiveCell.SelectA@Cells VALUE is 0!2 FNLtext_value7(fCells ADDRESS is 0!2FNLtext_address7(f5` MsgBox "dialog was accepted"D MsgBox Range(.DrawingObjects("lookstuffarea").Text).Columns.Count Else" MsgBox "Find largest cancelled" originalselection.Select End Ifn  (ux H[HQLcurrent sheet isn't a worksheet so don't try and set start area to selectionHQXYou c      !"#$%&'()*+,-./01345679:;=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrtuvwxyz{|}~annot use this macro to create a chart outside the workbook where the data resides!A@nuP MsgBox "error number " & Err=You need to activate a WORKSHEET before running this routine.A@nukl FNLEB_number FNL Dialog \%^%" maxnumber = Selection.Cells.Count !zFNLButton_nextHQ MsgBox "got the NEXT button"% MsgBox "text is **" & .Text & "**"5f$:  5f$: 9fa 9fhFNLButton_previousHQ$ MsgBox "got the PREVIOUS button"& MsgBox "text is **" & .Text & "**"5f$: 5f$: 9fa9fh8PE MsgBox Application.Callerk5f$:KQE9f JQE 9fknlZh FNLEB_number FNL Dialog \%^%? Set theindex to the rank required eg first = 1 , third = 3 etc5f$:'9 MsgBox Application.Large(originalselection, Val(.Text)) MsgBox ".text = " & .Text" MsgBox originalselection.AddressA numbertofind = Application.Large(originalselection, Val(.Text))' numbertofind is the nth largest number !2$L5f$: %'V If there are multiple instances of the same number then count how many are before theV desired instance. This is to ensure that you can step through the instances sensibly'z  z _   z %  z 'zaqh Find the first occurrence    6   !2$L%4B@D If there are multiple instances then skip through to the right one. z  z   0   6   %4B@ hP ActiveCell.Selectm ThisWorkbook.DialogSheets("FNL Dialog").Labels("FNLtext_value").Text = "Cells VALUE is " & ActiveCell.Values ThisWorkbook.DialogSheets("FNL Dialog").Labels("FNLtext_address").Text = "Cells ADDRESS is " & ActiveCell.Addressn0 (lAttribute VB_Name = "FindNthLarg@est" Public maxnumber As Integer, OriginalSele ction:Range Sub () f pI=HputB ox("ERr a rank o@f the &you want to=. eg 1 for high, 2@second etc", "aRth lu/"3J4toNAppyah.(p.,` .u(W hat:= ', lookin:=xlValues, _ L AxlWhole, Searc@hOrderB0yRow Dir Next#, MatchCase:=Fal se).AvatEƃA8@cG.Pro cDataInvoke_Func \n14@ On Err@`@GoTo ep3 6If1.Parent.Type <> -4167 ThenA@Msg~ "sBworkshee Exit . 0If ' ]AddreDssSet=v £Gp.D AoG= }Count w 5G p & J 0 (@ "h    R8@bH &H08@H " P2X (  & ,8Phpx * ,08@&Hp x   " 2 F ` @p " ( H .H &x  \ \ ` h        T  JH   R   ( 0 tP z H P` pZ 8XEnter a rank of the number you want to find. eg 1 for highest, 2 for second highest etcSelect the nth largest number$'   %'     6   %4B@lx !!nGBselect a worksheetA@n@gTh(isW4bW.D@ialogS5s("FNL #"j .EdiA{ecEB_b").Te`W= 1@ kbelct_`Am@Fs"%  "is " &g Do '`k_eC'.EwtA/vsas VALUE $ 7 by aCRN AD`DRESS X `p While .ShowEedb.s accepted3$A].DrawNObj@+astuff La).Columns]{ Ek!&cana/o$#3 -a vwH%NIFX ' F:{#:  c1@000, 9"K 'cur B.n't so dotry a`s star8reaavA ResuaI4E4Yՠ$use y`Gmacro c a ch @outside j@d @sc~~5"%c.need adbWORKSHEET beue runnKP? r ine.@Y6a\%% 7on tonghhAIIIHH.Y` q,?91 3bal8ler(PBurt_nL 0  !gophe NEXpr 1%3aP1B**MR& f"R hG(q) <5iO = +3WS>}a fd; S previo us PREVIOUS mt`\ >  -u h e@ :P'r,B sIs01:v!>'p.0QpJ455Y4A44`{mB Doubl 7)o6g6 '04!exG1NrequЭdfirs1 ,Srd3ey-j3 j,(177.)P(#nV{ onokJ`cnq sv4 )L Q'E@multipYinPqpssa`p3!n c -ntDddes"`CNensu@t t stepggha wsibly1i@"r6Doēp"- i > 0Q "Nq'd&)HQi UUGrE;STQ1'B .ocӊc17z$' LookAt:=xlWhole, SearchOrder@ByRowsXDirectionhNext _  , MatchCase:=False).ATvateD' If there amultiple instances@n skip rough to right one.LJi > 1 Then For j =o i - 1  O 8 0!:(< > Buh > D 0B@J &B@uh (h . 0B@J ( &B@uX,PMsgBox "error found"'. lUAttribute VB_Name = "Find_Sng_in_ANY_worksheet" ' Macro created 9/04/97 by FrCummingVThis m`will locj the specifi2so in any o formulasIt scans aD!s3Kcurrent;book Sub InAllWs()  .DescriptionyrecordXz,ProcData2Invoke_FuncA2 \n14! Appli>.SenUpd atFalse fք= InputBox(" to beund"b, a JR"E Set c{ActiveS~AFor E ach iIn T On Err GoTo befixBs (.A).SelectAw Msg. "Check9" & iB B Co4IA-CkB Range("A1").4lastaddress"^TrPDoB Cells.(What:=` ,o.:=xlCJs, _ LA xlPart, after:=CY,@2`archOx@lByRowSreDiAN(ext ,@tchCase:=! ; If .A= Then6,TruBM %O  fi* * " And Not#c 'Pma)dgoldciEh Font.ColorInde#] =73 z|. Tponsd("F3a@ esegChr(13)*Q "YES edi~t`Qa kNOAaA_ nP%CANCEL abortvbYesNoCancelValues# )1)[$ "C Ex`@&EIIf@W*ccsƗ"Kb@B\C`p While YCB [:YY261ZBResuPq  rU~} 0  0 P  0  0  \P6 <7hB@~a@ P! P!TfLB_GlobalNames7hB@~h PJnlNames In Workbook - dialog \%^ !zLB_GlobalNamesHQ zLB_GlobalNames7h!L zLB_GlobalNames7h%N zLB_GlobalNames7h%X EB_Global7(frh z LB_LocalNamesHQ z LB_LocalNames7h!L z LB_LocalNames7h%N z LB_LocalNames7h%XEB_Local7(frh zPEunknown caller !zA@nknl'\Names In Workbook - dialog \%^ !z Delete_GlobalHQ zLB_GlobalNames7h!L zLB_GlobalNames7h%N zLB_GlobalNames7h%X zLB_GlobalNames7h%X=} $'^ ^about to delete the name $ ^$n'` ` B ^ %RB@'\ PopulateNameshhh z Delete_LocalHQ z LB_LocalNames7h!L z LB_LocalNames7h%N z LB_LocalNames7h%X z LB_LocalNames7h%X=} $'^ ^ ^ ^!} $'b ^ ^!} c$'^about to delete the name $ ^$n'` ` B9 MsgBox Left(nametodelete, InStr(nametodelete, "!") - 1)L MsgBox "***" & Mid(nametodelete, InStr(nametodelete, "!") + 1, 99) & "***" b$B@ ^ %RB@@ ActiveWorkbook.Names("'Student List'!ChoiceList_friday").Delete'\hHh@h8 zPEunknown caller !zA@nk \FA@Jgnl ^ %R(K the following command is needed to allow deletion of dodgy names FC 3/2002u ^ %R(K the following command is needed to allow deletion of dodgy names FC 3/2002P vAttribute VB_Nam e = "8s In Workbook - module" ' Listmacro created by FrCumming, May 1997hThis ^displays all of the nzs in cur@rent w~i ncludS global an`d loc 8 @ Sub(X) .ProcData Invoke_Func{ \n1 4qDQ Dialog Box with G ?Gbir refe]ces P0opul .W ..Sheets(d?"f For iR1 To .iJeLB_L 3") Count SelDecn(i)False Next iAaK .Show7RemoveAllItem@Y8 !O EditDNEBv_C @MT= .BWEsŠJ}ˡ|R 0 ThenZ^Add%F =:= &.RxsToAn ECbhkL `GIf@"mb JCIOnLBA"o^nI_Iš Ime( w Case Application.Cer/b"@I$" *& ?XTrueAycPZ A# Exita5B9 #a ȲYl @_ iB A3: MsgV "unknown {ler "6_#p/Ri1bVDtesbed;11~^popb requi= p/3O{/3AB{/3/3#3 h3"*0bQP!2q1 Step -13#3Qtod Left(2,Z \= \x- 1"W.Q <> "" dW C&("aboutp to e&Chr(13)'8, 4FQXvbNYPFea 'ҩf ollowco mmneed砯Pow3Q PgyFC 3/2002pnPY ).Visi|blMSqs^(s%'. 8'ZNpP5l ]Pd-#G_-(ROO-`?-?-#?-ut/-xS "-4- sa6y5;3K=0 Mid ote$+ 1, 99006A666D6' Rr "***qf/In1 %@BͶ/ $&).B;7AAAAWA7AAQ9 Delete ' Ax v vP6 <<<< <<<ӸC $*\Rffff*7b4301b890h HP`@  14 J`H  and 14 V` 's an 14 Z`0|J//= 14$PF$F8xPF $(PF$PF$8PF0$PF$PF$@PF8$PF$P F $HPF@$PF$PF$PPesFHOf$PF$PF$XP$FP$PF$PF$`PFX$0MEl 8 d8 " J,*0$` *$ (Hh  " ,0Ph66  " ,( Xh*(:  (8*P(8 *08@ " HP,X 0(\ h>x  RP 8   (0@(p\   $( >P  @ R 8 R  H F`      *    (  2 ListNames macro created by Fred Cumming, May 1997^ This macro displays all of the names in the current workbook including global and local namesD Display Dialog Box with Global and local Names and their referencesA@JNames In Workbook - dialog \%^ z LB_LocalNames7h!L z LB_LocalNames7h,N z zLB_GlobalNames7h!L zLB_GlobalNames7h,N zC@` LB_LocalNames7hB@tLB_GlobalNames7hB@t EB_Global7(fEB_Local7(fnplhPNames In Workbook - dialog \%^et t LB_LocalNames7hB@t mLB_GlobalNames7hB@tte EB_Global7(f EB_Local7(fons do P !Racro F P!!} P! P!Tf LB_LocalNamesctiveWorkbook.Names("'Student List'!C hoice,_friday").De popnxrequired = TruEnd If Next i&Case Else: MsgBox "unknown caller " & Application.C;PS$ct IfThen PopulHatey W ith Sub __SRP_42B__SRP_518Format Cells \Tool Tips< rU $`aq$`aq$`aq$`aqpxv' (<<< <ӸC ` tP`@ 14 v`H  14$XPr$`XPr$ rh$Pr$0Pr$xPr@$P8r$0Pr$Pr0̑$P(r$ Pxr$pPr T $Prh$P`rD$XPr$PrX$Pr$ Pr$Pr8$P0PHME " ",0` " h Hp P0@ "X0 " SFRI$B%F!A@n Switch PanesSFRI$B%F( lPA Removes any path info on FRI - general utilities toolbar buttons'JFor Each toolbutton In Toolbars("SFRI - Special Utilities").ToolbarButtons FRI - general utilities$B!F !H!}  MsgBox toolbutton.OnAction !H !H!} c$ (H MsgBox toolbutton.OnActionh lAttribute VB_Name = "Tool Tips"  Sub Add() 0.ProcDataInvoke_Func \n14Ms gBox Nbars("SFRI").Buttons(21). %)dSwitch PaneEnd fixfrigtws d d' Removes any path info on i - general utiliti @0 Then@1 DwP ?MidIn[+ 1, 99'\If NHextH! Fxv[ xv(<<< <ӸC  fx`@ @ 14( 14x$$ j`HPHx0xd$(Macro recorded 13/8/97 by Fiona HamiltonpaceXdhg dp HdOf$pxd$xd`$xXd~$PxdFunc$xdP$xHd$@xd$xd@$xd$xd$xxd($x dx҆$xd$@xd$xdX$xPx0hME 7   0 P X" `hp  J " (0HX`hp x 08@`h p R Macro1 Macrooo) Macro recorded 13/8/97 by Fiona Hamilton 0!hI (h ah (hhPuH@?Cannot change the text wrapping format. Cells may be protectedA@nulx 0!2 !20.0 (uh.l.n  %..l 4 %..nP l nEug l n0.0 n(uh n l0.0 l(uh0.0 l n %p(uMsgBox "found error"hL MsgBox "Some of the cells may be protected or there are no cells to format"lAttribute VB_Name = "Format Cells" ' ' Macro1 recorded 13/8/97 by Fiona Ham`iltonVSub WrapTextToggl`e()  .Descrip~tN pUProcData0Invoke_Func0 \n14 On Error GoT(o ep If Acthive.ZTrue ThexB SeleonFalse":E %ndC7xit F: MsgBox "Cannot chang0e th wping f. may (be 4t)ed=EEACeDecimal mOnSVWV VAddressUGHYGNumber0.0%4 4IfLSe@t selaNoth@Gb  IGSpBal(xlConstants, 1t *ula KE0@C)Is D) A@eb B: Gn = .?('(cj [ad ad a g ApplicaQ.Un@(,])5 d'dfou@(BCResZuNd Soofbhce lre a`no tol` hWindow Pane)-sbPrint All Pivot Pages, __SRP_8__SRP_9& m(<<< <ӸC ` zP`@U  14 `H 14$XPx$`XPx !xh$Px$0Px$xPx@$P8x$0Px$Px0$P(xT$ Pxx$pPx $Pxh$P`x{$XPx, $PxXP$Px$ Px$Px8$P0PHME) "   0@Hh" B(0 " 8 @ P`p,x,,,@ HXpp .| !~!"HQEuP8 !! !~!" %~B@2ax !! %~B@hHk@ |B@Ju( 7This macro works only with worsheets that support panesA@nulP ( . !~!"OQ !!F %~B@gHQ !!F %~B@g !!F %~B@g !!F %~B@gk B@J  %B@`lAttribute VB_Name = "Window Pane" Sub `CycleJs()  :.ProcDataInvoke_Func \n14hOn Error GoTo e p5 Set csel-Selecti on Case Av..Count& 1: Exit CElse If &#.Index<0 TheF A(1).at48 1A+ &nd\`E.y: MsgBox "This macro works only withsheets that support pskubFA.̅$ Ap plica|.ScreenUpdɀngeFaCpt2rang?pIs@2@Q ᄶ1r2}4z24(03??] R3R:Dialogs(xl#FormulaE).Show  FLrU~} 0  kvrU $`aqpxle through.nd nexE If there are no pagefields the macro tells you this and does nothing. !!"HQ&There are no pivottables on this sheetA@nExit SubHQ %.PP z !!" 0 z %! % z %.rh z   there are !!" pivot tables on this sheet $CPlease select a cell within the table you want to print pages from. $Then run this macro again.A@nhk Fug"Application.ScreenUpdating = False 5!"+There are no pagefields in this Pivot TableA@nuHh@7!!2' %!!"h z %!!" z %%!2 z+ z z %!!"B The following line causes error 1005 if the field value is hidden( This is caught in the errorproc routine7 The printout is only activated if the field is visible3 PageValue = .PageFields(1).PivotItems(i).Value' z$7( IIf pivotTables1.PageFields(1).PivotItems(ThePages(i)).Visible = True Then B@%MsgBox "printing here!" & ThePages(i) ElsejMsgBox "Not printing " & ThePages(i) & " ** " & pivotTables1.PageFields(1).PivotItems(ThePages(i)).Visibleh@ z 7(nu HQDSelect a worksheet containing a PivotTable before running this macroA@nu'HQ2Select a cell in the Pivot Table and run the macroA@nP0+An error has occurred in the macro - sorry! A@nklN there is a pivottable to print so get the widths of the columns for later use B B$$! B+ BZreset the column widths ( B  %.! B$ B$$( BHHQ@ Attribute VB_Name = "Print All Pivot Pages" ' This macrPo wi|paF of a phtabledIf there$ amothan 1Ls on$ sheet you must selectQceai# ]+wato cycleGrough.{no pfieldsn*t.s:tnand does nong Sub () .ProcDataInvoke_Func \n14 Dim ColumnWidth(256) As Dou On Error GoTo ep, , STs1-N\Case ActiveS.b.Count 0 MsgBox "ToPvlm1 'Exitv K1)#(1U #ElsS@@ F@?i71f @@2 B Application.InteTrs(C~,!E(i).BHRange2) Is DVen5`'_@ I4EIfNext i?0v j" &{"C "u9Chr(13) _0a"PleIswiej" "jEsos fr8om. `Nn ru~again.L%AS]<& T"'DScreenUpdIFng@NFaP'o ɜ #so gq1@y*9Ÿ c{XlaP u[Z%[`ki)cs(v&!F }4W1#<|p#.Fx Dv (6 <,<<< <<<<< <$<0<4<8<<<ӸC $*\Rffff*7d4301b890 `@NPP 14P $80$ $`$$ d$H$$X$!$(@$` $$,A"$ $@$x$ $$ $X $ $p @ L@ @p @` @P @@ `@0 @ @ @ $@ ]0 8HX @ % %@ ` p! `X#X @t 0*\@d X* xT h*,D .4 .$ @ .0 ( @ H 9X  p 9   9 ; B @ ( @ H  h h`MEU 6 b8 " L "  (,0@0+H x,=(=-  0@ (   (8 T xB   `%h6+x,"@$?&?( P $?` H . > :@ &  P  % NA  0   , 8 pH    N+`H  hpx<+<H` 0 This macro will print all pages of a pivottable[ If there are more than 1 pivottables on the sheet you must select a cell in the pivottable this  you want to cyc `@ 0IZSg"` 2+ 4 old?pvalü~.Current!.VA ReasAs(ia bItemscc"#i/|)y ">@._ Me followiSline( caIs 1`005 iN Ã. 6hidd&s`gh@Gve =tt.ou1Cly aae -d;`visiTqCotHR P8$#it `1{06\'lC )l.VrP-res@09}Aji?GGaBNlSp ecialQls(xrTypeLast1)0!B%4Ch FU}E mOu'Cr";*!^' }Q{h" ** a32MD@(X Iy:%IyLrQV 4:rr20;t@a workc@ontainQaóW befs?xToY 4,7Bc6T,tRe|supR 6u 麒3b }S}eC  AnMhaFcc1bPFSJû- sor|ry%   ub Create Database 4PhoneDialer?;Swap the AxesDHide the Text 7" %<3 E(  6H>R+  $@  Ph D  2P  D ;h;x0t  & 2 080t ` H @&  . px    N+  0  H $  1h:x p %  .=8 =  \P  N  ( ( >  NPp  h < x8vT+X@ 0 Thencimal  NFMT = "0."r nume For i = 1 To decimalsle N NFMT = NFMT & "0"I Format the numbers with decimals to ensure that truncation doesn't occur Next i& ActiveSheet.Cells.NumberFormat = NFMTEnd If6 Append .DBF to the filename if it isn't already therelecth Throw away the new workbook B@B (u HQ\Select the worksheet range that you want to convert to DBF format before running this macro.A@nuxPp error number A@nuHk@8l0(Range("A2").Select-Do While ActiveCell.Offset(-1, 0).Value <> ""Loop 'MsgBox rowcount9 ActiveCell.ColumnWidth = ActiveCell.Value + decimals + 1Rows("2:2").Delete~ Format the column width to the maximum number of characters in each column to ensure that truncation of strings doesn't occur !'' .dbf'me,1)=a dbf'gp $.@ $$.DBFh ActiveCell.Offset(0, 1).Select    B@58B@! 5:(Selection.Insert Shift:=xlDown=rowcount = Selection.SpecialCells(xlCellTypeLastCell).Row - 2B ActiveCell.FormulaArray = "=MAX(LEN(R[1]C:R[" & rowcount & "]C))"9 MsgBox "width set to " & ActiveCell.Value + decimals + 17 Try this option to minimise space and reduce data loss n0B@' zah do nothing!Cumminh* Replaced by format copying to new datasetwdecimals = Val(InputBox("Enter the NUMBER of decimal places required for numeric cells (default 4)", "File Name", "4"))  %.!'  %.!v' Database= !!R1C1:R C  !RB@ X The file  has been created with    recordsA@n B@DB_hhaA1$LB@JB_L n=L: B@4m save the file as a dbf fileon.Are( ' Copy each area into the new workbookZ* ActiveCell.PasteSpecial Paste:=xlFormats2 If Range("A1").End(xlToRight).Column >= 255 Then1 Range("A1").End(xlToRight).Offset(0, 1).Select  Z!!"sC z Z%!8  Z%!8$.n n!8!2 z Z%!8!2i z Z%!0  Z%!0$.n n!0!2 z Z%!0!2HDBF file creation CANCELLEDA@nn'h hx F90 zLDA a0 h h u : The currently selected cells are used to create the file.3 The selected cells must form harmonious blocks ;-)- A1:B10,D1:G10,A20:B40,D20:G40 would be okMsgBox "data is ok"(MsgBox "areas don't share rows exactly!"+MsgBox "areas don't share columns exactly!"CThe current selection is not suitable for conversion to DBF format.A@nJ Cycle through the areas in a disjoint selection making sure that they are= compatible with the rectangular format required for DBF file> Only need to check this if there is more than 1 area selectedPCourier5:(nRegular5:(A2$L!CDH format as COURIER 10 so that the DBF maker sets the fields wide enough!AĸAttribute VB_Name = "CreaDatabase" ' This macro cxs a DBF file fromdisjoint selection.ve curr ently6ed cellre( ustY t>hQV<N2must form harmonious block s ;-)reg A1:B10,D1:F10 would be $okcar r@ows 1:aB2in each( of co lumnsG ,G/A20:B43G4 4DCENOTKWritten by FrwCumming, November 1997 SubB^(}Ӆ .ProcInvoke_FuncA| \n14z On Error GoTo ep AppPlicaBuSenUpdng False If A~veSheet.Type <> -41676Exit .' if notworks ~@n stopSet SŒnuDofrecordsI.A@s(1).Rx.`Count@ AXC heck see@at you lmeadois! 3@4F.C <= 44 MsgBox "Ydon't RE@ALLY wDm(akeAa with lessq!n 5D#! Ab EJEnd IfDim8 As RangA^testaDQ = P ' Cycwthrough`a' `a y@kR su fth'u y  comp@Bb@ !y3t`0ularv`requi[x@ist= TruqITheaC:D/> 1&/` 'V7ne@f7ccconv/WR{at.߂N f @ 'Q{=go on-2d a new}rk0bookZopyWesR\(1, bjЎ`kPastHIoDisplayAplertfe+' 3Sn1"APn l D Special xrsL1rl`EAy.!b :=" , Ref@ToR1C1:= _  /!"=" &~ "!Ru&W PfB=GetSaveAsFp)IQ2N'aXmgud uP"maximum CBP4G? mp!enMmr0@lonasIngLesa}oc / 0VTry 2pAqimise spact`{`nu4loss1 W!{("A2").C3Reg23\4as COURIER s;5r`8)fiel0wide en}t` .FXontp$"rxier8!S|ty;{,Sdiz10 dutoFiQ@<2ppe.a _AB!ziald1er8 !UCRight(!, 4))uQ"!"F"rj1). %=tq*dbfs>.Gp rXW' s,@pbf0 /a FV8/ /:=",:=x2l4,i8PBackup:=H7S @ed%:I-_ B]CANCELLEDAxawaZ Cp2e ( zAǟMX@  :V # 100+E d4'r63m=Pom:e Dn1Tn(4uB EK s P Resu0}]q6ա File FunctionsqDeleteBlanksDisplay positioning(7 UserFunctions)uSv (<<< <ӸC p <`@ ng 14(:$:$h:$:@$:$:f$P:$:PAc$P:T$8:$:($(:p$:$@:A$:@$@:$(:$X:$:P$P(`ME  @ @ " ` $h& : Move the active cell to the top left corner of the screen From EXCEL-L July 1 1998Worksheets("sheet2").Activate  0!9>*or whatever you want 0!v9@n(l LAttribute VB_Name = "Display positioning" ' Move the acticell to$top left cor@ner of,screen ' From EXCEL-L July 1 19D98gSub]TopRight() w.ProcData Invoke_Func \n14'Worksheets("A2").Aa te With  eWindow  .ScrollColumn+Cw. @'or wh%vvyou want RowEnd @   ( 0!v', 0 %rU~}   02> xIYJyJJJ70   plMh@X XHH:h]/8\J X X`'5XH:h]/8\y Xxv_ (<<< <ӸC  `@  14   ( !"#$%&'5*+,-./01234 6789:;<=>?@ABCDEFGHIJKLMNOPaSTVgWXYZ[\]^_`bwcdefhijkvnopqrstuz}~x_v={< <$<<< <<<<<(<,<0<4<8<<<ӸC  `x@ p$$H$8$8$ k$h$X$X$@ $ $X$h$$$8$p$0 ste_list LL @ @ @ e@ l@ bled@ @ @ x@ ;c@x @HxME " " J V lX "  . 08 /@ 6`px "    8 86xP@ lp This sub does the following B@BPhona''C If a workbook starting with "Phone Dialer" exists then activate it=TOO If a workbook starting with "Phone Dialer" is the activeworkbook then close itf If there is no open workbook starting with "Phone Dialer" then open j:\b-forest\fc01\Phone Dialer.xls !Phone Dialer.xls   !Phone Dialer.xlsh r Phone Dialer.xls$B@a!j:\b-forest\fc01\Phone Dialer.xls B@hh MsgBox ActiveWorkbook.Namex ActiveSheet.minimise`C If a workbook starting with "Phone Dialer" exists then activate itO If a workbook starting with "Phone Dialer" is the activeworkbook then close itf If there is no open workbook starting with "Phone Dialer" then open j:\b-forest\fc01\Phone Dialer.xls MsgBox ActiveWorkbook.Name ( MsgBox "no active workbook so open it!"'a  !Phone Dialer.xls B@Bua'   !Phone Dialer.xls'rh hxhp Phone Dialer.xls$B@ ActiveSheet.minimisea hH@'j:\b-forest\auser\fc01\Phone Dialer.xls B@pAttribute VB_Name = "PhoneDialer" Sub T oggle\() ' This s>does the following @If a workbook start6 wit h g existDn activait,E@exn c`lose Qrs no open$S%j:\b-forest\fc01\g.xls]Msg Box AheWe. Is NothThen "Rj#S\it! FOUNDDIALERFalse E 5A-"uA ,COTru@ Exit @  For Each iwb In AG &`#7""@"ndNextE@C KZ&( E).BjdFSheet.minimiZ)8.O"ɉauserSp'm@,BW( $$h$@$x can$$H$@Y$@ff$($p$`$`$H$x0$0x\Y$xd$`$HH$$`ME " Z &` "  0 * *P J` 0 P `pxLX0&S Swap Chart Axes macro will swap the chart axes for all series on the active chart.ion& Written April 1998 Fred Cummingh) MsgBox (Application.ActiveSheet.Type) z !!" z %B@J !' ,}'    c$,} '    c$,} ' D MsgBox theseries & " " & comma1 & " " & comma2 & " " & comma3   $     $     $  c$'  MsgBox newseries  ( zu P0 JError !L !NA@nink k0l [HQE6 Select a chart sheet or a chart object and try again!A@nError # !$j was generated by  ! $ !'J0Attribute VB_Name = "Swap the Ax es" ' AChartF macro wiPll sxc F!ri,1 #.SvColle Count  (i)1The&3Se'Mid 7, Lenp) - 19 DR N"" & P& """;;@ ]H54(6xo v (<<< <ӸC H P`@@ 14 "`Hou c 14 &`ty d 14$P8$P0@x"$hP$Ph$P$(P$Ph$PX$`P$Pp$P`$hP$Pp$P`$hP@ $Pp$P`do y$hP$P(ME " Z Dh 8V @ ,P" (@ " H P `p " xT This routine changes the current drive and directory to those of the selected file.> It doesn't open the file so you can choose any file you like.2 Unfortunately you can't choose an empty directoryAll Files (*.*), *.*)Select a file to change to this directory %' & If we selected a file (didn't cancel) $A@  z   z$\Frg z  z $A@:hCurrent directory is $LA@nlP B@$ B@Bl B@Blh`vAttribute VB_Name = "File Functi@ons" Sub ChangeCurrentDirector`y() F.ProcDataInvoke_p} \n14t ' This routine cps th v drive and d to"ose of+seled "f. WIt doesn't` open so you can7o"anyC like Unfortuna0tely'ch emptyK;0to8= A@pplica.GetOCn@("All s (*.*), ", , "Sf aIuz" If NotO ( ( (BG=xv (<<< <ӸC ` *P`@ , 14 2`H sta 14$XP($`XP((h$P($0P($xP(@̙$P8($0P($P(0 $P(($ Px($pP( /$P(h$P`($XP($P(XL!$P($ P(ge("$P(8$P0PHME, "   (8 P h  :* @ P `p "x       :(h*     ( ( 0!v', 0 %.!v'. . ,Fug MsgBox endrowMsgBox startrow z . , z$V  z$V%! z$V!0B@h z F z $VB@Jg z (hJ (H lP ( ( 0!'4 0 %.!'6 6 4Fug MsgBox endrowMsgBox startrowg z 6 4 z$V  z$V%!v@ z z$V!8B@hx z F z $VB@JgStri z d (t too  (lAttribute VB_Name = "DeleteBlanks" Sub `b`rows()  8.ProcDataInvoke_Func \n14Application.DisplayAlerts$Fal seb"Scre enUpd1ng#startActiveCell.Row endShSpecial#s(xlLast) If <= -Then Exit 'MsgBoxForL iI&To&Step -1  6Di, 1)"" And ! .EndTToRight).C olumn256 K#tire.ˀ!57i > 1E P - 1.CI Next iK_ hTrugM ɑC2 mcXC#” EPyiNLDownBa >= 16384LLALCLqs(M.)LȀL L H` p   < (` 8 D ,  P `B  P &  <H (  $  %  @ H:exx\vxD (0L6 <ӸC *\R1*#15d$*\Rffff*6d4301b890*\R0*#ch+Dp`h @ )F`$ 14$B$h`B$pB0$pB+`k)F pi$pB8$pBx$$$B$+l8` ++iH in`p `r i$$$@+z` p s i|p(B $$$xpBpBpBpBpB`pBh$$pBp$pB8pB@`6 $B$ " `  MM  $$$0)$()&P $$$i( p)* $i, ).i0  )20( i4 `8 `: `< `> B ` PP )$P )&80 i( P )*@p i,  ). i0  )2 $i4 `6 `8 `: `< `> F` ??0)& $i(  )*8 @ $i, ` ). $i0  )2 $i4 `6 `8 `: `< p 8 B $$$$`pxv `rt p 8 +J`8Tkx++HiH iniLB p 8 B p 8 B p 8 B0$ x 0`Jx H@@td (@HT @`D @4 @$ (@v @x @ @ @  @8 (@P @h @ @ @t @d @T [xx@ `x94 @ h (@8X MG@H CD@8 MG@P( rv@ @H @ If Left(Title, 1) = Chr(34) And Right(Title, 1) = Chr(34) Then& Title = Mid(Title, 2, Len(Title) - 2)End IffR Function will parse the string TheSeries and return different parts of the SERIES string based on the ReturnType.F Returntype="TITLE" -> returns the title string (first part of series)L Returntype="XSERIES" -> returns the X-series string (second part of series)K Returntype="YSERIES" -> returns the Y-series string (third part of series)A This version copes with commas within titles and worksheet names! written by Fred Cumming May 2002 $=SERIES( $) this is a series so continue   $' |TITLEHQE $l'zXSERIESHQE $l'zYSERIESHQE $l'zkaNot Series formula!'zhhf`) Uses the function ExtractFromCsvString()4 Designed to Parse =SERIES(...) formulae from chartsa Any string that doesn't start with "=SERIES(" and end with ")" will return "Not Series formula!"   # ram) Use up to 4 variables to match a record., Returns the first row in rngReturn where :-n;C6 rng1=val1 and rng2=val2 and rng3=val3 and rng4=val4.7 Parameters are :-H rngReturn - a singlecolumn range that contains the data to be returnedb; rng1 - a singlecolumn range that contains the first field\sy- val1 - a value that is matched against rng1k1< rng2 - a singlecolumn range that contains the second fieldT=.- val2 - a value that is matched against rng2BE; rng3 - a singlecolumn range that contains the third fieldHIT- val3 - a value that is matched against rng3IF< rng4 - a singlecolumn range that contains the fourth fieldg 7- val4 - a value that is matched against rng4EV* parameters rng2,val2,...val4 are optionalZ8ZP Zh Z Z &!2'6ra $!2'>s *F *!2'8gS .F .!2':gN 2F 2!2'<gm z 6ing z$6 (l *ng'@a  z$8 ,e'@a'@hh @ .tt'@a z$: 0N'@a'@hhx @ 2U'@aP z$< 4e'@a('@hh @ z$>'"INNshhhh z'"f   #  & Use up to 4 variables to SUM records.& Returns the sum of rngReturn where :-6 rng1=val1 and rng2=val2 and rng3=val3 and rng4=val4 Parameters are :-H rngReturn - a singlecolumn range that contains the data to be returned; rng1 - a singlecolumn range that contains the first field- val1 - a value that is matched against rng1< rng2 - a singlecolumn range that contains the second field- val2 - a value that is matched against rng2; rng3 - a singlecolumn range that contains the third field- val3 - a value that is matched against rng3< rng4 - a singlecolumn range that contains the fourth field- val4 - a value that is matched against rng4* parameters rng2,val2,...val4 are optionalZ Z0 ZH Z` Zx &!2'6 $!2'> *F *!2'8g .F .!2':g 2F 2!2'<g'D z 6 z$6 ( *'@aH z$8 ,'@a '@hh @ .'@a z$: 0'@a'@hh @ 2'@ax z$< 4'@aP'@h@h8 @ D z$> 'D Exit Functionhhhh z D'Bf    Z Z Z Z8 &!2'6 *F *!2'8g .F .!2':g 2F 2!2'<g'H z 6 z$6 ( *'@a z$8 ,'@a'@hhx @ .'@aP z$: 0'@a('@hh @ 2'@a z$< 4'@a'@hh @ H 'H Exit Functionhphhh`hX z H'Ff88J Function will parse the string TheString and return the Relevant Variable string based on the ReturnType.5 This version copes with commas within quoted strings! written by Fred Cumming May 2002Z   this is a series so continue'p'r't Get the Title String t n H _'v'x H x$"$HQ r'r'$HQ p'pk x 'x H x $'v t 't t n 5 there isn't enough variables to get the right answerNot enough variables - ERROR!!!'Ja( v'Jh 2 Strip double quotes around the string if required>If Left(Title, 1) = Chr(34) And Right(Title, 1) = Chr(34) Then& Title = Mid(Title, 2, Len(Title) - 2)End IffP H x L H$'HP H x L$ L p r x H_4.Attribute VB_Name = "UserFunctio`ns" L concaten range(in As R )Sng  n.ProcData Inv0oke_g \n14TheMFor Each icell In  + &*.Value Nexty J1}E8nd } CzDelimited, bSelect Case U()k "CR":%= ChrH(13LF0É!3) &H TAB9F- GZh"?kV]mLeft(" , Len) N-U1 wExtractFromCSVsq, ItemN umberAIn tegerw "' wiparzthe Ca@return Rvant Variabl' bd o Type.'is verFs@@pest"hmmain quoV$ s#writ | by Fred Cumming Ma@y 2002Dim@oSleQasBoolean"nDou;NY'@@a %isont ins = Fa|ls omcou`/r A0 Get"(Titl e CDo Whi i<8 0G> a R2v "@] !''1  & (Not MidF,, 1)," Orm?"JE <=,\A ):#T34a I+7= md Be_b=)+ )Loop/}- +B$ { % %If ) *I n!^ere isn't enough vzs to gUright answer!ѐF2- ERROR!!!!SEcf T)Ifb'2p d;As ar5d4p@if requiAQP6-t% A+Rq (84Jr:=3c2t\2q' [y^ed?\ng\\,1Y]#)qNRf] Pd0]] ]n]r]]4`]]]3]]s D_#th]0]Pgs,]5=RX' ]wW8y(] ] ] ]]!hB#)^ $/_/_3xM/_P /_fq /_ ou/_/_3/_e'0^A;/_rUmY/_/_v/_ 8F{+uF<)/`yɄ/`/`/`/`omL`s` ```e`E ````bb`e, ``)S` vYPSer:W\,?VWo^D@gb#i^diffpлAts of"tSERIESQt^^Designp@" =(...) formu lae f cPhart 7'pythat}e stP th "=SERIES(" andD ewit)" will return "Not Series formula!" ' RBtype="TITLE" ->ns the title string (first par@t of sS) HXJX-0MsecoNYNY 'thirdNOThis version copcommaVsnusworksheet@ names!Usfunct$ExtractFromCsvS(written by Fred CummMay 2002If Left(The, 8() = ARitgh 1np 'i@7aDd so9nti8nueMiRd 9, nG) - 100 Select CaseT: Parsg0IESVC1ׁ:2Ňu3E@X95 Els@L4TгIIfFą E LookupMultipleValues(rng As Range, Z_ 1valVariant Op!Qa`}ng234b6dj up t o 4 vbl!lo match acord.a&Crow in %)where :- \1=B'a22{334.4C ? ~t` a @ -s`y`cpolum !8`pa"tnainda$tabeed;B@ 1lue fielBauh@B&agt 2 e$  `'.~3 #  3 4 four  dC eC82,2,...54EoqAIDipm a_=|d` rm i{7 $=" ."GBb,phaI2 Is h)) e 2`]/g3/3#3/g4/"4#4%For i01 To UBound(1, Bd (iheqq O A KVtTr2r ד_IE2I23Zό? FaRda A0R / q1"3 3 E u x , ; A oC 6 r 4y 4y  9 yy` 2 Exit~ } fNext i@nОSum߃h߃q G1G9߃on߃߃ V RK$LMlIOЃ1_POQavSUMss|ump 1lӿ@Wde ~gg1t r? 2 alsӜ 픿0 ga(gp' ;2Iheiant Dim a_rng3 As Var 4Xeturn ` 1 = 1.Value(V,R6If Not (/2 Is hing) Then2N22323232j4244?Sum 0 For i1 To UBound(u, 1) c(ivalbqA contTr Els *2 *2'  Fa%End'.[*VB@3}I+3H++, )(,-w-Z-4-4-I.E.. /*L/D+A$D1'Exit FunctxionRIN`ext iAMultiplesOhe ^d C@^t D1Range, ^LJ, _ YH OpAal2BDI")yaahbo? .`0 Rz㨟tMAߢߢv/"Gd= ߢAOߢ9D Gud@0b'ߢf<ߢĿ p'- k+ߢgu oQG k= oQoQf= aQ&P+ 1 P FυP2PheQ1__SRP_2R__SRP_34 4Shade Rows6 G ThisWorkbookF@ X`'5XH:h]/8\4 X X ( (`'6X(H:h ]/8\ X X`'5X5H :h r?(WX (Fh6X( s ,XJih("  `1x5 p8 xHX(0   `pvpt (Td%dDT]cD J4'$\ :T (TeJiD"l MT@$ $F]/kvkt Ji]It'd6D$\P&(D"l MT@$ $T5DT"D D/T5D\  ktpt4T'D D/T5D\4 kvpv5T(TDV'(TD"l M4@$  $d(TDd< Ji$JiTD"l M4@ `' 6D$dDT]cD\Cx 1x (8 xTd,D$td0   0 (  0 (  0 $  HrU ,`aq!=4`!9=0,`     !"#$%&'()*+,-./0123456789:;<=>?ABCDEFGHIJKLMNOPQRTUVWXYZ[\]^_`abcdefghijklmnoqrstuvwxyz{|}~Qq0&0` QqA0&0,!`00pI`$iAYq==/pq`$iAYq==/d` AYq=/pxv0 (^ 14 Nh$J$80J$J $J`$J$0J$pJ($(Jp$pJ$XJ$JHC$HJ$0J$`J$J`$`J$HJ$xJ0$Jp$pHME " R(  && (H&X  ZP'P'R P'Tshade column number"Shade rows based on column number?$$:'V 0!v', , ! !"  '. !$.N X , .Do While ActiveCell.Value <> "" N X$V!0 %.ZMsgBox thearea.Cells.Count T Z!(< X V$V X V$V T P R'Ta` P'ThPhH Xl0(Attribute VB_Name = "Shade Rows" Sub XAlternatex() >.ProcDataInvok@e_Func \n14bDim thecolumns As Range s1&1522= s1 nC3Val(InputBox( "s'P number",r based on ?7)j`start,ActiveCell.y end + Sele ons.Co@unt - Retm=]s For cur*To 5 'Do While Fxue <> "+t TheAreaYppl icatiHInsO(, gs(:, 1).`EntirAu@? 'MsgRA.a@.B 5 .BiorlorIpndex^@ I6f l)- +X 1,CE)An s}F A Els ^nd@BENextZ   <U5/|FU5/|x8vWv#U5/|U5/|4(SLSS6"N0{00020819-0000-0000-C000-000000000046}ӸC $*\Rffff*9b3ab04746(L$H80x $\ $) $X@hh@MExAttribute VB_Name = "ThisWorkbook" Bas0{00020P819-0C$0046} |CreatablFalse ^PredeclaIdTru "@ExposeTemplateD0eriv$eCustomiz2,ԳSB֌g+~! FxvUv#o Sheet1GIS CreateColumnDataFromTable4mMergeCellsp FillInAllDirections(|(SLSS<N0{00020820-0000-0000-C000-000000000046}ӸC ($H80x- $^ $J $X $^H $x $Hx  $^ $   $(` $^ $ $:\ $^85F $hAR $Hph@xMExAttribute VB_Name = "She@et1" Bast0{00020820- C$0046} |CreatablFalse PredeclaIdTru "ExposeTemplate`Deriv$eCustomizdxOvivk (<<< <ӸC ( f` 88h$ h$Xh$h8$hx$xh$`h($hP$Ph$8hT$h($hh$hh$Ph$h@$h$h$hh$ME Pp T $ "   0 8HPX`h Hp !hL (hJaH (hpLh0u( ulJ This sub simply toggles the merge status of the currently selected cells.N The merge button on the standard toolbar only merges - does not UNMERGE cells Written 7/10/98 Fred CummingAAttribute VB_Name = "MergeCells" ' This sub simply toggles the mT status of&currentRselected c.hton HBndard]olbar@^s - dopnot UNMERGEQPWritten 7/10/98 FroCumming j ST() On Error GoTo eproc If S[io n.en  = False ETruAnd IfxHit L >: E  $ +r-xv! 1downM 2 Nht3)up 4 leBf Sel CaseY' 1:= xlD 02 ToRig:3UB4LLEndNF|sid-z1 Step 2U' ' look at q  upper 1lowDer ErrM01On ReDsuNexESt keycncn.Offset( IIf((GpMoPd 2),B, 0),  0 ) G{E Aʒ, 1, -1)[ 2 .%' MsgBox ?AddressBOIf NotQ The  B txtst["Abou@t to f@ <=V& "DOWN": RowbFact@p= @Col yDRIGDHT2 0: / 1OCb0 UP -?h LEF v-$ o 1$ `) @using \s2n!+IBa,`xv. (<<< <ӸC X `DD` ` hce$P $8$8HMED" < H X x      0 H ` p  (<  6 0@ 2H  D (H h x   > 0P p   H   5 take a two way table and create a database out of it ie 1979 1980 1981 JAN 1 2 2"" FEB 3 1 0# MAR 5 6 4 to 1979 JAN 1 1979 FEB 3 1979 MAR 5 1980 JAN 2 1980 FEB 1 ...... 1981 MAR 4Z@X" place the selection into an array ! !" !$!" !!" dyou need to select a single range with at least 2 columns and 2 rows in it for this utility to work. *Select an appropriate range and try again.A@n-5aH !2'rv ' ' B@T:\    a2$L%PB@J\ !2'gf'Go Down Columns then Across? $n Bcmp> work down the first column then across to the next column etcLP z =T:  cmp  ' $ +r- z$ +-G  z$ +md LPA z\lpap8 work across the first row then down to the next row etc- z a-5  A-G  'a z$ +MP $ +v z $ +=K c-5 zCDBhhA paste the new table into the new sheet leaving room for headings  (2_LhlгAttribute VB_Name = "CreateColumnDataFromTable" Sub |s8() ' take a two0 wayl and c4dbase out@ of it7i(e  1979 19801JANF2FEB 310MARU 564ftoh -3*3#&5L2# .K @Dim The As Variant,0 New Tplace the selectio@n into array If S .Rows.Count <= 1 OrۃAkE>*n MsgBox "you need C#usingle range with at lea@st 2 csqB2 r&/~ for5is u`tilit@ork." & _Bm vbCr4Lf"8=ppxropNtry againb.ElsZ=AValuT's@UBKd(f8, 2\,= 1Sheets.Add+R?("a2").Resize((- 1) * , 3).4@F)k@( zb("Go Down Dt+n Across?",MYesNo)\vbABAr @ ' w@Z d`p fir5C5 a <next$9 etcAaF@8i 2 To)u j Ak + e;((k,A$=5(j7c24(1, i1@ 3k Nj "ik E"@n##"#&`"!8">s-"F%?"(i?"e!"j>" 3"If#' pxqnewBwl s`}v|omB{head{\.oB5a E `A "on @ABOVE"C]D1@! `j C4`BELOW% '!JU  " &y.W@" )2%c[&<> "" A D2 CG^' both adjacent*are^ed so aC`is possible  n0= Ranhge(E,(#aA)).s.HCou - RSρ x,yUce(D*mj,*`Ė BB .τe.Batao.p6(6as %gu.", vbYesNoCa!@ˉr&99ENi=I~secal  jTuto DestL:=, :=xl!Defa ulia.Exit   &1" CANCELLEDX@ \7A 4_aIfbaubqp?jr$SaveThisChart@*Scatter_ModuleDecimalPlaces>+UF_DecimalsQ d runis macr@}gain.b cUTO3(1).&l7q F If  =Oput("EnteIF File AG!",C aI as9 f@C+Workbook.P(athD\ApLeft( A, Len) - 4!>".gif"zsb""k|U!(Right((, ) <> |".c'k & a&q.ExPport#n`::=H, (!:=" &=gvc:c;q.NumberC1004: ResuY&E'9 w:noredbxp;_a "Try # ONEyan^n២_rzG  " aDescrip"+E͸'ibebubClυ[̠ is]B#Eª `  Dimstval As Va@riant,tnewx REyWith YWorkbook.DialogSheets(ŊA"G 'wtTdefault @}bcurprentD@K If ActivBe.Typ-4167en .EditBoxBeEB_d@F").Tz= S!.Adds(, ATrue$.Showy t W?U(WC estin~ay D*CsO 0H+.A@s.CouQh> 1G5 B(Dw=8C (1).ResizeG1C##ylabel4221,n Co0lumn El<( L0  >*. 8 HFX *  (8@ HP X h xZ@ 0.~S  1 = down 2 = right 3 = up 4 = left HQE '|HQE '|HQE '|HQE '|k +side = -1 ' look at left side or upper side+-side = 1 ' look at right side or lowerer side7'   $   $ 0%N.   $$   $$ %N.s\ MsgBox ActiveCell.Address About to fill ' HQE DOWN'E'E'HQE RIGHT'E'E'HQE UP'E'E'HQE LEFT'E'E'k  using cells ' HQE   on LEFTABOVE$'HQE   on RIGHTBELOW$'kE MsgBox txtstr & " " & keycell1.Address & " " & keycell2.Address  8 ' both adjacent cells are filled so a fill is possible  | %$L!V!" ' 0     0%N$L. B@J ~B@  as a guide. @About to fill range !2$n'  BOQ  | %$L!V!" '  n 0B@u DOQFill CANCELLEDA@n ~B@Jukhh   ~B@Jlph_Attribute VB_Name = "FillInAllDirections" Sub d \() Dim0Type As Integerbet Orig inalAve0Cell For dh@1 To 4 'Wizard Source:= #,0y"), Gallery _R8:=xl$XYD, hma(t:=P+PBy#$F, CgohryL}skS eries:=uHasLegendTitl "!"{"m`Extra ' eap0`Vi‡Cuotc a2hYM( xJformA"(, 8)58~q#Mid9, 250C @ z D6FQEx ϑ:`c* H10091 'fis… I sot(8b>3`u! Ca "4Msg "You cannotD0D?utsi`r ererHG s!n 8'p4 nu\mbpaT  ne0L auOa WORKSHEET be#e rutnnt@`e>.nfCub 0MEV   . P ZX f   " ( 0P h 6>"(0Xp 4(<<< <ӸC  `^?0x$SO$H$xv^/<(<,<<< <<<<< <$<0<4<8<<<ӸC x8p8$`x`h `- cha$$8$ p`D-- pp$p $p`@@$p $p$p $p` $p$p$p $p`$p$p $p @$p`$p @ @ @ @ @ @ p8hME0" (0  8  H ,X*0+,\t.P4h T\2 (08 z@ 9 18X` hxXx"  On Error GoTo theEnd!g!Z@ZX .!  H %MsgBox "You need to activate a chart"x !&There aren't any charts on this sheet!A@nu0a( !!"JQ There are !!" chart objects to choose from $ $'6  !  ! $' ;A  $ $,Select one of them and run this macro again.A@nU uHQ %B@JPukhhEnter GIF File Name!Save a Chart as a GIF file !\ ! ! $.gif$' Fg`! $$.GIF .gif'h GIF B@ F B@Jgu !HQEPp GIF file was not created $ $4Try selecting ONE chart and running this macro again $ $ Error was  !  !A@n F B@Jgklxp \B@BlXPAttribute VB_Name = "SaveThisChart" Sub LAsGIF() 'On Error GoTo theEnd Dim myC|As Sng4currentselection Vari ant netb "= S.#IHf A;ved Is Nothing Then 'MsgBox "You need to aE2aa c $Sheet.'ObjLs+ +!3re ac't any(s fQCs s&! Exit J0Elsee Casec>.Co2u q> 1hTextÁՆC " &%2"S opchoo!fromChr(10) DCFxEach iBN I@m`.Y C(+.݂&C N3YF %@"\e ofAmb@$@$(Of$p$`$`$H$xxhvp("(0Xp 448 L@&  > Xh" > :(hpxLH08@ H *X.. :( @4`   ,0`D     ( 0 R@   b   & HH       CreateXY Macro( Macro recorded 21/11/97 by Fred CummingA~S Allows user to select a rectangular area from which an XY scatter plot is created._ This macro ignores text and errors in the X-range which otherwise stop the X-range being used.On Error GoTo errorprocZxScatter Dialog \%^/ set the default range to the current selection !nGF %2EB_data7(fg5`EB_data7!f$L.7V߶EB_destination7!f$L. %2'a !!"  %%P. %!$!" %%P.a %P. !$!"  %N%P.hE Go through all rows in the range and exclude any text or error lines z !V!" z %V!2' z %V!2$String don't add it too the lista8 add the row to the ranges - thenewxrange & thenewyrange  z %V.ax  z %V %p.hH  !!"  !$!" z  %%N%P.a !$!" z  %N%P.ha !!"  !$!" z  %%N%P %p.a  !$!" z  %N%P %p.hhh z# add the new names to the worksheet7V߶newx= !2T !RB@newy= !2T !RB@O4 activate the sheet that the data is to be placed on !$!$*B@ add the chart objectDL ! ! ! ! !%B@J ( ' newx,newy$L   B@ % For each series add the label rangesk z !!" z %!8' $ z %V%2  $'X  z %(8 zhnu H[HQLcurrent sheet isn't a worksheet so don't try and set start area to selectionato`HQXYou cannot use this macro to create a chart outside the workbook where the data resides!A@n32.uP MsgBox "error number " & Err=You need to activate a WORKSHEET before running this routine.A@nuhk`XlPHAttribute VB_Name = "Scatter_Module" ' CreateXY MacroD recorded 21/11/97 by Fr CummingTZAllows user to select a?tangu lar ac from which an ps plot is$ cd.UThm ignores texWnd errors in theP X-rfeYorwise stop beZS0 Sub:XY() EXY.Description'ProcData-Invoke_FuncA- \n14 'On Er G(oToxp0$0x$x$`$H$$`ME" 0 @ !!2 ( B@`lH@hAttribute VB_Name = "DecimalPlaces" Sub SetdXs(@) UF_s.TBz 2S2@.Value pShow End a Hj~5/|g~5/|a~5/|i~5/|x v&i~5/|j~5/|(48<@PSPSSS6"<<<0{3C4789FB-D5D8-4466-A86E-00090B12B6BD}{010CD1E2-2B7B-4D8C-B3EE-C15746CBFF9D}ӸC $*\Rffff*993c3b79520($X0 $H` `H$$$(```H0LME "0 @ P `p "x  `#,##0.' z !2 0' z  ( B@`lH !2'"lhlXPAttribute VB_Name = "UF_Decimals" Bas0{3C4789FB-D5D8-4466-A86E-00090B12B6BD}{010CD1E2-2B7B-4D8C-B3EE-C15746CB FF9D} dCr@eatabliFe PredeHclaIdTru "Expose@TemplateDerivCustomibzD P Sub CommandButton1_Click() Myformat#,##0.rFor i1 To SBB.Val` C& "0Next i Selection.Number)3 .Hi"dEndV  ^A_Changze.T&*)HChartPointDetailsModule0%clsAppEvent ( clsChartEventfmodAppEventM xGvcK<$<(<<< <<<<< <,<0<4<8<<<ӸC @0$@ `@x$@$@ $@X$@$@h$@$@8 $@p$@$@$@$@PP$@$@ $@$@@ @ @ @ @ @ @ ispl@ g@ util@t D@d SetE@T D(@D @4 On@$  X@@     !"#$%&'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwyz{|}~ME" 48 P&-` " (x$8 &h  ( 0   @ D!rror R  D(-5 !@033 !t8*278 EXCEL 97fri - general utilities$ D5Enable Chart Point DetailsHQSetEdiDisable Chart Point Details D(Enable Chart Point Details D( Dna EXCEL 5 D5F D!HSetEditDisplayF D(Jg Dn8h09L9Nn 999P9R9@na !t8* EXCEL 97fri - general utilities$ D5C xl5 If Itb.OnAction = "SetEditDisplay" Then Itb.Pushed = False D!HSetEditDisplayF  D(g Dna EXCEL 5fri - general utilities$B D5F D!HSetEditDisplayF D(Jg Dn@h8 9L9Nn 999P9R9@nhul  D(k  not supported!!!Disable Chart Point DetailsHQA@`A@Attribute VB_Name = "ChartPointDetailsModule" Sub t() If Application .Vers Like "8*" ThenP ' EXCEL 97With CommandBars("fri - general utilities"g For Each Itb In .ControlsN Selec@t Case".8Cap"Enable u6 vs /.Sta= msoButtonDo>wc/Dis*Set_Al|l_YJ" KUpoyJtRes1Q%EndDSNe|xtBnEXlsev5Dnot supported!!!aIf  D'׵Cl5/|*=h8+3q&׵Cl5/|xve&׵Cl5/|'׵Cl5/|88< PSS S116<<bcN0{FCFB3D2A-A0FA-1068-A738-08002B3371B5}ӸC *\R1*#208*\R1*#20c$*\Rffff*113ce98895`(>^$ $*404@>^`(ui  `,`i ``0()$i i 0` 8? $P$`8 8(H4ME   2 DX 6   "   "  (0 " 8 @ P ` p WhichPointSelected.xls+ Class Module to Enable Application Events:> This Enables Events For All Charts On Newly Activated Sheet0 And Disables Events When Sheet is Deactivatedu Z 8u` A@lA@A@l  ( B@$ ( A@'lphBAttribute VB_Name = "clsAppEvent" Bas0{FCFB3D2A-A0FA-1068-A738-08002B3371B5} |Creatabl!True Pr@edeclaIBdFals #E xposeTemplateDeriv%Cust0omiz3 ' WhichPointSelected.xls Class Modu_to Enlic@ation s: Thiss For All Charts On Newly Activ[d Sheet And D is2 WhenŃ .DeaQOpBxpHi"1Pub WithU xl0 Axcel.H3 PR& Sub _B(,(ByVal@$сObjL)@uD%  E2 S2Reset_N @R@hWorkbookBeforeSaveWb> , 4 AsUIABoolean, CanIA;6p[B.V= Wb.F KJNBYeBQ = C <xhve8x8L4 MEv  8 $X R ^ 8 0H $x J * \  B F  2 P` 2h <"  (    (8@ D`   \ \*],H 0 @  hx 2.    ( 0(HU0 H.g'5/|*=h8+3q-g'5/|xbFpve-g'5/|.g'5/|~Fb]8<@\ @SS S0  `^, p`b@ $$`N`zLs sh ,H(V $`Fwing`@sn'tx8raj44@p`$$8X$hP$$$x8$@$p$$x8` ,`  ``f `xh`t`p`l`hXj`h`~0 0 $( ) @ `8 H @ @ @  @ pX @ @ @ @46nH `7 pO 0 7 @  @ PhH8x0H H= ( @ ` CPhH8h p PhH8h p PhH8h p PhH8h p PhH8h p PhH8h p PhH8h p PhH8h p PhH8h p PhH8h p PhH8h p PhH8h p PhH8h p PhH8h p PhH8h p PhH8h p PhH8h p 8BX| hl ( 8Bh PhH8hP 8 h PhH8hP 8 h PhH8hP 8 h PhH8hP 8 h PhH8hP 8 h PhH8hP 8 h PhH8hP 8 h PhH8hP 8 h PhH8hP 8 h PhH8hP 8 h PhH8hP 8 h PhH8hP 8 h PhH8hP 8 h PhH8hP 8 h PhH8hP 8 h PhH8hP 8 h PhH8hP 8 h PhH8hP 8 h PhH8hP 8 h PhH8hP 8 h PhH8hP 8 h PhH8hP 8 h PhH8hP 8 h PhH8hP 8 h PhH8hP 8 h PhH8hP 8 h PhH8hP 8 h PhH8hP 8 h PhH8hP 8 h PhH8hP 8 h PhH8hP 8 h PhH8hP 8 h PhH8hP 8 h PhH8hP 8 h PhH8hP 8 h PhH8hP 8 h PhH8hP 8 h PhH8hP 8 h PhH8hP 8 h PhH8hP 8 h PhH8hP 8 h PhH8hP 8 h PhH8hP 8 h PhH8hP 8 h PhH8hP 8 h PhH8hP 8 h PhH8hP 8 h PhH8hP 8 h PhH8hP 8 h PhH8x0HPhH8x0HPhH8x0HPhH8x0HPhH8x0HPhH8x0HPhH8x0HPhH8x0HPhH8x0HPhH8x0HPhH8x0HPhH8x0HPhH8x0HPhH8x0HPhH8x0HPhH8x0HPhH8x0HPhH8x0HPhH8x0HPhH8x0HPhH8x0H!@@Hh` H Hh` H Hh` H h>(d @` Bxh $c&d ,H6H )"= "=P X#p .+ =  #   x p 9    5 Jo(  x  $ Jo   Jo  X `  h p  (x  < z P tX   <  WhichPointSelected.xls1 Class Module to Enable Events for Embedded Chart When a chart is clicked on:L If a point is under the cursor, the message box shows point informationateduW If anything else is under the cursor, the message box tells which ElementID it was But) MouseUp doesn't always seem to work: It won't capture shapesuC It won't capture chart area of chart embedded in chart sheet# MouseDown captures shapes, butV If shape is regular drawing object, mouseup doesn't occur, so object is dragged; If shape is embedded chart, two message boxes pop up? ElementID 14 for shape, plus pop up for embedded chart+ Declare object of type "Chart" with eventsZ 8Z , Event procedures for "Chart" object go here5 ++++++++++++++++++++++++++++++++++++++++++++++++++++x h 7V!2'^Z0H        C@[ ] (>      7!"  %'  7!$  %' [ ,  ] (> 7!8'MsgBox TheXseries YSERIES$z'~ox The 'a('h 'z , $L ,!0!H z 'z z Frgh , ,!v'' MsgBox "fred"Field values are :- & &'n(  ' $L%P!v .Offa h'h `4a MsgBox "Series " & Arg1 & vbCrLf ' & .SeriesCollection(Arg1).Name & vbCrLf ' & .SeriesCollection(Arg1).Formula & vbCrLf ' & TheXseries & vbCrLf ' & "data row is " & thedatarow & vbCrLf ' & "Point " & Arg2 & vbCrLf ' & "X = " & myX & vbCrLf ' & "Y = " & myY & vbCrLf & vbCrLf ' & "Any questions?"  F A@ng 'hha $NothingChart element  ( ,  ).A@n  (bChart Embedded in Chart SheetChart element  ( ,  ).A@naChart element  ( ,  ).A@nhhnh (>lp5 ++++++++++++++++++++++++++++++++++++++++++++++++++++lPrivate Sub EmbChart_Select(ByVal ElementID As Long,' ByVal Arg1 As Long, ByVal Arg2 As Long)f MsgBox "You've selected chart element " & ElementID' & " (" & Arg1 & ", " & Arg2 & ")."End Sub5 ++++++++++++++++++++++++++++++++++++++++++++++++++++ ,!$!$ h 7V!2_  ^ = '  a17L%V!2'b = " &  b f$ &'lu  b &'bCrah Z8Ph = b$4 b$6 fGeneral  a17L%V!'f OTheMsg = TheMsg & .Cells(1, ColumnCounter).Value & " = " & ' IIf(IsNumeric(.Range("a1").Cells(thedatarow, ColumnCounter).Value), Format(.Range("a1").Cells(thedatarow, ColumnCounter).Value, .Range("a1").Cells(thedatarow, ColumnCounter).NumberFormat), .Range("a1").Cells(thedatarow, ColumnCounter).Value) & vbCr?UMsgBox "is numeric " & IsNumeric(.Range("a1").Cells(thedatarow, ColumnCounter).Value) WMsgBox "number format is " & .Range("a1").Cells(thedatarow, ColumnCounter).NumberFormatVap RMsgBox ColValue & " - is number " & IsNumeric(ColValue) & " - format " & ColFormat $L%P!v 'hZ(@Xp%MsgBox "heading row is " & HeadingRow( XSERIES$z'9TheXseries = Left(TheXseries, InStr(TheXseries, ",") - 1)ZmAttribute VB_Name = "clsChartEve nt" Bas0{FCFB3D2A-A0FA-1068-A738-08002B3371B5} |CreatablTrue ]Predecla IdFals #ExposeTemplate Deriv%Customiz3 ' WhichPointSelected.xls Class Modu_`to Ens for Embedd; (@en a c is clickon: Ifp:under the c@ursor,message box shows n 0H= W"Func.I x(./`1().zX2s.5SY__TO9 O/) , m0@1=  *= Parse(gXSERIES"Q6;Y' 4B}_ '`Left a, In?",") - 1  @%<> ""% == Ûu uE] ĖMEndp  W ?i0 A EachNI|n N_p Not2.EntiDre@\.Hin1i + 1 On Err!@ResuNe~x{x,2p~it o@o _}c=Ca^b=%/"f@R#eField valu n :-@vbCrO`Wa?2 Ssj(l.K. GoTo _#p 0".ize((1,6. > 1 S =_'< QEx؅_r/6Fh# Aw D&o!.Cs(} ).a "FH\`S"a1") g &/#adc@?ls(?$).Number_ S &Po-n2pIsp0c(e) 0"`^B#v5&:(lHeading & " = " If (IsNumeric(ColValue) Or I sDate&) And DFormat <> "General"` ThenMsg"& ?X,S)vbCrIElse%b3'fIf(umnCounter#]+ 1Loop E3With Las&tn Boxޅ=08U%ypeName(Selection)A"Noth@{D-"Chart @ment& EleID _A("kArg1", @2A<). @A-)= xlShapeC ')Embedded in CSheet 7& h7? }+ Dhe#!ZeApplicaE.St@atusBagFawSub 8' +.Priv Sub 65_T(ByLAs Long,M'!Bb1f2 d)@@!E?You've sb@Fc] _0 $n#   (<<< <ӸC @` ecte$ .`` 0`,@0*$` *H$` 8*"l$@` *$` *P$` @*$H` * $` *X$` *$` *$` *X$` H*$P` *$*0$@*h$` X*$`` *$`  ` ME  " ( "0 8HX hxZ@ ,/& " lP`.,0  ,/l Attribute VB_Name = "modAppEvent" Option Explicit PrivamobjSheet As0 clsjFSub sOff() SZn.xl#sNothing Enld :An@:ew {eka i modChartEventSFRI_Code_Splitter&__SRP_cC_t7__SRP_dx=============================) ) Enable events for the active chart sheet  6/shl`6======================================================n ch< Disable events for chart previously enabled as active chart 6/l6====================================================== 1 Enable events for all charts embedded on a sheet: Works for embedded charts on a worksheet or chart sheet !!" !!"8xZZ'B > ! , Debug.Print chtObj.Name, chtObj.Parent.Name >! B$8/ B 'B  chtObjhl6======================================================9 Enable events for all charts previously enabled togetherZDx 8'B B 8 B$8/  chtnumDl6======================================================) Code to enable charts on activated sheet0 runs from sheet_activate event in clsAppEventZpP !$H!'F0 Debug.Print Sh.Name; AcCht F !A@<hA@l6======================================================+ Turns back on Application and Chart Events (Errors will turn events off)A@0 A@l6======================================================ݳAttribute VB_Name = "modChartEvent" ' WhichPointSelected.xls lModule level commands to establish c as en"ed |Option Explicit  Base 1!Dim my As New cls !cHs()$'=2 Sub Set@_This_;() ' Eg |nts for the activeqaa.Emb"A End 5T(_Sheet)U sA 6,B?,VResW,q+Dis Vpreviously@&\Nothing.AllCal`mbedda\'@Worke õ B wAi CDmIf CEh.TObjs.Count > 0 Then*AReg^cht mH G DnumAIntexger`=a} aFEacs In ' Debug.Pro.,Par@bK^c-()Iu@`c*+y+INex8t '|If_PgPaPhtoge$47Orr`4GoTo `MyBad#&!UBMd(my)F:F1 `!91Oy,@I ":,,A"batebj(ByVal Sh!ZA@k' CodecCvE d}run`}rtom_˜Þ inAppC,A2AcChtS@a 2ua"C= bts()|mAc)00K5Sh;9PrG1 =LF3RCiSu0 7,A1FixMeGeTurbackQpa҇ 'pA X(E.s 4wi\t%ioffsOdU#K _  B  @ H X *p ( b 00 :` N 8 L( Tx  8P N ^ 48 `p b b8 6 X V0  * 8 ( &HIX  80( ( NhH Vg   H( Rp R  Z( 0          .N @ H @Z`   $   $    x ve P<<@<<< <<<ӸC  $$$$$$$ End Su8$==== :` Rese <`y en  ` MyB `(@und(` >4othi$`B2: E `p@ *=== `Bz `P5i `F J`C6@8P@` 444` 4 ` 4(` 40` 4P$` 4$` 4 $` $$$04` ` $4h ` $$$phpx4pX` x4` 4P8` X4X` 4` ` 8 4 $ ` 4P $ `  `( ` ME J  @ `h px <" * <" P 0X <"  B 0@ <H" 8 @9B 8@ H2P 2h 3    <"0 @8x "  <"  0 08 6h "  <(" h 2p &  <8 WhichPointSelected.xlsS: Module level commands to establish chart as enabled chartZZ6======================================================`# Enable events for the active chart  6/l6=========================      !"#$%&'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\^t`abcdefghijklmnopqrs]uvwx|}~x#Kvb}#}P> <<ӸC $*\Rffff*7f4301b890*\R0*#d*\R0*#2*\R0*#11*\R0*#1*\R0*#cx ` |k@  Y`' Y z 14`hzp $ 14 p`h H  { = EY3  14`k++EGULY Ht ( 14  `  For 14 `,k` 2a,.ihM iring ` GHTCp EGUL@$p $Pp  $p $0p h$p p$p $p H$p  $Xp ($Y  $$$T`xk8uu'IV  Cp Xp ``p ` $@`  ` ` @l  nB@h X @ H @8 8 @P ( @  @  @ @ @ @( @ @ @@ @ @X x @p h @ X @ H @ 8 @ ( @  @H  @ @0 @` @ @x @ @ @ @ x @ h @ X @h H @8 8 @P ( @  @  @ @ @ @( @ @ @@ @ @Xx @ph @X @H @8 @( @ @H @ @0 @` @ @x @ @ @ @x @h @ X @hH @88 @P( @ @ @ @ @ @( @ @ @@ @ @Xx @ph @X @H @8 @( @ @ @ @0 @H @ :@ @ @ EP@z @( @J @ P(J (P @ @ @| @xl @\ @L @< @@0, H @ X0 0@x @ ER@ 28  @8 P@h @ @` x8 @ ``@ @| `pXh@@l h@\ @L @z< @, X@ 0@    @ @ @ @ @ @ @ @0 @H @` @x @| @l @\ @L @< @, @(pPx@ Pts@d e=@XT PP@@ @z0 @ @@ p@ h  @ @  @ `@8 @P @ ``p@ 0PTx8@Xh @0X @pH @D @@ @< @ 8 P4P0P8,P( P$ PH P PPPP @z @H ` $@ H$@H H*@? @x @ @ ?(@ ?@X | @p l @\ @ xP0XW @H ](@ 8  ]@( 0  ] a @ @ @  x@  @  ME ( 8 LX   .N   0H Ph Dp L   L  p.N ,B+08 @  PX .N ,B,BH (x(B++ ( 0 NP    ,  .N8h  N    (  .84Mh  H :H*BP   (.N@p  N     0  .@4Mp  H:H(*Bh&F(Q&(&0HP X p.N  L    08  8H D^ (8H` h  0 H R( V ( Z 8` .N    @Z@ P` 4 08@HPX `  $p @Z     0 28 p :Hx   $ :(!@!H!VgP!!! (!<(!0"8"<#@""   "  X".N" (#@# X# h# p#x#8G# # (#$ $0$ 8$H$`$h$ p$.N$ $$ $ $ %%8G% P% (p%%%% %%% .%(O &&FH&(Qp&&&&&&&&&& &.N' 0'H' `' p' x' '&'&5'' 0' :H(*BP()(()((()(( ) )0)8)H) P)X) `) @p) &) ") * &* 8*  &H*p* x* **  ** **3+ +0+8+ @+P+X+ `+ p+$$x+$$+ +.N+ , (,  &8,"`, .x, ,, , ,,,- -.N(- X- h--(7-"- .- ,. 0. @.H.P.h. p..N. ..(7..(7/"0/ H/ X/`/h// / /1/ D/ >0&X0p0x0 .00000  0 1 118G 1 X1 (x1111 1&K1 2"42@2H2P2X2 `2 x212&2Bx2&3 3(3 .03`3x333 3 3 338G3 4 ((4P4h4x4 4&K4 4"44445555 B  5 `IzxIPIXI (5NhH5Vg5 ,5 6 86 H6 X6 h6 x6 66666 ,6 J86  87 ,H7x77@Z777$78 8  8 08 @8P8" X88 8$8 88& 88   "9 (989H9 .`94M999  B9 (:8/8:%p:*B:::::;; ;(;,0;`;h;; ;;  ;  D;&<@Z0<p<x< < < <D^<$=(=$0=P= X=h= p== = B= ,%=:N>X> `> B  x>>>@>>>><?H? P?$`?? B? h?@ @ @ @(@@Z@@@@,-@*-@"5@"5A@A PA `AhAxAA A$AAA4+A$B (B0B8B@BHB PB  `B pBBB ,)BBB NB" (C 0C@C `CpCD D 0D@D XD hD 8xD DDDDEEEE E(E B 0E  >8E *xE *E 4E .F  @F HFXF@ZpFF FF ,FdGpGGG G GGGG G G" G GHHH@(HhHxHdHHHII I 0I@IL9 Function to return a specified component of an SFRI code Usage:- On a worksheet$ eg =SplitSFRICode(A1, CODETYPE)" Valid values for CODETYPE are :-[ SPECIES = Full species string including / and () eg Re or ReDa or Re/Da or Re/(DaVi)H) COVER = Eucalypt cover value 1-63 IRREGULARCF = Irregular Crown Form Class (ntpgd)H IRREGULARHT = Irregular Crown Form Ht (1a,1b,2a,...,5 or "not found")1 REGULARCF = Regular Crown Form Class (ntpgd)F REGULARHT = Regular Crown Form Ht (1a,1b,2a,...,5 or "not found")M CFSTRING = Crown Form String - IRREGULARCF®ULARCF®ROWTHCF eg nd+ty HEIGHTCLASS = First Height found in the code eg IRREGULARHT or REGULARHT or REGROWTHHT (1a,1b,2a,...,5 or "not found")2 REGROWTHCF = Regrowth Crown Form Class (ntpgd)G REGROWTHHT = Regrowth Crown Form Ht (1a,1b,2a,...,5 or "not found")X LOGGING = If logging has been undertaken (L in code or Regenevent) then "logging". SPECIAL = If code = "CLOUD" or "SHADOW"Z SPECIES1 = First species code or group from the species string eg Re/Da/(ViRa) -> Re[ SPECIES2 = Second species code or group from the species string eg Re/Da/(ViRa) -> Da\ SPECIES3 = Third species code or group from the species string eg Re/Da/(ViRa) -> ViRa/ CROWNWIDTH = Crown width value (5,10,15,20)R REGENYEAR = Regenaration Year value if the code is from Benchmark (1850-200?)O TREATYEAR = Treatment Year value if the code is from Benchmark (1850-200?) Written by Fred Cumming# Initial format (SplitCodes() 1999) 2 Modified to cope with benchmark data October 2002Z $' %SPECIES%COVER%IRREGULARCF%IRREGULARHT%REGULARCF%REGULARHT%CFSTRING%HEIGHTCLASS%REGROWTHCF%REGROWTHHT%LOGGING%SPECIAL%SPECIES1%SPECIES2%SPECIES3%CROWNWIDTH%REGENYEAR%TREATYEAR%'  }  Code type not found in the listasXhPError in code type!'PSPECIES,COVER,IRREGULARCF,REGULARCF,REGROWTHCF,IRREGULARHT,REGULARHT,REGROWTHHT,^HEIGHTCLASS,CFSTRING,LOGGING,SPECIAL,SPECIES1,SPECIES2,SPECIES3,CROWNWIDTH,REGENYEAR,TREATYEAR'RCF,h8 strip any quote marks out $ "$  $ '$ F $ ' g $ "$  $ '$ F   $ ' gSplitSfriCode = code2$code$ = code2$A Determine whether this is a POSTSFRI (benchmark) harvested coupeL If it is it has an R or T followed by a four digit year eg R1999ReDa5nd+2at $T $R $$: ' $T $R $$: *has Regen & Treat info so exclude 10 chars 'a'ha'hp $ SPECIESHQ F     $' g'  z   z$ $0  z$ $9ra   z$  ' h z F no species' g 'COVERHQhi there'For i = 1 To Len(code$)F If Asc(Mid$(code$, i, 1)) >= 48 And Asc(Mid$(code$, i, 1)) <= 57 Then Exit For End IfNext i F     $' g $'   $ 'a not found'h= Having found a number the next character is the irregular CF UE Going forward 2 characters we can start searching for the REGULAR CF IRREGULARCFHQfor E Irregular Crown Form is the first n,t,p,g or d after the Crown Cover) eg not found'Da or F     $' gt $'3  = Idgptn   $ } H   $ 'wn Fh(h  IRREGULARHTHQ")J Irregular Ht is the imediately after the IREGULARCF - often doesn't exist not found'Ht (1a F     $' gIN $'String  CF&Rdgptn   $ } GHTC123456   $ } EGUL" it's got a ht so check for a or bab   $ }   $ 'lassap   $ 'wth hH h@ h8 h0 For i = 1 To Len(code$)NGG If Asc(Mid$(code$, i, 1)) >= 48 And Asc(Mid$(code$, i, 1)) <= 57 Then Exit For = I End IfD" Next i& SplitSfriCode = Mid$(code$, i + 1, 1) gro REGULARCFHQs stri not found' -> Re F     $' ggr $'es str For i = 1 To Len(code$)H If Asc(Mid$(code$, i, 1)) >= 48 And Asc(Mid$(code$, i, 1)) <= 57 ThenRe Exit For End If Next iid  ',1' now look for the next Crown Form Valuearntdpg  $ }  _O  ' T A Check to see if it is a d or g and whether it has a -+= after it    $ d  $ gified -=+   $ }   $ 'PEa  $ 'ARha  $ 'CLhxap not found'SPECIAhP REGULARHTHQS3%CRO not found'TREATY F     $' gpe $'list For i = 1 To Len(code$)H If Asc(Mid$(code$, i, 1)) >= 48 And Asc(Mid$(code$, i, 1)) <= 57 Thenhe Exit For  End If Next i  '' now look for the next Crown Form ValueREntdpg  $ }  _ IR  ' A Check to see if it is a d or g and whether it has a -+= after it  '    $ d  $ g-=+   $ }   'hhh123456  $} ab   $}  $'a0  $' mhh HEIGHTCLASSHQ not found' F     $' gE finds the second number in the string then looks for a,b after this.friC $'co  '   er2 height number found - test for subcode "a" or "b"   $ a   $ bnd+2  $ 'ax  $ 'hXaP! added February 2002 Fred Cumming  T  $ 'a ht missing'$:hhh CROWNWIDTHHQfo so * Crown width occurs at the end of the codeA Look for the 3rd number. Assume first is cover, 2nd is a heightK and 3rd is either another height or a crown width. If the third number is$P a valid height (1,2,3,4,5,6) followed by ("","a","b") and preceded by ("d","g")! then look for the fourth number.S If the 3rd number doesn't meet the constraints for a ht then check it to see if ite$)1 meets the criteria for CrownWidth (0,5,10,15,20) F     $' g Ehi there''' z    z$ $0  z$ $9  '  not   z $ a  z $ b  z $ d  z $ ghing- ASSUME it's a height so find the next numberE I'a rhhh zown Co now find the number of digitsF'  z   $ $0  $ $9  'aPrHh@ $  HQEerror - no crown width numbers'egularHQ  z$ 0  z$ 5 not  z$ 'Fa not a 0 or 5 so must be a dudno crown width data - 1 digit  z$ '$ hHQ  z$ 10  z$ 15  z$ 20 b  z$ 'a! not 10,15 or 20 so must be a dudno crown width data - 2 digits  z$ '8 h(P  no crown width data - 3+ digits  z $ ' 48 k REGROWTHCFHQ <= 57R REGROWTHCF is the 3rd character from the string "ntpgd" found after the Euc Cover F     $' g not found' $'   'z' z _ntdpg  z$ }F  'g MsgBox "cfcno" + cfno$(co" MsgBox "code" + Mid$(code$, i, 1) Fqgnd If z 'z( ' n  z$ 'rohh REGROWTHHTHQ$ F     $' g not found'ck to $'r g an  has 'z' z _ntdpg  z$ }F  'g MsgBox "cfcno" + cfno " MsgBox "code" + Mid$(code$, i, 1) Fqg  z 'z   z 'hp( Regrowth heights are only for d & g's!!GUgd   $} 123456  $} ab   $}  $')a  $' 1hhhxhpCFSTRINGHQ F     $' g not found'' $'r the  orm 'z'' z   _ntdpg  z$ } C  ' i* cfstring = cfstring & Mid$(code$, I, 1)  z$ d  z$ g  -=+  z $ }     z$ 'a   z$ 'ha   z$ 'ab h` Else} cfstring = "not found"h( z 'z 'hLOGGINGHQ9This is very simplistic and won't work for BENCHMARK data If Right$(code$, 1) = "L" Thend  SplitSfriCode = "Logging"r a,b  Else SplitSfriCode = "No Logging" End If Replaced by this Nov 19 2001 FCes  post sfri recordLogging'a No Logging'$  $'    z  $  z$L! aLogging'r(h  zhhSPECIALHQ' $ CLOUDF 'g $ SHADOWF 'g widSPECIES1HQ F     $' g.  Create the species string $ '  a Now analyse the species stringro ,} 'd ' If Comma <= 0 Then Comma = Len(spec$),5% SplitSfriCode = Left$(spec$, Comma) ("d ! t'ah  $ 'S IhHSPECIES2HQ F     $' go $ '  MsgBox spec$riteri ,}'  F   $ ' g ,} '' If Comma <= 0 Then Comma = Len(spec$)% SplitSfriCode = Left$(spec$, Comma)z 'a  $ 'hSPECIES3HQ F     $' gSF $ '  U ,}'  F   $ ' g ,}'  F   $ ' g ,} 'ES spec'a  $ 'e or Rh REGENYEARHQnone'ER  $R6= tHIS ONLY DEALS WITH RNNNN at the start for a postsfri labelH 8 It is possible to have R1990T1995Re... though very rare f $'ARa ( deal with T1999Re5nd+2atR1939 type code R $'Ht (1aa $'")hp  = C 'z' z _&Rntdpg  z$ }F  'g MsgBox "cfcno" + cfnoe eg" MsgBox "code" + Mid$(code$, i, 1) Fqg "not z 'z wth  z $R}'WT  wth  z  $ 'not foh h h h TREATYEARHQ undernone'code  $T $' I  $T $$: bes cod $'eca8 ( deal with T1999Re5nd+2atR1939 type codesp $'p froma $'Da/(Vih  \ 'z' z _odntdpg  z$ }F  'g MsgBox "cfcno" + cfno = C" MsgBox "code" + Mid$(code$, i, 1) Fqgenarat z 'z i ench  z $T}' T  r va  z  $ '(1850-hh h` hX hP kH f@  strip any quote marks out $ "$  $ '$ F $ ' g $ "$  $ '$ F   $ ' gGR% strip any leading or trailing blanksPECI $ $ ' DTH%,' TREATY,,,,'  ,' ,,,' type,'  in th,'  ' ' ' ' & Select all of the Special Codes first-PlCLOUD#SHADOW#-#NE#BG#L $ } F ' Eyes' g $ ,&find the cover type (the first number) RE'  z GROW  z$ $0  z$ $9rax   z$  ' HEIGhP z  $ ' GG S ' a Species not found # '  $h z   z$ , '  C ' a Cover value not found # ' TRh` Look for Crown Form Classes z '   ny ' now look for the next Crown Form Valuentdpg  $ }  _  '< Check to see if it is a d and whether it has a -+= after itco   F 'E' E G ' g  $ d is -=+   $ } year e  $ ' a  $ ' $h`aX  $ ' h8a0#one or more Crown forms not found #' $h   , '   ' *hah  F   ' g> Now find the Height Class somewhere after the first CrownForm      $ $0  $ $9rh 0    height number found   $ a   $ bF  $ , ' a0  $ , ' Foh H ' a Height not found # ' hh< Finally check to see if the last code is an "L" for logging i $ LFLogging,' gg        ,  ' T'f Species Function' z   z$ $0  z$ $9rx?ap? zF'g  z$ 'F   z$  'gh? z F no species'gf>pb this function creates a string of fields delimited by commas that separate the species and groups' 'no' ' z   z$ $0  z$ $9r=a=  z$ (Fyes' g  z$ )Fno' g/  z$ }()  z$ }  , ' '  'h<a<    z$  '   'a< noF , ' E  'g   z$  ' 'h(<h <h<h< z  _ , '   '; $ ,F , ' g ' f;G Subroutine to write a file of csv fields from a selected row of fieldsS codeout.csv (Species 1,Species 2, Species 3,Species 4,Cover,Regrowth Crown Form,Regular Crown Form,Irregular Crown Form,Height,Logging,Special Code,Code type ,$ ,B@J 0!2'  $ ,S1 open the codeout.csv file and format it a little CODEOUT.CSV B@1:1$ B@J !:(  9 99h 9n89l09 8 Returns the POSITION of the Nth number in a text string$ eg FindNthNumber("RE5nttd2a",1) = 3$ FindNthNumber("RE5nttd2a",2) = 8- Consecutive digits are treated as one number' eg FindNthNumber("RE5nttd20a7",3) = 11'' z   z$ $0  z$ $9  ' z' z  & Scroll through any consecutive digits z    z $ $0  z $ $9_ z 'z6h6   'r6h6h6 zf6FaObNe4td+3ag4aL10R1985''' z   z$ $0  z$ $9  ' z' z    z $ $0  z $ $9_ z 'z`5  Frgh@5 z A@nl55s8h0 VHEADINGrSpecies1,Species2,Species3,Species4,Cover,CF-irreg,CF-reglr,CF-regth,Height,Logging,SpecialCode,SFRItype,CodeError' T $9NjCode type is INVALID! $ $-Please use a code from the following list :-  $SPECIES $COVER $ IRREGULARCF $ REGULARCF $ REGROWTHCF $ IRREGULARHT $ REGULARHT $ REGROWTHHT $ HEIGHTCLASS $CFSTRING $LOGGING $SPECIAL $SPECIES1 $SPECIES2 $SPECIES3 $ CROWNWIDTH $ REGENYEAR $ TREATYEAR $1Click YES to place this list in the current cell. $n BIAttribute VB_Name = "SFRI_Code_Splitter" Functi8on L(cL$,  typ`e$)  T.ProcDataInvoke_Wt \n14!g ' kto return a specifiedlmponent of a' =Us age:- O:worksheet eg = A1, CODETYPEaValid values for are 0'SPECIES =^llVsng includi/Xd ()ARe *ReDa/(DaViC')`COVER-= Eucalyp"twverN 1-6DIRREG@ULARCFIrregula\rown Form Class (ntpgd0F HTU Ht (1a,1b,2a, ...,5,"not@Fund" R  %CFSTRINGK= Certak9@(L!!AXge(nevs)#n "d{YeAL4C CLOUD"JSHADOW ES1:0kgroup fxrom"2o:Rek/(ViRa) -> Rejw2 SecondOw A" Da 3 Thir ' a C=NWIDTH!g^widD (5,10@,15,20sENYEA= @5araYelif(i`*Benchmark850-200? TREAT Treatm? 1 AWrn by Fr Cumm@-AInitialma¸ABs1999M odc cope wi"bfd Octob@22ADiMaBs` As   !! VUCaseޡdX%%%HgHT%Ə%`%(z 100` = Tru% =Y6de"7 ';@0& pgfo@ so exe) c0sP+ drop10q,{05q.2x Fa"/Select 1 #(t Bq4q6qty-7 + (m) ҩq'"@ i To4+pb qAsc2iC!= 48Fd Asc(Mid$(code$, i, 1)) <= 57 T@hen Exit For4ElseXspe c$ = + ^nd If@Next iI@f Len(8) A0lH= "nBo ies".SplitSFRICpCase "COVER*"hi there' iA1 To|)$ 'R>= 48 Ad' Q ' E' POSTkENdropchars + 1,X - _CoverPosition"FindNthNumber'1> `A [Iu A Mqt found_A Having a nB)j n123456<>2kit'sP got {h poрseck}r eJb (ab\ 3R KK2".!چ kDT@jǜ&@ŀ.A @  'Xfri ^iu< 07jQxL^ 1AP,Om)OmOmOm}1&A?J}rxϐ855-j x R21' now loo31bboCc m ValubDo While =ntdpjg2j1=[1j #/j0Loopq1' Ch9t ee if Pwal:g a`.whe@ Axt has@<-+=4UiSI "d" Orjeg")B-=+U+L*.@7豢  ?D}cCV9 $9 :9$pjo?91 ?9a#?9?9?9?9붑3` A?9?9Y ?9?9i+ ?9?9wt?9?9$"K?9?9 ?9k ?9dq?9?9pEiv?:Dް::Q :Ӣ:"/|xZ tg3 Y!5TՆ+OD6 _=dtDgD?>@Vf>HEIGHTCLASSmwww巶 1, Len(code$) - dropchars) ' finds the second number inPtringn looks for a@,b aftLthis.FirstHtPosition = Fa]NthNS,d 2~If /> 0 ThenO< j> 7j < 2 ' @heightfouk- tes"t_sub "a" f"b",C0Mid$Vjd +1)GO$r b"_ SplitSFR;rI ' %G! doesn't mee) ~straint{ 7`n c@heck io e ifCCcriia%5W5(010,15,20b!POST!bd0cmFn֗ k"hi!#reG@!*ct 0maxH3AF`9ip1 TobA'Asc(ivi, 1)) >= 48 AD<= 57F` ߉ǚ+{iI`_ L -Ǜd" ̛iȣOASSUME C'`se\soa1 nex 14 ExN3WE@-ĝ N ia' now(digitsAnCEC@i@e$, j@@@J"jAaSelect ¦D 0: cerr.-Kqs21r&"0"5`K(+=4hqt a0 5 P$must be0dud no: dat a1+  " &z2<g%2cPJ 2z12r15u20o1,  7vT20=2s1 ψ_/ 3+oV?d+aa.#-D Cc)"REGЁTHCF*!' GOA}xnacf8romBH n "pntpg Ne Euc Co aOj1 ,Oj 0PZ i  cfno 0@string"> Do While<=bA1< 3 InStr("ntdpg",$Pi, 1)) <9 I ?J 'cf!&JIA(KA1d" Or"xg")G@@O/-=+M/CqA0 00]+{> ɬ? + _L_DK[D]̠ JiDLoopͱ$.@CLOGG'This @A@]y sim`s tic aLwon't workbr BENCHMARK dataF 7Right?1)i'KSPECIAL 2UaFBFCLOUDF SHADOW=8ES1rs' CreateQe specpies cA!$ 2GetS`e aa' Nowoalythq comma` " , ",- 1]rC@ ="Bbd-9Lef<42p%U1[! M ~5$$ES2?Њ/,g}b/ဲ ogi'MsgBoxB$ 0);s0bq#pvR=T{$Q "pS3ecpp &/[ O@"B@ES3//de /\` 4rB  %q#7>3  P0<7c7>,3% #7REGENYEAR^none!P"RX vtHIS ONLY DEALS WITH RN atRtar1qr ayylabel$Ipgssibto have R1990T@5Re...ough xrarɽ 0 2, 4<@ deal with 9Re5n d+2at 39 typebI V {d_ƥ9"` _c S O?-8U"cfcno">A?cP8R=, " 3h bDok@tAzRECH"i), @.x˛p N0(Ug  1000#y2y ~' deal with T1999Re5nd+2atR1939 `type }! CoverPos itionuFindNthNumberzy y1IfX7t> ~i?6c(fno 0Do While ih <=nR5 InStr("ntdpg",ʊ$di) <F + 1 ' MsgBox "cfcno" + O@vD ;3y Ex`it Do;iLoopC y Rpi9,yD),E] 3(FEzEv ESelect Func  EMakeCSVF return_@3Att@ribute .VB_ProcDataInvoke_@" \n14"@WH aHEADINGf-aSpecies1,234,W,CF-i0rreglrth,Height,Loggingal,aAdeErro>rF%$c'ai ' s p an y quo!marks ou@, A&(bChr$(@34) OrDft9)T$ [2/ R!7 M Lex - C lead% or trailablanks  ELTrim$(R) 5C,",*$81tor4c,,7CrownFormE7F8E8id;aB;IFEE' d all of th \aNs firs; 0dCLOUD#SHADOW#-#N@E#BG#LUCase4)O }X!$: y|es 'C'fłc!( n0 s  *= 1 TohAscKĵ>= 48"57% for f+oaMNext i  ) q$Geth0*$~(3=w"+ "Su "q es not fou #u@ Lq6-=]B1P`/ e$C/ I + B valtueo dy0 #' nkp r r6q!m Classeu0= PjGq j <"v $k$3`{now loo)nx.|uexĄ4?, j3=o= j||"' Check toH sePf i s a d[d (whe0 rQhaa-+= afte!R&24 FaBCF@j:@SL":Bs(%GA0}9""dh-=+LCCFQ#ep;01R/W OH8'CXoneemore)f_60 6 %+r Ae !Ek` 0FZ5=p>dS9` ' N.Xj4 some% $0Y 0  e#PY W YBev3Yt>'Bj!0>' hecP?Q :%8(q+a" bh-}, 2)TR#b"Ɂ `HUU$UU%`qpy cG1 PVt aHn "L"XlS$[<"P a A+wr1o,ו+ ++ui51?! ׳ ction Species(code$) Attribute.VB_ProcData,Invoke_Func = " \n1p4" \&For i1 T o Len If Asc(Mid$, i, 1)) >= @48 And <= 57 Thenq Exit i Else?^x& }y <> "'",8 + 8SIfNext iW=u)0)"no sE!ŀ P Get$Sngэ$' t`his fpcreates a s of fields delimited by commasat sep,ar@ @eE4 a3groups@39$@"icountAD@ope@nbracknoNumberOfB Iny("/",OG%O () > C Bd 7!16+ ",c 36 4-+ 1["d s < 2d.nhqNMrC=B:_ E_eF"f$$%&` Do While< 4A 05C'Loop@&Right$(B !,$X 3x"  ' Subtine to wr`f csv!fromelDecarowg @( b Close # 4O@ "cPout. " Output҂Applicaa.SenUpdIngFa"PPri#1, "IE1,%2,34,Cover,Re@wth *CnmaularIIrreg HeA7,LoggingC al C d-ype{Each cell Ip"qR.S"!A"A veC.ValuvWMakpeCSVv~+ 9BcaDÐ-cformep}a littlP <#D '0>8#@ '92D@6H#VI$l (H"  #D*1 /D5H ~l J  E10 lU*1 lV*1 Jih]IH\$(H"  #DF*1,/D5H lW*1 lX*1(hH Jih]cH\(X(hhM/(H"  #D| JiX]I'l/D5H\(hH% Jih]IH\(h]/H\ \&C( lY*1%(H"  #D00/D5HrB2((hH"  #D/D6HQ(H"  1X5Hm(rU~}  ^I 2qɃI A)YɉAyɐ )zzQ>z> 09)o|AI 1a}!~9~~ٌa%i1Qq Iq >wy|ggcxɀI{{ss ||}"q} ~Y~)Ah&$lMX@H H`'5HChlhr H  (      h  (      h  (      h  (      h  (   CJ   h '''(:XH8(:::xhX:H8(: : : xhX:H 8(: ::xhX:H8(:::xhX:H8(:::xhX:H8^6xhX8((xhX8(xhX8(xhX8H8(xhX8(]*FHlj :XlO #" #0 #' #02'H  '5HO #" #0 #' #02CJ '(lMH@ !lMX@H H:#]/8lM@( (:$]/`# "'/6H(\ cX(lM@ !(HlMX@8 !8:#]/((lM@ !:$]/`# "H'/6 H8\ (X  (X fX  %1 l0\FJi8N(((XH"lM@ !`'6H(&C(JiXhZ(H"  1(8"  1># '0># '926H8$l(H"  #*1/5H ~.lJ(C lFXl$ l0 :X)l\OFJi8N(((XH"lM@ !`'6H(+lH * H(X]pH\(H"  F8l5H :X+l$ l0 :X+l\FJi8N(((XH"lM@ !`'6H(+lH * H(X]pH\B,(8(XH"  #/6H8+(8(XH"  F(l6H8$ l 0o :X+l\ FJi8N(((XH"lM@ !`'6H(+lH * H(X]pH\j B,(8(XH"  #/6H8h B-(8(XH"  #/6H8f B.(8(XH"  #/6H87 +(8(XH"  F(l6H8d +(8(XH"  F(l6H8$ l 0 :X+l\ FJi8N(((XH"lM@ !`'6H(+lH * H(XH|M/(H|"  #|Ji]I8'(/5H\d |(XH| |JiX]IH\n H(H|"  #00(8|"  #1026H8I B2(8|(XH"  #/6H8$ (H|"  F8l5HD (H|"  F8l5Hi (H|"  F8l5H{  :X+l$ l 0- :X+l\ FJi8N(((XH"lM@ !`'6H(+lH * H(XH|M/(H|"  #|Ji]I8'(/5H\u |(XH| |(XHl|JiX]IH\<H(H|"  #00(8|"  #1026H8:B2(8|(XH"  #/6H88|(XHlF:-(Hl"lMX@8 !8((]p6H8\(Q:.(8l(XH"lM@( !((H]p6H8(\&(Hl"lMX@8 !8l5H&&(Hl"lMX@8 !8l5H$ l0 :X+l\FJi8N(((XH"lM@ !`'6H(+lH * H\\(X]pH\ \||JiX]cH\^(8|(XH"  #30(|(("  #4026H8([(H|"  F8l5H{(H|"  F8l5H|JiX]/H\(H|"  F8l5H :X5l$ l0\)FJi8N(((XH"lM@ !`'6H( :X)l (XL (X<(JiXhZ(H"  1(8"  1># '0># '926H8L(XHLL# '0># '926H8A (XH HU |~  (X]/\~ :X6l(X]/\(H(H"  #70(8"  #8026H8(H"  F8l5H#(9(H"  #*F8l/5H(X]/\g(H"  #:0(8"  #;0(("  #<026H8((H"  F8l5H(=(H"  #*F8l/5H#> "  #*FHl/5$ l 0\yFJi8N(((XH"lM@ !`'6H( :X+l+lH * H(X]pH\  (XJiX]IH\M5/(H"  #/5H(XH(X]/H\6M(XH(X]/H\}(H"  F8l5H$ l 0\FJi8N(((XH"lM@ !`'6H( :X+l+lH * H(X]pH\  (XJiX]IH\5/(H"  #/5H(XH(X]/H\(XH)(X]/H\(XHlO:?(8l(XH"lM@( !((H]p68(\F:-(Hl"lMX@8 !8((]p6H8\Q:.(8l(XH"lM@( !((H]p6H8(\&(Hl"lMX@8 !8l5H&(Hl"lMX@8 !8l5H$ l0r\vFJi8N(((XH"lM@ !`'6H( :X+l+lH * H(X]pH\m  (X :X&&JiX]IH(]c8'(\d5/(H"  #/5HK(XHH(H"  #00(8"  #1026H8B2(8(XH"  #/6H8)(H"  F8(6H8)(H"  F8(6H8I)(H"  F8(6H8(XH l$ l0:\ :X@l5 :XAl+lH * H(X]pH\3JiXh33(H"lMX@8 !8:B]/(6H8\& :X@l3 ~$ l0 :X&l %#C0/v FXl %#D0/ FXl$ l0d\FJi8N(((XH"lM@ !`'6H( l E1"lFiX(X]/H\G :X&l_"l FHl$ l0~\FJi8N(((XH"lM@ !`'6H( l E1lFiX(X]pH\%'8(XH"l  16H8"lFiX(X]/H\a :X&ly"l FHl$ l0\FJi8N(((XH"lM@ !`'6H( l E1lFiX(X]pH\9%'8(XH"l  16H8lFiX(X]pH\%'8(XH"l  16H8"lFiX(X]/H\ :X&l"l FHl$ l0! :XGl1lMX@H H:$]/8'(5H\] &(HlMX@8 !8l5H!\ +lH * H +lH * H(X]pH\!  (XJiX]IH\P!5/(H"  #/5H"!(XH(X]/H\9!P!(XH (X]/H\!KJi8(XH"lM@( !(:$|6H8(|(X]pH\!4(((XH|8"  Fl6H8($ l0$ :XGl1lMX@H H:#]/8'(5H\p"&(HlMX@8 !8l5H$u(lM@ !(HlMX@8 !8:#]/('`# "'/6H8\#&(HlMX@8 !8l5H$\4#+lH * HO#+lH * H(X]pH\$  (XJiX]IH\$5/(H"  #/5H#(XH(X]/H\#$(XHw#(X]/H\$KJi8(XH"lM@( !(:#|6H8(|(X]pH\$4(((XH|8"  Fl6H8(/$$xl,|Llh| \< EH8(xhX8(xhX8(xhX8(xhX8(xhX8(X:hN]/H\"OCxO #D" #@0 #<#D '0>8#@ '92D@6Ht ~( Jih]cH\R^((hH"  #D30((|l"  #@402D@6Hl$(H"  #DF*1$/D5HA$(H"  #DF*1$/D5H l[*1` l\*1 #DB0/D]C Nl0l,*#Dl(*#@l$*## '0># '926aT%(0"  #I0/5JCP%(0"  #K0/5HCP5L(0"  #/5R5M(0"  #/5ltJ ltF*1t (Td@(T@Pd(T]c\$lt(0"  #*1t/5d(TdN lPH0 ltF*1t@(T@$lt(0"  #*1t/5 (Td 0~@@(T]c\ ltF*1t@(T@alt #F=/ ltF*1tltCxD,x0Ptd@$0    (L\ DLl(,<JiLh Z(<"  1(<"  1># '0># '926\(L\ <<JiL]c\g(<(,"  1(L<(|l"  1<JiL]c># '0'># '9<',26lL\g<(L<\DL]/\ l <~ 1P<$l <\8 lL,0   MrU , `q00, `q0/( `q0( `q0$! `q,I ` !0&$q `p᷀0* pHd FPAGENRL .XLS<@ =Add_LabelJmodLookupMultipleValues0._VBA_PROJECTbdir Allow the process to go ahead for certain chart types onlyBIN !np Pp H H H H H H HQi.hl H H H HQp Pp,You cannot use this procedure on this objectA@nuXPPHk@B Set embeddedchart to true if the chart is embedded on a worksheet !n 'a'hOn Error GoTo 0 @ Input Number of columns to use for the offset from the Y valuesPsGNumber of offset columns for label text from y values (can be + or -) ?$$:'Y find the reference to the y values (between second & third commas in the formula string)'''ZxM   _  $ 'HQ F'`F'g,HQW 0.M  '  + MsgBox "found a comma"hk FqgMAI  'Rh $ $$  $'. figure out the drive,book,sheet and reference '} ߶ MsgBox yrange MsgBox "single quote found"X  $'} $'   $'a@ MsgBox "NO single quote found"0 !}  MsgBox "EXCLAMATION found"  !} $'   $'RY.a MsgBox "EXCLAMATION NOT found"' 'hPhH  activesheet and active workbook !'k !'@!a [} [}p3'a  [} $'   $'  % 1 =The data for this series is probably in an unopened workbook. $ $(Open the file and run the process again. $ $AAlternatively - the data could also be on a strangely named sheet $ $?Look for [ ] \ : in the sheet tab and change {xv c5(<<< <ӸC  `@p(Macro recorded 20/8/96 by Fiona Hamilton 14 el t X`   \ph$h\\P$`\ $\X$\$$ xME?   ,( \X 4 & "  (8 @X` 8h   B   ,86X  H    2 F@ \  (  `HPX`p    "08@Xh :p  4  $.@p & $  @ &Hpx   &   ( 0P(p    &0 X  >`  T    0 @ H  P ` h p x  &  N (( P "h  " ` p   ,  T(X0  $ H` hpx>8(08@`h p x $ F \ 4P & "   8 8\X    B  8,Hx6  H $8  F`   ` 0@ Xh   "( :0  4p  $.0P &X $  &08@H  P &`   (0 &  (8@HP &h  N (   "8 ` "  0 @ ` p ,  T((P` Tp0  $ H@   > 8 @ P ` h p x    ! SetLabelValues Macro& Macro created 20/8/96 by Fred Cumming1U This macro uses cell contents to assign a label to each point in an x-y scatter plot. It will also work with some other chart types It will work on embedded chartst (;C: On Error GoTo 0\''2 the Y range is created from the selection.formula !8'32;to something else. $ $PROCESS ABORTING!!A@nuh  ]} $'     $ 'Xax8 sheet = booksheet ' replaced April 2002 by Fred CummingM Following line replaces previous one April 2002 to allow defined name ranges߶ to function appropriately  !' !'a ' !'hphhh`'z n B@ MsgBox "about to do each point"X On Error GoTo 0G OLD CODE REPLACED 21 March 2001 to allow filtered lists to be labelled"For Each Point In Selection.Points i = i + 1;T' MsgBox "doing point " & i Point.DataLabel.SelectX If Not thispointerror Thenn4{ Point.DataLabel.Characters.Text = Application.Index(Workbooks(book).Sheets(sheet).Range(absrange).Offset(0, numcols), i)7V߶ Else thispointerror = False End IfX Next PointP&' MsgBox "Dealt with " & i & " points" END OF OLD CODEN NEW code added 15 March 2001 to allow filtered lists to be labelled properly!} ,   $%%L ,!0!H z 'zT0  ,%N!2 z %!!(fh , END OF NEW CODE 15 March 2001HL  FThis series contained  #N/A errorsA@nguph H[HQW4You cannot use this procedure on the selected objectA@nHQ.You must select a SERIES to run this procedureA@nHHQ  ''P error is A@nkpuhl` SetLabelValuesAllSeries Macro? Macro created 20/8/96 by Fred Cumming Modified 23 October 2001U This macro uses cell contents to assign a label to each point in an x-y scatter plot. It will also work with some other chart types It will work on embedded charts ( On Error GoTo 0''2 the Y range is created from the selection.formulaMsgBox ActiveChart.NameGNumber of offset columns for label text from y values (can be + or -) ?$$:' Z ! ZB@J !8'; Allow the process to go ahead for certain chart types only !n H H H H H H HQ H H H HQ,You cannot use this procedure on this objectA@nu P  k B Set embeddedchart to true if the chart is embedded on a worksheet !n F'`F'g@ Input Number of columns to use for the offset from the Y valuesY find the reference to the y values (between second & third commas in the formula string)'''Z   _  $ 'HQ F'`F'g,HQ   '  + MsgBox "found a comma"h k Fqg  ' $ $$  $'. figure out the drive,book,sheet and reference '}  MsgBox yrange MsgBox "single quote found"  $'} $'   $'a MsgBox "NO single quote found" !}  MsgBox "EXCLAMATION found"  !} $'   $'a MsgBox "EXCLAMATION NOT found"' 'hh  activesheet and active workbook !' !'a( [} [}'a  [} $'   $'  % 1 =The data for this series is probably in an unopened workbook. $ $(Open the file and run the process again. $ $AAlternatively - the data could also be on a strangely named sheet $ $?Look for [ ] \ : in the sheet tab and change to something else. $ $PROCESS ABORTING!!A@nuh  ]} $'     $ 'a ' !'hh'z n B@ MsgBox "about to do each point" On Error GoTo 0G OLD CODE REPLACED 21 March 2001 to allow filtered lists to be labelled"For Each Point In Selection.Points i = i + 1' MsgBox "doing point " & i Point.DataLabel.Select If Not thispointerror Then{ Point.DataLabel.Characters.Text = Application.Index(Workbooks(book).Sheets(sheet).Range(absrange).Offset(0, numcols), i) Else thispointerror = False End If Next Point&' MsgBox "Dealt with " & i & " points" END OF OLD CODEN NEW code added 15 March 2001 to allow filtered lists to be labelled properly! ,   $%%L ,!0!H z 'zM Characters.tText doesn't like being null (empty) so try on error resume next  ,%N!2 z %!!(fh , END OF NEW CODE 15 March 2001  FThis series contained  #N/A errorsA@ng Zu80 H[HQ4You cannot use this procedure on the selected objectA@nHQ.You must select a SERIES to run this procedureA@nHHQ  ''hP` error is A@nk8u0l(, MsgBox "embedded chart is " & embeddedchart MsgBox Selection.Type Attribute VB_Name = "Add_Label" ' Set<Values M0acro\ created 20/8/96 by FrCummingPThis ml uscell contents to assign a lweach point in x-y scatter plotWIt wiJalso workth Dsooth)chart typ es 0+on embeddV Sub se tDvx() .Descripti(recor-a Hamilt on*ProcData0Invoke_FuncA \n14 `Appli+.SenUpdatFe On Error GoTo e]p- 0 countNAAtnaB ' W Y range @wEfrom selecD.f@ormulaym =@F#A0llowA(esghead  certa@Av onlyAMsgBoxT}S" CasexlXYSC, xl3DBarColumnLineA7A Piɂ 3D3D  *"You cannotƀ>Ra>dure@6CobjS"BExit 4Elsy w End 0y' U6 sequo= 2, cJ), 2T abs`y%4Q#BG "NO g_ R !"EEXCLAMATIDON;und LeftF, q,2 2R0 NOTW$"  B)Sv* "" ' aB\#^1ߢ`^].` 1S{p [`7F]= 1 b.W586Q((9B62"mj a`rsss8eri{bablyunop\en@Q.AkChr(10) [_!1H2"O`e`7=unyБagAJlnСve -ǂ" 0ld Bj~b+pnam _ro  |PNkb[ ] \ :eh2 tab | s`kng e "PROCESS ABORTING!!1 1 JX"])+j&!$(XN|J->Bp) - I> '608replac0April`02?'Fo1 lШ s pr@evious0epRde`= Wpo fap% A9'?pCQC}.VcGNipaёyԒs !:=xlS8how"nnaboauo d҆%'0qOLD CODE REPLACED 21`r 2001aa2fߠd l8ist7"led EPIn "!P i3'(qd@gDU:i r..3Isf Not thispointerror Then ' P.DataLabel.Characs.Text = Application.Index(Workbooks( ).Sheets(s ).Range(absr).Offset(0, numcols), i)}Else = FaEnd If&N ' MsgBox "Dealt with " &$ i" s"6END OF OLD CODE  NEW code added 15 March 2001 to allow0 fillists be ll properly! F Ea cell In * q.Entire@Row.HiOn ؀ii + 1 On EResume  Selecقps(i)yBOj.Valuh Ea QWSW R"If@ZuntNA > 02nT series ntainV)qco &u#N/A BAtExit Sub a c:= CaseE@9KYou cann-@u A Aed@ure oneǀ%P#objS 1000F1!must a SERIESdruAis 6, 5<=KyTre ~>;$ `+F 9%(*+'1eSet""AsAllS5Macro creat`%20/8/96 by FrCumming @Modifi3 Octoberbm!@m 1A)Ahconnaspsign*Bo*eAmb| in x0-y su plot Iq@o wso ehoth cy8pes b@embeƒA OZ t"(ʥS"enUpd` vGoTo#[OaFz?BLBNA$ '9VY ®=E2fr omZformula@?'dDAvet.Nama~ĵ`?(Input("Nu"pr of@AUl`umns te@y vA(s (m + -) ?")%{ &I$KCol$@ D.&Xy=@3. cd' VowB |s"gheadhcera Bl~y @ c(TFxlXYSO, xl3DBar #cL!Ch AiHPie3D3~D@j\?(L SLcxB(R_0e 't e1o tH ifC 34a7s70a3.p&xlOQsbcb= aO <5B * w*P*p}^q*?+1Yg*# fiЇreferenceG-betwelen_0KdЇ :rdP1mmasI0( strPBEh >< .ActiveWork.NameW]iD0 Seleon.ApplyDataLab Type:=xlShow ' MsgBox "abouo do each point' On Error GoTo 1OLD CODE REPLACED 2 1 Mar2001allow filtered lists be lHled@FEP\V G0s 7iS^@' -d g* i .>.CIf NoQ:hisB8eB6T(hen Charac@0s.TextYUicatiXIndex(Egs|).S$s({.R(absr).Offset(0, numcols), i@@5'= Fa x' N$,.rDealt witVhD@"HssE@ND OF Ep  A>NEW co@de addk15/s prope0rly!ucellAu*I $5.EntireR ow.Hin o6@~B'5t6doesn'tAOke beAnu@(empty) try on CAresume n`+_Rǁ.T(i)HNF?.Valukz:C35!4"nj3@@`%countN`A > 0#FdT@] series DShade Rows 6e4301b890*DJ0 ThisWorkbook 9b3ab04746*D]Wv  Sheet1 4a4276f96b*D_Uv8o2CreateColumnDataFromTable 6f4301b890*D. MergeCells 704301b890*Dhivh &FillInAllDirections 714301b890*Dx^sSaveThisChart 724301b890*D^/Scatter_Module 734301b890*DTpXDecimalPlaces 744301b890*D!`UF_Decimals 993c3b7952*D&.ChartPointDetailsModule 754301b890*D(KclsAppEvent 113ce98895*DeH)clsChartEvent 5b3dacd10a*De`b]+modAppEvent 764301b890*D*ex modChartEvent 774301b890*D4e $SFRI_Code_Splitter 7f4301b890*Db}}Add_Label 7a4301b890*D\c5.modLookupMultipleValues ac40f4bd84*D 8PX`H0xhP8 (@H px`8hS>^~`:S>^~`:S>^~`:S>^~`:(k5/|S>^~`:S>^~`:S>^~`:S>^~`:S>^~`:S>^~`:S>^~`:S>^~`:S>^~`:S>^~`:S>^~`:S>^~`:S>^~`:~`:§S>^~`:ħS>^~`:ƧS>^~`:ȧS>^~`:ʧS>^~`:̧S>^~`:7"aߜB5/|ЧS>^~`:ҧS>^~`:ԧS>^~`:֧S>^~`:اS>^~`:ڧS>^~`:ܧS>^~`:ާS>^~`:S>^~`:WOUwS>^~`:V%`:hY%`:8@h`:N OiO(k5/|Ǜʃ75/|w 75/|w 75/|(w 75/|0׵Cl5/|'g'5/|Gr75/|x^vz(6 << <ӸC $*\Rffff*ac40f4bd848 0$@`h c)&xi( A6)*IVi, ).$ i0( @)2,`hi40 `6 `8 `: `< `> B8`@ PP a@`h4)&'i( )* 'i, ). i0 @)2`hi4 `6 `8 `: `< `> F8`@ ==`hi( )*i, ).i0 )28@i4 :`6 :`8 5/|`: :`< :: $ME0) Use up to 4 variables to match a record., Returns the first row in rngReturn where :-x6 rng1=val1 and rng2=val2 and rng3=val3 and rng4=val4 Parameters are :-H rngReturn - a singlecolumn range that contains the data to be returned; rng1 - a singlecolumn range that contains the first field- val1 - a value that is matched against rng1< rng2 - a singlecolumn range that contains the second field- val2 - a value that is matched against rng2; rng3 - a singlecolumn range that contains the third field- val3 - a value that is matched against rng3< rng4 - a singlecolumn range that contains the fourth field- val4 - a value that is matched against rng4* parameters rng2,val2,...val4 are optionalZZZZZ &!2'6 $!2'>x *F *!2'8g .F .!2':g 2F 2!2'<g z 6 z$6 ( *'@a z$8 ,'@ax'@hhh` @ .'@a8 z$: 0'@a'@hh @ 2'@a z$< 4'@a'@hh @ z$>'"shh`hXhPhH z'"f(   # & Use up to 4 variables to SUM records.& Returns the sum of rngReturn where :-6 rng1=val1 and rng2=val2 and rng3=val3 and rng4=val4 Parameters are :-H rngReturn - a singlecolumn range that contains the data to be returned; rng1 - a singlecolumn range that contains the first field- val1 - a value that is matched against rng18< rng2 - a singlecolumn range that contains the second fieldr.- val2 - a value that is matched against rng2; rng3 - a singlecolumn range that contains the third field- val3 - a value that is matched against rng3< rng4 - a singlecolumn range that contains the fourth field- val4 - a value that is matched against rng4* parameters rng2,val2,...val4 are optionalZZZZZ &!2'6 $!2'> *F *!2'8g .F .!2':g 2F 2!2'<g'D z 6 z$6 ( *'@a z$8 ,'@a'@hh @ .'@a` z$: 0'@a8'@h(h  @ 2'@a z$< 4'@a'@hh @ D z$> 'D Exit Functionhxhphhh` z D'Bf@   ZZZZ &!2'6 *F *!2'8g .F .!2':g 2F 2!2'<g'H z 6 z$6 ( *'@a8 z$8 ,'@a'@hh @ .'@a z$: 0'@a'@hh @ 2'@ah z$< 4'@a@'@h0h( @ H 'H Exit Functionhhhh z H'Ff4Attribute VB_Name = "modLookupMultipleValues" a^ *\G{000204EF-0000-0000-C000-000000000046}#3.0#9#C:\Program Files\Common Files\Microsoft Shared\VBA\VBA332.dll#Visual Basic For Applications *\G{00020813-0000-0000-C000-000000000046}#1.2#0#C:\Program Files\Microsoft Office\Office\EXCEL8.OLB#Microsoft Excel 8.0 Object Library*\G{00020430-0000-0000-C000-000000000046}#2.0#0#C:\WINNT\System32\STDOLE2.TLB#OLE Automation*\G{ACA5E1BF-51B5-45AB-B406-1AFB6E58A5C5}#2.0#0#C:\WINNT\System32\MSForms.TWD#Microsoft Forms 2.0 Object Library*\G{1346AEE6-97E7-11D3-8C1E-00608CE9893A}#2.0#0#C:\TEMP\VBE\MSForms.EXD#Microsoft Forms 2.0 Object Library.E .`M *\G{2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}#2.0#0#C:\Program Files\Microsoft Office\Office\MSO97.DLL#Microsoft Office 8.0 Object Library2 ӸC /%& $.*'(0"v2Text to Number 484301b890*D;lookupstuff 993ab04746*D@Lookstuff sub 7e4301b890*DX0Edit SFRI 4b4301b890*DH/ Utilities 4c4301b890*Dhlabel_sheet 4d4301b890*Dx?*FillFromAdjacentRange 4e4301b890*D',Change Blanks 4f4301b890*D[Clear Statusbar 504301b890*D$GoToLast Real Cell 514301b890*D0Set Edit or View Display 524301b890*D<.Fit Selection to Screen 534301b890*DT? "Wait Meter Module 544301b890*DZ $1"MsgBox for charts 554301b890*D8PasteSpecialAdd 564301b890*DP(!Next Value Down 574301b890*Dh Auto_open 864301b91f*D"-FindNthLargest 7c4301b890*D!#8Find_String_in_ANY_worksheet 824301b8d3*DPk 4Names In Workbook - module 7b4301b890*DFvFormat Cells 5f4301b890*DdTool Tips 604301b890*Dr' Window Pane 614301b890*Dx[ *Print All Pivot Pages 7d4301b890*D( Create Database 644301b890*D@+ PhoneDialer 654301b890*D=Swap the Axes 664301b890*Dp_ Hide the Text 674301b890*DFile Functions 684301b890*D DeleteBlanks 694301b890*D( &Display positioning 6a4301b890*D: UserFunctions 6d4301b890*DB` ǦD175}9BoBM0D =_~ As bCallCase=Dim@DoBDoubleCEachDElseRfSFalseUFor_GoTo`IfcInkIntegerzLooppublicresumeSetSubThenTrueVariantWhilewith Attribute VB_CreatableVB_DescriptionVB_Name Excel+ VBAWin16~Win32Mac FPAGENRL.XLStstdole` Text to Number _Evaluate(text_to_numberh Application*ScreenUpdating!  displaytype( DisplaySplashScreen; numberofcellst SelectionZRowsUCount0vColumnsp9 dispnumbern countcells celldisplay3kcell cellvalue@ ActiveCellValueK IsNumeric$*IsDate2FormulaqYValCompletedPercent StatusBarʜ (lookupstuffluttablematchvalwmatchcol! returncol=ltable:+Range OffsetResize@4neMatchRCells Lookstuff subY (lookstuffsub" ThisWorkbook| DialogSheetsKShowareanameDrawingObjectsߺ lookvalue ListBoxes%StrFillDownTMsgBoxR(setLists lookrangeRemoveAllItemsu4Row offsetnumberli`itemtextAddItem  (EditSFRIPLots Edit SFRIc(EditSFRIPlotSheets ActiveSheet%N Unprotect? ActiveWindow+DisplayHeadings/~DisplayOutlinex Referencee Add Label5>(0setlabelvalues' errorproccountNA7thispointerroryrange xlXYScatter)xl3DBar)| xl3DColumnxl3DLine>xlBarxlLineWPxlColumn .xlAreaxlPiexl3DAreaxl3DPie" xlWorksheet, embeddedchartnumcolsJ-InputBox ij]jasqfoundpcomma0K booksheet>absrangeKbookActiveWorkbooksheet[driveChrK~ApplyDataLabelst xlShowLabelSPointPoints DataLabel CharactersrgIndex WorkbooksBSheets Erro (label_sheet7 CalculationhxlManual PageSetup LeftHeaderPath2 CenterHeaderD CenterFooterUserName\ RightFooter|S xlAutomatic@ FillFromAdjacentRange}(FillFromAdjacentColumndwholeselectionHA startcellPColumnitryleftltryright[ filldirectionBxlDown0D Change Blanks3Q (ChangeBlanksPartNo2IsEmpty  Clear Statusbar(ClearStatusBar+7 GoToLast Real Cell( GoToLastRealCelltest4( PowerUsedRangeW wsWorksheet Worksheet rLastCellrSupposedLastCell FilterModeII ShowAllDatauf SpecialCells xlLastCell%AddressFindnxlByRows xlPrevious  xlByColumns Set Edit or View Displayp( SetEditDisplayDisplayWorkbookTabs˝ToolbarsItb ToolbarButtons nOnActionPushed DisplayFormulaBarDisplayStatusBarDisplayHorizontalScrollBar<DisplayVerticalScrollBar Fit Selection to Screen g( FitSelectionToScreenZoom Wait Meter Module\( ShowMeterandMoveOnSendKeysNHide9z( ShowTimeandMoveOn ( ShowDBandwait_WaitNow% TimeValue ( runYourMacrotestarea) ClearContents{ starttimeLTimerX cellcounteroTimeCaller=disptype@percent^ MsgBox for charts( startdoubleclick ActiveChart  OnDoubleClick7( displaychartlabel  HasDataLabelxlNone LegendKeySaX xlShowValuer> PasteSpecialAdd(psaddaParentCopyƿ PasteSpecialKPastexlAll- OperationyxlAdd CutCopyModedopaste x(MultiplySelectionl multiplyrange7 multiplycell( xlConstants xlMultiply@ SkipBlanksұ Transpose (PasteValuesansxtvbOK%xlValueso Next Value DownG(FindNextValueDown͇svalue$lastrowwActivate| (Auto_openV Visible (Auto_closew (XLA_closeiwbW(fixtoolbuttonsk toolbutton{(Makexla/ DisplayAlertsAddIns InstalledJIsokthedriveExecuteExcel4MacroOAddrFilenamejcopyfile(UpdateMacroFilePDeletex(FpaHelp(FindNthLargest (maxnumber(OriginalSelection findnumberʚ numbertofind8LargeWhatClookinPLookAtOxlWhole SearchOrderSearchDirectionrxlNext MatchCase (findlargey OldFormatݱ NumberFormatR EditBoxes Labels,(FNLFind;? (FNLonbutton4theindex:-afterXFind String in ANY worksheet~(FindInAllWorksheetsN findstringQ}csheetCisheet1 WorksheetserrorfixWU errorfound cselection lastaddress \ xlFormulasZxlPart<oldciFontU ColorIndexresponse vbYesNoCancelqvbYesa?vbCancel' Names In Workbook - module+; (ListNamesQ (PopulateNames7 ListCount6SelectedYNNameNamesοRefersTo (OnLBActionmXList*(Deleteselected}popnamesrequiredJ; nametodelete=cdodeletey sheetname6 Format Cells(WrapTextToggleQ WrapText} (OneDecimalselaselbUnion Tool Tipsh (AddToolTips (fixfrigtools< Window Panes(CycleWindowPaneslcselPanes ActivePane_ (FindwithPanes( thisrangeDialogsxlDialogFormulaFind+ Print All Pivot Pages~[(PrintAllPivotPages pivotTables1w0 PivotTablesw Intersect1 TableRange2zd PageFields' oldpagevaluex CurrentPage@ThePages0 PivotItems  PageHiddendPrintOut. Create Database.(createDataBaser TheSelection&inumberofrecordshAreasKiarea xlToRight| RefersToR1C1cfnameRdecimalsxNFMT}  ColumnWidthSaveAsf; FileFormatxlDBF4 CreateBackupScatter ModuleM('createXYtestval( thenewxrange- thenewyrange DataRangeMPDestinationRangek rangesheetWxrangeW ylabelrangeeTypeName ChartObjectsHTop5Height| ChartWizardsSourceGGalleryPlotBy$ xlColumnshCategoryLabelshs SeriesLabelsth HasLegendBTitle~ CategoryTitleN ValueTitleaR ExtraTitle>SeriesCollection;: TheFormulaΧ thenewformula*D Swap the Axes_(swapaxesn TheSeriesɨ FormulaR1C1Scomma1comma2comma3 newseries Hide the Text (HideTextŸInterior;(HideNumbersInCells*OicellG File Functionsu(ChangeCurrentDirectory/ filetoopenGetOpenFilename]?ChDrive%(SaveCloseActiveWorkbookASave(NOSaveCloseActiveWorkbook Y DeleteBlanksY(DeleteblankrowsFstartrowendrow EntireRow(DeleteblankColumns startcolumn endcolumnnS EntireColumn Display positioningF (MoveTopRightw ScrollColumnLH ScrollRow" UserFunctionsh(concatenaterange@inrange TheString6 Shade Rows( ShadeAlternateRowsI thecolumns͇s1_s2_shade ShadeColumncurrowkgTheAreaiMSFormsC Sheet1Sheet2WorkbookkModule1b ($ToggleMerge (MergeCellsکTestChangeBlanks vTempMnRowMnColstartVersion(ChangeBlanksOld FillInAllDirections(%FillAllDirections<FillTypeOriginalActiveCell0V direction&xlUpxlToLeftWYsidekeycell1IIfzkeycell2 txtstr50RowOffsetFactorColOffsetFactor'ncells~ FillRange Filloption cAutoFill_ Destinationл xlFillDefault]j(&SaveThisChartAsGIF4 SaveThisChartZ3 myChartNameRcurrentselection1ChTextEicharttheEnd0UCase5 Right Export FilterNameNumber- Description (&CloseThisWorkbook_B_var_b_B_var_toolbutton_Defaultj _B_var_Midp _B_var_Chr\; _B_var_ChTexte _B_var_ichart _B_var_LeftQ _B_var_Right9 _B_var_UCasea _B_var_iwbh]State msoButtonUp6 msoButtonDownJ _B_var_EndF CommandBars commandbarButtonspcommandButtonsControlsKItemz _B_var_Itb]_B_var_msoButtonUp_B_var_msoButtondown (PressButtons9JMacro1N OfficeuInsertrShiftrowcountuxlCellTypeLastCellG FormulaArrayΠ _B_var_svalue@_B_var_lastrow PhoneDialerA/(TogglePhoneDialer] _B_var_ifPmsgbo _B_var_msgbo| FOUNDDIALER_B_var_FOUNDDIALER^ _B_var_ForjLactiveworksheet_B_var_activeworksheet`restore>minimiselGetSaveAsFilename_B_var_TheSelection_B_var_numberofrecordseR_B_var_i  _B_var_iarea_B_var_GetSaveAsFilename _B_var_fname?_B_var_decimals;P _B_var_NFMTr_B_var_rowcountEnterfile%DBFDATA֎DBF_B_var_DBFDATAZ _B_var_JIsok_B_var_thedrive__B_var_ColumnWidth|_B_var_pivotTables1i_B_var_oldpagevalueX_B_var_ThePagese_B_var_PageHidden#icol_! _B_var_icolˆ _B_var_cellHidden]MsgQHelpFile HelpContext/O SelectEndǘ _B_var_case Scatter_Modulep Add Label1(0SetLabelValuesAllSeries UISeriesí Add_LabelC _B_var_On6_B_var_countNA_B_var_thispointerror#_B_var_numcolsM!_B_var_ISeriesơ _B_var_yrange_B_var_embeddedchart; _B_var_ijT_B_var_j _B_var_sqfoundd _B_str_MidX_B_var_booksheet_B_var_absrange# _B_var_book&_ _B_var_sheetH _B_var_drive0trashme SetLastCellIRXo UsedRangeȉ ResetLastCell_B_var_X( GoToLastRealCellOldVersion_ DecimalPlaces[ ((SetdecimalsN UF_Decimals TB_Decimals SB_DecimalsCommandButton1_ClickMyformat&SpinButton1_Change SpinButton18UserForm_ClickUserFormNCommandButton1|SB_Decimals_Change(ConcatenateRangeDelimitedg DelimitStrKClickChange FelimitStrJ>seelct\ _B_var_seelct _B_var_Select'Echar) _B_var_char DelimtStrt_B_var_DelimtStrz_B_var_TheStringZ0 _B_var_icell (*ChartPointDetailsy ChartPointDetailsModuleOseklect_B_var_seklectjCaptionx _B_var_Set(.Set_All_ChartsZ(.Reset_All_Charts_B_var_set_all_charts7 _B_var_callChart point details.xla\Run__B_var_reset_all_chartsiClass1+ clsAppEventNxlAppŲxlApp_SheetActivateSh_ (.ActivateSheetxlApp_SheetDeactivate  (.Reset_ChartZxlApp_WorkbookBeforeSaveeWb_SaveAsUICancel EnableEvents clsChartEventcwEmbChart" Chart?LastMsg?jEmbChart_MouseUp ButtonYl ElementIDNArg1BArg2BmyXmyY TheXseries thedatarow ColumnCounter/}TheMsgW\ hasXseriesxlPrimaryButtonGetChartElement$xlSeriesF xlDataLabel6WorksheetFunction&XValuesbValuesvbCr$xlShape modAppEventN mobjSheet? (-AppEventsOff.: (-AppEventsOn& modAppEvent1j4 modChartEventWmyChartFmycharts(.Set_This_Chart(.Set_Chart_SheetchtObjw ChartObjectƹchtnumguMyBadcAcChtOCharts(.FixMeClassMouseUpIsNumberm_B_var_IsNumber _B_var_Format SheetActivate SheetDeactivate*WorkbookBeforeSave,NumberFormatchr ColHeading_B_var_ColHeadingWColValue_B_var_ColValue ColFormatG HeadingRowq_B_var_HeadingRow0(ExtractFromCSVstring ItemNumberhGInSingleQuotesFInDoubleQuotes| itemcounter0c Returnvalue strcountery (ParseSeries`  ReturnType; TheYseries' Code Breaker (/SplitSFRICode5codecodetype; CodeMasterPOSTSFRIL dropchars/specAsc!u CoverPosition, (/FindNthNumberFirstHtPositionnumcountR maxnumcount>ndigits[cfnoUcfstringv(/GetSpeciesString RpositionMakeCSVWLTrimbRTrim SpecialCodem Species1to4ϥCover:z CrownFormLogging1SFRItype ValidCode4 CodeError(CFE}kbFirstCF?|CF\(/SpeciesBicountl openbrackbNumberOfSpecies/J(/writecsvEBoldHorizontalAlignmentS@ xlGeneralbVerticalAlignmentʡxlBottom? Orientationw xlHorizontalTheText IndNumberAind1(/testfn SFRI_Code_Splitter(DateStamp_sheetX DoWholebook_B_var_DoWholebookdvbYesNoDoWholeWorkbook_B_var_DoWholeWorkbookn _B_var_isheetFDoWholeW Utilities%!(CreateFormulaFromStatisticaOutputDSdataXneedsfix_B_var_TheFormula_B_var_needsfixxWAutoFit&  CurrentRegion| xlFormatsUiarae& _B_var_ans1_B_var_dopaste l_B_var_SkipBlanksTestPasteValuesrP pasteareaվAutoFilterMode"lastcol64_B_var_lastcol7 _B_var_Loopisokv _B_var_isokZ modLookupMultipleValues((LookupMultipleValues~ rngReturnrng1Mval1rng2Mval2rng3Mval3rng4Mval4a_rng1a_rng2a_rng3a_rng4a_return~cont{(SumMultipleValues&1TheSum|(CountMultipleValuesTheCount(ExtractFromDelimitedString TheDelimiter8_B_var_itemcounterT_B_var_Returnvalue _B_var_strcounter-(/SFRICodeMakeCSV  return_type _B_str_LeftV _B_str_Chrm# _B_str_Right#( _B_str_RTrim _B_str_LTrim v_B_str_SpecialCodeB&_B_str_Species1to4 _B_str_CoverP_B_str_CrownForm6 _B_str_Height_B_str_Loggingn_B_str_SFRItype6k_B_str_ValidCodeW_B_str_CodeError} _B_str_CFE# _B_str_UCaseK _B_str_spec%_B_var_k _B_var_FirstCFBp _B_str_CF _B_var_icount_B_str_openbrackx_B_var_NumberOfSpecies3_B_var_POSTSFRI;$_B_var_dropchars_B_var_CoverPosition _B_var_lookin{_B_var_FirstHtPosition<_B_var_numcountZ_B_var_maxnumcount_B_var_ndigitsI_ _B_var_cfno_B_var_cfstring> _B_var_comma_B_var_Rposition & _B_var_indB\(#CreateColumnsFromTableTheTable^NewTable%vbCrLfthecolsAtherows| CreateColumnDataFromTable%(SelectVisibleCellsaBNewrange@(PressButtonsDown(PressButtonsUp FontStyleSize _B_var_WithY Find_String_in_ANY_worksheeti firstaddressd Cm/keK s  y \_" i$+-5.U'y% ]0!1!"%  &1&)(.8)679 ;A=YO= LR]!N*@PU `_[ f+X\nGd)]#Z,a;c p@@   @@   @@  @@@   @ "B  v ӸDC <Vrstdole>stdole f%\*\G{00020430v-C 0046}#2.0#0#C:\WINNT\System32\STDOLE2.TLB# Autom`ation^MSFor ms>SFErms/z pFACA5E1BF-51B5-45AB-B406-1AFB6E580A5C5F3.TWD#Microsoft = ` Object Library90j?1346AEE6-97E7-11D3-8C1E608CE9893A?TEMP\VBE\>a5PP.eSpecialAPddOd6Pմ-epcP.űGd_(#>N{Value Dow"LNP3{VlPjeDw0G. #_onp*eApQ(_pq(#{FindNthLargesW f?ӉFn;NhQEr\E>Q_String_in_ANY_workn28$U _P t09ig_ _phNYuwrR`r5k1[ Names In W b - m;24 N"*m#s"I W bp -ݰm= v? "at"c@=>2[Uja_<"_[!+Tool Tip ,i! Tpq' W,ow PanS W AeQCn[aP*t All Pivot3P)qeAmlP vP1q _x`% AJCreatPKataXbasOeKCGqQd D)t0Qs0+@PhoneD[oneDialer2PhoneDialHrdH1$(, H=!+  Swap the Axes"$2%wAkp teQAxysy_V yyHideyTexty yHd4Zs@DtAi$g _aJUs\eraU erb`D_ @6ShaRow_12a5CR@=w O0 _AwThisWorkbook[M{T@A[W@rkboOF{kWv"zaheet1Tg2bS eѠJt1p7o U  CreateColumnDataFromTable229e[C lkmIDba`=rmTjbOe0E-.a;MergepCelld=c=MQ`!rgC`lˠ lpZiv i lInAllDxireŋlclTIAlr`sO^+!AjSave!PCh\arJlS -v`PCDa4t0/4//,!Scatter_ModuCoSct`e _M`?d B_>pDecimalPlacJED,im l5`lc`q@!+@UF^_$ @N cU \_](p4?Q5PointDetpails2l.Pq]t ti0)s?&K=ݟclsAppEven/(cApE)np!_ e3`/a1* /PSѕb]Pamod{s^m2/YHe *S_? `= ؜ SFRI_Code_Spdli@q2,$0RO_`1w_SlCb߭}b}{Add_Label 22 A0~d_*Lp.bP?lNcr5͛6L~upMultipleValuGo7SL uMOqїeV[ 1reQ [prU~~~~~~s     __SRP_0M__SRP_1H0PUF_DecimalsL {d56d5f1q 1A I P Y 1IqYa QOy9y9a IOi Iqa IOaY9aa IxOII\Aw\wws 1 Q y!;HHqMPPP1Qa IPaxaa!bAbib)]i; w i  Text to Number lookupstuff Lookstuff sub Edit SFRI Add Label label_sheetFillFromAdjacentRange Change BlanksClear StatusbarGoToLast Real CellSet Edit or View DisplayFit Selection to ScreenWait Meter ModuleMsgBox for chartsPasteSpecialAddNext Value Down Auto_openFindNthLargestFind String in ANY worksheetNames In Workbook - module Format Cells Tool Tips Window PanePrint All Pivot PagesCreate DatabaseScatter Module Swap the Axes Hide the TextFile Functions DeleteBlanksDisplay positioning UserFunctions Shade Rows ThisWorkbookSheet1Sheet2 MergeCellsFillInAllDirections SaveThisChartModule1 FPAGENRL.XLSFF`:C:\TEMP\VBE\MSForms.EXD XLA_close=C:\Program Files\Common Files\Microsoft Shared\VBA\VBA332.dllVBA a (F 3C:\Program Files\MicrK)^`//rU ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~y 9gW~*pC^d|  2a IOydYyosoft Office\Office\EXCEL8.OLBExcel ! 80FC:\WINNT\System32\STDOLE2.TLBstdole  ) q H4VO`:C:\WINNT\System32\MSForms.TWDMSForms  Q X.E .`M   Q hL-[DR 2C:\Program Files\Microsoft Office\Office\MSO97.DLLOffice a  xShowMeterandMoveOnShowTimeandMoveOn ShowDBandwait runYourMacroDisplaySplashScreen  ListNames PopulateNames OnLBActionDeleteselected findlarge FNLonbuttonFNLFind   lookstuffsubsetLists EditSFRIPLots Auto_closefixtoolbuttonsMakexlaUpdateMacroFileFpaHelp PressButtonsFFName fpagenrl.xlaClose VBA332.DLL S  FPAGENRL.XLA has been closedFindNextValueDownFFFWrapTextToggle OneDecimal AddToolTips fixfrigtoolsPgv3+3o /Looking for next value that is not the same as  text_to_numberEditSFRIPlotSheetssetlabelvaluesFillFromAdjacentColumn ChangeBlanksChangeBlanksOldClearStatusBarGoToLastRealCellPowerUsedRangeFSetEditDisplayFitSelectionToScreenstartdoubleclickdisplaychartlabelpsaddMultiplySelection PasteValuesFindInAllWorksheetsCycleWindowPanes FindwithPanesPrintAllPivotPagescreateDataBasecreateXYswapaxesHideTextHideNumbersInCellsChangeCurrentDirectorySaveCloseActiveWorkbookNOSaveCloseActiveWorkbookDeleteblankrowsDeleteblankColumns MoveTopRightShadeAlternateRows ToggleMergeFillAllDirectionsSaveThisChartAsGIFCloseThisWorkbook PhoneDialerTogglePhoneDialerFOnAction Phone Dialer Phone Dialer.xlsF !j:\b-forest\fc01\Phone Dialer.xlsTypeAreasRowsCountCells% GYou don't REALLY want to make a database with less than 5 cells do you! E  4[)xZDBF1 ^The selection has areas with incompatible numbers of rows. Adjust the selection and try again.ActivateCopy A1 B1 Database = !R1C1:R C dbfdata File Name Enter a file name T  VBInternal& IEnter the NUMBER of decimal places required for numeric cells (default 4) 0. 0 NumberFormat 2:2SelectInsertShift A2 SpecialCellsRow =MAX(LEN(R[1]C:R[ ]C))Delete  8fĤIx8<0 \Select the worksheet range that you want to convert to DBF format before running this macro. error number n[)xZ Fm[)xZDF Worksheet @ Enter database file name *.dbf DBFDATA.DBF *.DBF[)xZ ! !Forest Resource General UtilitiesWF   j:\B-forest\xl5tools\*.* C: J:\B-forest VBA.MAKE.ADDIN(" \xl5tools\fpagenrl.xla") \xl5tools\fpagenrl.xla FRI - general utilities\F_F x  j:\B-forest\xl97tools\*.* \xl97tools\fpagenrl.xla") \xl97tools\fpagenrl.xla PivotTables &There are no pivottables on this sheet TableRange2 `  there are   pivot tables on this sheet# CPlease select a cell within the table you want to print pages from. Then run this macro again. ColumnWidth PageFields +There are no pagefields in this Pivot Table CurrentPageValue PivotItemsColumnPrintOutRange!=h8+3q$ DSelect a worksheet containing a PivotTable before running this macro 2Select a cell in the Pivot Table and run the macro +An error has occurred in the macro - sorry!aU5/|`U5/|Scatter_Module Add Label1 Add_LabelsetlabelvaluesAllSeriesApplyDataLabels% GNumber of offset columns for label text from y values (can be + or -) ?FFormula ,You cannot use this procedure on this object      % !"#$w  ' , i  [ PROCESS ABORTING!! ]F =The data for this series is probably in an unopened workbook. (Open the file and run the process again." AAlternatively - the data could also be on a strangely named sheet! ?Look for [ ] \ : in the sheet tab and change to something else. EntireRowHiddenOffsetPoints DataLabel CharactersText This series contained  #N/A errors 4You cannot use this procedure on the selected object .You must select a SERIES to run this procedure error is  ResetLastCell UsedRange Last cell on this sheet is now  DecimalPlaces UF_DecimalsGoToLastRealCellOldVersionSetLabelValuesAllSeriesconcatenaterangeConcatenateRangeDelimited  8  CR LF CRLF TAB #j:\B-forest\utilities\xl97tools\*.* Disable Chart Point details #\utilities\xl97tools\fpagenrl.xla") !\utilities\xl97tools\fpagenrl.xlaChartPointDetailsChartPointDetailsModule Setdecimals 8* fri - general utilities F FCaption Enable Chart Point detailsState Disable Chart Point DetailsClass Enable Chart Point DetailsClass1 clsAppEvent clsChartEvent modAppEvent modAppEvent1 modChartEvent AppEventsOff AppEventsOn Set_This_ChartSet_Chart_Sheet Reset_ChartSet_All_ChartsReset_All_Charts ActivateSheetFixMe  +=h8+3q׵Cl5/|*=h8+3q׵Cl5/|DFEmbChart  # EmbChart_MouseUp L488 ChartObjectsFXValuesValues , Field values are :-  1    a1  = O  Nothing Chart element   ( ).  .׵Cl5/|-׵Cl5/| is numeric number format is 2׵Cl5/|1׵Cl5/|  <׵Cl5/|;׵Cl5/| general General - is number  - format - Jm5/|Jm5/| heading row is ExtractFromCSVstring ParseSeries Code BreakerSFRI_Code_SplitterDateStamp_sheet DATE STAMP %Datestamp ALL sheets in the workbook? PageSetup \  -  LeftHeader CenterHeader "  Prepared by  d mmm yyyy hh:mm CenterFooter Page &P RightFooter@׵Cl5/|?׵Cl5/|  kboo SplitSFRICodeMakeCSVSpeciesGetSpeciesStringwritecsv FindNthNumbertestfn  c '5/|c '5/|  k You asked for all sheets Paste Values () You DIDNT asked for all sheets Utilities   !CreateFormulaFromStatisticaOutputColumns4 eYou need to select a 2 column range (with more than 2 rows) starting at the cell labelled 'Intercept' InterceptParent select a worksheet ) / 5The selection needs to start with the intercept line! 0123456789 _ -+.,=/ !@#$%^&*() k !  +   * ResizeEnd width set to  .DBF .dbf . dbf 'j:\b-forest\auser\fc01\Phone Dialer.xls 4Currently Selected Cells will be replaced by VALUES! (Any formulae in these cells will be lost !Press CANCEL to abort the process PasteSpecialPaste Operation SkipBlanks Transpose No changes were made  B" ASelect a range of cells on a worksheet before running this macro. Error number Address The file  has been created with   records  `F "areas don't share columns exactly!CountMultipleValues ,,,, ,,, areas don't share rows exactly! data is ok Data is no good  # CThe current selection is not suitable for conversion to DBF format. DBF file creation CANCELLEDmodLookupMultipleValuesExtractFromDelimitedStringLookupMultipleValuesSumMultipleValues no Not enough variables - ERROR!!!SFRICodeMakeCSV HEADING& ISpecies1to4,Cover,CrownForm,Height,Logging,SpecialCode,SFRItype,CodeError h HB  `B j xB   B   B CLOUD#SHADOW#-#NE#BG#L yes  B S Species not found # Cover value not found # ntdpg G d -=+ #one or more Crown forms not found # a b H Height not found # L Logging, (,ԳSB֌g+~!r\K`A%AutoFilterModeHeight; rSpecies1,Species2,Species3,Species4,Cover,CF-irreg,CF-reglr,CF-regth,Height,Logging,SpecialCode,SFRItype,CodeError -Please use a code from the following list :-  SPECIES COVER IRREGULARCFZ %SPECIES%COVER%IRREGULARCF%IRREGULARHT%REGULARCF%REGULARHT%CFSTRING%HEIGHTCLASS%REGROWTHCF%REGROWTHHT%LOGGING%SPECIAL%SPECIES1%SPECIES2%SPECIES3%CROWNWIDTH%REGENYEAR%TREATYEAR% Code type is INVALID! REGULARCF REGROWTHCF IRREGULARHT REGULARHT REGROWTHHT HEIGHTCLASS CFSTRING LOGGING SPECIAL SPECIES1 SPECIES2 SPECIES3 CROWNWIDTH REGENYEAR TREATYEAR Error in code type! T R no species hi there not found dgptn 123456 ab g ht missing error - no crown width numbers 5 no crown width data - 1 digit  10 15 20 no crown width data - 2 digits  no crown width data - 3+ digits  gd Logging No Logging CLOUD SHADOW none 1Click YES to place this list in the current cell.* PSPECIES,COVER,IRREGULARCF,REGULARCF,REGROWTHCF,IRREGULARHT,REGULARHT,REGROWTHHT,  Find_String_in_ANY_worksheet1 ^HEIGHTCLASS,CFSTRING,LOGGING,SPECIAL,SPECIES1,SPECIES2,SPECIES3,CROWNWIDTH,REGENYEAR,TREATYEARCreateColumnDataFromTable  CreateColumnsFromTableSelectVisibleCellsPressButtonsDownPressButtonsUp% FAll buttons on the FRI - general utilities COMMANDBAR will be inverted 14disptypepercent &Macro recorded 4/10/95 by Fred Cumming (Macro recorded 20/8/96 by Fiona Hamilton 'Macro recorded 28/08/96 by Fred Cumming ) y wsWorksheet &Macro recorded 9/04/97 by Fred Cumming (Macro recorded 13/8/97 by Fiona Hamilton 'Macro recorded 21/11/97 by Fred Cummingval3rng4val4 return_type uMacro recorded 9/16/95 by Fiona Hamilton Labels the workbook with authors name and date and workbook details !0  Q*(inrange DelimitStrSh S8 T7HLastMsgEmbChart 9=XButtonShiftXY TheString ItemNumber TheSeries ReturnTypecodecodetypeTheText IndNumber TheDelimiter rngReturnrng1val1rng2val2rng3$H @}(  N"H,"{@Tahoma(OKOuTahomaSB_Decimals( 8TB_Decimals,4CommandButton1 oKMCompObjNaVBFrame-PROJECTwmJA  Microsoft Forms 2.0 FormEmbedded Object9qVERSION 5.00 Begin {C62A69F0-16DC-11CE-9E98-00AA00574A4F} UF_Decimals Caption = "Set Decimal Places" ClientHeight = 840 ClientLeft = 45 ClientTop = 330 ClientWidth = 2985 StartUpPosition = 1 'CenterOwner TypeInfoVer = 5 End Text to NumberText to NumberlookupstufflookupstuffLookstuff subLookstuff subEdit SFRIEdit SFRIlabel_sheetlabel_sheetFillFromAdjacentRangeFillFromAdjacentRangeChange BlanksChange BlanksClear StatusbarClear StatusbarGoToLast Real CellGoToLast Real CellSet Edit or View DisplaySet Edit or View DisplayFit Selection to ScreenFit Selection to ScreenWait Meter ModuleWait Meter ModuleMsgBox for chartsMsgBox for chartsPasteSpecialAddPasteSpecialAddNext Value DownNext Value DownAuto_openAuto_openFindNthLargestFindNthLargestNames In Workbook - moduleNames In Workbook - moduleFormat CellsFormat CellsTool TipsTool TipsWindow PaneWindow PanePrint All Pivot PagesPrint All Pivot PagesCreate DatabaseCreate DatabaseSwap the AxesSwap the AxesHide the TextHide the TextFile FunctionsFile FunctionsDeleteBlanksDeleteBlanksDisplay positioningDisplay positioningUserFunctionsUserFunctionsShade RowsShade RowsThisWorkbookThisWorkbookSheet1Sheet1MergeCellsMergeCellsFillInAllDirectionsFillInAllDirectionsSaveThisChartSaveThisChartPhoneDialerPhoneDialerScatter_ModuleScatter_ModuleDecimalPlacesDecimalPlacesUF_DecimalsUF_DecimalsChartPointDetailsModuleChartPointDetailsModuleclsAppEventclsAppEventclsChartEventclsChartEventmodAppEventmodAppEventmodChartEventmodChartEventSFRI_Code_SplitterSFRI_Code_SplitterUtilitiesUtilitiesAdd_LabelAdd_LabelmodLookupMultipleValuesmodLookupMultipleValuesCreateColumnDataFromTableCreateColumnDataFromTableFind_String_in_ANY_worksheetFind_String_in_ANY_worksheetID="{53E8A7E5-5E3E-11D2-8B7E-00608CE9893A}" Module=Text to Number Module=lookupstuff Module=Lookstuff sub Module=Edit SFRI Module=label_sheet Module=FillFromAdjacentRange Module=Change PROJECT 0001 1#SummaryInformation(S6DocumentSummaryInformation8;       !"#$%&'()*+,-./02345789:<=>?@ABCEBlanks Module=Clear Statusbar Module=GoToLast Real Cell Module=Set Edit or View Display Module=Fit Selection to Screen Module=Wait Meter Module Module=MsgBox for charts Module=PasteSpecialAdd Module=Next Value Down Module=Auto_open Module=FindNthLargest Module=Names In Workbook - module Module=Format Cells Module=Tool Tips Module=Window Pane Module=Print All Pivot Pages Module=Create Database Module=Swap the Axes Module=Hide the Text Module=File Functions Module=DeleteBlanks Module=Display positioning Module=UserFunctions Module=Shade Rows Document=ThisWorkbook/&H00000000 Document=Sheet1/&H00000000 Module=MergeCells Module=FillInAllDirections Module=SaveThisChart Module=PhoneDialer Module=Scatter_Module Module=DecimalPlaces Package={AC9F2F90-E877-11CE-9F68-00AA00574A4F} BaseClass=UF_Decimals Module=ChartPointDetailsModule Class=clsAppEvent Class=clsChartEvent Module=modAppEvent Module=modChartEvent Module=SFRI_Code_Splitter Module=Utilities Module=Add_Label Module=modLookupMultipleValues Module=CreateColumnDataFromTable Module=Find_String_in_ANY_worksheet HelpFile="" Name="FPAGENRL.XLS" HelpContextID="0" CMG="4143A62EAA2EAA2EAA2EAA" DPB="D6D4312E5356E05EE05E92E0B13664CDAD31" GC="6B698CC520C620C620" [Host Extender Info] &H00000001={3832D640-CF90-11CF-8E43-00A0C911005A};VBE;&H00000000 [Workspace] Text to Number=0, 0, 560, 465, lookupstuff=0, 0, 0, 0, C Lookstuff sub=0, 0, 0, 0, C Edit SFRI=0, 0, 0, 0, C label_sheet=198, 198, 735, 598, Z FillFromAdjacentRange=0, 0, 0, 0, C Change Blanks=88, 88, 686, 486, Clear Statusbar=0, 0, 0, 0, C GoToLast Real Cell=22, 22, 699, 444, C Set Edit or View Display=132, 132, 547, 465, Fit Selection to Screen=0, 0, 0, 0, C Wait Meter Module=44, 44, 459, 377, MsgBox for charts=0, 0, 0, 0, C PasteSpecialAdd=34, 52, 700, 598, Next Value Down=87, 48, 734, 448, Auto_open=42, 29, 762, 425, FindNthLargest=0, 0, 0, 0, C Names In Workbook - module=88, 88, 483, 488, Format Cells=0, 0, 0, 0, C Tool Tips=0, 0, 0, 0, C Window Pane=66, 66, 481, 399, Print All Pivot Pages=22, 22, 437, 360, Z Create Database=17, 43, 731, 559, Z Swap the Axes=66, 66, 576, 595, Hide the Text=0, 0, 0, 0, C File Functions=0, 0, 0, 0, C DeleteBlanks=0, 0, 0, 0, C Display positioning=0, 0, 0, 0, C UserFunctions=22, 22, 648, 543, Shade Rows=0, 0, 415, 333, ThisWorkbook=0, 0, 0, 0, C Sheet1=0, 0, 0, 0, C MergeCells=110, 110, 742, 521, C FillInAllDirections=0, 0, 0, 0, C SaveThisChart=132, 132, 718, 528, PhoneDialer=0, 0, 586, 447, Scatter_Module=0, 0, 0, 0, C DecimalPlaces=44, 44, 721, 466, UF_Decimals=88, 88, 765, 510, C, 66, 66, 743, 488, ChartPointDetailsModule=22, 22, 492, 423, Z clsAppEvent=0, 0, 0, 0, C clsChartEvent=4, 13, 731, 564, modAppEvent=176, 176, 720, 576, modChartEvent=154, 154, 698, 554, SFRI_Code_Splitter=106, 25, 719, 632, C Utilities=13, 542, 670, 713, Add_Label=22, 22, 548, 422, Z modLookupMultipleValues=74, 140, 699, 620, CreateColumnDataFromTable=66, 66, 667, 483, Find_String_in_ANY_worksheet=0, 0, 0, 0, C  fc01" @varietyabtypeabacost?@@@@@ Oh+'0HP| "Forest Resource General UtilitiesEx Fred Cummingce  Fred Cummingce Microsoft Excel@Qwvt@~Lo՜.+,D՜.+,DHP X`hp x 1 Sheet1Macro1 Lookupdialog FNL DialogNames In Workbook - dialogScatter Dialog  WorksheetsExcel 4.0 MacrosDialogs 6> _PID_GUIDAN{53E8A7E6-5E3E-11D2-8B7E-00608CE9893A} FMicrosoft Excel WorksheetBiff8Excel.Sheet.89qCompObjDf