1 / 25

Visual Basic for Applications in Microsoft Excel (1)

Visual Basic for Applications in Microsoft Excel (1). Week 6. Week 6. Assessment 2 Do Loops Custom Functions. Sub SheetCounter () MsgBox ("There are " & Worksheets.Count & " worksheets in this _ workbook") End Sub Sub CreateStaffList () Dim r As Worksheet Dim s As Worksheet

abraham-taylor
Download Presentation

Visual Basic for Applications in Microsoft Excel (1)

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Visual Basic for Applications in Microsoft Excel (1) Week 6

  2. Week 6 Assessment 2 Do Loops Custom Functions

  3. Sub SheetCounter() MsgBox ("There are " & Worksheets.Count & " worksheets in this _ workbook") End Sub Sub CreateStaffList() Dim r As Worksheet Dim s As Worksheet Worksheets.Add.Name = "StaffList" Set r = Worksheets("net_pay") Set s = Worksheets("StaffList") r.Range("G2:G10").Copy s.Range("A1:A10") End Sub

  4. Assessment 2 Produce a macro that, if run on a new workbook will: • Ask the user how many stores and how many products they wish to monitor • Create new worksheets so that there are enough for one for each week (4?) and a summary sheet • Name each work sheet appropriately • Ensure each sheet is set up ready for the user to input data , with formulas to calculate the row and column totals

  5. Extensions • Add formulas in the summary sheet to “pull through” the data from the previous sheets • Extend the summary sheet to hold other useful calculations (eg. best performing store / product) • Allow the user to input the number of weeks the workbook will be used for • Ask the user which month/ year the workbook is intended for and save the workbook using month/year as a name

  6. Do loops • There are four varieties of the Do loop: • Do While . . . Loop • Do . . . Loop While • Do Until . . . Loop • Do . . . Loop Until

  7. Do While • (Note: Ctrl + Break should terminate most runaway loops; if not Ctl Alt + Delete and end Excel - and lose unsaved work!)

  8. Example of Do While Sub MarkEmptyCells() Do While IsEmpty(ActiveCell) With ActiveCell .Value = "This cell is blank" .Font.Bold = True .Offset(1, 0).Activate End With Loop MsgBox "We are out of the loop. That's all folks" End Sub

  9. Do..Loop While

  10. Do Until .. Loop and Do .. Loop Until

  11. Example of Do Until Sub markEmptyCells_until() Do Until not IsEmpty(ActiveCell) ActiveCell.Value = "Default value" ' whatever ActiveCell.Font.Bold = True 'makes it easy to see ActiveCell.Offset(1, 0).Select Loop MsgBox "We are out of the loop. That's all folks" End Sub

  12. Points to Note: • All the following programs select the first empty cell in the same column below the active cell, four illustrating the use of Do loops; the fifth uses the statement that is recorded when you press Ctrl and the Down Arrow key

  13. Points to note:

  14. Points to note:  Loops can contain control statements such as If Then or other loops If statements can contain loops You can break out of a loop if necessary by using the Exit Do keyword

  15. Do Loop Exercises Do Loop Leap Years (part 1)

  16. Creating Custom Functions function functionname() -- function code goes here -- end function You are probably familiar with using built-in functions like SUM, AVERAGE and IF. With VBA, you can create your own functions, uniquely tailored to your needs.

  17. Analysis of a NetPay Function Let's say you want to create a function that calculates how much your Net Pay is after deductions. The function would involve these values: GrossPay: how much you earn before deductions Income Tax as a percentage National Insurance as a percentage Pension Fund as a percentage

  18. Analysis of a NetPay Function NetPay is how much you take home after IncomeTax, National Insurance and Pension Fund contributions have been deducted. The maths would look like this: Deductions = (GrossPay * IncomeTax) + (GrossPay * NI) + (GrossPay * Pension) NetPay = GrossPay - Deductions

  19. Analysis of a NetPay Function Function NetPay _ (GrossPay As Double, IncomeTax _ As Double, NI As Double, Pension As_ Double) As Double Dim Deductions As Double Deductions = (GrossPay * IncomeTax) +_ (GrossPay * NI) +(GrossPay * Pension) NetPay = GrossPay - deductions End Function

  20. Using the function You can use functions in the same way as you use Excel functions. Your VBA functions will appear in the “User Defined Functions” category

  21. Another Example A function called myFV, (so it doesn’t conflict with the inbuilt Excel function FV) which works out the value of an investment after compound interest has been added, i.e. its future value. Function myFV(pr As Single, rate As Single, nper As Integer) As_ Single myFV = pr * (1 + rate) ^ nper End Function  On the other hand you might also want a function that works out just the compound interest i.e. minus the principal. Function Compound(pr As Single, rate As Single, nper As Integer) as Single Dim fv As Single fv = (pr * (1 + rate) ^ nper) compound = fv - pr End Function

  22. VBA Text manipulation functions

  23. Calling the Insert Function dialog You can create a macro to call the FunctionWizard or Insert Function dialog – which would save a few seconds each time you run it: ActiveCell.FunctionWizard You would then choose which function to use …

  24. WorksheetFunction method You can call functions with the following code: Application.WorksheetFunction.FunctionName(arg1,arg2)

  25. Next Week Error Handling Conversion Functions

More Related