1) Create a new workbook in Microsoft Excel. Rename worksheet 1 "Input" and worksheet 2 "Output."

2) If Microsoft Excel 2007 is used, open the Developer tab (right click on the main toolbar, select "Customize Quick Access Toolbar", select "Popular" from menu, and check box that says "Show Developer Tab on the Ribbon"). In earlier versions of Excel, open the Visual Basic Toolbar (Tools...Customize...Visual Basic)

3) Open the Visual Basic Editor (Button on toolbar). Insert a module to the editor. This can be done using the menus on top: Insert...Module, or by right-clicking on Microsoft Excel Objects in the directory on the left of the screen. Make sure the module (Module1 is the default) is shown as a Microsoft Excel Object.

4) The full computer program SERBt and comments are attached in a file and listed below. Paste into the module screen the full text from the attached file (click download at bottom)or from immediately below:

Option Explicit ' Requires all variables to be declared

' Variable definitions

Dim Fss, Frs, Frr As Double ' Frequency of three genotypes (ss, rs, rr)
Dim RefWss, RefWrs, RefWrr As Double ' Fitness of three genotypes in refuge
Dim BtWss, BtWrs, BtWrr As Double ' Fitness of three genotypes in Bt field
Dim Wss, Wrs, Wrr As Double ' Fitness of each genotype (across both fields)
Dim PRef, PBt As Double ' Proportion of habitat planted to refuge and Bt fields
Dim Inits, Initr, Freqs, Freqr As Double ' Initial frequency of alleles and frequency over time
Dim Wm As Double ' Population weighted mean fitness
Dim Deltar As Double ' Change in r allele frequency each generation
Dim GenYear As Integer ' Number of generations per year
Dim GeneCrit As Double ' Genotypic Criterion (FREQr = 0.5 is standard)
Dim Gen, A, Years As Integer ' Loop Counters

' Beginning of program code

Sub BtResistance()

Sheets("Input").Select ' Input data on worksheet "Input" (Worksheet must be named "Input")

' All input variables are drawn from worksheet "Input". Values could be entered below directly if desired.
Inits = Cells(3, 1) ' Initial frequency of s allele (Input taken from Row 3, Column A)
Initr = Cells(3, 2) ' Initial frequency of r allele in (Input taken from Row 3, Column B)
Freqs = Inits ' Frequency of s allele
Freqr = Initr ' Frequency of r allele
PRef = Cells(6, 1) ' Proportion refuge (Input taken from Row 6, Column A)
PBt = 1 - PRef ' Proportion Bt
GenYear = Cells(6, 5) ' Number of generations per year (Input taken from Row 6, Column E)
Years = Cells(6, 10) ' Number of simulated years
Gen = Years * GenYear ' Number of simulated generations

RefWss = Cells(3, 5) ' Fitness of ss in refuge (Input taken from Row 3, Column E)
RefWrs = Cells(3, 6) ' Fitness of rs in refuge (Input taken from Row 3, Column F)
RefWrr = Cells(3, 7) ' Fitness of rr in refuge (Input taken from Row 3, Column G)
BtWss = Cells(3, 10) ' Fitness of ss in Bt field (Input taken from Row 3, Column J)
BtWrs = Cells(3, 11) ' Fitness of rs in Bt field (Input taken from Row 3, Column K)
BtWrr = Cells(3, 12) ' Fitness of rr in Bt field (Input taken from Row 3, Column L)

' Values of generation counter determine number of generations to run unless stop point is reached

For A = 1 To Gen

' Calculate genotype frequencies at beginning of generation

Fss = Freqs * Freqs
Frs = 2 * Freqs * Freqr
Frr = Freqr * Freqr

' Calculate fitness of each genotype and population weighted mean fitness

Wss = BtWss * PBt + RefWss * PRef
Wrs = BtWrs * PBt + RefWrs * PRef
Wrr = BtWrr * PBt + RefWrr * PRef

Wm = Frr * Wrr + Frs * Wrs + Fss * Wss

' Calculate change in r allele frequency in each generation

Deltar = (Freqr * Freqs * (Freqr * (Wrr - Wrs) + Freqs * (Wrs - Wss))) / Wm

' Calculate allele frequencies after each generation

Freqr = Freqr + Deltar
Freqs = 1 - Freqr

' Delete old output (Requires a worksheet named "Output")

Sheets("Output").Select

If (A = 1) Then

Cells.Select
Selection.ClearContents
Range("A1").Select

End If

' Output initial conditions to Row 2, Columns A-C (Requires a worksheet named "Output")

Cells(1, 1) = "Generation"
Cells(1, 2) = "Years"
Cells(1, 3) = "s Freq"
Cells(1, 4) = "r Freq"
Cells(1, 5) = "ss Freq"
Cells(1, 6) = "rs Freq"
Cells(1, 7) = "rr Freq"
Cells(1, 9) = "Years to Reach Genotypic Criterion"
Cells(1, 13) = "Dominance, h"
Cells(4, 9) = "Frequency of r allele in Year 20"
Cells(4, 13) = "Frequency of rr genotype in Year 20"
Cells(2, 1) = 0
Cells(2, 2) = 0
Cells(2, 3) = Inits
Cells(2, 4) = Initr
Cells(2, 5) = Inits * Inits
Cells(2, 6) = 2 * Inits * Initr
Cells(2, 7) = Initr * Initr
Cells(2, 13) = ((BtWrs - BtWss) / (BtWrr - BtWss))

' Output conditions of model runs to Row 3+, Columns A-G, Worksheet "Output"

Cells(A + 2, 1) = A
Cells(A + 2, 2) = A / GenYear
Cells(A + 2, 3) = Freqs
Cells(A + 2, 4) = Freqr
Cells(A + 2, 5) = Freqs * Freqs
Cells(A + 2, 6) = 2 * Freqs * Freqr
Cells(A + 2, 7) = Freqr * Freqr

' Output years in which genotypic and phenotypic criteria are reached, Worksheet "Output"

If (Cells(A + 2, 4) >= 0.5 And Cells(A + 1, 4) < 0.5) Then

Cells(2, 9) = Cells(A + 2, 2)

End If

If (A = Gen And Cells(A + 2, 4) < 0.5) Then

Cells(2, 9) = "Not Reached"

End If

' Output r allele frequency and rr genotype frequency after simulated number of years

If (A = Gen) Then

Cells(5, 9) = Freqr
Cells(5, 13) = Frr

End If

Next A

End Sub

5) The values of the several variables are input to the program from the worksheet "Input". The values can be entered directly into the program code, but we find it is easier to manipulate the variables on the spreadsheet. The best way to do this is to format the Input Screen as Follows (corresponds with code above):

Row 3, Column A: Initial s allele frequency (Value from 0 to 1)
Row 3, Column B: Initial r allele frequency (Value from 0 to 1; 1 – Initial s)
Row 3, Column E: Fitness of ss genotype in refuge (Value from 0 to 1)
Row 3, Column F: Fitness of rs genotype in refuge (Value from 0 to 1)
Row 3, Column G: Fitness of rr genotype in refuge (Value from 0 to 1)
Row 3, Column J: Fitness of ss genotype in Bt field (Value from 0 to 1)
Row 3, Column K: Fitness of rs genotype in Bt field (Value from 0 to 1)
Row 3, Column L: Fitness of rr genotype in Bt field (Value from 0 to 1)

Row 6, Column A: Proportion Refuge (Value from 0 to 1)
Row 6, Column E: Number of generations per year (Integer greater than or equal to 1)
Row 6, Column J: Number of simulated years (Integer greater than or equal to 1)

We used Rows 1, 2, and 5 of the worksheet "Input" to label the variables. No values from these rows are used as inputs to the program, but they are helpful for reference.

6) To run the program in Microsoft Excel 2007, click on Macros (Button on developer tab). BtResistance should be highlighted. Click on run. In earlier versions of excel, click on Run macro (Button on Visual Basic Toolbar). Bt resistance should be highlighted. Click on run. All output of the program is displayed on the worksheet "Output". Note: the program automatically replaces old output. If you are in design mode the program will not work. You can exit design mode using button on Visual Basic Toolbar.

7) On future use of the program, when you open Excel, it will ask you about Macros. Click "Enable Macros" upon opening Excel to use the program.