10/16 Day #4 VBA - MsgBox, InputBox, and more

[ajlee12]

Morning Exercise Review

RandomQuestion Macro

Developer tab -> Visual Basic -> Insert a Modules folder on the same level as Microsoft Excel Objects

The options:

• Option Explicit
• Option Base 1

The Sub:

Sub Random_Question()

' 1. We want to clear out values in A3 (question) and A7 (answer) in UI sheet so they can be populated with a random question later.

' 2. We want a random row from the QandA sheet.

' 3. We want a Message Box.

End Sub

1. Clear out values

We can Select and then ClearContents:

Range("$A$3").Select
Selection.ClearContents

Range("$A$7").Select
Selection.ClearContents

Or we could just set the cells' values to empty strings:

Range("$A$3", "$A$7").Value = ""

2. Store a random row from the TriviaTable

Dim UserInterface As Worksheet
Dim QuestionsAndAnswers As Worksheet
Dim TriviaTable As Object
Dim QuestionsRange As Range
Dim QuestionsCount As Integer
Dim RandomIndex As Integer

' The data type needs to be a Range, not Integer, because later we'll be assigning a range of cells to it.
Dim RandomRow As Range

' Assign the actual sheets to the variables.
Set UserInterface = Sheets("UserInterface")
Set QuestionsAndAnswers = Sheets("QuestionAndAnswers")

' This only works if the table was actually named "TriviaTable".
Set TriviaTable = QuestionsAndAnswers.ListObjects("TriviaTable")

' Within the Table, we want only the range that contains the questions.
Set QuestionsRange = TriviaTable.DataBodyRange

' This gets us the total number of questions,
' Don't need "Set" for a primitive value.
QuestionsCount = QuestionsRange.Rows.Count

RandomIndex = Int(Rnd * QuestionsCount) + 1

Set RandomRow = QuestionsRange.Rows(RandomInd)

' If we have multiple sheets, remember to preface your range with the sheet's name.
UserInterface.Range("$A$3,$A$7").Value = ""

' Get the value in the first column of the random row and assign it to the row in "UserInterface" 
UserInterface.Range("$A$3").Value = RandomRow.Columns(1).Value

---

• Hint: You can declare the variables in the "module level" next to the options so that they're available for all Subs in the module.

  • You'll still need to do the Sets in each Sub though.
  • You can read more about module-level variable declarations here.

• Hint: You cannot undo a Macro. You may want to create a backup copy of the targeted sheet.

• Hint: If you see "Out of range" error, make sure that you have the correct workbook open.

3. VBA Message Box

MsgBox is a built-in function. Check here (docs) for the syntax and the arguments, and here for more code examples.

MsgBox "Click OK to show the answer", vbOKOnly, RandomRow.Columns(1).Value

• 1st arg (req'd): Prompt

• 2nd arg (optional): The button do you want to show

  • "=" means a default is provided. In this case, it's vbOKOnly.

• 3rd arg (optional): Title of the prompt

UserInterface.Range($A$7).Value = RandomRow.Columns(2).Value

Clear Screen Function

Sub Clear_Screen()

  Dim UserInterface As Worksheet
  Set UserInterface = Sheets("UserInterface")
  UserInterface.Range("$A$3,$A$7").Value = ""

End Sub

Hint: Calling another Sub from within a Sub -- We can call Clear_Screen from within Random_Question to leverage the former's clearin functionality.

Guess the Number Game

Worksheets:

1. UserInterface
2. SecretNumber

The Sub:

Sub Guess_The_Number()
  ' Check and see if the secret number is already stored somewhere.

  ' If not, record it.

  ' If yes, assign it to a variable.

End Sub

Generating secret number

Let's target the SecretNumber worksheet:

Dim SecretNumberWorksheet As Worksheet
Set SecretNumberWorksheet = Sheets("SecretNumber)

Check if a cell is empty:

Hint: Google search "vba check if cell is empty:

' If a cell is empty...
If IsEmpty (SecretNumberWorksheet.Range("$A$1").Value) Then
' Put in that cell a random value between 1 and 100.
  SecretNumberWorksheet.Range("$A$1").Value = (Rnd * 100) + 1
End If

' If the cell is NOT empty, just assign the cell's value to a `SecretNumber` variable 
' (Don't forget to "Dim" it as an integer first).
SecretNumber = SecretNumberWorksheet.Range("$A$1").Value

Letting the player guess a number

We need to:

• Ask the user for a guess and store the guess in a var.
• If the guess is too low
• If the guess is too high
• Otherwise

Use InputBox to get user's input

Good explanations here: https://docs.microsoft.com/en-us/office/vba/api/excel.application.inputbox

UserGuess = Application.InputBox("Guess the number", Type:=1)

• Note that we set the return data type with Type:=1 (1 represents the number type).

Use If conditionals to handle guesses

If UserGuess < SecretNumber Then
  ComputerFeedback = "TOO LOW"
Else If UserGuess > SecretNumber Then
  ComputerFeedback = "TOO HIGH"
Else 
  ComputerFeedback = "JUST RIGHT"
End if

Put user's guesses into the table

Add rows to a table

GuessesTable.ListRows.Add AlwaysInsert:=True

Put the user's input to the last row.

' To target the last row, count the number of existing rows and insert that number as the argument for "Rows".
GuessesTable.Range.Rows(GuessesTable.Range.Rows.Count).Columns(1).Value = UserGuess

' Also, we want the feedback to appear on the column next to the user's guesses.
GuessesTable.Range.Rows(GuessesTable.Range.Rows.Count).Columns(2).Value = ComputerFeedback

Congratulate if the user got it right

If ComputerFeedback = "JUST RIGHT"
  MsgBox "Amazing guess!", vbOKOnly , "You win!"

Else
  MsgBox "That's not the correct answer. Try again.", vkOKOnly, "Wrong answer!"
End If

Afternoon Group Assignment

Random Student Points

Overall strategy:

1. Generate a random number between 1 and total number of students.

2. Select a random row from StudentsTable.

3. Prompt user to input score.

4. Check if the score cell is empty.

   • If yes, store user input value to the cell.
   • If not, add user input value to existing value the cell.