Visual Basic for Applications/Arrays

This lesson introduces arrays.

Objectives and SkillsEdit

Objectives and skills for arrays include:

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

ReadingsEdit

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

MultimediaEdit

ExamplesEdit

'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

ActivitiesEdit

  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 AlsoEdit

ReferencesEdit

  Type classification: this is a lesson resource.