Version 1.0
Contents
1. a Select first site based on Richness b Subsequent sites selected by Maximum s of neighbours ties by Representation further ties by Random Repeat b until all soecies represented or no more sites are within specified dmax RESERVES XLA Version 1 0 User manual APPENDIX 2 End User Licence Agreement Reserves xla Version 1 0 An Add in program for Microsoft Excel to implement reserve selection algorithms Copyright C Robert A Briers 2001 All Rights Reserved This Software is protected by the copyright laws of the United Kingdom of Great Britain and Northern Ireland and international copyright treaties RESERVES AXLA VERSION 1x0 END USER LICENSE AGREEMENT Robert A Briers Author hereby grants to you User a non exclusive licence to use Reserves xla Version 1 0 Software and accompanying documentation on the terms below The User may use the Software on any computer make an unlimited number of copies of the Software for personal use redistribute the Software provided this is done solely by transfer of un modified copies of the file Reservesl zip or the files Reserves xla Reserves pdf and licence txt together or the distribution floppy disks no money is charged for it it is not included as a part of any commercial package or used or distributed in support of a commercial service without prior express written permission from the Author In addition the user may publish texts that include or comment results2. generation Several procedures are used by the algorithms to break ties between sites but if there is still a choice eventually one site will be chosen at random from the candidate sites Reserves xla uses the Visual Basic Rnd function to generate random numbers this pseudo random number generator uses the linear congruential method to generate the numbers The Rnd function is not the best generator around in terms of the statistical properties of the random numbers produced but for the purposes here it is likely to be sufficient For more information on the method used by VB VBA see http support microsoft com support kb articles Q231 8 47 asp Acknowledgements Parts of the VBA code of Reserves xla were adapted from macros written by Andy Brewer and Don Whittle to whom thanks are due for sharing code and ideas for this and other projects The code for the menu was adapted from macros available at John Walkenbach s excellent Excel VBA website http www j walk com ss excel RESERVES XLA Version 1 0 User manual 4 APPENDIX 1 Description of rules and algorithms used in reserve selection procedures A Description of rules used in algorithms Rule Richness Representation Neighbour Maximum connectivity Maximum sAC Maximum s of neighbours Random B Algorithm descriptions Algorithm 1 Greedy Algorithm 2 Greedy Neighbour Algorithm 3 Greedy Maximum connectivity Algorithm 4 sAC selection Al
3. Reserves xla An Add in for Microsoft Excel to implement reserve selection algorithms Version 1 0 2001 Dr Robert A Briers All Rights Reserved Institute of Biological Sciences University of Wales Aberystwyth E mail roo aber ac uk URL hittp users aber ac uk rob reserves This software is distributed as Freeware under the terms of the accompanying licence see Appendix 2 of this document or the accompanying file licence txt Purpose This add in implements a series of reserve selection algorithms for choosing sites to be included in a reserve network Full details of the algorithms and suggested usages are given in Briers RA 2002 Incorporating connectivity in reserve selection procedures Biological Conservation 103 77 83 A summary of the selection rules implemented by the various algorithms is given in Appendix 1 of this document This program works on the GIGO principle Garbage In Garbage Out Certain tests are performed on the data that are entered into the program to check the validity of the input However the program is essentially dumb i e it will perform calculations on whatever data are given to it If it is given inaccurate input the output will also be wrong It is up to the user to ensure that valid data are used in selection procedures Full details of the required input are given below System requirements Reserves xla is written in Visual Basic for Applications VBA It runs from within Microsof
4. e Microsoft Corporation RESERVES XLA Version 1 0 User manual 6
5. gorithm 5 Distance based selection Description Choose site containing the highest number of species not already represented in the network Choose site supporting species with the lowest total representation in the reserve network i e the number of times the species present at the site have been represented in the sites already selected Choose site which is nearest to a site already selected Choose site which maximises the mean connectivity of the reserve network C Choose site with the maximum value of SAC i e the number of unrepresented species added by the site multiplied by the change in connectivity resulting from the addition of the site to the network Find all sites within specified maximum distance dmax of currently selected sites and select site with the highest number of unrepresented species _ Choose site at random from sites available for addition to network Richness ties broken by Representation further ties by Random Repeat until all species represented Richness ties broken by Neighbour further ties by Representation further ties by Random Repeat until all species represented Richness ties broken by Maximum connectivity further ties by Representation further ties by Random Repeat until all species represented a Select first site based on Richness b Subsequent sites selected by Maximum sAC ties by Representation further ties by Random Repeat b until all soecies represented
6. he way of the output will be overwritten If this is going to occur the user will be alerted but the data will still be overwritten anyway Algorithm choice Click on the appropriate button to choose which algorithm is to be used A brief summary of the algorithms is given below For full details of the algorithms see Briers 2002 and Appendix 1 of this document Greedy selects sites on the basis of site species richness i e the site with the highest number of species that are not currently in the reserve is selected at each step Greedy neighbour as above only if sites have equal numbers of species the site that is nearest to a site already included in the reserve network is chosen Greedy max connectivity as above only if there is a tie between sites the sites which maximizes mean connectivity of the reserve network is chosen Connectivity C is defined here as Y exp j gt i where djis the distance between sites and j Mean connectivity is C n where n is the number of sites included in the network Note that although distances between sites are assumed to be in metres as a result of numerical limits on the size of values that can be stored in a double precision variable distances are converted to kilometres for the calculation of C s delta A C for each candidate site the number of species that would be added to the reserve by the site s is multiplied by the change in connectivity that would result fro
7. m addition of the site deltaC The site added has the maximum value of s deltaC Distance based selection at each step all the sites that are within a specified distance dmax of Currently selected sites are located and the site which adds the maximum number of species added If no sites can be located within dmax the selection process is terminated Beep when finished As the selection process can take some time checking this box will cause the program to alert the user when the process has been completed Click on OK to start the selection process About Reserves xla This gives details of the program version and the URL of the website where the program is distributed and the author can be contacted RESERVES XLA Version 1 0 User manual 3 Remove add in Clicking on this closes the add in and removes the Reserves menu Note if the add in has been installed using Tools Add Ins this will not un install the add in but will simply remove the menu which will re appear next time Excel is started The add in will still remain in memory Output details The program will produce a list of sites included in the final reserve network in the order in which they were selected The X and Y coordinates of the sites are given along with the number of species added to the reserve by each site and the cumulative number of species represented by the network Additionally a summary of the selection process is given detailing the fi
8. nal connectivity of the reserve network C the number of sites selected n from which the mean connectivity can be calculated C n see Briers 2002 along with the number of times if any that random selection was invoked and the time taken to complete the selection process Important notes please read Compatibility with other programs The algorithms implemented by Reserves xla are fairly computationally intensive particularly with large data sets due to the number of calculations carried out in the selection process The program will basically use as much of the CPU processing time and free memory as it can get hold of Therefore it is not particularly friendly to a multitasking environment i e when multiple applications are running Most of the time if other applications are running this will simply increase the processing time However printing at the same time as running the algorithms is NOT a good idea the printer may freeze up and the print job may be affected This is nota bug simply a result of the memory allocation to different processes running concurrently in Windows Time taken to complete selection The time taken to complete a selection depends on the size of the data set and the speed of the computer on which the program is being run The examples given in Briers 2002 131 sites and 256 species took between 2 and 6 minutes to complete depending on the algorithm used on a 200Mhz Pentium Il Random number
9. obtained with the Software but only if these results or comments are accompanied by one of the following references Briers RA 2002 Incorporating connectivity into reserve selection procedures Biological Conservation 103 77 83 Briers R A 2001 Reserves xla An Add in program for Microsoft Excel to implement reserve selection algorithms Version 1 0 The User may not permit other individuals to use the Software except under the terms listed above translate reverse engineer decompile disassemble the Software rent or lease the Software or remove any proprietary notices on the Software THE SOFTWARE IS PROVIDED AS IS WITHOUT WARRANTY OF ANY KIND EITHER EXPRESS OR IMPLIED INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT INDIRECT INCIDENTAL CONSEQUENTIAL LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES EVEN IF THE AUTHOR HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES The Software is not a supported product The Author will attempt to address any problems that may be encountered in using the Software but no commitment or liability to do so is accepted The licence will terminate automatically if the User fails to comply with the limitations described above On termination the User must destroy all copies of the Software and Documentation Microsoft is a registered trademark of th
10. on 250m T M Beep when finished Cancel Data range Click on the box and then select the worksheet data which are to be used in the selection process using the mouse to click and drag over the required range The format required for the program is indicated below 1 oitenames Site 1 ote 2 Site g 2 4 coordinates J33 24 oe 3 Y coordinates PGE 425 J32 4 Species 1 0 0 5 Species 2 1 0 1 6 Species 3 0 1 1 Coordinate data are assumed to be in metres but different units can be used provided that they come from a Cartesian coordinate system Species presence or absence at a particular site should be indicated by a 1 presence or 0 absence in the appropriate place in the data matrix The program performs basic checks on the validity of the data When the program is run if there are any non numeric data in the species presence absence matrix or species are not present at any site a warning will be given the offending data highlighted in yellow and the selection procedure will be terminated RESERVES XLA Version 1 0 User manual Output range Click on the box and then select a single cell in a worksheet where the output will be placed If more than one cell is selected the top left cell is used as the starting point for the output The size of the area required for output depends upon the number of sites selected It is recommended that the output is placed in a blank worksheet due to the fact that any data in t
11. t Excel version 97 or 2000 and has been tested under Windows 95 98 2000 and NT4 If has not been tested on Windows Excel XP but do not forsee any problems It will NOT work under version 5 or 95 of Excel Most of the VBA code was originally written in Excel 95 but due to changes in the Excel Object Model between 95 and 97 have standardised the code to run under Excel 97 If you require a version that runs under older releases of Excel please e mail me see above Running the program To use the add in start Excel then select File Open locate the directory in which the file was placed and double click on the filename Reserves xla It is recommended that the add in is not permanently installed via Tools Add Ins as this method uses up memory which could be used for other purposes Opening the file results in the creation of a new top level menu Reserves Clicking on this menu gives you three choices Data Reserves Window Help A7 Select sites kj r a O bout Reserves xla ae KE Remove addin RESERVES XLA Version 1 0 User manual Select sites Clicking on this brings up the dialog pictured below into which the details of the data and algorithm required are entered Reserves xla version 0 8 03 Ei Ed Data range Output a Algorithm choice Simple greedy richness based C Greedyineighbour C Greedyimaximum connectivity C s deltat selection C Distance based selecti
Download Pdf Manuals
Related Search