Visual Basic for Applications/Classes

This lesson introduces classes.

Objectives and Skills

Objectives and skills for classes include:

• Assigning object references to a variable by using the Set statement
• Setting properties of an object by using With statements
• Making procedures from a class or module available to code
• Calling procedures in different modules

Readings

1. Wikipedia: Object-oriented programming
2. Microsoft: Program with Class Modules
3. Pearson Software: Classes in VBA

Examples

Module

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

Option Explicit

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

Class Module

'This class module contains functions to calculate various results.

Option Explicit

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

Activities

In these activities you will create macros that use functions inside class modules 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 functions to convert years to months, years to days, years to hours, and years to seconds.
   3. Include input validation and error handling in each procedure and function.
   4. Put the conversion functions in a separate class module. Create an instance of the class and then call the conversion functions to calculate, but not display, the converted values.
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 functions to convert Fahrenheit to Celsius and Celsius to Fahrenheit.
   3. Include input validation and error handling in each procedure and function.
   4. Put the conversion functions in a separate class module. Create an instance of the class and then call the conversion functions to calculate, but not display, the converted values.
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 separate functions to calculate the area of each shape.
   3. Include input validation and error handling in each procedure and function.
   4. Put the conversion functions in a separate class module. Create an instance of the class and then call the conversion functions to calculate, but not display, the converted values.
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.
   4. Put the two functions in separate class modules. Create an instance of each class and then call the functions to validate and calculate the greatest common factor.

References

Type classification: this is a lesson resource.

← Procedures

Visual Basic for Applications

Word →