Slide 1: VBA Programming for Excel      Review Excel Objects Excel Methods Identifying Specific Cells Review Functions for Excel Custom Menus
Slide 2: Range Objects  Range(Name)   Name: text string “B3”,”Input” Range(“B3”).Offset(2,1) = Range(“C5”) Offset numbers can be called MyNumber = 3 Range(“D4”).Offset(myNumber, -1).Select <Results: Cursor ends up in cell C7>  Offset  
Slide 3: Default Objects  ActiveCell   ActiveCell.Offset(0,1).Select RowNum = ActiveCell.Row ActiveSheet.Name = “Data” ActiveSheet.Visible = VeryHidden Selection.Clear  ActiveSheet    Selection 
Slide 4: What does this code do? ActiveCell.Offset(Range(“B2”),-2) = [b4]/4 4
Slide 5: Controlling Objects   Use assignment statements to change objects or properties Different effects, similar results     Range(“F3”).Value = Range(“D3”).Value*15 Range (“F3”).Formula = “=D3*15” First form enter a number – no updates! Second form enters a formula
Slide 6: Collections  Worksheets   Worksheets(1) Worksheets(“Sheet2”)  Columns Columns(“C:D”).HorizontalAlignment = xlCenter  Rows Rows(5).RowHeight = 19.5  Note difference between Row and Rows
Slide 7: Excel Methods   Record macros to define Copy, Paste Range(“B3:D6”).Select Selection.Copy  Sort
Slide 8: Find a Given Cell  GoTo    Edit, Goto, Special Last Cell Current Region   {End}{Down} Find first column in the last row
Slide 9: Look-ups  VLookUp(value, table, col_num, close)   Value: item to find Table: range of data to search  Must be sorted by 1st column   Col_num: which column has data? Close: true or false   True: select nearest match – always finds False: find exact, or return #N/A
Slide 10: VLookUp True: find closest match Value to LookUp Search range Return column
Slide 11: Spreadsheet Functions in VBA  Application.WorkSheetFunction.Name(Arguments) Application.WorksheetFunction.Today()  Cell addresses must appear as ranges Application.WorkSheetFunction.IsNumber(Range(“B3”))   Most worksheet functions have a VBA equivalent Functions must be used in assignment statements vAns = Application.WorkSheetFunction. _ vLookup(Range(“A10”), Range(“A2:C8”), 3, True) vOut = Range(“A10”).formula & “ lives in “ vAns MsgBox vOut
Slide 12: Find()       VBA Function – not available on sheet Expression.Find(What) Expression must define a range on the spreadsheet Returns Range location of first match Expression range need not be sorted If no match is found, it returns Nothing
Slide 13: Find( ) Function Range(“C10”).Value = _ Range(“A2:A8”).Find(“Gene”).Offset(0,2).Value Looks in cells A2:A8 for “Gene”, returns [A5] Offsets 2 cells right from [A5] returns [C5] Finds the value in [C5] = 58 Puts the value 58 in [C10]
Slide 14: User Defined Functions  You can write your own custom functions     Decide what information will be passed in (Arguments) Decide what value will be returned Decide how VBA will use the arguments to calculate the returned value Example: Determine employee bunuses    Argument: Amount of sales Return value: Bonus amount Bonus = 2% if Sales > $50,000
Slide 15: User-defined Functions   Form: Function Name(Arguments) Unlike Sub – the name of the function must be repeated in the code Function Bonus(Sales) If Sales > 50000 Then Bonus = Sales * 0.02 Else Bonus = 0 End If End Function
Slide 16: Using Custom Functions  Functions can be called from another sub vSales = Range(“B3”).Value vBonus = Bonus(vSales) Range(“C3”).Value = vBonus  Functions can be used in the spreadsheet Use Function Generator [fx]  Look under “User-defined”  Place cursor in [C3], write: =Bonus(B3)    Note how the results differ! See VBAFunctions.xls in the handouts
Slide 17: Custom Menus   Define Variables Use Set to define contents Dim myButton As CommandBarButton Set myButton = CommandBars("Worksheet Menu Bar")_ .Controls("Tools").Controls.Add With myButton .Caption = "Say Hi" Caption: Words in menu list .MoveBefore:=4 MoveBefore: Position in list .OnAction = "SayHi" OnAction: Macro to call .FaceId = 2174 FaceID: Icon to display End With
Slide 18: Removing Menu Items   Search the existing buttons Remove identified items For Each Item In CommandBars("Worksheet Menu Bar")_ .Controls("Tools").Controls If Item.Caption = "Say Hi" Then Item.Delete Exit For End If Next Item
Slide 19: Activating Custom Menus      Menu code goes in a macro Macro must be run to make menu appear or disappear Use WorkBook_Open to add menues Use WorkBook_BeforeClose to remove See SayHi.xls in the handouts