Unfamilliar with how to use VBA code

[Guest]

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]

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.

 

[Guest]

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]

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.