Visual Basic for Applications/Excel
Jump to navigation Jump to search
This lesson introduces Excel macros.
Objectives and Skills
Objectives and skills for Excel scripting include:
- Using Microsoft Excel objects
- Microsoft: Refer to Cells by Using Index Numbers
- Microsoft: Looping Through a Range of Cells
- Microsoft: Date Function
- Microsoft: Weekday Function
- Microsoft: DateSerial Function
'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
'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
In these activities you will create macros which interact with Excel workbooks.
- Calendar Month
- 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.
- Calendar Year
- 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.
- 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.