1 ' Model windfarm geometry 2 ' 3 ' Program to compute expected number of turbine encounters 4 ' and expected mortality probability. For a discussion of the 5 ' input and output specifications see the paper ... 6 ' 7 ' We know this program isn't nearly as well structured as it 8 ' deserves to be. We were learning VBA on the fly to write it. 9 ' We've done some refactoring, but there are still way too many 10 ' global variables! 11 ' 12 ' Ethan Bolker 13 ' Jeremy Hatch 14 ' Catalin Zara 15 ' 16 ' Code released under the GNU Public License 17 ' www.gnu.org/licenses/gpl.txt 18 ' 19 ' 20 --------------------------------------------------------------------------- 21 ' Global variables 22 23 ' Strings identifying the various worksheets 24 Public WindfarmName 25 Public Data 26 Public Output 27 Public Graphs 28 Public Scratch 29 Public Temp 30 31 ' Input parameters, in Worksheets(Data) 32 Public RangeDirectionsLeft 33 Public RangeDirectionsRight 34 Public bladeLength As Double 35 Public ProbSafe As Double 36 37 Public TurbineHeight As Double 38 Public VerticalRangeLow As Double 39 Public VerticalRangeHigh As Double 40 41 Public NumberDirections ' number of compass bearings 42 Public StepAngle As Double 43 44 Public WindDirectionsBegin As Double 45 Public WindDirectionsEnd As Double 46 Public WindStepAngle As Double 47 Public NumberWindDirections 48 Public WindDirection ' global variable should be parameter to 49 ComputeVerticalRange 50 Public windCounter ' ditto 51 52 ' Turbine location locations, in Worksheets(WindfarmName) 53 Public TurbCoordStart ' first row containing turbine coordinate 54 Public TurbCoordEnd ' last row containing turbine coordinates 55 Public XColumn ' column with turbine x-coordinate 56 (y-coordinate next) 57 Public YColumn 58 Public NumTurb ' number of turbines 59 60 ' output locations, in Worksheets(Data) 61 Public OutputStartRow As Integer 62 ' first row for angle,counters,prob... 63 Public AngleCol As Integer 64 65 Public VerticalOffset ' adjust when adding documentation rows in 66 Data 67 68 ' output statistics from full vertical range computations in these 69 columns 70 Public RangeCountActual As Integer 71 Public RangeProbActual As Integer 72 Public RangeCountCircular As Integer 73 Public RangeProbCircular As Integer 74 Public MaxNumber As Integer 75 Public MaxProb 76 77 ' output statistics for varying wind direction in these columns 78 Public ThisWindDirection As Integer 79 Public WindCountActual As Integer 80 Public WindProbActual As Integer 81 Public WindCountCircular As Integer 82 Public WindProbCircular As Integer 83 84 ' output columns for original statistics: computations for passage 85 at turbine height 86 Public CountActual 87 Public ProbActual 88 Public CountCircular 89 Public ProbCircular 90 91 Public Radius As Double ' radius of circle containing windfarm 92 93 ' in Worksheets(Scratch) 94 Public ExitPointsStartRow ' where bearings are saved 95 Public ExitPointsColumn 96 97 Public WindPointsStartRow 98 Public WindPointsColumn 99 100 ' globally visible since set inside a function 101 Public AccumNumber 102 Public AccumProb As Double 103 Public AccumArea As Double 104 Public FlightDirection As Double 105 Public DirWorstCase As Double 106 107 ' constants 108 Public Pi As Double 109 110 ' Entry point for the computation 111 112 Private Sub RunEstimate_Click() 113 114 ' Identify worksheets 115 Data = "Data" 116 Scratch = "Scratch" 117 Graphs = "Graphs" 118 Temp = "Temp" 119 120 Pi = 3.14159265358979 121 122 Worksheets(Temp).Range("A1:Z1000").ClearContents 123 Worksheets(Scratch).Range("A1:Z1000").ClearContents 124 ExitPointsStartRow = 6 ' in Scratch 125 ExitPointsColumn = ExitPointsStartRow 126 WindPointsStartRow = 8 127 WindPointsColumn = WindPointsStartRow 128 129 VerticalOffset = 12 130 OutputStartRow = 5 + VerticalOffset 131 With Worksheets(Data) 132 '.Range(OutputStartRow, 1000).ClearContents 133 .Range("H17:Z1000").ClearContents 134 End With 135 136 ' columns for output statistics 137 AngleCol = 8 138 RangeCountActual = 9 139 RangeProbActual = 10 140 RangeCountCircular = 11 141 RangeProbCircular = 12 142 MaxNumber = 13 143 MaxProb = 14 144 145 ' for worst case and debugging - original algorithm 146 CountActual = 22 '16 147 ProbActual = 23 '17 148 CountCircular = 24 '18 149 ProbCircular = 25 '19 150 151 ' for looping over wind directions 152 ThisWindDirection = 16 153 WindCountActual = 17 154 WindProbActual = 18 155 WindCountCircular = 19 156 WindProbCircular = 20 157 158 ' Get the parameters 159 With Worksheets(Data) 160 WindfarmName = .Cells(4 + VerticalOffset, 4).Value 161 162 bladeLength = .Cells(9 + VerticalOffset, 4).Value 163 ProbSafe = .Cells(10 + VerticalOffset, 4).Value 164 RangeDirectionsLeft = .Cells(18 + VerticalOffset, 4).Value 165 RangeDirectionsRight = .Cells(19 + VerticalOffset, 4).Value 166 StepAngle = .Cells(20 + VerticalOffset, 4).Value 167 NumberDirections = (RangeDirectionsRight - 168 RangeDirectionsLeft) / StepAngle + 1 169 170 WindDirectionsBegin = .Cells(13 + VerticalOffset, 4).Value 171 WindDirectionsEnd = .Cells(14 + VerticalOffset, 4).Value 172 WindStepAngle = .Cells(15 + VerticalOffset, 4).Value 173 174 Dim lo As Double 175 Dim hi As Double 176 177 TurbineHeight = .Cells(23 + VerticalOffset, 4).Value 178 lo = .Cells(25 + VerticalOffset, 4).Value 179 VerticalRangeLow = lo - TurbineHeight 180 hi = .Cells(26 + VerticalOffset, 4).Value 181 VerticalRangeHigh = hi - TurbineHeight 182 183 TurbCoordStart = .Cells(5 + VerticalOffset, 4).Value 184 TurbCoordEnd = .Cells(6 + VerticalOffset, 4).Value 185 NumTurb = TurbCoordEnd - TurbCoordStart + 1 186 XColumn = .Cells(7 + VerticalOffset, 4).Value 187 YColumn = 1 + XColumn 188 End With 189 190 ' Copies the original coordinates for the turbines in the range 191 ' of interest to worksheet Temp in range (1,1) to (2,NumTurb) 192 ' changes the coordinates so that they average to 0 193 194 With Worksheets(Temp) 195 SumX = 0 196 SumY = 0 197 For RowCounter = TurbCoordStart To TurbCoordEnd 198 .Cells(1 + RowCounter - TurbCoordStart, 1).Value = _ 199 Worksheets(WindfarmName).Cells(RowCounter, 200 XColumn).Value 201 SumX = SumX + .Cells(1 + RowCounter - TurbCoordStart, 202 1).Value 203 .Cells(1 + RowCounter - TurbCoordStart, 2).Value = _ 204 Worksheets(WindfarmName).Cells(RowCounter, 205 YColumn).Value 206 SumY = SumY + .Cells(1 + RowCounter - TurbCoordStart, 207 2).Value 208 Next 209 AverageX = SumX / NumTurb 210 AverageY = SumY / NumTurb 211 For RowCounter = TurbCoordStart To TurbCoordEnd 212 .Cells(1 + RowCounter - TurbCoordStart, 1).Value = _ 213 .Cells(1 + RowCounter - TurbCoordStart, 1).Value - 214 AverageX 215 .Cells(1 + RowCounter - TurbCoordStart, 2).Value = _ 216 .Cells(1 + RowCounter - TurbCoordStart, 2).Value - 217 AverageY 218 Next 219 End With 220 221 ' Compute radius of surrounding circle and write it to output 222 Radius = ComputeRadius() + bladeLength 223 Worksheets(Data).Cells(28 + VerticalOffset, 4) = Radius 224 225 ' Variables to hold totals for later averages 226 Dim SumAveNumber As Double 227 Dim SumAveProb As Double 228 Dim SumAveNumberCirc As Double 229 Dim SumAveProbCirc As Double 230 231 SumAveNumber = 0 232 SumAveProb = 0 233 SumAveNumberCirc = 0 234 SumAveProbCirc = 0 235 236 ' Loop on the wind directions specified by the user 237 NumberWindDirections = 1 + (WindDirectionsEnd - 238 WindDirectionsBegin) / WindStepAngle 239 For windCounter = 1 To NumberWindDirections 240 windSpot = OutputStartRow - 1 + windCounter 241 localWindDirection = WindDirectionsBegin + (windCounter - 1) * 242 WindStepAngle 243 WindDirection = localWindDirection ' set ugly global variable! 244 245 ' Do the real computations for this wind direction 246 ComputeVerticalRange 247 248 ' Record results for this wind direction 249 With Worksheets(Data) 250 currentCountActual = .Cells(OutputStartRow - 1, 251 RangeCountActual).Value 252 currentProbActual = .Cells(OutputStartRow - 1, 253 RangeProbActual).Value 254 currentCountCircular = .Cells(OutputStartRow - 1, 255 RangeCountCircular).Value 256 currentProbCircular = .Cells(OutputStartRow - 1, 257 RangeProbCircular).Value 258 259 SumAveNumber = SumAveNumber + currentCountActual 260 SumAveProb = SumAveProb + currentProbActual 261 SumAveNumberCirc = SumAveNumberCirc + currentCountCircular 262 SumAveProbCirc = SumAveProbCirc + currentProbCircular 263 264 .Cells(windSpot, ThisWindDirection).Value = 265 localWindDirection 266 .Cells(windSpot, WindCountActual).Value = 267 currentCountActual 268 .Cells(windSpot, WindProbActual).Value = currentProbActual 269 .Cells(windSpot, WindCountCircular).Value = 270 currentCountCircular 271 .Cells(windSpot, WindProbCircular).Value = 272 currentProbCircular 273 End With 274 Next windCounter 275 276 ' Finished looping on wind directions, so compute and record averages 277 With Worksheets(Data) 278 .Cells(OutputStartRow - 1, WindCountActual).Value = _ 279 SumAveNumber / NumberWindDirections 280 .Cells(OutputStartRow - 1, WindProbActual).Value = _ 281 SumAveProb / NumberWindDirections 282 .Cells(OutputStartRow - 1, WindCountCircular).Value = _ 283 SumAveNumberCirc / NumberWindDirections 284 .Cells(OutputStartRow - 1, WindProbCircular).Value = _ 285 SumAveProbCirc / NumberWindDirections 286 End With 287 288 ' Call the subroutine that uses the original algorithm to compute 289 ' counts and probabilities at turbine height parallel to wind. 290 ' Use output for worst case analysis and debugging. 291 ComputeAtTurbineHeight 292 293 ' Create charts 294 PlotTurbines 295 PlotCounter ' by flight direction 296 PlotProbability ' by flight direction 297 PlotCounterByWindDirection 298 PlotProbabilityByWindDirection 299 300 'CreateEncounterChart 301 End Sub 302 303 ' For each angle (direction) find the average probability of safe passage 304 ' and average number of turbines encountered when flying in that direction 305 ' through the vertical range. 306 ' 307 ' Uses global variable WindDirection as an input parameter 308 309 Sub ComputeVerticalRange() 310 311 Dim wind As Double 312 If WindDirection > 360 Then 313 wind = WindDirection 314 With Worksheets(Scratch) 315 .Cells(WindPointsStartRow, WindPointsColumn).Value = 0 316 .Cells(WindPointsStartRow, 1 + WindPointsColumn).Value = 0 317 End With 318 Else ' convert to radians 319 wind = (90 - WindDirection) * Pi / 180 320 ' Generate wind points for this wind direction, for graphing 321 later 322 With Worksheets(Scratch) 323 .Cells(WindPointsStartRow + windCounter - 1, 324 WindPointsColumn).Value = _ 325 Radius * cos(wind) * 1.1 326 .Cells(WindPointsStartRow + windCounter - 1, 1 + 327 WindPointsColumn).Value = _ 328 Radius * sin(wind) * 1.1 329 End With 330 End If 331 332 ' Variables to hold totals for later averages 333 Dim SumAveNumber As Double 334 Dim SumAveProb As Double 335 Dim SumAveNumberCirc As Double 336 Dim SumAveProbCirc As Double 337 338 SumAveNumber = 0 339 SumAveProb = 0 340 SumAveNumberCirc = 0 341 SumAveNumberProb = 0 342 343 ' loop on specified flight directions 344 For dirCounter = 1 To NumberDirections 345 346 Dim theta As Double 347 Dim angle As Double 348 349 spot = OutputStartRow - 1 + dirCounter ' row index in output 350 for this direction 351 352 ' Generate theta in radians from input in degrees East of 353 North 354 angle = RangeDirectionsLeft + (dirCounter - 1) * StepAngle 355 FlightDirection = angle 356 theta = (90 - angle) * Pi / 180 357 358 ' Generate exit points for this direction, for graphing later 359 With Worksheets(Scratch) 360 .Cells(ExitPointsStartRow + dirCounter - 1, 361 ExitPointsColumn).Value = _ 362 Radius * cos(theta) 363 .Cells(ExitPointsStartRow + dirCounter - 1, 1 + 364 ExitPointsColumn).Value = _ 365 Radius * sin(theta) 366 End With 367 368 Dim startRow As Integer ' first row for projection output data 369 in scratch worksheet 370 Dim col As Integer ' column for projection output data 371 Dim endRow As Integer ' last row for projection output data 372 startRow = 1 373 col = 1 374 endRow = startRow + 2 * NumTurb - 1 ' two rows per turbine 375 376 ' Find the projections of ends of turbines onto a direction 377 ' orthogonal to the current direction, store results in 378 ' Scratch worksheet 379 ' starting at indicated row and column, then sort. 380 ' 381 ' I don't understand why Project has to be a function, but it 382 is ... 383 'foo = Project(theta, wind, bladeLength, startRow, col, 384 endRow) 385 386 ' compression factor shortens bladeLength when flight 387 ' direction isn't parallel to wind direction 388 389 Dim compression As Double 390 If wind > 360 Then 391 compression = 1 ' don't take wind into account 392 Else 393 compression = Abs(cos(theta - wind)) 394 End If 395 foo = Project(theta, compression, bladeLength, startRow, col, 396 endRow) 397 398 ' Find interval containing projections in current direction 399 ' and the percentage of the circular area represented by 400 ' the actual wind farm area in that direction. 401 402 With Worksheets(Scratch) 403 HghLeftEndProj = .Cells(startRow, col).Value 404 HghRightEndProj = .Cells(endRow, col).Value 405 End With 406 407 ' Correction term since at the endpoints of the projection 408 ' we don't see a whole blade. 409 Dim correction As Double 410 correction = 2 * (1 - compression) * bladeLength 411 412 HghLengthProj = HghRightEndProj - HghLeftEndProj + correction 413 PercentRegion = HghLengthProj / (2 * Radius) 414 415 ' Compute the average number of turbines encountered and 416 ' the average probability of safe passage for current 417 direction 418 DirWorstCase = 0 419 NumberActiveTurb = 0 420 AccumNumber = 0 421 AccumProb = 0 422 AccumArea = 0 423 Length = correction ' start here 424 425 ' For each subinterval, from left to right, counted 426 ' by the index of the right endpoint of the subinterval 427 428 Dim active As Integer ' number of currently encountered 429 turbines 430 Dim entryIndex As Integer ' index to position of last 431 increase in active 432 Dim i As Integer ' index for loop through state 433 changes 434 Dim LengthSubinterval As Double 435 436 entryIndex = 0 ' count turbines as you encounter their 437 projections 438 active = 0 ' number of turbines whose projection you are 439 currently in 440 LengthSubinterval = 0 441 For i = 1 To 2 * NumTurb 442 With Worksheets(Scratch) 443 If i > 1 Then 444 LengthSubinterval = .Cells(i, 1).Value - .Cells(i - 445 1, 1).Value 446 Length = Length + LengthSubinterval 447 End If 448 If .Cells(i, 2).Value > 0 Then 'encountering another 449 turbine 450 active = active + 1 451 entryIndex = entryIndex + 1 452 .Cells(entryIndex, 3).Value = .Cells(i, 1).Value 453 'DirWorstCase = WorksheetFunction.Max(active, 454 DirWorstCase) 455 Else ' update accumulated totals for turbine currently ending 456 foo = UpdateStatsForTurbine(active, i, entryIndex, 457 _ 458 VerticalRangeLow, VerticalRangeHigh, 459 compression) 460 active = active - 1 461 End If 462 End With 463 Next i ' Finished collecting the data for one direction 464 465 Dim area As Double 466 If VerticalRangeHigh = VerticalRangeLow Then 467 ' all flights at one height, so area is a length 468 area = Length 469 Else 470 area = (VerticalRangeHigh - VerticalRangeLow) * Length 471 End If 472 473 'For debugging 474 'Worksheets(Data).Cells(23, 4) = area 475 'Worksheets(Data).Cells(24, 4) = AccumArea ' unsafe paths 476 477 ' Update accumulated probability of safe passage by adding in 478 ' the safe passage through areas where no turbines have been 479 ' encountered. 480 AccumProb = AccumProb + (area - AccumArea) 481 482 ' Compute the average number of turbines encountered in the 483 ' current direction as the sum of all the accumulations, 484 ' divided by the area of the projection 485 ' of the ACTUAL windfarm region 486 487 Dim AveNumber As Double 488 AveNumber = AccumNumber / area 489 SumAveNumber = SumAveNumber + AveNumber 490 491 ' Compute the average probability of mortality in the current 492 ' direction as 1 - the sum of all the safe accumulations, 493 ' divided by the area of the projection of the ACTUAL windfarm region 494 Dim AveProb As Double 495 AveProb = 1 - (AccumProb / area) 496 SumAveProb = SumAveProb + AveProb 497 498 ' Compute the average number of turbines encountered in the 499 current direction 500 ' as the sum of all the accumulations, divided by the 501 length of the projection of the 502 ' CIRCULAR region that encompasses the windfarm 503 Dim AveNumberCirc As Double 504 AveNumberCirc = AveNumber * PercentRegion 505 SumAveNumberCirc = SumAveNumberCirc + AveNumberCirc 506 507 ' Compute the average probability of safe passage in the 508 ' current direction as 1 - the sum of all the accumulations, 509 ' divided by the length of the projection of the 510 ' CIRCULAR region that encompasses the windfarm 511 Dim AveProbCirc As Double 512 AveProbCirc = PercentRegion * AveProb 513 SumAveProbCirc = SumAveProbCirc + AveProbCirc 514 515 foo = WriteOutput(spot, angle, AveNumber, AveProb, 516 AveNumberCirc, _ 517 AveProbCirc, DirWorstCase, 1 - ProbSafe ^ 518 DirWorstCase) 519 520 ' Clear the intermediate results 521 'Worksheets(Scratch).Range("A:C").ClearContents 522 523 Next dirCounter 524 525 ' Finished looping on flight directions, 526 ' so compute and record averages 527 With Worksheets(Data) 528 .Cells(OutputStartRow - 1, RangeCountActual).Value = _ 529 SumAveNumber / NumberDirections 530 .Cells(OutputStartRow - 1, RangeProbActual).Value = _ 531 SumAveProb / NumberDirections 532 .Cells(OutputStartRow - 1, RangeCountCircular).Value = _ 533 SumAveNumberCirc / NumberDirections 534 .Cells(OutputStartRow - 1, RangeProbCircular).Value = _ 535 SumAveProbCirc / NumberDirections 536 End With 537 538 539 End Sub 540 541 Function WriteOutput(spot, angle As Double, AveNumber As Double, _ 542 AveProb As Double, AveNumberCirc As Double, _ 543 AveProbCirc As Double, maxEncounters, _ 544 maxMortality As Double) 545 With Worksheets(Data) 546 .Cells(spot, AngleCol).Value = angle 547 .Cells(spot, RangeCountActual).Value = AveNumber 548 .Cells(spot, RangeProbActual).Value = AveProb 549 .Cells(spot, RangeCountCircular).Value = AveNumberCirc 550 .Cells(spot, RangeProbCircular).Value = AveProbCirc 551 .Cells(spot, MaxNumber).Value = maxEncounters 552 .Cells(spot, MaxProb).Value = maxMortality 553 End With 554 WriteOutput = 100 555 End Function 556 557 ' Called when state change is exiting from an interval when number of 558 ' active turbines decreases. 559 ' 560 ' Compute the contributions to three integrals: 561 ' survival probability, 562 ' total number of encounters 563 ' total area for which number of encounters > 0 564 ' from this point back to the entry points for all the currently 565 ' active turbines. 566 ' 567 ' This is a tricky recursive call with additions and subtractions. 568 ' The (modified) inclusion exclusion argument allows us to compute the 569 ' weights (areas) of relatively simple figures: regions between two 570 ' overlapping circles of equal radii. 571 ' 572 ' If you're interested you can provide your own mathematical proof 573 ' that this algorithm works. We've checked enough sufficiently general 574 ' special cases both by hand and with this program. 575 576 Function UpdateStatsForTurbine(active As Integer, _ 577 exitIndex As Integer, _ 578 entryIndex As Integer, _ 579 lo As Double, hi As Double, _ 580 compression As Double) 581 Dim weight As Double ' contribution from entryIndex to exitIndex 582 Dim entrySpot As Double 583 Dim exitSpot As Double 584 585 With Worksheets(Scratch) 586 entrySpot = .Cells(entryIndex, 3).Value 587 exitSpot = .Cells(exitIndex, 1).Value 588 weight = ComputeEllipticalWeight(bladeLength, exitSpot - 589 entrySpot, _ 590 lo, hi, compression) 591 End With 592 593 If weight > 0 Then 594 DirWorstCase = WorksheetFunction.Max(active, DirWorstCase) 595 End If 596 AccumNumber = AccumNumber + active * weight 597 AccumProb = AccumProb + (ProbSafe ^ active) * weight 598 AccumArea = AccumArea + weight 599 If active > 2 Then ' Adjust blade length to account for wind 600 direction 601 AccumNumber = AccumNumber + (active - 2) * weight 602 AccumProb = AccumProb + (ProbSafe ^ (active - 2)) * weight 603 AccumArea = AccumArea + weight 604 End If 605 If active > 1 Then 606 AccumNumber = AccumNumber - 2 * (active - 1) * weight 607 AccumProb = AccumProb - 2 * (ProbSafe ^ (active - 1)) * weight 608 AccumArea = AccumArea - 2 * weight 609 foo = UpdateStatsForTurbine(active - 1, exitIndex, entryIndex 610 - 1, lo, hi, compression) ' recursive call 611 End If 612 UpdateStatsForTurbine = 0 ' why not? 613 End Function 614 615 ' Compute the area between circles of radius r, centers separated by 616 ' delta between distances lo and hi from line joining centers. 617 ' 618 ' Required area is a twice a (wedge + two triangles - a rectangle). 619 ' (picture here) 620 621 Function ComputeWeight(r As Double, delta As Double, _ 622 lo As Double, hi As Double) 623 624 Dim rSquared As Double 625 Dim c As Double 626 Dim h As Double 627 Dim a As Double 628 Dim b As Double 629 630 rSquared = r * r 631 c = 0.5 * delta 632 h = Sqr(2 * r * c - c * c) 633 a = WorksheetFunction.Max(lo, -h) 634 b = WorksheetFunction.Min(hi, h) 635 636 If a = b Then ' flight at constant height so weight is length 637 If a >= h Or b <= -h Then 638 ComputeWeight = 0 639 Else 640 ComputeWeight = 2 * Sqr(rSquared - a * a) 641 End If 642 643 Else ' flight through a range of vertical heights so weight is an 644 area 645 Dim wedge As Double 646 Dim triA As Double 647 Dim triB As Double 648 Dim rect As Double 649 Dim aOverr As Double 650 Dim bOverr As Double 651 652 If a >= h Or b <= -h Then 653 ComputeWeight = 0 654 Else 655 ' make sure a/r and b/r are between -1 and 1. Roundoff 656 ' error might have destroyed this necessary property 657 aOverr = WorksheetFunction.Max(a / r, -1) 658 aOverr = WorksheetFunction.Min(aOverr, 1) 659 bOverr = WorksheetFunction.Max(b / r, -1) 660 bOverr = WorksheetFunction.Min(bOverr, 1) 661 662 wedge = 0.5 * rSquared * _ 663 (WorksheetFunction.Asin(bOverr) - 664 WorksheetFunction.Asin(aOverr)) 665 666 ' the max in the next two lines should be unnecessary 667 ' since r is at least as large as a or b, but roundoff 668 ' error(?) seems 669 ' to lead occasionally to a negative number when r=b 670 triB = 0.5 * b * Sqr(WorksheetFunction.Max(0, rSquared - b* b)) 671 triA = 0.5 * a * Sqr(WorksheetFunction.Max(0, rSquared - a* a)) 672 rect = (b - a) * (r - c) 673 ComputeWeight = 2 * (wedge + triB - triA - rect) 674 End If 675 676 End If 677 678 End Function 679 680 ' Calculate the area between two ellipses with vertical (major) axis 681 ' 2r, horizontal (minor) axis 2r/compression, horizontal distance 682 ' between centers delta. 683 ' 684 ' algorithm: divide delta by compression to convert the ellipses to 685 ' circles, call the function that computes the area of intersection 686 ' of circles multiply by compression 687 688 Function ComputeEllipticalWeight(r As Double, delta As Double, _ 689 lo As Double, hi As Double, compression As 690 Double) 691 If compression = 0 Then 692 ComputeEllipticalWeight = 0 693 Else 694 ComputeEllipticalWeight = _ 695 compression * ComputeWeight(r, delta / compression, lo, 696 hi) 697 End If 698 699 End Function 700 701 702 ' Original code here, pretty much unchanged. Leave it in place for 703 ' testing. 704 705 Sub ComputeAtTurbineHeight() 706 707 ' Generate the data 708 For dirCounter = 1 To NumberDirections 709 710 Dim theta As Double 711 712 spot = OutputStartRow - 1 + dirCounter ' row index for this 713 direction 714 715 ' Generate angle in degrees East of North (convert from radians) 716 angle = RangeDirectionsLeft + (dirCounter - 1) * StepAngle 717 theta = (90 - angle) * Pi / 180 718 719 ' write to spreadsheet so user sees progress (except on mac!) 720 With Worksheets(Data) 721 .Cells(spot, AngleCol).Value = angle 722 '.Cells(29, 3).Value = dirCounter 723 End With 724 725 ' Generate exit points for this direction, for graphing later 726 With Worksheets(Scratch) 727 .Cells(ExitPointsStartRow + dirCounter - 1, 728 ExitPointsColumn).Value = Radius * cos(theta) 729 .Cells(ExitPointsStartRow + dirCounter - 1, 1 + 730 ExitPointsColumn).Value = Radius * sin(theta) 731 End With 732 733 ' Initialize values for 734 ' average number of turbines hit in the current direction 735 ' average probability of safe passage in the current direction 736 ' maximum number of turbines hit in the current direction 737 738 AccumProbByDirection = 0 739 AccumNumByDirection = 0 740 AccumNumByDirectionCirc = 0 741 AccumProbByDirectionCirc = 0 742 743 DirWorstCase = 0 744 TotLengthByDirection = 0 745 TotLengthByDirectionCirc = 0 746 747 ' For each angle, find the average probability of safe passage 748 ' and average number of turbines encountered when flying in that 749 ' direction. 750 751 Dim startRow As Integer ' first row for projection output data 752 in scratch worksheet 753 Dim col As Integer ' column for projection output data 754 Dim endRow As Integer ' last row for projection output data 755 startRow = 1 756 col = 1 757 endRow = startRow + 2 * NumTurb - 1 ' two rows per turbine 758 759 ' Find the projections of ends of turbines at current height 760 ' onto a direction orthogonal to the current direction, store 761 ' results in Scratch worksheet 762 ' starting at indicated row and column, then sort. 763 ' 764 ' I don't understand why Project has to be a function, but it is ... 765 foo = OriginalProject(theta, bladeLength, startRow, col, 766 endRow) 767 768 ' Find interval containing projections at current height and 769 ' direction and the percentage of the circular area 770 ' represented by the actual wind farm area at that height 771 772 With Worksheets(Scratch) 773 HghLeftEndProj = .Cells(startRow, col).Value 774 HghRightEndProj = .Cells(endRow, col).Value 775 End With 776 777 HghLengthProj = HghRightEndProj - HghLeftEndProj 778 PercentRegion = HghLengthProj / (2 * Radius) 779 780 ' Compute the average number of turbines encountered and 781 ' the average probability of safe passage 782 ' for current direction and current height 783 784 NumberActiveTurb = 0 785 AccumNumber = 0 786 AccumProb = 0 787 Length = 0 788 789 HghSumProb = 0 790 HghSumNum = 0 791 792 ' totLength = 0 793 794 ' For each subinterval, from left to right, counted by the 795 ' index of the right endpoint of the subinterval 796 ' 797 For i = 2 To 2 * NumTurb 798 ' If the left endpoint of the subinterval is a projection 799 of the left edge of a rotor, 800 ' then increase the number of active turbines by 1 801 ' If the left endpoint of the subinterval is a projection 802 of the right edge of a rotor, 803 ' then decrease the number of active turbines by 1 804 ' 805 With Worksheets(Scratch) 806 LengthSubinterval = .Cells(i, 1).Value - .Cells(i - 1, 807 1).Value 808 NumberActiveTurb = NumberActiveTurb + .Cells(i - 1, 809 2).Value 810 End With 811 812 ' If the number of active turbines is more then the 813 ' current maximum for all heights, 814 ' then make this number the new maximum 815 DirWorstCase = WorksheetFunction.Max(NumberActiveTurb, 816 DirWorstCase) 817 818 ' Update the length at the projection at the given height 819 Length = Length + LengthSubinterval 820 821 ' The probability of safe passage on the subinterval is 822 ' (ProbSafe for one turbine)^(number of active turbines) 823 ' The contribution to the average probability at this 824 ' height is ProbSafe for the subinterval * the length of the 825 ' subinterval 826 AccumProb = AccumProb + (ProbSafe ^ NumberActiveTurb) * 827 LengthSubinterval 828 829 ' The contribution to the average number of active 830 turbines at this height is 831 ' number of active turbines * the length of the 832 subinterval 833 AccumNumber = AccumNumber + NumberActiveTurb * 834 LengthSubinterval 835 836 Next i ' Go to the next subinterval 837 838 ' Finished collecting the data for one height and one direction 839 840 ' Compute the average number of turbines encountered at the 841 ' current height and direction as the sum of all the 842 ' accumulations, divided by the length of the projection of the 843 ' ACTUAL windfarm region 844 AveNumber = AccumNumber / Length 845 846 ' Compute the average probability of safe passage at the 847 ' current height and direction as the sum of all the 848 ' accumulations, divided by the length of the projection of the 849 ' ACTUAL windfarm region 850 AveProb = AccumProb / Length 851 852 ' Compute the average number of turbines encountered at the 853 ' current height and direction as the sum of all the 854 ' accumulations, divided by the length of the projection of the 855 ' CIRCULAR region that encompasses the windfarm 856 AveNumberCirc = AveNumber * PercentRegion 857 858 ' Compute the average probability of safe passage at the 859 ' current height and direction as the sum of all the 860 ' accumulations, divided by the length of the projection of the 861 ' CIRCULAR region that encompasses the windfarm 862 AveProbCirc = 1 - PercentRegion * (1 - AveProb) 863 864 ' Updates the total length of the projection of the 865 ' ACTUAL wind farm active area at all heights 866 TotLengthByDirection = TotLengthByDirection + Length 867 868 ' Updates the contribution of the given height to the 869 ' computation of 870 ' the probability at all heights relative to the ACTUAL area 871 ' AccumProbByDirection = AccumProbByDirection + AccumProb 872 AccumProbByDirection = AccumProb 873 874 ' Updates the contribution of the given height to the 875 ' computation of the average number of active turrbines at all 876 ' heights relative to the ACTUAL area 877 'AccumNumByDirection = AccumNumByDirection + AccumNumber 878 AccumNumByDirection = AccumNumber 879 880 ' Updates the total length of the projection of the 881 ' CIRCULAR area that encompasses the wind farm 882 TotLengthByDirectionCirc = TotLengthByDirectionCirc + 2 * 883 Radius 884 885 ' Updates the contribution of the given height to the 886 ' computation of the probability at all heights relative to 887 ' the CIRCULAR area 888 AccumProbByDirectionCirc = _ 889 AccumProbByDirectionCirc + 2 * Radius * AveProbCirc 890 891 ' Updates the contribution of the given height to the 892 'computation of the average number of active turrbines at all 893 ' heights relative to the CIRCULAR area 894 AccumNumByDirectionCirc = AccumNumByDirectionCirc + 895 AccumNumber 896 897 ' Clear the intermediate results 898 Worksheets(Scratch).Range("A:C").ClearContents 899 900 901 ' Finished computing the data for all heights in a given direction 902 903 AveNumByDirection = AccumNumByDirection / TotLengthByDirection 904 AveProbByDirection = AccumProbByDirection / TotLengthByDirection 905 AveNumByDirectionCirc = AccumNumByDirection / 906 TotLengthByDirectionCirc 907 AveProbByDirectionCirc = AccumProbByDirectionCirc / 908 TotLengthByDirectionCirc 909 910 With Worksheets(Data) 911 .Cells(spot, CountActual).Value = AveNumByDirection 912 .Cells(spot, ProbActual).Value = 1 - AveProbByDirection 913 .Cells(spot, CountCircular).Value = AveNumByDirectionCirc 914 .Cells(spot, ProbCircular).Value = 1 - AveProbByDirectionCirc 915 '.Cells(spot, 13).Value = DirWorstCase 916 '.Cells(spot, 14).Value = ProbSafe ^ DirWorstCase 917 End With 918 919 Next dirCounter 920 921 End Sub 922 923 ' Compute radius of smallest circle centered at (0,0) containing 924 ' turbines. 925 ' 926 ' If we get fancy some day we could compute the smallest containing 927 ' circle. That's a tricky problem in computational geometry with a 928 ' known solution. 929 930 ' Comment by CZ on 9/28/06: This version using original coordinates 931 ' replaced by reference to worksheet Temp containing normalized 932 ' coordinates for the desired range of turbines. 933 ' 934 ' Function ComputeRadius() 935 ' r2SoFar = 0 936 ' For counter = TurbCoordStart To TurbCoordEnd 937 ' x = Worksheets(WindfarmName).Cells(counter, XColumn).Value 938 ' y = Worksheets(WindfarmName).Cells(counter, YColumn).Value 939 ' r2SoFar = WorksheetFunction.Max(r2SoFar, x * x + y * y) 940 ' Next counter 941 ' ComputeRadius = Sqr(r2SoFar) 942 ' End Function 943 ' End of comment by CZ 944 945 Function ComputeRadius() 946 r2SoFar = 0 947 For counter = 1 To NumTurb 948 x = Worksheets(Temp).Cells(counter, 1).Value 949 y = Worksheets(Temp).Cells(counter, 2).Value 950 r2SoFar = WorksheetFunction.Max(r2SoFar, x * x + y * y) 951 Next counter 952 ComputeRadius = Sqr(r2SoFar) 953 End Function 954 955 ' Find the projections of ends of turbines (using blade length 956 ' argument) onto a direction orthogonal to direction theta. 957 ' Store results in Scratch worksheet starting at indicated row and 958 ' column, then sort. 959 ' 960 ' I don't understand why Project has to be a function, but it is ... 961 962 Function Project(theta As Double, compression As Double, bladeLength 963 As Double, _ 964 startRow As Integer, col As Integer, endRow As Integer) 965 966 ' Modified by CZ on 9/28/06 967 ' 968 969 Dim s As Double 970 Dim c As Double 971 s = sin(theta) 972 c = cos(theta) 973 startIndex = startRow - 1 ' so looping is cleaner 974 975 ' Adjust blade length to account for wind direction 976 'If wind > 360 Then ' no wind direction specified 977 ' bl = bladeLength 978 'Else 979 ' bl = bladeLength * Abs(cos(theta - wind)) 980 'End If 981 bl = compression * bladeLength 982 983 For projCounter = 1 To NumTurb 984 985 ' Find projection of center 986 x = Worksheets(Temp).Cells(projCounter, 1).Value 987 y = Worksheets(Temp).Cells(projCounter, 2).Value 988 center = x * s - y * c 989 990 991 ' Find projections of blade ends, store results 992 ' in odd rows the first number is the position of the left 993 ' endpoint of the projection 994 ' the second number is +1 to encode the left endpoint 995 ' since passing over that coordinate increases the 996 ' number of active projections by 1 997 ' in even rows 998 ' the first number is the position of the right endpoint of 999 ' the projection 1000 ' the second number is -1 to encode the right endpoint 1001 ' since passing over that coordinate decreases the 1002 ' number of active projections by 1 1003 1004 With Worksheets(Scratch) 1005 spot = 2 * projCounter 1006 .Cells(startIndex + spot - 1, col).Value = center - bl 1007 .Cells(startIndex + spot - 1, 1 + col).Value = 1 1008 .Cells(startIndex + spot, col).Value = center + bl 1009 .Cells(startIndex + spot, 1 + col).Value = -1 1010 End With 1011 Next projCounter 1012 1013 ' sort 1014 With Worksheets(Scratch) 1015 .Range(.Cells(startRow, col), _ 1016 .Cells(endRow, 1 + col)).Sort _ 1017 Key1:=.Columns(col), Key2:=.Columns(1 + col), 1018 Order2:=xlDescending 1019 End With 1020 Project = 100 ' need to return something - this is garbage 1021 1022 End Function 1023 1024 ' Original code to project turbine diameters. This code is called from 1025 ' ComputeAtTurbineHeight. It does not take wind direction into account. 1026 1027 Function OriginalProject(theta As Double, bladeLength As Double, _ 1028 startRow As Integer, col As Integer, endRow As Integer) 1029 1030 Dim s As Double 1031 Dim c As Double 1032 s = sin(theta) 1033 c = cos(theta) 1034 startIndex = startRow - 1 ' so looping is cleaner 1035 1036 For turbcounter = TurbCoordStart To TurbCoordEnd 1037 projCounter = turbcounter - TurbCoordStart + 1 1038 1039 ' Find projection of center 1040 x = Worksheets(WindfarmName).Cells(turbcounter, 1).Value 1041 y = Worksheets(WindfarmName).Cells(turbcounter, 2).Value 1042 center = x * s - y * c 1043 1044 1045 ' Find projections of blade ends, store results 1046 ' in odd rows 1047 ' the first number is the position of the left endpoint of 1048 ' the projection 1049 ' the second number is +1 to encode the left endpoint 1050 ' since passing over that coordinate increases the 1051 ' number of active projections by 1 1052 ' in even rows 1053 ' the first number is the position of the right endpoint of 1054 ' the projection 1055 ' the second number is -1 to encode the right endpoint 1056 ' since passing over that coordinate decreases the 1057 ' number of active projections by 1 1058 1059 With Worksheets(Scratch) 1060 spot = 2 * projCounter 1061 .Cells(startIndex + spot - 1, col).Value = center - 1062 bladeLength 1063 .Cells(startIndex + spot - 1, 1 + col).Value = 1 1064 .Cells(startIndex + spot, col).Value = center + bladeLength 1065 .Cells(startIndex + spot, 1 + col).Value = -1 1066 End With 1067 Next turbcounter 1068 1069 ' sort 1070 With Worksheets(Scratch) 1071 .Range(.Cells(startRow, col), _ 1072 .Cells(endRow, 1 + col)).Sort _ 1073 Key1:=.Columns(col), Key2:=.Columns(1 + col), 1074 Order2:=xlDescending 1075 End With 1076 OriginalProject = 100 ' need to return something - this is garbage 1077 1078 1079 End Function 1080 1081 1082 ' Edit the chart that plots the locations of the turbines 1083 1084 Sub PlotTurbines() 1085 1086 With Worksheets(Graphs) 1087 .ChartObjects("Chart 1").Activate 1088 End With 1089 1090 ' data for turbine positions 1091 ' Modified by CZ on 9/28/06 1092 With Worksheets(Temp) 1093 ActiveChart.SeriesCollection(1).XValues = _ 1094 .Range(.Cells(1, 1), .Cells(NumTurb, 1)) 1095 ActiveChart.SeriesCollection(1).Values = _ 1096 .Range(.Cells(1, 2), .Cells(NumTurb, 2)) 1097 End With 1098 ' End of changes by CZ 9/28/06 1099 1100 ' data for the flight directions on the circle 1101 With Worksheets(Scratch) 1102 lastRow = ExitPointsStartRow + NumberDirections - 1 1103 ActiveChart.SeriesCollection(2).XValues = _ 1104 .Range(.Cells(ExitPointsStartRow, ExitPointsColumn), 1105 .Cells(lastRow, ExitPointsColumn)) 1106 ActiveChart.SeriesCollection(2).Values = _ 1107 .Range(.Cells(ExitPointsStartRow, 1 + ExitPointsColumn), 1108 .Cells(lastRow, 1 + ExitPointsColumn)) 1109 End With 1110 1111 ' data for the wind directions 1112 With Worksheets(Scratch) 1113 lastRow = WindPointsStartRow + NumberWindDirections - 1 1114 ActiveChart.SeriesCollection(3).XValues = _ 1115 .Range(.Cells(WindPointsStartRow, WindPointsColumn), 1116 .Cells(lastRow, WindPointsColumn)) 1117 ActiveChart.SeriesCollection(3).Values = _ 1118 .Range(.Cells(WindPointsStartRow, 1 + WindPointsColumn), 1119 .Cells(lastRow, 1 + WindPointsColumn)) 1120 End With 1121 1122 Dim localRadius As Double 1123 Dim log10Radius As Double 1124 Dim bound As Double 1125 1126 localRadius = 1.1 * Radius 'to get far enough out for wind 1127 ' direction marks 1128 log10Radius = Math.Log(localRadius) / Math.Log(10) 1129 'zeroCount = Math.Round(log10Radius) - 1 ' No Round in macintosh 1130 ' Math library! 1131 zeroCount = WorksheetFunction.Round(log10Radius, 0) - 1 1132 tickMark = 10 ^ zeroCount 1133 topTick = (1 + WorksheetFunction.Round(localRadius / tickMark, 0)) 1134 * tickMark 1135 1136 ActiveChart.PlotArea.Select 1137 ActiveChart.Axes(xlValue).Select 1138 With ActiveChart.Axes(xlValue) 1139 .MinimumScale = -topTick 1140 .MaximumScale = topTick 1141 .MinorUnit = 2 * tickMark 1142 .MajorUnit = 2 * tickMark 1143 End With 1144 ActiveChart.Axes(xlCategory).Select 1145 With ActiveChart.Axes(xlCategory) 1146 .MinimumScale = -topTick 1147 .MaximumScale = topTick 1148 .MinorUnit = 2 * tickMark 1149 .MajorUnit = 2 * tickMark 1150 End With 1151 1152 End Sub 1153 1154 ' Edit the chart that plots the expected number of turbines 1155 ' encountered as a function of direction 1156 Sub PlotCounter() 1157 1158 Dim maxEncounters As Double 1159 1160 With Worksheets(Graphs) 1161 .ChartObjects("Chart 3").Activate 1162 End With 1163 1164 Dim lastRow As Integer 1165 lastRow = OutputStartRow + NumberDirections - 1 1166 foo = SetDataRanges(OutputStartRow, lastRow, AngleCol, _ 1167 RangeCountActual, RangeCountCircular) 1168 1169 With Worksheets(Data) 1170 maxEncounters = _ 1171 WorksheetFunction.Max(.Range(.Cells(OutputStartRow, 1172 RangeCountActual), _ 1173 .Cells(lastRow, 1174 RangeCountActual))) 1175 End With 1176 1177 With ActiveChart.Axes(xlValue) 1178 topTick = WorksheetFunction.RoundUp(maxEncounters, 0) 1179 .MinimumScale = 0 1180 .MaximumScale = topTick 1181 .MinorUnit = 0.1 1182 .MajorUnit = 0.2 1183 End With 1184 1185 ComputeTickSpacing (StepAngle) 1186 1187 Dim windString As String 1188 If WindDirection > 360 Then 1189 windString = "Parallel to Flight Direction" 1190 Else 1191 windString = CStr(WindDirection) 1192 End If 1193 1194 ActiveChart.ChartArea.Select 1195 With ActiveChart 1196 .HasTitle = True 1197 .ChartTitle.Characters.Text = _ 1198 "Average Number of Turbines Encountered (by Flight Direction) 1199 " + _ 1200 "Wind Direction " + windString 1201 End With 1202 1203 End Sub 1204 Function ComputeTickSpacing(angle As Double) 1205 Dim tickSpacing As Integer 1206 If angle < 5 Then 1207 tickSpacing = 5 1208 Else 1209 tickSpacing = 1 1210 End If 1211 With ActiveChart.Axes(xlCategory) 1212 .CrossesAt = 1 1213 .TickLabelSpacing = 2 * tickSpacing 1214 .TickMarkSpacing = tickSpacing 1215 .AxisBetweenCategories = False 1216 .ReversePlotOrder = False 1217 End With 1218 ComputeTickSpacing = tickSpacing 1219 End Function 1220 1221 Function ComputeBoundsAndScale(maxMortality As Double) 1222 Dim topTick As Double 1223 Dim lowTick As Double 1224 Dim interval As Double 1225 Dim diff As Double 1226 ' should do the next branch with logs 1227 If maxMortality < 0.01 Then 1228 topTick = WorksheetFunction.RoundUp(maxMortality, 2) 1229 Else 1230 topTick = WorksheetFunction.RoundUp(maxMortality, 1) 1231 End If 1232 lowTick = 0 1233 diff = (topTick - lowTick) / 10 1234 interval = WorksheetFunction.RoundUp(diff, 3) 1235 With ActiveChart.Axes(xlValue) 1236 .MinimumScale = lowTick 1237 .MaximumScale = topTick 1238 If interval > 0 Then 1239 .MinorUnit = interval 1240 .MajorUnit = interval 1241 End If 1242 End With 1243 End Function 1244 1245 1246 Function SetDataRanges(startRow As Integer, lastRow As Integer, _ 1247 xCol As Integer, values1 As Integer, values2 As 1248 Integer) 1249 With Worksheets(Data) 1250 ActiveChart.SeriesCollection(1).XValues = _ 1251 .Range(.Cells(startRow, xCol), .Cells(lastRow, xCol)) 1252 ActiveChart.SeriesCollection(1).Values = _ 1253 .Range(.Cells(startRow, values1), .Cells(lastRow, 1254 values1)) 1255 ActiveChart.SeriesCollection(2).XValues = _ 1256 .Range(.Cells(startRow, xCol), .Cells(lastRow, xCol)) 1257 ActiveChart.SeriesCollection(2).Values = _ 1258 .Range(.Cells(startRow, values2), .Cells(lastRow, 1259 values2)) 1260 End With 1261 SetDataRanges = 0 1262 End Function 1263 1264 1265 1266 ' Edit the chart that plots the mortality probability 1267 ' as a function of direction 1268 1269 Sub PlotProbability() 1270 1271 Dim minMortality As Double 1272 Dim maxMortality As Double 1273 1274 With Worksheets(Graphs) 1275 .ChartObjects("Chart 4").Activate 1276 End With 1277 1278 Dim lastRow As Integer 1279 lastRow = OutputStartRow + NumberDirections - 1 1280 foo = SetDataRanges(OutputStartRow, lastRow, AngleCol, _ 1281 RangeProbActual, RangeProbCircular) 1282 1283 With Worksheets(Data) 1284 minMortality = _ 1285 WorksheetFunction.Min(.Range(.Cells(OutputStartRow, 1286 RangeProbCircular), _ 1287 .Cells(lastRow, 1288 RangeProbCircular))) 1289 maxMortality = _ 1290 WorksheetFunction.Max(.Range(.Cells(OutputStartRow, 1291 RangeProbActual), _ 1292 .Cells(lastRow, 1293 RangeProbActual))) 1294 End With 1295 1296 ComputeBoundsAndScale (maxMortality) 1297 ComputeTickSpacing (StepAngle) 1298 1299 Dim windString As String 1300 If WindDirection > 360 Then 1301 windString = "Parallel to Flight Direction" 1302 Else 1303 windString = CStr(WindDirection) 1304 End If 1305 1306 ActiveChart.ChartArea.Select 1307 With ActiveChart 1308 .HasTitle = True 1309 .ChartTitle.Characters.Text = _ 1310 " Average Mortality Probability (by Flight 1311 Direction) " + _ 1312 "Wind Direction " + windString 1313 End With 1314 End Sub 1315 1316 Sub PlotCounterByWindDirection() 1317 1318 ' Edit the chart that plots the mean expected number of turbines 1319 ' encountered as a function of wind direction 1320 1321 Dim maxEncounters As Double 1322 1323 With Worksheets(Graphs) 1324 .ChartObjects("Chart 5").Activate 1325 End With 1326 1327 Dim lastRow As Integer 1328 lastRow = OutputStartRow + NumberWindDirections - 1 1329 foo = SetDataRanges(OutputStartRow, lastRow, ThisWindDirection, _ 1330 WindCountActual, WindCountCircular) 1331 1332 With Worksheets(Data) 1333 maxEncounters = _ 1334 WorksheetFunction.Max(.Range(.Cells(OutputStartRow, 1335 WindCountActual), _ 1336 .Cells(lastRow, 1337 WindCountActual))) 1338 End With 1339 1340 ComputeTickSpacing (WindStepAngle) 1341 1342 Dim flightString As String 1343 flightString = CStr(FlightDirection) 1344 ActiveChart.ChartArea.Select 1345 With ActiveChart 1346 .HasTitle = True 1347 .ChartTitle.Characters.Text = _ 1348 "Average Number of Turbines Encountered (by Wind Direction) " 1349 + _ 1350 "Flight Direction " + flightString 1351 End With 1352 1353 End Sub 1354 1355 Sub PlotProbabilityByWindDirection() 1356 1357 Dim minMortality As Double 1358 Dim maxMortality As Double 1359 1360 With Worksheets(Graphs) 1361 .ChartObjects("Chart 6").Activate 1362 End With 1363 1364 Dim lastRow As Integer 1365 lastRow = OutputStartRow + NumberWindDirections - 1 1366 foo = SetDataRanges(OutputStartRow, lastRow, ThisWindDirection, _ 1367 WindProbActual, WindProbCircular) 1368 1369 With Worksheets(Data) 1370 minMortality = _ 1371 WorksheetFunction.Min(.Range(.Cells(OutputStartRow, 1372 WindProbCircular), _ 1373 .Cells(lastRow, 1374 WindProbCircular))) 1375 maxMortality = _ 1376 WorksheetFunction.Max(.Range(.Cells(OutputStartRow, 1377 WindProbActual), _ 1378 .Cells(lastRow, 1379 WindProbActual))) 1380 End With 1381 1382 ComputeTickSpacing (WindStepAngle) 1383 ComputeBoundsAndScale (maxMortality) 1384 1385 Dim flightString As String 1386 flightString = CStr(FlightDirection) 1387 ActiveChart.ChartArea.Select 1388 With ActiveChart 1389 .HasTitle = True 1390 .ChartTitle.Characters.Text = _ 1391 "Average Mortality Probability (by Wind Direction) " + _ 1392 "Flight Direction " + flightString 1393 End With 1394 1395 1396 End Sub 1397 1398 ' Code fragment here is the start of an attempt to build the 1399 ' charts from scratch each time rather than editing existing ones, 1400 ' so that meaningless charts don't appear. 1401 1402 Sub CreateEncounterChart() 1403 lastRow = OutputStartRow + NumberDirections - 1 1404 Worksheets("Graphs").Activate 1405 Dim DataSheet As Worksheet 1406 DataSheet = Worksheets("Data") 1407 With ActiveSheet.ChartObjects.Add _ 1408 (Left:=10, Width:=375, Top:=10, Height:=375) 1409 With .Chart 1410 .ChartType = xlLine 1411 .Parent.Name = "Encounters" 1412 Dim MyNewSrs As Series 1413 Set MyNewSrs = .SeriesCollection.NewSeries 1414 MyNewSrs.XValues = _ 1415 DataSheet.Range(.Cells(OutputStartRow, AngleCol), 1416 .Cells(lastRow, AngleCol)) 1417 MyNewSrs.Values = _ 1418 DataSheet.Range(.Cells(OutputStartRow, 1419 RangeCountActual), .Cells(lastRow, RangeCountActual)) 1420 1421 With .SeriesCollection.NewSeries 1422 .Name = "Actual Region" 1423 ' lastRow = OutputStartRow + NumberDirections - 1 1424 '.XValues = _ 1425 ' Worksheets("Data")!Range(.Cells(OutputStartRow, 1426 AngleCol), .Cells(lastRow, AngleCol)) 1427 '.Values = _ 1428 ' Worksheets("Data")!Range(.Cells(OutputStartRow, 1429 RangeCountActual), .Cells(lastRow, RangeCountActual)) 1430 End With 1431 'With Worksheets("Data") 1432 ' lastRow = OutputStartRow + NumberDirections - 1 1433 ' .SeriesCollection("Actual Region").XValues = _ 1434 ' .Range(.Cells(OutputStartRow, AngleCol), 1435 .Cells(lastRow, AngleCol)) 1436 ' .SeriesCollection("Actual Region").Values = _ 1437 ' .Range(.Cells(OutputStartRow, RangeCountActual), 1438 .Cells(lastRow, RangeCountActual)) 1439 'End With 1440 End With 1441 End With 1442 End Sub 1443 1444 1445 1446