Visual Basic for Applications/Arrays

This lesson introduces arrays.

Objectives and Skills

Objectives and skills for arrays include:

• Declaring and initializing fixed-size arrays
• Changing the size of an array dynamically

Readings

1. Wikipedia: Array data type
2. Microsoft: Declaring Arrays
3. Microsoft: Using Arrays
4. Microsoft: ReDim Statement

Examples

'This macro accepts user input and then displays the product of the values entered.

Option Explicit

Sub Arrays()
    Const Title = "Arrays"
    Dim Values(2) As Single
    Dim Result As Single
    
    Values(1) = InputBox("Enter Value 1:", Title)
    Values(2) = InputBox("Enter Value 2:", Title)
    Result = Values(1) * Values(2)
    MsgBox "The product is: " & Result, vbOKOnly + vbInformation, Title
End Sub

Activities

1. Initialize an Array
   1. Review Microsoft: Array Function.
   2. Create a macro that initializes an array with three values, such as the scores of three assignment grades.
2. Calculate Average
   1. Review MathsIsFun: Average.
   2. Extend the macro above so that it calculates and displays the average of the three array values.
3. Store Values in an Array
   1. Extend the macro above so that instead of initializing the array with three values, the macro uses InputBox to request the three values from the user and store the values in the array.
   2. Calculate and display the average of the three array values as before.
4. Age Calculations
   1. Create a macro that asks the user how old they are in years, and then calculate and display their approximate age in months, days, hours, and seconds. Use an array to store the input value and the values for months, days, hours and minutes as they are calculated. Then display the results.
5. Area Calculations
   1. Create a macro that asks the user for the dimensions of different shapes and then calculate and display the area of the shapes. Use an array to store the input values and the values for the results as they are calculated. Then display the results.

See Also

• EasyExcelVBA: Arrays

References

Type classification: this is a lesson resource.

← Loops

Visual Basic for Applications

Errors →