Attribute VB_Name = "Module7"
' Subroutine Name: PrintColumnHeadings()
' Parameters Byval: numOfLoci, colonyNum
' Description: Displays column headings for results page.
Public Sub PrintColumnHeadings(ByVal numOfLoci, ByVal colonyNum)
' [DECLARE VARIABLES]
Dim row As Integer, column As Integer, count As Integer
' [INITIALISE VARAIBLES]
row = 1
column = 3
Sheets.Add
ActiveSheet.Name = "EzyMate Results - Colony " & colonyArray(colonyNum) ' Names spreadsheet.
ActiveSheet.Cells(row, column).Select ' Selects the desired cell.
For count = 1 To numOfLoci
ActiveCell.HorizontalAlignment = xlCenter
Selection.Font.Bold = True
Selection.ColumnWidth = 18 ' Text size.
ActiveCell.Value = "Locus " & count & ": " & lociArray(count) ' Prints loci names.
ActiveCell.Offset(0, 2).Select
Next count
ActiveCell.HorizontalAlignment = xlCenter
Selection.Font.Bold = True ' Bolds text.
Selection.ColumnWidth = 24
ActiveCell.Value = "Observed No. of workers"
End Sub
' Subroutine Name: PrintTable()
' Parameters Byval: numOfLoci
' Description: Calculates and prints the coefficient of relatedness and the effective/observed
' number of matings.
Public Sub PrintTable(ByVal numOfLoci)
' [DECLARE VARIABLES]
Dim colonySize As Double, effectiveMatings As Double, coeffOfRel As Double
Dim total As Double, proportion() As Double
Dim observedMatings As Integer, count As Integer, adjustment As Integer
' [INITIALISE VARAIBLES]
total = 0
colonySize = ActiveCell.Value
observedMatings = numOfFathers
adjustment = (2 * numOfLoci) + 2 ' where 2 represents the constant column diffence (from the first two columns).
ReDim proportion(1)
' This "block" of code displays the summary table on the results printout.
' The ActiveCell.Offset statements simply pivot from one row or column to the next.
' Where the first dimension refers to the row shift (positive being increasing row)
' and the second dimension referring to the column shift (positive being right).
ActiveCell.Offset(2, -adjustment).Select
ActiveCell.Value = "Sample size:"
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = colonySize
ActiveCell.Offset(1, -1).Select
ActiveCell.Value = "Observed number of matings:"
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = observedMatings
' Calculates the proportion of offspring sired by a particular drone.
For count = 1 To (UBound(numOfOffspringPerFather) - 1)
proportion(count) = (numOfOffspringPerFather(count) / colonySize)
ReDim Preserve proportion(UBound(proportion) + 1)
Next count
For count = 1 To (UBound(proportion) - 1)
' total variable used for equations to solve for relatedness of effective number of matings.
total = total + (proportion(count) ^ 2)
Next count
effectiveMatings = 1 / total
' Displays effective number of matings on results page.
ActiveCell.Offset(1, -1).Select
ActiveCell.Value = "Effective number of matings:"
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = effectiveMatings
' [RE-INITIALISE VARIABLES]
total = 0
For count = 1 To (UBound(proportion) - 1)
' Total is reset to calculate the coefficient of relatedness.
total = total + (proportion(count) * ((0.75 * proportion(count)) + (0.25) * (1 - proportion(count))))
Next count
coeffOfRel = total
ActiveCell.Offset(1, -1).Select
ActiveCell.Value = "Coefficient of relatedness:"
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = coeffOfRel
ActiveCell.Offset(5, -1).Select
pivotRange = ActiveCell.AddressLocal
End Sub