Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Unfamilliar with how to use VBA code
If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm But I took your code and put it into a new workbook. You can download a copy from: http://www.savefile.com/files/401195 I didn't do anything except clean up some text wrapping problems and declare a couple of undeclared variables (and change "as Integer" to "As Long" in a few spots). You can rightclick on the sheet1 tab and select view code. You'll see the worksheet_change event that holds a little of the code you posted. While you're in the VBE, you can hit ctrl-r to see the project explorer. If you select your project (It'll look like: VBAProject (Karl.xls)) You'll see a Modules branch. Under that is Module1. Double click on that to see the code that goes into the General module. I didn't look at any of the code except to make sure it compiles. I have no idea what it really does. Karl wrote: Hi I have some code that I found on doing A Bowling Sheet with Excel but not sure how to use it. The code is attached Thanks Karl The code! This goes to the standard module!!! Type BallStatus1 Strike As Boolean Spare As Boolean Score As Integer End Type Type Oneframe1 Thisround(0 To 1) As BallStatus1 End Type Sub Scoring2(ByVal PlayerNumber As Integer) Dim MyScore(9) As Oneframe1 Dim MyScorePerFrame(9) As Integer Dim colx As Integer Dim Bunos As Integer colx = 2 Rowx = 3 * PlayerNumber For I = LBound(MyScore) To UBound(MyScore) Select Case UCase(Range(Cells(Rowx, colx), Cells(Rowx, colx)).Value) Case "X" MyScore(I).Thisround(0).Strike = True MyScore(I).Thisround(0).Spare = False MyScore(I).Thisround(0).Score = 10 MyScore(I).Thisround(1).Strike = False MyScore(I).Thisround(1).Spare = False MyScore(I).Thisround(1).Score = 0 Case Else MyScore(I).Thisround(0).Score = Range(Cells(Rowx, colx), Cells(Rowx, colx)).Value End Select If MyScore(I).Thisround(0).Strike = False Then TMP = UCase(Range(Cells(Rowx, colx + 1), Cells(Rowx, colx + 1)).Value) Select Case TMP Case "S" MyScore(I).Thisround(1).Strike = False MyScore(I).Thisround(1).Spare = True MyScore(I).Thisround(1).Score = 10 - MyScore(I).Thisround(0).Score Case Else MyScore(I).Thisround(1).Strike = False MyScore(I).Thisround(1).Spare = False MyScore(I).Thisround(1).Score = Range(Cells(Rowx, colx + 1), Cells(Rowx, colx + 1)).Value End Select End If colx = colx + 2 Next I TMP = Range(Cells(Rowx, colx - 1), Cells(Rowx, colx - 1)).Value Select Case UCase(TMP) Case "X" MyScore(UBound(MyScore)).Thisround(1).Score = 10 MyScore(UBound(MyScore)).Thisround(1).Strike = True MyScore(UBound(MyScore)).Thisround(1).Spare = False If UCase(Range(Cells(Rowx, colx), Cells(Rowx, colx)).Value) = "X" Then Bunos = 10 Else Bunos = Range(Cells(Rowx, colx), Cells(Rowx, colx)).Value End If Case "S" MyScore(UBound(MyScore)).Thisround(1).Score = 10 - MyScore(UBound(MyScore)).Thisround(0).Score MyScore(UBound(MyScore)).Thisround(1).Strike = False MyScore(UBound(MyScore)).Thisround(1).Spare = True If UCase(Range(Cells(Rowx, colx), Cells(Rowx, colx)).Value) = "X" Then Bunos = 10 Else Bunos = Range(Cells(Rowx, colx), Cells(Rowx, colx)).Value End If Case Else MyScore(UBound(MyScore)).Thisround(1).Score = Range(Cells(Rowx, colx - 1), Cells(Rowx, colx - 1)).Value MyScore(UBound(MyScore)).Thisround(1).Strike = False MyScore(UBound(MyScore)).Thisround(1).Spare = False Bunos = 0 End Select For I = LBound(MyScore) To UBound(MyScore) - 1 If MyScore(I).Thisround(0).Strike = True Then If MyScore(I + 1).Thisround(0).Strike Then If I = UBound(MyScore) - 1 Then MyScorePerFrame(I) = MyScore(I).Thisround(0).Score + MyScore(I + 1).Thisround(0).Score + MyScore(I + 1).Thisround(1).Score Else MyScorePerFrame(I) = MyScore(I).Thisround(0).Score + MyScore(I + 1).Thisround(0).Score + MyScore(I + 2).Thisround(0).Score End If Else MyScorePerFrame(I) = MyScore(I).Thisround(0).Score + MyScore(I + 1).Thisround(0).Score + MyScore(I + 1).Thisround(1).Score End If ElseIf MyScore(I).Thisround(1).Spare = True Then MyScorePerFrame(I) = MyScore(I).Thisround(0).Score + MyScore(I).Thisround(1).Score + MyScore(I + 1).Thisround(0).Score ElseIf (MyScore(I).Thisround(0).Strike = False) And (MyScore(I).Thisround(1).Spare = False) Then MyScorePerFrame(I) = MyScore(I).Thisround(0).Score + MyScore(I).Thisround(1).Score End If Next I MyScorePerFrame(UBound(MyScore)) = MyScore(UBound(MyScore)).Thisround(0).Score + MyScore(UBound(MyScore)).Thisround(1).Score + Bunos TMP = 0 'output score colx = 2 Rowx = PlayerNumber * 3 + 1 For I = LBound(MyScore) To UBound(MyScore) - 1 TMP = MyScorePerFrame(I) + TMP Range(Cells(Rowx, colx), Cells(Rowx, colx)).Value = TMP colx = colx + 2 Next I Range(Cells(Rowx, colx), Cells(Rowx, colx)).Value = TMP + MyScorePerFrame(UBound(MyScore)) End Sub And this goes the sheet module!!! Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo en Application.EnableEvents = False Application.ScreenUpdating = False Set isect = Application.Intersect(Target, Range("b2:V11")) If Not isect Is Nothing Then PlayerNumber = ActiveCell.Row / 3 Call Scoring2(PlayerNumber) End If en: If Err.Number < 0 Then MsgBox "Error occurs" & Chr(13) & Err.Number End If Application.ScreenUpdating = True Application.EnableEvents = True End Sub -- Dave Peterson |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Unfamilliar with how to use VBA code
Hi Dave I looked at all the code and tried to make it work but it's not doing
what it looked like at this somewhat older web page. http://www.mrexcel.com/archive2/67900/78736.htm it is 2/3 of the way down the page under the Tue Feb 24, 2004 3:33 am There is a nice color sheet and the code that i posted follows. I know nothing about VBA. I am greatful for your help so far and if you can't spend time looking at this could you point me to other help maybe. It might just be me how do you compile the code to see if there are errors even? Thanks Karl |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Unfamilliar with how to use VBA code
If you're looking for a way to score your bowling matches, you can use a
workbook that Harlan Grove did. ftp://members.aol.com/hrlngrv/ Look for Bowling.xls I copied and pasted from the web page and put a new copy in: http://www.savefile.com/files/401462 I'm not sure if it's different from the version you posted--I didn't look. If it doesn't work, maybe someone will jump in to look at the logic. Karl wrote: Hi Dave I looked at all the code and tried to make it work but it's not doing what it looked like at this somewhat older web page. http://www.mrexcel.com/archive2/67900/78736.htm it is 2/3 of the way down the page under the Tue Feb 24, 2004 3:33 am There is a nice color sheet and the code that i posted follows. I know nothing about VBA. I am greatful for your help so far and if you can't spend time looking at this could you point me to other help maybe. It might just be me how do you compile the code to see if there are errors even? Thanks Karl -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA: For Count, when count changes from cell to cell | Excel Discussion (Misc queries) | |||
Cell value not recognized by code. | Excel Discussion (Misc queries) | |||
Text formatting | Excel Worksheet Functions | |||
VLOOKUP for Zip Code Ranges | Excel Worksheet Functions | |||
Macro for changing text to Proper Case | Excel Worksheet Functions |