Several questions about a macro

[JenniferMurphy]

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,

• 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?

'====================================================================
'   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

 

[Sulprobil]

Hello,

My suggestion is to let CInt () do the work:

'====================================================================
'   Tally Aces macro
'
' Tallies are in named ranges Aces0, Aces1, Aces2, Aces3, & Aces4
'====================================================================
Sub TallyAcesNew()
Const rnAces As String = "Aces" 'Base name for all ace tally cells
Const Prompt = "Enter number of aces (0-4)" & vbCrLf & vbCrLf _
               & "Click Cancel to quit"
Dim NumAces As Variant  'The number of aces to tally
On Error GoTo ErrHdl
Do
    'Get the reply and trim leading and trailng spaces
    NumAces = InputBox(Prompt, "TallyAces Macro", "0")
    If NumAces = "" Then Exit Do
    NumAces = CInt(NumAces)
    If NumAces < 0 Or NumAces > 4 Then
Resume_CInt_Error:
        MsgBox "Invalid data"
    Else
        Range(rnAces & NumAces).Formula = Range(rnAces & NumAces) + 1
    End If
Loop
Exit Sub
ErrHdl:
Resume Resume_CInt_Error
End Sub

CInt will convert your variant into an integer which you then can compare to 0 and to 4, or the conversion will fail, resulting in an error which will take the sub to the ErrHdl and then resume at label Resume_CInt_Error. Of course it's not considered good programming style to jump right into the middle of an if statement but I decided to go for a quick & dirty approach.

In your original Sub you needed to get rid of spaces in NumAces not because of any integer treatment but because the Range "Aces1" is different from "Aces1 ", for example. You needed to add 0 before comparing to a Round() result in order to force VBA to make a number out of NumAces. Val(NumAces) would have worked as well.

Note that I have used the default value 0 in the input box because I thought that would be the most likely value you have to enter.

Regards,
Bernd

 

[JenniferMurphy]

Hello,

My suggestion is to let CInt () do the work:

The reason I didn't use CInt is because I didn't think of it. But even if I had, I would have needed to to do something to flag input that is a valid number, but not an integer.

In your original Sub you needed to get rid of spaces in NumAces not because of any integer treatment but because the Range "Aces1" is different from "Aces1 ", for example. You needed to add 0 before comparing to a Round() result in order to force VBA to make a number out of NumAces. Val(NumAces) would have worked as well.

Your array solution in the other thread led me to replace the 5 individual ranges with a single 1x5 range.

Note that I have used the default value 0 in the input box because I thought that would be the most likely value you have to enter.

That won't work for me. My poor typing skills cause me to inadvertantly hit the Enter key way too often.

Here's my updated macro:

'====================================================================
'   Tally Aces macro -- Assigned to CS+Z
'
' Tallies are in the 1x5 named range: AceTallies
'
' 10/23/22 Helped by MrExcel thread 1220049.
'====================================================================
Sub TallyAces()
Const MyName As String = "TallyAces Macro"

Const rnAceTallies As String = "AceTallies" 'Range of tallies (0-4)

Dim vReply As Variant    'The number of aces to tally as entered
Dim nNumAces As Double   '.Converted to number
Dim iAceIndex As Integer '.Converted to integer
Const Prompt = "Enter number of aces (0-4)" & vbCrLf & vbCrLf _
               & "Click Cancel or press Enter to quit"
Do
  'Get the reply. X,Y coordinates by trial and error
  vReply = InputBox(Prompt, MyName, "", 11000, 5000)
  'Cancel returns "", but so does "", so confirm both
  If vReply = "" Then
    If vbYes = MsgBox("Click Yes to exit", vbYesNo, MyName) Then
      Exit Do
    Else
      GoTo Continue
    End If
  End If
  On Error GoTo BadInput
  nNumAces = CSng(vReply)       'Convert to number
  iAceIndex = CInt(nNumAces)    '.then to integer
  If nNumAces <> iAceIndex Then GoTo BadInput
  If iAceIndex < 0 Or iAceIndex > 4 Then GoTo BadInput
 
  'Finally, do the tally
  Range(rnAceTallies)(1, iAceIndex + 1).Select  'Select the cell within the array
  ActiveCell.Value = ActiveCell.Value + 1       '.add 1
  GoTo Continue
 
BadInput:
  MsgBox "Invalid data", vbOKOnly, MyName
Continue:
Loop

End Sub

And it works! Of course, if you have any other suggestions, please fire away.

 

[Sulprobil]

Hello,

If you are OK with the program, it's fine, of course.
I thought the variable nNumAces is not really needed, but if you like it, name it sngNumAces and declare it as Single because you assign it with CSng, I suggest.
On Error Goto is supposed to be used with an error handler which could simply resume to BadInput in this case.
Now, my suggestion was not the cleanest, either. So, if it ain't broke, don't fix it.

Regards,
Bernd

 

[JenniferMurphy]

It seems to be working. Here are the results of the first 500 deals:

Ace Odds.xlsm
	B	C	D	E	F	G	H	I
4	#Aces	0 Aces	1 Ace	2 Aces	3 Aces	4 Aces	1-4 Aces	Totals
5	Tallies	282	174	42	2	0	218	500
6	Expected	275	183	38	3	0	225	500
7	Delta	+7	-9	+4	-1	-0	-7
8	Prob	55.0356%	36.6904%	7.6794%	0.5818%	0.0129%	44.9644%	100.0000%
9	Actual	56.4000%	34.8000%	8.4000%	0.4000%	0.0000%	43.6000%	100.0000%
10	% Error	+2.4792%	-5.1522%	+9.3838%	-31.2444%	-100.0000%	-1.270128
11	Combin	7,7 = 1	7,1 = 7	7,2 = 21	7,3 = 35	7,4 = 35
12
13	#Aces	0 Aces	1 Ace	2 Aces	3 Aces	4 Aces	1-4 Aces	Totals
14	Tallies	232	134	32	2	0	168	400
15	Expected	220	147	31	2	0	180	400
16	Delta	+12	-13	+1	-0	-0	-12
17	Prob	55.0356%	36.6904%	7.6794%	0.5818%	0.0129%	44.9644%	100.0000%
18	Actual	58.0000%	33.5000%	8.0000%	0.5000%	0.0000%	42.0000%	100.0000%
19	% Error	+5.3864%	-8.6954%	+4.1751%	-14.0556%	-100.0000%	-1.185759
20	Combin	7,7 = 1	7,1 = 7	7,2 = 21	7,3 = 35	7,4 = 35
21
22	#Aces	0 Aces	1 Ace	2 Aces	3 Aces	4 Aces	1-4 Aces	Totals
23	Tallies	178	96	24	2	0	122	300
24	Expected	165	110	23	2	0	135	300
25	Delta	+13	-14	+1	+0	-0	-13
26	Prob	55.0356%	36.6904%	7.6794%	0.5818%	0.0129%	44.9644%	100.0000%
27	Actual	59.3333%	32.0000%	8.0000%	0.6667%	0.0000%	40.6667%	100.0000%
28	% Error	+7.8091%	-12.7837%	+4.1751%	+14.5926%	-100.0000%	-0.940160
29	Combin	7,7 = 1	7,1 = 7	7,2 = 21	7,3 = 35	7,4 = 35
30
31	#Aces	0 Aces	1 Ace	2 Aces	3 Aces	4 Aces	1-4 Aces	Totals
32	Tallies	118	64	17	1	0	82	200
33	Expected	110	73	15	1	0	90	200
34	Delta	+8	-9	+2	-0	-0	-8
35	Prob	55.0356%	36.6904%	7.6794%	0.5818%	0.0129%	44.9644%	100.0000%
36	Actual	59.0000%	32.0000%	8.5000%	0.5000%	0.0000%	41.0000%	100.0000%
37	% Error	+7.2034%	-12.7837%	+10.6860%	-14.0556%	-100.0000%	-1.161532
38	Combin	7,7 = 1	7,1 = 7	7,2 = 21	7,3 = 35	7,4 = 35
39
40	#Aces	0 Aces	1 Ace	2 Aces	3 Aces	4 Aces	1-4 Aces	Totals
41	Tallies	63	29	8	0	0	37	100
42	Expected	55	37	8	1	0	45	100
43	Delta	+8	-8	+0	-1	-0	-8
44	Prob	55.0356%	36.6904%	7.6794%	0.5818%	0.0129%	44.9644%	100.0000%
45	Actual	63.0000%	29.0000%	8.0000%	0.0000%	0.0000%	37.0000%	100.0000%
46	% Error	+14.4715%	-20.9602%	+4.1751%	-100.0000%	-100.0000%	-2.167851
47	Combin	7,7 = 1	7,1 = 7	7,2 = 21	7,3 = 35	7,4 = 35
Aces

Cell Formulas
Range	Formula
H8:H10,H5:H6	H5	=SUM(D5:G5)
I8:I9,I5:I6	I5	=SUM(C5:G5)
C6:G6	D6	=TallyOdds*TotalDeals
C7:H7	D7	=D5-D6
C8:G8	C8	=COMBIN(4,SEQUENCE(1,5,0,1))*COMBIN(48,7-SEQUENCE(1,5,0,1))/COMBIN(52,7)
C9	C9	=C5/I5
D9	D9	=D5/I5
E9	E9	=E5/I5
F9	F9	=F5/I5
G9	G9	=G5/I5
C10:G10	C10	=C9/C8 - 1
C11	C11	="7,7 = " & COMBIN(7,7)
D11	D11	="7,1 = " & COMBIN(7,1)
E11	E11	="7,2 = " & COMBIN(7,2)
F11	F11	="7,3 = " & COMBIN(7,3)
G11	G11	="7,4 = " & COMBIN(7,4)
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
AceTallies	=Aces!$C$5:$G$5	I5, C9, C7
TallyOdds	=Aces!$C$8:$G$8	I8, C10, C6
TotalDeals	=Aces!$I$5	C9:G9, C6:G6