The procedures listed below could be used in just about any VBA project. You have already seen the PlayWav() procedure in the Battlecell program from Chapter 5. I have added one more procedure called Delay(). The entire content of the code module follows:
Private Const DELAY_CODE = 0
Private Const CONTINUE_CODE = 1
Public Declare Function sndPlaySoundA Lib "winmm.dll" _ (ByVal lpszSoundName As String, ByVal uFlags As Long) As Long
Public Sub PlayWav(filePath As String)
sndPlaySoundA filePath, CONTINUE_CODE End Sub
Public Sub Delay(curTime As Single, pauseTime As Single) Do While Timer < pauseTime + curTime DoEvents
This module contains two short and simple public procedures, two module level constant declarations, and one API declaration for playing .wav sound files. The PlayWav() sub procedure is simply one line of code that calls the sndPlaySoundA() function in the winmm.dll system file. The constants (DELAY_CODE and CONTINUE_CODE) clarify the action of the API function call. In this case, program execution continues while the sound file is played. The PlayWav() procedure is called to play sound files when the program shuffles or deals cards.
The Delay() sub procedure is called to delay the execution of the Blackjack program. The delay is needed when a new hand is dealt and when the dealer draws more cards to give the game an appearance of dealing one card at a time. It is also called for an aesthetic affect when the Shuffling form is displayed because it only takes the program a fraction of a second (processor dependent) to effectively shuffle the cards. The delay is caused by a Do-Loop that executes until a specified number of seconds (indicated by the variable pauseTime) has passed. The VBA function DoEvents() yields the computer's processor so that the operating system can process other events. This allows the player to make other selections while the loop executes.
Public Procedures and Variables for the Blackjack Program
The second standard module included with the Blackjack program includes the variables and procedures specifically related to the Blackjack game and are not transferable to other applications.
The module uses two public enumerations named CardDeck and CardSuits to define related sets of constants that describe a deck of cards. The CardDeck enumeration defines the number of cards in a single deck, the number of cards in a suit, and the number of suits in a deck. The CardSuits enumeration defines integer constants that will be used later to initialize a deck of cards by suit. The suits are used in the filenames of the images so a card's suit must be known in order to load the correct image. The constants defined in these enumerations have public scope so they are available in all code modules. Since they are constants, and therefore cannot be changed elsewhere in the program, I don't have to worry about data contamination.
Next, a custom data type for the deck of cards is defined with two elements: value and filename. The integer element value represents the face value of a card. The string element filename stores the name of the image file associated with a card. All three elements of the custom data type are arrays with fifty-two elements (the number of cards in a single deck). The custom data type is named Deck and a public dynamic array variable of type Deck is declared and named theDeck. The array theDeck must be dynamic because its length will vary with the number of decks selected by the player.
Public Enum CardSuits bjSpades = 1 bjDiamonds = 2 bjClubs = 3 bjHearts = 4 End Enum
Public Enum CardDeck bjcardsindeck = 52 bjCardsInSuit = 13 bjNumSuits = 4 End Enum
Type Deck value(bjcardsindeck - 1) As Integer filename(bjcardsindeck - 1) As String End Type
Public theDeck() As Deck
Public Sub Blackjack()
frmTable.Show vbModal End Sub
Public Sub ClearResults() Dim lastRow As Integer lastRow = ActiveSheet.UsedRange.Rows.Count Range("A2:C" & lastRow).ClearContents End Sub
The two public procedures Blackjack() and ClearResults() are short and simple. Each procedure is attached to a Command Button control on the worksheet. The Command Button controls provide the player with an easy interface to show the Blackjack form and clear the results from the worksheet. The form is shown modally for no particular reason. If you prefer, you can certainly change it to a modeless form. The worksheet is cleared by calling the ClearContents() method of the Range object after determining the last used row in the worksheet via the UsedRange property of the Worksheet object. The UsedRange property returns a Range object representing the used range on the worksheet. The Rows property returns another Range object representing the rows in the range returned from the UsedRange property. Finally, the Count property returns an integer representing the number of rows in the range.
The code module for the Shuffling form (named frmShuffle) contains the part of the Blackjack program that simulates the shuffling of the deck. The Activated event procedure of the UserForm object is triggered when the form's Show() method is executed from elsewhere in the program. The custom sub procedures InitDeck() and ShuffleDeck() are called from the Activate() event procedure in order to initialize and shuffle the deck. A sound file simulating a deck being shuffled is played while program execution is delayed for one and a half seconds. The program is delayed so that the player can actually see the form before it is hidden again with the form's Hide() method.
Private Sub UserForm_Activate() Const DELAY_TIME = 1.5
'Initialize and shuffle the deck(s) values.
'Play shuffle sound while program delays long 'enough to display the form.
PlayWav (ActiveWorkbook.Path & "\Sounds\shuffle.wav") Delay Timer, DELAY_TIME frmShuffle.Hide End Sub
The InitDeck() sub procedure first re-dimensions the size of the global Deck array variable theDeck to the number of decks selected in the Combo Box control (named cmbNumDecks) on the Blackjack form. Next, the custom array is filled with values, and filenames representing each card in a deck using nested For/Next loops. Note the use of array indices for the custom data type variable theDeck and each of its elements: value and filename because each deck has fifty-two cards.
For each deck, the card values are sequentially filled from one to ten, where aces are one, face cards are ten, and all other cards are face-value. Each deck is also filled with the strings for the filenames of the card images which are built using the enumerations, the GetSuitLabel() function procedure, and the card number (ranges from one to thirteen). Please note the use of line continuation characters in some of the longer program statements.
Private Sub InitDeck()
Dim curCard As Integer, curSuit As Integer, curDeck As Integer Dim numDecks As Integer, cNum As Integer
'Initialize N decks with values 1-10. Fours suits per deck. 'Ace=1, Jack=King=Queen=10
numDecks = frmTable.cmbNumDecks.value - 1 ReDim theDeck(numDecks) For curDeck = 0 To numDecks
For curSuit = 1 To bjNumSuits
For curCard = 0 To bjCardslnSuit - 1 cNum = curCard + 1 If (curCard + 1) < 10 Then theDeck(curDeck).value(curCard + bjCardslnSuit * (curSuit - 1)) = curCard + 1
Else theDeck(curDeck).value(curCard + bjCardslnSuit * (curSuit - 1)) = 10
End If theDeck(curDeck).filename(curCard + bjCardslnSuit * _ (curSuit - 1)) = cNum & GetSuitLabel(curSuit)
Next curCard Next curSuit Next curDeck End Sub
Private Function GetSuitLabel(suit As Integer) As String Select Case suit
Case Is =bjSpades
GetSuitLabel = "Spades" Case Is =bjDiamonds
GetSuitLabel = "Diamonds" Case Is =bjClubs
GetSuitLabel = "Clubs" Case Is =bjHearts
GetSuitLabel = "Hearts" End Select End Function
The ShuffleDeck() sub procedure performs five-hundred swaps per deck of two randomly selected cards in the deck array variable theDeck in order to effectively shuffle the deck. You can change the number of swaps at Design Time by simply changing the value of the NUMSWAPS constant. A series of variables serve as temporary storage locations for all the elements that describe a card (the index value for the deck, the value of the card, and the filename of the image representing the card) so two cards can be swapped as illustrated in Figure 6.19.
Private Sub ShuffleDeck()
Dim ranCardl As Integer, ranCard2 As Integer Dim ranDeck As Integer
Dim tempCard As Integer, tempSuit As Integer Dim tempName As String
Dim curSwap As Integer, numDecks As Integer Const NUMSWAPS = 500
Randomize numDecks = frmTable.cmbNumDecks.value
'Shuffle the deck by swapping two cards in the array.
For curSwap = 0 To NUMSWAPS * numDecks ranCardl = Int(Rnd * bjcardsindeck) ranCard2 = Int(Rnd * bjcardsindeck) ranDeck = Int(Rnd * numDecks) tempCard = theDeck(ranDeck).value(ranCard1) tempName = theDeck(ranDeck).filename(ranCard1) theDeck(ranDeck).value(ranCard1) = _
theDeck(ranDeck).filename(ranCard1) = _
theDeck(ranDeck).value(ranCard2) = tempCard theDeck(ranDeck).filename(ranCard2) = tempName Next curSwap End Sub
The Shuffling form only appears for a second or two, but it serves a very important purpose. First, it informs the player that the marker for the end of the deck was reached and the deck is being reshuffled. Second, the code contained within its code module effectively shuffles the array representing the deck(s).
Now it is time to get to the meat of the program which is contained in the Blackjack form code module. Module level variable declarations define a host of integers required by the program. Most of the names are self-explanatory. These variables are used in multiple procedures in the form module and store the following values: the number of cards drawn by the player and dealer (numPlayerHits and numDlrHits), the current deck and the current location in the deck (curDeck and curCard) from which the dealer draws the next card, the location and image for the dealer's face-down card (hiddenCard, hiddenDeck, hiddenPic), the value of the cards in the player's and dealer's hands (scores), and the dealing order for the first four cards dealt for a new hand (dealOrder).
Private numPlayerHits As Integer
Private numDlrHits As Integer
Private curCard As Integer
Private curDeck As Integer
Private hiddenCard As Integer
Private hiddenDeck As Integer
Private hiddenPic As Image
Private scores(4, 1) As Integer
Private dealOrder As Variant
'Track the location in the deck.
'Track the location in the deck if there is more than one deck.
'Temporary storage of the face-down card.
'Track values of cards dealt to dealer and player. 'Set the order of Image controls for initial dealing of four cards.
Private Const PLAYER = 1 Private Const DEALER = 0 Private Const DEALERSTAND = 16
'Use to reference array index for scores. 'Dealer stands on this value or higher.
The Activate() event of the UserForm object initializes the variant array dealOrder. This array is a list of strings that match the Name property of four Image controls. The order of the strings is set to the order in which the initial four cards are dealt to the dealer and player for a new hand. I created this array so that I could simulate the dealing of the four cards using a loop (see DealCards() sub procedure); otherwise, a lot of repetitive code would be needed.
The InitForm() sub procedure is called to initialize some of the ActiveX controls on the form—namely, the Label and Combo Box controls.
Private Sub UserForm_Activate()
dealOrder = Array("imgDlr1", "imgPlayerl", "imgDlr2", "imgPlayer2") InitForm End Sub
Private Sub InitForm() Dim I As Integer
'Clear label controls.
lblResult.Caption = "" lblDlrScore.Caption = "0" lblPlyrScore.Caption = "0"
'Set values to be displayed in dropdown lists for the 'number of decks, and the value of a bet.
cmbNumDecks.Clear cmbNumDecks.Addltem ("1") cmbNumDecks.Addltem ("2") cmbNumDecks.Addltem ("3") cmbBet.Clear cmbBet.Addltem ("$2") cmbBet.Addltem ("$5") cmbBet.Addltem ("$10") cmbBet.Addltem ("$25") cmbBet.Addltem ("$50") cmbBet.Addltem ("$100") End Sub
The Change() event procedure of the cmbNumDecks Combo Box is triggered when the user changes its displayed value. This forces an immediate reshuffling of the deck with a call to the NeedShuffle() procedure that will show the Shuffling form and trigger its previously listed code. The Caption property of the Command Button control is set to "Deal" in case the player changes the number of decks immediately after the form is loaded and shown (i.e., when the Caption property reads "Begin").
The NeedShuffle() procedure accepts one optional Boolean argument that, when used, forces a reshuffling of the deck. If it is not forced, then the deck will still be shuffled if the current card location in the deck has reached the marker specified by the constant LASTCARD. If neither condition is met, then program execution exits the procedure without shuffling the deck. Remember, this procedure will have to be called after each card is dealt; so in most instances, the NeedShuffle() procedure will not cause the deck to be shuffled.
Private Sub cmbNumDecks_Change() NeedShuffle True cmdDeal.Caption = "Deal" End Sub
Private Sub NeedShuffle(Optional forceShuffle As Boolean) Public Const LASTCARD = 10
Test for the number of cards already played to see if the deck needs reshuffling. Must increment the deck and reset card number when using multiple decks.
Val(cmbNumDecks.value) * (bjcardsindeck - 1) - LASTCARD) _ Or forceShuffle Then frmShuffle.Show curCard = 0 'Reset deck location after reshuffling. curDeck = 0 Elself curCard > 51 Then curCard = 0 curDeck = curDeck + 1 End If End Sub
The Click() event of the Command Button control cmdDeal is triggered from the Blackjack form, but the action taken depends on the value of its Caption property. If the Caption property is set to "Begin", then the deck is shuffled and the Caption property is reset to "Deal". The Caption property will only read "Deal" when the program is set to begin a new hand; therefore, when the Caption property is set to "Deal", the game table must be cleared with a call to the ClearBoard() sub procedure before a new hand is dealt by calling the DealCards() sub procedure.
The last possible value of the Caption property is "Stand". In this case, the player has decided to stand on the current score of his or her hand and it is the dealer's turn to draw. First, the dealer's hidden card is displayed and score calculated with a call to the CalcScore() procedure. The simulation of the dealer's turn to draw is handled by the DealerDraw() procedure. After the dealer's turn is over and program execution returns to the Click() event, the game is ended with a call to GameOver().
Private Sub cmdDeal_Click()
If cmdDeal.Caption = "Begin" Then frmShuffle.Show cmdDeal.Caption = "Deal" Elself cmdDeal.Caption = "Deal" Then ClearBoard DealCards
Else 'Player decides to stand.
cmdHit.Enabled = False imgDlrl.Picture = hiddenPic.Picture CalcScore DEALER DealerDraw GameOver End If End Sub
The ClearBoard() sub procedure serves to reset variables and ActiveX controls on the form. The images of the cards from the Image controls are removed by setting their Picture property with the LoadPicture() method while passing it an empty string. The For/Each loop iterates through all ActiveX controls on the form, identifying those controls whose name begins with "img" in order to find the Image controls. Since all ActiveX controls on the form are part of a Controls collection object, I use a For/Each loop to iterate through the controls on the Blackjack form (named frmTable); however, I need the decision structure to identify the first three letters in the name of each control because there is no collection object for control types, only for all controls on the form.
The dealer's and player's hands are stored in the two-dimensional variable array called scores. The array's size is five rows by two columns, where the first column is reserved for the dealer's hand, and the second column for the player's hand. The value of each card dealt to both players is stored in this array.
Private Sub ClearBoard() Dim I As Integer Dim imgCtrl As Control
'Clear images of card from image controls.
For Each imgCtrl In frmTable.Controls
If Left(imgCtrl.Name, 3) = "img" Then imgCtrl.Picture = LoadPicture("") End If
'Reset variables and controls.
numPlayerHits = 0 numDlrHits = 0 lblDlrScore.Caption = "0" lblResult.Caption = "" For I = 0 To 4
cmbBet.Enabled = False End Sub
The DealCards() sub procedure handles the initial dealing of the four cards required to start a new hand. Since most of the required actions for each card dealt are the same, I wanted to handle this task with a loop; however, it is a bit more difficult to loop through four specific Image controls from a group of ten. This is why I declared the variant variable array named dealOrder—to identify these four Image controls. I also was careful to add the Image controls to the form in the same order specified in the dealOrder array (see Activate() event procedure). This way, I ensure that the For/Each loop iterates through the four Image controls in the desired order. (That is, once the first Image control listed in the dealOrder array is found.)
Once a proper Image control is identified, the program loads the card image into the Image control, the value of the card is stored in the variable array scores, the .wav file is played, and the program tests if the deck must be shuffled with a call to the NeedShuffle() procedure.
The first card is dealt face down to the dealer (represented by the image file Back.bmp); however, the program must remember the location of this card in the deck using the module level variables hiddenCard and hiddenDeck because it will be needed when the hand ends—at which time the program must display the card and calculate the dealer's score. The card image is also stored for later use by loading it into the Picture property of the image object variable hiddenPic with the LoadPicture() method. This does not display the image anywhere on the form because hiddenPic is an object variable, not an ActiveX control. This effectively stores the image in the computer's memory until it is needed. Alternatively, you could add another Image control to the form, set its Visible property to false, and load the image for the face-down card into its Picture property until it is needed. Figure 6.20 shows an example of the Blackjack form after the initial four cards of a hand are dealt.
Starting a new hand of Blackjack.
Starting a new hand of Blackjack.
Private Sub DealCards()
'Deals four cards; two each to the player and dealer.
Dim fileCards As String Dim fileSounds As String Dim imgCtrl As Control Dim I As Integer fileCards = ActiveWorkbook.Path & "\Cards\" fileSounds = ActiveWorkbook.Path & "\Sounds\"
Loop through the controls to find next image control. Load the image of the card, store the value of the card for scoring, increment to the next card, play the draw sound, and test if the deck needs reshuffling.
If I >= 4 Then Exit For 'Already found the 4 Image controls. If imgCtrl.Name = dealOrder(I) Then If (I = 0) Then imgCtrl.Picture = LoadPicture(fileCards & "Back.bmp")
hiddenCard = curCard hiddenDeck = curDeck
Set hiddenPic = New Image hiddenPic.Picture = LoadPicture(fileCards & _
theDeck(hiddenDeck).filename(hiddenCard) & ".bmp") scores(0, DEALER) = theDeck(curDeck).value(curCard)
Else imgCtrl.Picture = LoadPicture(fileCards & _
theDeck(curDeck).filename(curCard) & ".bmp")
If (I = 1) Then scores(0, PLAYER) = theDeck(curDeck).value(curCard) ElseIf (I = 2) Then scores(1, DEALER) = theDeck(curDeck).value(curCard)
Else scores(1, PLAYER) = theDeck(curDeck).value(curCard) End If curCard = curCard + 1 PlayWav (fileSounds & "\draw.wav") Delay Timer, 0.5 NeedShuffle I = I + 1 End If
'Score the player's hand.
CalcScore PLAYER cmdDeal.Caption = "Stand" cmdHit.Enabled = True End Sub
The Blackjack program calculates the dealer's and player's score with the variable array scores and the CalcScore() sub procedure. A For/Next loop iterates through the scores array, identifying which player's score to sum using the iPlayer argument, and totals the values of each card in a hand. The number of Aces in a hand are counted and scored as eleven; unless the total score exceeds twenty-one, in which case the Aces are scored as one.
Private Sub CalcScore(iPlayer As Integer)
'Calculates the player's and dealer's score. Pass 0 'for the dealer and 1 for the player.
Dim I As Integer Dim numAces As Integer Dim score As Integer Const MAXHANDSIZE = 5
'Calculates the score. Aces count one or eleven.
score = score + scores(I, i Player) If scores(I, i Player) = 1 Then numAces = numAces + 1 Next I
If (numAces > 0) Then score = score + 10 * numAces For I = 1 To numAces
If (score > 21) Then score = score - 10 Next I End If
If (iPlayer = 0) Then lblDlrScore.Caption = score
Else lblPlyrScore.Caption = score End If End Sub
The Command Button control cmdHit is enabled after the first four cards of a new hand are dealt (see Figure 6.20). Its Click() event is triggered each time the player decides (and is allowed) to draw another card. This procedure loads a card image into the proper Image control and records the value of the card before playing the .wav file that sounds like a card being flipped. Next, the score of the player's hand is calculated using CalcScore().
The module variable numPlayerHits was incremented by one early in the procedure. If the value of this variable reaches three, then the Command Button control cmdHit is disabled and this Click() event procedure cannot be triggered. The same is true if the player busts (score exceeds twenty-one). The screen shot in Figure 6.21 shows a hand where the player busted after drawing two cards (the two of hearts and king of clubs). Since the player busted, the dealer did not have to draw any more cards despite having a score less than sixteen.
A player bust in a hand of Blackjack.
O Microsoft Excel - ULackjnck.xls
L^I file Cdt View fcisert format Tack Gnta Wnd
Typo oqueslcn fct hot
The player's turn at drawing cards is over when they bust, draw three cards (giving them a total of five cards), or choose to stand on their current hand. The action taken when the player stands is handled in the Click() event procedure of the Command Button control named cmdDeal. If the player busts, the hand is immediately ended by displaying the dealer's hidden card, calculating its score, and calling the GameOver() sub procedure. If the player manages to draw three cards without busting, then the player is forced to stand on his or her hand because it is the only enabled Command Button on the form.
As always, when a card is dealt, the NeedShuffle() procedure is called to test if the deck needs to be shuffled.
Private Sub cmdHit_Click()
'Player chooses to draw another card.
Dim fileCards As String fileCards = ActiveWorkbook.Path & "\Cards\"
'Load the card image and record the score.
numPlayerHits = numPlayerHits + 1 If (numPlayerHits = 1) Then imgPlayer3.Picture = _
LoadPicture(fileCards & theDeck(curDeck).filename(curCard) & ".bmp") If (numPlayerHits = 2) Then imgPlayer4.Picture = _
LoadPicture(fileCards & theDeck(curDeck).filename(curCard) & ".bmp") If (numPlayerHits = 3) Then imgPlayer5.Picture = _
LoadPicture(fileCards & theDeck(curDeck).filename(curCard) & ".bmp") scores(numPlayerHits + 1, PLAYER) = theDeck(curDeck).value(curCard) PlayWav (ActiveWorkbook.Path & "\Sounds\draw.wav")
'Calculate player's score, increment deck to next card, and 'test if the player has reached maximum number of allowed hits.
CalcScore PLAYER curCard = curCard + 1 If numPlayerHits > 2 Then cmdHit.Enabled = False CalcScore DEALER End If NeedShuffle
'If player busts, show dealer's hand and end the game.
If IblPlyrScore.Caption > 21 Then imgDlrl.Picture = hiddenPic.Picture
After the player has selected to stand on his or her current hand, the DealerDraw() procedure is called in order to simulate the dealer's turn at drawing additional cards. This procedure uses a loop to draw up to three cards for the dealer as long as the dealer's score is less than sixteen. When a card is drawn, the card's image is loaded into the appropriate Image control, the card's value is stored, the dealer's score calculated, and the deck is tested to see if it needs shuffling.
Private Sub DealerDraw()
'Call if dealer needs hits. Dealer must stand on '16 or higher and hit with <16.
Dim fileCards As String fileCards = ActiveWorkbook.Path & "\Cards\"
'Dealer takes hits while score is <16 to a max of five cards.
Do While (IblDlrScore.Caption < DEALERSTAND) If (numDlrHits = 3) Then Exit Sub numDlrHits = numDlrHits + 1
If (numDlrHits = 1) Then imgDlr3.Picture = LoadPicture( _
fileCards & theDeck(curDeck).filename(curCard) & ".bmp") If (numDlrHits = 2) Then imgDlr4.Picture = LoadPicture( _
fileCards & theDeck(curDeck).filename(curCard) & ".bmp") If (numDlrHits = 3) Then imgDlr5.Picture = LoadPicture( _
fileCards & theDeck(curDeck).filename(curCard) & ".bmp")
PlayWav (ActiveWorkbook.Path & "\Sounds\draw.wav") Delay Timer, 0.5
scores(numDlrHits + 1, DEALER) = theDeck(curDeck).value(curCard) CalcScore DEALER
curCard = curCard + 1 NeedShuffle
A hand is over when the player busts or the dealer finishes drawing cards. In both cases, the GameOver() sub procedure is called to determine the winner, update the player's balance based on how much the player bet, and output the results to the form and the worksheet (calls WorksheetOutput() procedure) before resetting the ActiveX controls.
Figure 6.22 shows the Blackjack form after a hand won by the player when the dealer busted drawing the nine of diamonds.
A dealer bust in a hand of Blackjack.
□ Microsoft Excel Blackjack.*!:, i^J Blc Etfr: "¿low Insert Farmat leob ßata ündow HcJp
i Type a qlbstion fat h=l;
Private Sub GameOver()
'Display results when the hand is finished.
Dim earningsLength As Integer
Dim pScore As Integer, dScore As Integer earningsLength = Len(lblEarnings.Caption) betLength = Len(cmbBet.value) pScore = IblPlyrScore.Caption dScore = lblDlrScore.Caption
'Dealer and player push.
If (dScore = pScore) Then lblResult.Caption = "Push" End If
'Player wins if their score is higher than dealer's 'without busting or if dealer busts.
If ((Val(dScore) < Val(pScore)) And (Val(pScore) < 22)) _
Or ((Val(pScore) < 22) And (Val(dScore) > 21)) Then lblResult.Caption = "You Win!"
lblEarnings.Caption = "$" & Val(Right(lblEarnings.Caption, _
earningsLength - 1)) + Val(Right(cmbBet.value, betLength - 1))
'Dealer wins if their score is higher than player's 'without busting or if player busts.
If ((Val(dScore) > Val(pScore)) And (Val(dScore) < 22) _
Or (Val(dScore) < 22) And (Val(pScore) > 21)) Then l blResult.Caption = "Dealer Wins!"
lblEarnings.Caption = "$" & Val(Right(lblEarnings.Caption, _
earningsLength - 1)) - Val(Right(cmbBet.value, betLength - 1))
'Calculate player's balance.
earningsLength = Len(lblEarnings.Caption)
If Val(Right(lblEarnings.Caption, earningsLength - 1)) < 0 Then lblEarnings.ForeColor = RGB(255, 0, 0)
Else lblEarnings.ForeColor = RGB(0, 0, 150) End If
WorksheetOutput cmdHit.Enabled = False cmdDeal.Caption = "Deal" cmbBet.Enabled = True End Sub
The last requirement of the program is to output the results of the hand to the worksheet. Most of this code should be quite familiar to you as it simply copies the Caption property of the Label controls to cells on the worksheet and formats the winning score in bold. The new technique here is using the Find() method of the Range object to locate the next empty cell in column A of the worksheet. The Find() method takes several arguments but the What argument is the only one required. The What argument identifies the string you are looking for in the specified range (in this case, A:A). The After argument is optional, but I use it here to tell the Find() method to start looking after cell A1.
The Find() method returns a Range object. I used the Row property of the Range object returned by the Find() method in order to return the index of the first empty row in column A to the variable nextRow. Next, I use the value stored in the nextRow variable to identify where to write the results of the hand.
Private Sub WorksheetOutput()
'Output results of the hand to the worksheet.
Dim nextRow As Integer
'Find first empty row in column A and write results to that row.
nextRow = Range("A:A").Find(What:="", After:=Range("A1")).Row Range("A" & nextRow).value = IblDlrScore.Caption Range("B" & nextRow).value = lblPlyrScore.Caption Range("C" & nextRow).value = lblEarnings.Caption
'Put the winner in bold font. Color the player's 'balance to match the form.
If lblResult.Caption = "Dealer Wins!" Then Range("A" & nextRow).Font.Bold = True Elself lblResult.Caption = "You Win!" Then Range("B" & nextRow).Font.Bold = True End If
Range("C" & nextRow).Font.Color = lblEarnings.ForeColor End Sub
Finally, the QueryClose() event of the UserForm object unloads the forms from the computer's memory before ending the program. The QueryClose() event is triggered when the player closes the form.
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) Unload frmTable Unload frmShuffle End End Sub
That's it for the Blackjack program. Take the code, play with it, change it, add to it, learn from it, and enjoy. If you have trouble, then focus on just a small piece of the program until you figure it out before moving on to the next problem.
Was this article helpful?