JenniferMurphy
Well-known Member
- Joined
- Jul 23, 2011
- Messages
- 2,535
- Office Version
- 365
- Platform
- Windows
The macro below works, but I have several questions about why I need to take certain steps and/or if there is a better way.
This macro adds 1 to one of 5 ace tallies (0-4). It works, but,
Thanks
This macro adds 1 to one of 5 ace tallies (0-4). It works, but,
- Why do I need to trim the spaces, if any. In the Immediate window ?"3 + " 4 " gets 7.
- Why do I need to add 0 before comparing with the rounded version?
- Am I making this harder than it needs to be?
VBA Code:
'====================================================================
' Tally Aces macro -- Assigned to CS+Z
'
' Tallies are in named ranges Aces0, Aces1, Aces2, Aces3, & Aces4
'====================================================================
Sub TallyAces()
Const rnAces As String = "Aces" 'Base name for all ace tally cells
Dim NumAces As Variant 'The number of aces to tally
Const Prompt = "Enter number of aces (0-4)" & vbCrLf & vbCrLf _
& "Click Cancel to quit"
Do
'Get the reply and trim leading and trailng spaces
NumAces = Trim(InputBox(Prompt, "TallyAces Macro", ""))
'Cancel returns "", but so does ""
If NumAces = "" Then Exit Do
If Not IsNumeric(NumAces) Then GoTo Error
'I had to add 0 to make this work
If (NumAces + 0) <> Round(NumAces) Then GoTo Error
If NumAces < 0 Or NumAces > 4 Then GoTo Error
'Finally, do the tally
Range(rnAces & NumAces).Select
ActiveCell.Value = ActiveCell.Value + 1
GoTo Continue
Error:
MsgBox "Invalid data"
Continue:
Loop
End Sub
Thanks