Visual Basic for Applications/Procedures

This lesson introduces functions and subroutines.

Objectives and Skills

Objectives and skills for procedures include:

• Creating, running, and associating Sub and Function procedures
• Identifying the difference between Sub and Function procedures

Readings

1. Wikipedia: Function (computer science)
2. Wikipedia: Parameter (computer programming)
3. Microsoft: Writing a Sub Procedure
4. Microsoft: Writing a Function Procedure
5. Microsoft: Calling Sub and Function Procedures

Examples

'This macro accepts user input and then displays double the value entered.

Option Explicit

Sub Functions()
    Const Title = "Functions"
    Dim Value As Single
    Dim Result As Single
   
    Value = InputBox("Enter a value:", Title)
    Result = DoubleValue(Value)
    MsgBox Value & " * 2 = " & Result, vbOKOnly + vbInformation, Title
End Sub

Function DoubleValue(Value As Single) As Single
    DoubleValue = Value * 2
End Function

Activities

In these activities you will create macros that use functions to perform calculations. Your macros should include Option Explicit, Dim, InputBox, MsgBox, titles on the dialog boxes, an icon on the message box, and use appropriate data types for your variables.

1. Age Calculations
   1. Create a macro that asks the user how old they are in years. Then ask the user if they would like to know how old they are in months, days, hours, or seconds. Use an If/ElseIf/Else or Select Case statement to determine their selected time frame.
   2. Use the IsNumeric() function to validate user input for the numeric entry.
   3. Use Else or Case Else to validate user input for the selected time frame.
   4. Use functions to convert years to months, years to days, years to hours, and years to seconds.
   5. Include input validation and error handling in each function. Functions should use the Err.Raise method to raise an error if they receive an invalid parameter.
2. Temperature Conversion
   1. Create a macro that asks the user if they would like to convert Fahrenheit to Celsius or Celsius to Fahrenheit. Use an If/ElseIf/Else or Select Case statement to determine their selection and then gather the appropriate input and calculate and display the converted temperature.
   2. Use Else or Case Else to validate user input for the selected conversion type.
   3. Use the IsNumeric() function to validate user input for the numeric entry.
   4. Add error handling to handle any runtime errors that could occur in the macro.
   5. Use functions to convert Fahrenheit to Celsius and Celsius to Fahrenheit.
   6. Include input validation and error handling in each function. Functions should use the Err.Raise method to raise an error if they receive an invalid parameter.
3. Area Calculations
   1. Create a macro that asks the user what shape they would like to calculate the area for. Use an If/ElseIf/Else or Select Case statement to determine their selection and then gather the appropriate input and calculate and display the area of the shape.
   2. Use Else or Case Else to validate user input for the selected shape type.
   3. Use the IsNumeric() function to validate user input for any numeric entries.
   4. Add error handling to handle any runtime errors that could occur in the macro.
   5. Use separate functions to calculate the area of each shape.
   6. Include input validation and error handling in each function. Functions should use the Err.Raise method to raise an error if they receive an invalid parameter.
4. Greatest Common Factor / Greatest Common Divisor Recursive Function
   1. Review MathsIsFun: Greatest Common Factor. Create a macro that asks the user to enter two integer values.
   2. Add an IsInteger function you create that determines whether the passed parameter is an integer and returns a Boolean value based on the input provided. Do not assume the function input is numeric. A user could pass a string value and ask if the content of the string is an integer. See the documentation on the IsNumeric function for more information.
   3. Based on the recursive algorithm provided in Wikipedia: Recursion (computer science), use a recursive function to calculate the greatest common factor (greatest common divisor) of the two values and then display the result.

See Also

• EasyExcelVBA: How to Write A Function

References

Type classification: this is a lesson resource.

← Errors

Visual Basic for Applications

Classes →