After playing cards, you will count points of all the cards you have at hand. Ace is 14, king 13, queen 12 and jack 11. Number cards have the value according to number. The one who has the highest or lowest score is the winner.

It is easy to count but only picture cards have letters, which need to be converted to numbers.

Excel can help you or at least make counting a bit faster.

First we are introducing the variables. Z_val is the value of cards. Z_start is the address of the starting cell address. Z_val is set to zero. The program starts going downwards the cells. If the cell contains A then 14 is added to  z_val,  K is 13, Q is 12 and J is 11.

The number cards have their own value, like 6 is 6 and 7 is 7. To define which cell contains the numeric value, we can use Excel function ISNUMBER. In Visual Basic we can use Excel functions with WorksheetFunction extension.

The macro goes downwards cell by cell adding the sum value z_val, till the macro hits the first empty cell. Then macro jumps back to start cell and enter the sum value z_val into cell one above the start cell z_start.

Here is the macro:

Sub z_cards2()

Dim z_val

Dim z_start

z_val = 0

z_start = ActiveCell.Address

Do Until ActiveCell.Value = “”

If ActiveCell.Value = “A” Then

    z_val = (z_val + 14)

ElseIf ActiveCell.Value = “K” Then

    z_val = (z_val + 13)

ElseIf ActiveCell.Value = “Q” Then

    z_val = (z_val + 12)

ElseIf ActiveCell.Value = “J” Then

    z_val = (z_val + 11)

ElseIf WorksheetFunction.IsNumber(ActiveCell.Value) Then

    z_val = (z_val + ActiveCell.Value)

Else

    z_val = z_val + 0

End If

ActiveCell.Offset(1, 0).Select

Loop

Range(z_start).Select

ActiveCell.Offset(-1, 0).Select

ActiveCell.Value = z_val

End Sub

In B6 we have a value which does not have any value in our calculations as there is no Z-card in playing cards.

Place the cursor in B4 and execute the macro.

Z-card was not counted. If you mistakenly enter a wrong letter, the macro can handle that by passing the cell.

Excel macro was counting the points for each player. Of course, you could have done that manually too, but Excel can be used also for counting playing card points.