{[ promptMessage ]}

Bookmark it

{[ promptMessage ]}

Homework5_VBAFxns_ClassEx_fall09

Homework5_VBAFxns_ClassEx_fall09 - Homework 5 Writing a...

Info icon This 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
Image of page 1

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

View Full Document Right 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.
Image of page 2
Image of page 3
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}

What students are saying

  • Left Quote Icon

    As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

    Student Picture

    Kiran Temple University Fox School of Business ‘17, Course Hero Intern

  • Left Quote Icon

    I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

    Student Picture

    Dana University of Pennsylvania ‘17, Course Hero Intern

  • Left Quote Icon

    The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

    Student Picture

    Jill Tulane University ‘16, Course Hero Intern