Visual Basic for Applications/Excel

This lesson introduces Excel macros.

Objectives and Skills

Objectives and skills for Excel scripting include:

• Using Microsoft Excel objects

Readings

1. Read Microsoft: Refer to Cells by Using Index Numbers.
2. Read Microsoft: Looping Through a Range of Cells.
3. Read Microsoft: Date Function.
4. Read Microsoft: Weekday Function.
5. Read Microsoft: DateSerial Function.

Examples

Spreadsheets

'This macro generates a multiplication table in the active worksheet.

Option Explicit

Sub Sample11a()
    Dim Row As Long
    Dim Column As Long
    
    For Row = 1 To 10
        For Column = 1 To 10
            ActiveSheet.Cells(Row, Column) = Row * Column
        Next
    Next
End Sub

Debug Window

'This macro demonstrates date functions.

Option Explicit

Sub Sample11b()
    Debug.Print "Month: " & Format(Date, "mmmm yyyy")
    Debug.Print "Current Day: " & Day(Date)
    Debug.Print "Day of Week: " & Weekday(Date)
    Debug.Print "Abbreviation: " & Format(Date, "ddd")
    Debug.Print "First Day: " & DateSerial(Year(Date), Month(Date), 1)
    Debug.Print "Days in Month: " & DateSerial(Year(Date), Month(Date) + 1, 1) - DateSerial(Year(Date), Month(Date), 1)
End Sub

Activities

In these activities you will create macros which interact with Excel workbooks.

1. Calendar Month
   1. Create a macro that generates a calendar for the current month in the active worksheet. The Date function returns the current date. The Format function can be used with a format of "mmmm yyyy" to generate the month and year. The Weekday function returns the day of week, which can be used to determine a day's column number. Your macro should include Option Explicit, Dim, and use appropriate data types for variables. Do not use the Select method or the Selection object in your macro. Instead use direct column and cell references.
2. Calendar Year
   1. Extend the calendar macro above to generate a calendar for the current year in the active workbook, with a separate worksheet for each month. Name each worksheet based on the month name.
3. Charts
   1. Create a macro that inserts a chart as a new sheet with the chart type and content based on the current selection. If the current selection is a single row, create a column chart. If the current selection is a single column, create a pie chart. If the current selection is multiple rows and columns, create a scatter chart.

References

Resource type: this resource is a lesson.