Homework5_VBAFxns_ClassEx_fall09 - Homework 5: Writing a...

Info iconThis preview shows pages 1–3. Sign up to view the full content.

View Full Document Right Arrow Icon
1 Homework 5: Writing a Custom VBA Function © Objectives of this in-class assignment : Recognize the benefits of writing a custom VBA function that may be used in multiple applications. Write a custom function in VBA. Test and use the custom function from a VBA procedure. Move the function module to a different workbook and implement it there. Use the Locals Window and/or Immediate Window to troubleshoot program errors as needed. Continue to gain a better understanding of the VBA programming environment. o Including use of variables o Function inputs, output, and data types for each Practice other chapter "skills" (for chapter 14) as listed in the Chapter 14 class notes. You may work individually or in teams of two for this in-class assignment. No more than 2 students may work together on this assignment. This assignment is worth 12 homework quiz points. To receive full credit, the file must be complete, and also named and submitted as directed (see end of this document for submission instructions). Part 1 Instructions Task: Write a custom function that creates a new worksheet name to use for a newly-added worksheet. The function should accept one input, the name of the previous month's worksheet. 1. Open a new Excel workbook and save it as a macro-enabled workbook. Name it anything you like. 2. Remove all except one worksheet. Name the one remaining worksheet Sep2009. 3. Insert a new VBA module into the project workbook. Name the new module " Functions ". 4. Double-click on the code module to open the code window (if it does not open automatically). In the code window write a custom function that implements the logic for assigning a new worksheet name when the previous worksheet name is known (used as an input to the function). 5. Write the VBA custom function code. The logic for this custom function is presented below along with the actual VBA code to add (in blue). a. Write the function header i. Function keyword ii. Function name iii. Parameter list (the function inputs and data type of each) Data type for function's return value Function NewSheetName(oldName As String) As String
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
2 b. Declare variables: i. To hold the month abbreviation parsed from the previous sheet name: Dim strMonth As String ii. To hold the "year" characters parsed from the previous sheet name. Dim intYear As Integer
Background image of page 2
Image of page 3
This is the end of the preview. Sign up to access the rest of the document.

Page1 / 6

Homework5_VBAFxns_ClassEx_fall09 - Homework 5: Writing a...

This preview shows document pages 1 - 3. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online