Objectives

Write a basic VBA program
Use Msgbox and Input for simple input and output
Declare variables to store values

VBA Programming Basics

Start all VBA modules with OPTION EXPLICIT
It is expected that you have already worked through some of the examples in the textbook and already have recorded a macro, and worked with VBA code from the text.

Simple Input and Output

Displaying a message on the screen:

Simple usage:

Msgbox "Text you want displayed."

Full Usage:

result = Msgbox(Prompt, [Buttons as VBMsgBoxStyle = vbOK], [Title], [Helpfile], [Context]) ' Returns a VBMsgBoxResult

Note: In this usage you must put parentheses around the arguments of the Msgbox function.

[Buttons]: A series of constants that control which buttons appear on the dialog box. For example, vbOK displays just the OK button. Intellisense provides a list of possible options:

(See Intellisense or Help for more options)
[Title]: Text to appear in the title bar of the dialog box.
[Helpfile]: Reference to links in the Office Helpfile. It is possible to create your own help system, but its quite a bit of work. It would be easier to provide hyperlinks to help within your document or to web pages stored in the current directory.
[Context]: Used when referring to specific items in the Office Help file.

Getting Input From a User:

myVar = InputBox(Prompt, [Title], [Default value], [XPos], [YPos], [Helpfile], [Context]) ' Returns a String

Since the value typed into the InputBox is stored as a String, if you are asking for numbers you must convert them into the correct data type. Converting strings to and from other datatypes is done using the functions: CByte, CInt, CDbl, or CStr.

Sub InputExample()
    ' A sample for InputBox and type conversion
    ' Declare variables before use
    Dim age As Byte
    Dim answer As String
    Dim msg As String
    Dim title As String

    msg = "How old are you?"
    title = "A simple question"
    answer = InputBox(msg, title, "-enter age here-")

    ' It works in simple cases without doing this, but it's bad practice.
    ' InputBox returns a String, but you want it to be a Byte
    age = CByte(answer)
    MsgBox "You say you are " & age & "?"
End Sub

Some further examples can be found on the Sample Message Box Code.

String Concatenation

In programming you sometimes want to combine two or more strings together, or combine a literal string and the value of a string variable. This is called concatenation and is done using the & character, as in the previous example. Other examples:

"My name is " & "Rick."

creates the string:

"My name is Rick."

If you have a variable named tot_pop then you can output its value (with a trailing period) in this way:

Msgbox "The total population was " & tot_pop & "."

Using Worksheet Functions

Never forget about the "A" in VBA. Remember (and learn!) about the advanced functionality of Excel that you have at your disposal and use it when necessary. Don't re-invent the wheel, and don't write code to do something that Excel already does.

You can access the worksheet functions in Excel directly by using the WorksheetFunction object.

The following example uses the WorksheetFunction object to count all the items in a list that match what the user inputs: using_worksheet_functions.xlsm.

Here is a much simpler example:

Sub simpleWorksheet()
    MsgBox "The total is: " & WorksheetFunction.Sum(Range("A1:A5"))
End Sub

The textbook explains the use of the WorksheetFunction object.

Practice

To practice for the task before you get to lab, you can work on the following:

In a spreadsheet enter the numbers from 1 to 10 in different cells in column A. Write a subroutine to write the sum of those numbers in cell C5.
Write a subroutine to ask the user for their name and their age, then display it back using the Msgbox command and write the results into two different spreadsheet cells.
Difficult: Write an spreadsheet application that calculates the user's BMI based on the formula here and warns them of possible illnesses from the site here.

All tasks are to be completed on your own.

Save the spreadsheet file as a Macro Enabled Workbook (.xlsm) file before you write more code. Name your spreadsheet with your network login followed by _l02.xlsm. For example, if your network login was barn4520, then your lab file should be named barn4520_l02.xlsm. Save often.
Show the Developer Tab in Excel.
Put all code into a Module. Use a different Module for each task and name them appropriately as Task1 or Task2 etc.
Copy and paste this header into your code modules and fill it out:

Option Explicit

' ==== CP212 Windows Application Programming ===============+
' Name: Your Name
' Student ID:
' Date:
' Program title:
' Description:
'===========================================================+

Make sure you test your tasks before submitting your lab to the dropbox to be marked.
When finished upload your file to the Lab 2 Task Dropbox in MyLearningSpace.

The file ExamScores.xlsx has scores for an exam in the range A1:A100. Write a sub that reports the average, standard deviation, minimum, and maximum of the scores in a message box. Use Excel's functions (with WorksheetFunction) to do the arithmetic. The resulting message box should look something like this:
- The Excel functions you need are called Min, Max, Average, and Stdev.
- Make sure you place a button on the worksheet to run your subroutine. Save this file as a Macro Enabled Workbook (.xlsm) file to make sure your code can be easily run.
- To round the values to 2 decimal places, you can use the Round method as follows: Round(value, 2)
- You can match the output above by using a constant for the Carriage Return / Line Feed (CRLF) characters in your output like this:
```
Msgbox "Firstline" & vbCRLF & "SecondLine"
```
  or:
```
Msgbox "Firstline" & vbNewline & "SecondLine"
```
Create a VBA subroutine to compute Body Mass Index (BMI) by asking for the user's height (in meters) and weight (in kg). Use MsgBox and InputBox for output and input. Place this task in a new module called Task2 inside the file containing Task1 above.

Body mass index (BMI) is a measure of body fat based on height and weight that applies to adult men and women. Enter your weight and height using standard or metric measures, as follows:
1. Ask user to enter his/her height in metres , for example: 1.85
2. Ask user to enter his/her weight in kg , for example: 75
3. Compute the BMI as : BMI = weight / height²
4. Use a Single float data type for your variable, height, weight, and BMI
5. Show the height, weight, and calculated BMI in a message box

Note
You have to use Button(s) on worksheet(s) to run the task(s) in this lab and all next labs. Marks will be deducted if you do not use Buttons

CP212 Lab 2 : Input/Output and Ranges