Thread created automatically to discuss a document in CD-Media.
New Scouting Database from Team 2834 by: Ed Law
This is a new scouting database with a new measure to rank teams, in addition to Offensive Power Rating (OPR), that takes into account both offense and defense.
I developed this new scouting database to help with rating teams. This does not replace pit scouting and watching matches but it helps small teams that does not have a lot of resources to do extensive scouting.
I included a presentation to explain the new measure which I call Calculated Contribution to Winning Margin (CCWM). Please note that the presentation has been updated in 2010 (4th file down) in 2012 (8th file down) and in 2014.
Got a few suggestions that might improve your excel file.
In order to make the getpicture sub to work every time a new query is requested, change your getpicture sub to the following functions:
Function getpicture(teamnum As String) As Boolean
Dim filen as String
If ActiveSheet.Name = "Query" Then
Dim AC As Range
Static P As Shape
On Error GoTo Done
Set AC = Application.Caller
If PicExists(P) Then
'look for a picture already over cell
For Each P In ActiveSheet.Shapes
If P.Type = msoLinkedPicture Then
If P.Left >= AC.Left And P.Left < AC.Left + AC.Width Then
If P.Top >= AC.Top And P.Top < AC.Top + AC.Height Then
filen = CurDir + "\" + Format(teamnum) + ".jpg"
Set P = ActiveSheet.Shapes.AddPicture(filen, True, True, AC.Left + 2.75, AC.Top + 5, 329.25, 247.5)
getpicture = True
getpicture = False
Function PicExists(P As Shape) As Boolean
'Return true if P references an existing shape
Dim ShapeName As String
On Error GoTo NoPic
If P Is Nothing Then GoTo NoPic
ShapeName = P.Name
PicExists = True
PicExists = False
As for calling the function add the following to “I5”:
Finally to make the query worksheet more user friendly, I would “unlock” cell B2. You can do this by right clicking on B2, Format Cells…, Click Protection, and uncheck Lock. Then you will want to protect the worksheet by going to Tools>Protection>Protect Sheet… then add a password if you like and uncheck select locked cells. That way the only selectable cell would be “B2” on the Query worksheet.
Hope that helps and Thanks for the information.
It is working now. Thank you. The picture automatically changes when the user type in a new team number in cell B2. One thing I have to change to get it to work is teamnum as Integer instead of teamnum as String.
The picture is okay if the aspect ratio is 4:3 but it get distorted if it is not. How can I maintain the aspect ratio? I tried to do it but was not successful. Can you take a look at the program and see what I did wrong?
I have decided not to protect the worksheet for now since I am still developing it.
I tried it and it worked. However when I tried it on Excel 2007, I think it changes it back to its original size and then stretch it to Height = 250 without maintaining the aspect ratio. I tried a few things but none seems to work. Can anybody help? I would like to make this work for everybody as more and more people will be using Excel 2007. Thanks.
Thanks Oris. Version 5 has this included. I also put in the protection as you suggested to help user avoid accidentally modifying fomulas in other cells. There is no password so users can unprotect it if they want to.
Sorry it took so long to reply. I have been busy with the robot. I download pictures from The Blue Alliance using downthemall with Firefox. You can take your own pictures and put them in the same directory as the scouting database.
Sorry it took so long. I had it done on Monday but was waiting for missing data from FIRST. As of this morning, there are still no match results data from Buckeye Regional and match 65, 66 and 67 results from Oklahoma Regional is still missing which will affect the OPR and CCWM results a bit.
Good luck to teams going to Week 2 events. May the traction force be with you!