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 it to dropbox.

• When finished upload your file to the Lab 2 Dropbox in MyLearningSpace to be marked.

1. The file TemperatureDegrees.xlsx has temperature degrees in the range A1:A50. Write a sub that reports the minimum degree, maximum degree, number of days it is expected to be snowed ( where temperature degree is less than or equal zero) and number of days is expected to be warm days ( where temperature degree is greater than or equal 30) is 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, CountIf.

   • 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.

   • 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"
     

   ---

2. Create a VBA subroutine to calculate Training Heart Rate (THR) by asking for the user's age and resting heart rate. Use MsgBox and InputBox for output and input. Place this task in a new module called Task2 inside the file containing Task1 above.

   In order for exercise to be beneficial to the cardiovascular system, the heart rate (number of beats per minute) must exceed the THR. (A THR is typically between 55 - 70 beats per minute (bpm).) A person's THR can be calculated from her age and resting heart rate (pulse when first awakening) as follows:

   1. Calculate the maximum heart rate as 220 - age.
   2. Subtract the resting heart rate from the maximum heart rate.
   3. Multiply the result in step 2) by 60%, and then add the resting heart rate.

Note