1 / 24

Lecture Outline

This lecture outline covers topics such as variable scope, calling subprograms, programming style, and the use of dialog boxes and worksheets in VBA programming. It includes tips on good programming style, variable naming conventions, indentation, and the use of comments and constants. The examples provided demonstrate the creation of a user form and the use of worksheet functions in VBA programming.

mwhalen
Download Presentation

Lecture Outline

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. Lecture Outline Variable Scope Calling another subprogram Programming Style Dialog Boxes (UserForm) Worksheet button Functions Spreadsheet vs VBA

  2. Variable Scope Procedure Level (local) Dim iMarbles As Integer placement: within Sub and End Sub Module Level (global) Dim giTotal As Integer placement: before all Sub statements Project Level (public) Public piGrandTotal As Integer placement: before all Sub statements

  3. Calling another subprogram Sub FirstOne() MsgBox “In 1st subprogram…” SecondOne End Sub Sub SecondOne() MsgBox “In 2nd subprogram…” End Sub

  4. Programming Style Good style improves readability of code and your program is easier to maintain! 1. Comments (documentation) at top (your name, date, etc) each subprogram each variable each major section of code Don’t restate the obvious! Dim iCount As Integer ‘ declare integer variable

  5. 2. Good variable names describe what variables store not too long, or too short 3. Indentation subprograms and if statements 4. Constants for values that do not change 5. White space leave the occasional blank line Programming Style

  6. 6. Minimize variable scope only make a global or public variable if needed 7. Declare and Initialize variables Option Explicit forces variable declaration set initial value of variables appropriately Programming Style

  7. Programming Style - example

  8. Programming Style - example

  9. UserForm Custom dialog box Example: create UserForm to collect personal info form will be launched from Excel data entered in form is stored in Excel

  10. Insert blank form InsertUserForm blank UserForm Toolbox with control objects

  11. Designform Add control objects to form Label (display text) TextBox (for input) CommandButton (perform an action)

  12. Initialize properties Initialize properties of controls Label (Name): lbl… Caption: TextBox (Name): txt… Text: CommandButton (Name): cmd… Caption:

  13. Write code Determine events to which VBA should respond (eg. clicking Add CommandButton) double-click Add button to open Code window and type the code Private Sub cmdAdd_Click() code goes here . . . End Sub

  14. Flowchart Start Separate name into first and last Store in worksheet: last, first, phone Yes Yes Name entered? Phone entered? Phone entered? No No Clear textboxes on form Report error Report error Stop

  15. Code refers to textbox contents

  16. Code

  17. Create macro How can we start this form from Excel? There is no macro listed! write a macro to display form form doesn’t appear until called put this code in Module code, not Form code: Sub EnterPersonalInfo () macro name UserForm1.Show form name End Sub

  18. Add worksheet button Optional: button on worksheet to run macro >View >Toolbars >Drawing >Oval icon right-click Add Text… right-click Assign Macro… >View >Toolbars >Control >CommandButton icon right-click Properties right-click View Code >Exit Design Mode icon

  19. Excel Functions Most worksheet functionshave a VBA equivalent In VBA: Application.WorkSheetFunction._ FunctionName(arguments) Application.WorksheetFunction.Today() Application.WorksheetFunction.IsNumber (Range(“B3”)) In Excel: =FunctionName(arguments) =Today() =IsNumber(B3)

  20. Find Function VBA Function – not available on sheet Function is called on an object that defines a spreadsheet range E.g., Selection.Find(“Tom”) Returns range location of first match, or returns nothing if no match found. Range object need not be sorted

  21. Macro asks for name to find and stores in E8 Find function locates name, uses offset to get its age vName = InputBox(“Name?”, “Enter name”) vAge = Range("A4:A10").Find(vName). Offset(0, 2).Value Find Function

  22. VLookUp Function Vlookup (value, table, col_num, close) value: item to find table: range of data to search must be sorted by 1st column col_num: return data from which column? close: true or false True: select nearest match – always finds False: find exact, or return #N/A

  23. How it works in Excel VLookUp Function

  24. How it works in VBA vName = InputBox(“Name?”, “Enter name”) Range(“E8").Value = vAge vAge = Application.WorksheetFunction. _ VLookup(Range("E8"), Range("A4:C10"), 3, True) Range("F8").Value = vAge VLookUp Function

More Related