Chapter 40, Functions

Chapter 40 is sort of a non-entity.
- Give it a read.
- Functions can not change cell values, they return a value.
  - It doesn't appear that you can do a msgbox either.
  - This makes sense, they are not necessairly part of the user interface
- Debugging,
  - set a breakpoint
  - Force a recalculate
A GCD function already exists, but
- ```
GCD(a,b) 
  if (b == 0) 
     return a
  else
     return GCD(b,a%b)
```
- Can you write a non-recursive version (GCDL)
- Can you write a recursive version (GCDR)

UserForms

This is chapter 41
He starts by looking at msgBox and InputBox
- These are not really part of the chapter.
- But they are primitive ways to get input from the user.

We have looked at the MsgBox already

Takes
- prompt: string
- list of buttions
  - Unix people, note this is + not &, strange
- title for the window
- help options.
Returns the button selected.

Sub Mesg()
    ActiveCell.Value = MsgBox("Click on One", 3 + 512 + 16, "The Title of the Window")
End Sub

You can give arguments out of order by using Name:=value

Sub Mesg2()
    PromptStr = "Give Me Input"
    TheButtons = 2 + 64
    ATitle = "Her Royal Magesty"
    msg = MsgBox(Title:=ATitle, Buttons:=TheButtons, Prompt:=PromptStr)
    ActiveCell.Value = msg
End Sub

Can you create a magic 8 ball macro

Prompts the user to state a question, and click done
Has the title "Magic 8 Ball"
Puts a response in a cell.
You might look into the Select Case statement.

Sub EightBall()
    Value = MsgBox("Speak a question for the magic 8 ball", 0, "The Magic 8 Ball")
    ans = rnd()*19
    Select Case ans 
       Case 0 To 4: msg = "yes"
       Case 6 To 9: msg = "no"
       Case 10 To 19: msg = "who knows"
    End Select
    ActiveCell.Value = msg

End Sub

The InputBox allows a single input.

Takes
- Prompt
- Title
- Default : default value
- Left: x position for dialog box.
- Top: y position for dialog box.
- Type
Returns the type specified or text

Sub GetName()
    Dim Name As String
    Name = Application.InputBox(Prompt:="What ... is your name?", _
                                Type:=2, _
                                Default:="Arthur, King of the Britons", _
                                Title:="Question 1")
    ActiveCell.Value = Name
End Sub

Note the _ extends a line
An Improved 8 ball

Sub EightBall2()
   Value = InputBox(prompt:="Enter a question for the 8 Ball", _
                    Title:="The mighty 8 Ball")
   Sum = Rnd() * 100
   For i = 1 To Len(Value)
      Sum = Asc(Mid(Value, i, 1)) + Sum
   Next i
   Sum = Sum Mod 20
   ActiveCell.Value = Decode(Sum)
End Sub

Function Decode(i)
    Select Case i
       Case 0: Decode = "It is certain."
       Case 1: Decode = "It is decidedly so."
       Case 2: Decode = "Without a doubt."
       Case 3: Decode = "Yes - definately."
       Case 4: Decode = "You may rely on it."
       Case 5: Decode = "As I see it, yes."
       Case 6: Decode = "Most likely."
       Case 7: Decode = "Outlook Good."
       Case 8: Decode = "Yes."
       Case 9: Decode = "Signs point to yes."
       Case 10: Decode = "Reply hazy, try again."
       Case 11: Decode = "Ask again later."
       Case 12: Decode = "Better not Tell You"
       Case 13: Decode = "Cannot predict now."
       Case 14: Decode = "Concentrate and ask again."
       Case 15: Decode = "Don't count on it."
       Case 16: Decode = "My reply is no."
       Case 17: Decode = "My sources say no."
       Case 18: Decode = "Outlook not so good."
       Case 19: Decode = "Very doubtful."
    End Select
End Function

Can you write a macro that asks all three questions?
- What .. is your name?
- What ... is your quest?
- And one of the following
  - What ... is your favorite color?
  - What ... is the capital of Assyria?
  - What... is the air-speed velocity of an unladen swallow?
- And returns alive or dead to the cell?