VBA Programming for Excel n n n Review

VBA Programming for Excel n n n Review Excel Objects Excel Methods Identifying Specific Cells Review Functions for Excel Custom Menus

Range Objects n Range(Name) n n n Name: text string “B 3”, ”Input” Offset n n Range(“B 3”). Offset(2, 1) = Range(“C 5”) Offset numbers can be called My. Number = 3 Range(“D 4”). Offset(my. Number, -1). Select <Results: Cursor ends up in cell C 7>

Default Objects n Active. Cell n n n Active. Sheet n n n Active. Cell. Offset(0, 1). Select Row. Num = Active. Cell. Row Active. Sheet. Name = “Data” Active. Sheet. Visible = Very. Hidden Selection. Clear

What does this code do? Active. Cell. Offset(Range(“B 2”), -2) = [b 4]/4 4

Controlling Objects n n Use assignment statements to change objects or properties Different effects, similar results n n Range(“F 3”). Value = Range(“D 3”). Value*15 Range (“F 3”). Formula = “=D 3*15” First form enter a number – no updates! Second form enters a formula

Collections n Worksheets n n n Worksheets(1) Worksheets(“Sheet 2”) Columns(“C: D”). Horizontal. Alignment = xl. Center n Rows(5). Row. Height = 19. 5 n Note difference between Row and Rows

Excel Methods n n Record macros to define Copy, Paste Range(“B 3: D 6”). Selection. Copy n Sort

Find a Given Cell n Go. To n n n Edit, Goto, Special Last Cell Current Region {End}{Down} Find first column in the last row

Look-ups n VLook. Up(value, table, col_num, close) n n Value: item to find Table: range of data to search n n n Must be sorted by 1 st column Col_num: which column has data? Close: true or false n n True: select nearest match – always finds False: find exact, or return #N/A

VLook. Up True: find closest match Value to Look. Up Search range Return column

Spreadsheet Functions in VBA n Application. Work. Sheet. Function. Name(Arguments) Application. Worksheet. Function. Today() n Cell addresses must appear as ranges Application. Work. Sheet. Function. Is. Number(Range(“B 3”)) n n Most worksheet functions have a VBA equivalent Functions must be used in assignment statements v. Ans = Application. Work. Sheet. Function. _ v. Lookup(Range(“A 10”), Range(“A 2: C 8”), 3, True) v. Out = Range(“A 10”). formula & “ lives in “ v. Ans Msg. Box v. Out

Find() n n n 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

Find( ) Function Range(“C 10”). Value = _ Range(“A 2: A 8”). Find(“Gene”). Offset(0, 2). Value Looks in cells A 2: A 8 for “Gene”, returns [A 5] Offsets 2 cells right from [A 5] returns [C 5] Finds the value in [C 5] = 58 Puts the value 58 in [C 10]

User Defined Functions n You can write your own custom functions n n 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 n n n Argument: Amount of sales Return value: Bonus amount Bonus = 2% if Sales > $50, 000

User-defined Functions n n 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

Using Custom Functions n Functions can be called from another sub v. Sales = Range(“B 3”). Value v. Bonus = Bonus(v. Sales) Range(“C 3”). Value = v. Bonus n Functions can be used in the spreadsheet Use Function Generator [fx] n Look under “User-defined” n Place cursor in [C 3], write: =Bonus(B 3) n n n Note how the results differ! See VBAFunctions. xls in the handouts

Custom Menus n n Define Variables Use Set to define contents Dim my. Button As Command. Bar. Button Set my. Button = Command. Bars("Worksheet Menu Bar")_. Controls("Tools"). Controls. Add With my. Button. Caption = "Say Hi" Caption: Words in menu list. Move. Before: =4 Move. Before: Position in list. On. Action = "Say. Hi" On. Action: Macro to call. Face. Id = 2174 Face. ID: Icon to display End With

Removing Menu Items n n Search the existing buttons Remove identified items For Each Item In Command. Bars("Worksheet Menu Bar")_. Controls("Tools"). Controls If Item. Caption = "Say Hi" Then Item. Delete Exit For End If Next Item

Activating Custom Menus n n n Menu code goes in a macro Macro must be run to make menu appear or disappear Use Work. Book_Open to add menues Use Work. Book_Before. Close to remove See Say. Hi. xls in the handouts