Excel VBA and Macros Excel uses a language

Excel VBA and Macros • Excel uses a language to implement all of the functional capability of the application. • VBA (Visual Basic for Applications) is that language. • Use VBA to tell Excel what to do: – Create your own new functions – Automate things you do every day 1

Macros • A macro is a set of instructions you give Excel in the VBA language i. e. when you create a macro, VBA program code is automatically generated • Analogy Javascript is to HTML as a Macro is to Excel – Javascript manipulates an HTML webpage – A Macro manipulates the functionality of Excel • Stored in the Visual Basic module (example later). • Automates keystrokes and actions in Excel • Examples: – – – Apply style and formatting Manipulate data and text Communicate with data sources (database, text files, etc. ) Create entirely new documents Any combination, in any order, of any of the above 2

Recording vs. Writing macros • Recording Macros can be used for simple repeated tasks – Simple – Sometimes unreliable – Very limited • Writing Macros can be used for very complicated tasks – More complicated to use – Very Reliable – Virtually unlimited 3

Recording a Macro • The easiest way to create a macro is to record a macro – Excel stores information about each step you take as you perform a series of commands. You then run the macro to repeat, or "play back, " the commands. • To create a macro, go to View > Macros > Record Macro. • Specify the following and click OK: – – Macro name (no spaces) Shortcut, if desired Location to store the macro and Description - a good reminder of what the macro does (not how, just what) 4

Recording a Macro • Once this is done, all of your actions are recorded – every cell change, scroll action, window resize, you name it. • There a couple of places which indicate Excel is record mode. – One is by viewing the Macro menu and noting that Stop Recording has replaced the option for Record Macro. – The other is in the bottom right corner. The ‘stop’ icon indicates it is in macro mode and pressing here will stop the recording (likewise, when not in record mode, this icon will be the Record Macro button, which you can use instead of going to the Macros menu). • Once you complete all the actions you want to take, stop recording. 5

GPACalc Recording Macro Overview • • These are the worksheet starting points prior to starting the recording for this macro As an overview (do not start yet; wait until next slide), what you want to do is: – – Copy the Sample. Term! worksheet data setup to a new worksheet The name of this new worksheet is in cell Input. Data!B 2 SP 15 Copy the data from the Input. Data!A 4: C 13 to SP 15!A 7: C 16 Determine the formulas needed for SP 15!D 7: D 16 as well as SP 15!B 1: B 4 Sample. Term! starting point for SP 15! Input. Data! Course. Points! 6

GPACalc data and formulas Sample. Term! starting point for SP 15! Input. Data! Course. Points! Put formula in D 7 (as shown) and copy down to D 16 7

GPACalc Recording the Macro • Start the recording – Add Macro Name, Shortcut. Key, Store macro location, and Description – Click OK • • Copy Input. Data!B 2 Open new worksheet Rename it SP 15 (i. e. paste) Copy all of Sample. Term worksheet to SP 15 Copy Input. Data!A 4: C 13 to SP 15!A 7: C 16 Put formula in SP 15!D 7 and copy down to D 16 Put formulas in SP 15!B 1: B 4 Stop recording 8

GPACalc Macro result New SP 15 worksheet 9

Use your first Macro, but wait… • The problem with running the macro as the file is now is that you already have all the results in the file that you want i. e. the new worksheet, the input data copied over, the formulas, etc. • So, you have to delete the information the macro just added! – Delete the SP 15 worksheet – Make sure your cursor is where it was originally when you copied SP 15 and any other issues that might have already been done (was the Sample. Term worksheet already highlighted when you saved it? etc) if you are not sure, check the original GPACalc file you downloaded • Remember, the macro is only going to do what you told it to do • Save the workbook once it is again in its original starting position • Close the file – you must exit so that it resets the new worksheet you want to create as Sheet 1 because this is what your VBA code should specify. 10

OK, really, use your first Macro • • To put the macro to use, open the macro -enabled Excel file which was created. When you do this, assuming you have standard security settings enabled, you will see a warning across the top of the workbook which says that macros are disabled. Go to the Macros icon (under the View tab) and select View Macros. – Macros can be malicious. – Only run macros from trusted sources. • In the resulting dialog box, choose the recorded macro and click Run. 11

Run it one more time • Exit the file without saving – You want to get it back to the “template” format i. e. the environment that was the starting point for the macro before you started to record it • Open the file again • Run the macro using the shortcut: CTRL-Shift-G • Exit the file without saving as it is ready to submit when you are ready to do so. NOTE: This is an exercise on recording macros. 12

QUESTION How many times do you need to actually get a macro recorded correctly? ! LOL 13

Developer Tab on Ribbon • To turn on the Developer tab, execute the following steps. – Right click anywhere on the ribbon, and then click Customize the Ribbon… or click on the File tab, then choose the Options link and click Customize the Ribbon – Under Customize the Ribbon, on the right side of the dialog box, select Main tabs (if necessary). – Check the Developer check box. – Click OK. 14

Developer Tab on Ribbon • Once you check the Developer box, you can find the Developer tab next to the View tab on the ribbon. • When you have the developer toolbar, you'll see the following tab in the Ribbon Notice that the Macros icon on the Developer tab = Macros icon then drop down View Macros on View tab Record Macro is its own icon on the Developer tab = Macros icon then drop down Record Macros on View tab 15

VBE – Visual Basic Environment • More to be added soon 16

Red Button Macro • Step 1. Select any cell and start the macro recorder – Developer Ribbon – Record Macro icon • Step 2: Give a name to your Macro – “Make. Me. Red” – No spaces or special characters except underscore – Click OK • Step 3: Fill the current cell with red color • Step 4: Stop Recording • Step 5: Assign your Macro to a button (go to next slide for these directions) • Step 6: Play with your macro • Save the file as Lab 7 Red. Button – Did you forget to save it as a macroenabled file? Be sure to save as macroenabled i. e. extension. xlms 17

Red Button Macro (continued) Step 5. Assign your Macro to a button • Choose Insert Icon • Choose Form Control Button • Draw a nice rectangle • On the Assign Macro window (which automatically pops up) choose the “Make. Me. Red” macro name and click OK. • Update the wording on the button – “Select any cell to be red” – Click on any worksheet cell • Click on the button you just drew… the cell you had clicked on prior to doing this should turn red. Highlight a range of cells. Click on the button… … go back to previous slide 18

Understanding the Make. Me. Red code • Right click on your current worksheet name - Click on View Code option • This opens the VISUAL BASIC EDITOR • Notice the yellow highlighted areas that contain the filename that you just created and saved • Select Modules from left side area of VBE – Click on plus sign and choose Module 1 • You can see the VBA Code behind Make. Me. Red on the right side 19

Make. Me. Red Macro Sub Make. Me. Red() ' ' Make. Me. Red Macro ' ' With Selection. Interior . Pattern = xl. Solid . Pattern. Color. Index = xl. Automatic . Color = 255 . Tint. And. Shade = 0 . Pattern. Tint. And. Shade = 0 End With End Sub Make. Me. Red(): This line tells Excel that we are writing a new set of instructions. The word SUB indicates that the following lines of VBA are a sub-procedure (or sub-routine). Which in computer lingo means, a group of related instructions meant to be followed together to do something meaningful. The Sub-procedure ends when Excel sees the phrase “End Sub” Lines starting with a single quote (‘): These lines are comments. Excel will ignore anything you write after a single quote. These are meant for your understanding. With Selection. Interior: While filling a cell with Red color may seem like one step for you and I, it is in fact a lot of steps for your computer. And whenever you need to do a lot of operations on the same thing (in this case, selected cell), it is better to bunch all of them. This is where the WITH statement comes into picture. When Excel sees With Seletion. Interior, Excel is going to think, “ok, I am going to do all the next operations on Selected Cell’s Interior until I see End With line“ Lines starting with. : These are the lines that tell Excel to format the cell’s interior. In this case, the most important line is. Color = 255 which is telling Excel to fill Red color in the selected cell…. Try 192 or 62. End With: This marks the end of With block. End Sub: This marks the end of our little macro named Make. Me. Red(). 20

Variables, Conditions and Loops 21

Explaining Store. Nuts • Dim statements: These lines declare the variables we are going to use. Notice the different data types (Integer, Range etc. ) we have used for various types of data we want to hold. • For Each store In Range(“C 7: C 30″): This line is going to tell excel that for each store (ie cell) in the range C 7: C 30, we need to repeat the instructions all the way until Next Store. In our case, Excel is going to repeat for 24 times. • store. Value = Input. Box(“Sales for Store ” & store. Num): This line shows a small box to you and asks for your input. You can enter a number and press OK (or enter). Whatever value you enter will be placed in current store’s cell. • reason = Input. Box(“Why are the sales deviated? ”, “Reason for Deviation”): This line shows a box to user with a title and default value (Reason for deviation). • store. Offset(, 1). value = reason: This statement places the reason for sales deviation in to the cell right to the store sales. Offset(, 1) does the trick here 22

Create and Use variables in VBA 23

24

25

Referencing individual cells/ranges 26

Referencing individual cells/ranges 27

Changing Cell Properties Worksheets(“Sheet Name”). Cells(Row, Column). Property ALL PROPERTIES OF CELLS including • Font Color, Font Type, Font Size – – . Font. Color=vb. Red. Font. Color=RGB(10, 255, 200). Font. Name=“Arial”. Font. Size=16 • Cell Border Type –. Borders(xl. Bottom. Line. Style=xl. Double • Border Color –. Borders. Color=vb. Greem • Interior Cell Color –. Interior. Color=vb. Blue –. Interior. Color=RGB(255, 100) • Values –. Value=“ABCD” –. Value=123 –. Value=sin(2*3. 14159*Worksheets(“Sheet 1”). Cells(1, 2). Value)) • Formula –. Value=“=sum(A 1: A 5)” –. Formula=“=sum(A 1: A 5) –. Formula. Array=_”=Sum(R 4 C 5: R 7 C 9)” 28

Subroutines & Sheet/Event-Driven Subroutines • All code you write will be part of a macro (or a subroutine) • All subroutines are either GENERAL or SHEETrelated (or Workbook-related) Subroutines • Subroutines in Excel will belong to one of the items – The workbook (meaning all sheets in the file) • Workbook-related subroutines are activated when an event occurs in any different sheets or the workbook itself (for example: when a sheet is double clicked, when the workbook is opened, …) • General workbook subroutines are activated manually and have access to all sheets of the workbook – One sheet in the workbook (for example, the sheets here are “Sheet 1”, “Data”, “Results”) • The name of the sheets appear in parenthesis “(…)” • Worksheet-related subroutines are activated when an event occurs in that particular sheet (for example: when that sheet is double clicked, when that sheet is activated, …) • General worksheet subroutines are activated manually and have access to all sheets of the workbook 29

General Subroutines • If declared as PUBLIC (default if not provided): – Appear as macros with the same names as the subroutine names, – Can be accessed from Public or Private subroutines of the same sheet or other sheets – Example: Assuming Sub belongs to “Sheet 1”, macro will be “Sheet 1. XYZ” : • If declared as PRIVATE: – Do not appear as macros (used to implement small tasks that do not qualify as complete processes), – Can be accesses from Public or Private subroutines of the same sheet but not of other sheets – Example (Assume Sub belongs to “Sheet 2”): • 30

Sheet Subroutines • Are ALL private to a specific sheet: • Cannot be activated manually, but must be activated by one of the EVENTS: – Activate: Sub executed when the sheet is activated (switch from any sheet to the sheet containing the macro), – Deactivate: Sub executed when the sheet is deactivated (switch from sheet containing macro to another sheet), – Before. Double. Click: Sub executed when sheet is double-clicked (Arguments passed to Sub are Target. Row & Target. Column) – Before. Right. Click: Sub executed when sheet is right-clicked (Arguments passed to Sub are Target. Row & Target. Column) – Calculate: Sub executed when any calculation is done in sheet – Change: Sub executed when any modification is done to sheet (Arguments passed are Target. Row & Target. Column) – Selection. Change: Sub executed when the another cell is selected in sheet (Arguments passed are Target. Row & Target. Column) 31

Helpful Functions in VBA for Excel • • To comment a statement, use ‘ (a single quotation mark) before the statement To set or get a property of a cell or range of cells in the same sheet that the subroutine belongs to – Worksheets(“Sheet Name”). Cells(Row, Column). Property – Worksheets(“Sheet Name”). Range(Cells(R 1, C 1), Cells(R 2, C 2)). Property To set or get a property of a cell or range of cells in a sheet other than the one that subroutine belongs to – Worksheets(“Sheet Name”). Cells(Row, Column). Property Same as above – Worksheets(“Sheet Name”). Range(Worksheets(“Sheet Name”). Cells(R 1, C 1), Worksheets(“Sheet. Name”). Cells(R 2, C 2)). Property To clear contents of a cell or range of cells, simply change “. Property” above to “. Clear. Contents”. To switch from once sheet to another, sheet called Sheet. Name, use Worksheets(“Sheet. Name”). Activate To Copy part of “Sheet 1” to “Sheet 3”, use the two statements Worksheets(“Sheet 1”). Range(Worksheets(“Sheet 1”). Cells(3, 4) Worksheets(“Sheet 1”). Cells(8, 9)). Copy. Worksheets(“Sheet 3”). Paste To set the interior color of a cell or range of cells, change “. Property” above to “. Interior. Color = RGB(Red_Value, Green_Value, Blue_Value)”. To set the font type of a cell or range of cells to “Arial” for example, change “. Property” above to “. Font. Name = “Arial” ”. 32

Helpful Functions in VBA for Excel • To set multiple properties of the same cell or range of cells use the “WITH” statements With Sheet 2. Range(Cells(3, 6), Cells(8, 12)). Font. Name = "Arial“. Font. Size = 14. Font. Color = vb. Blue. Value = “HELLO” ‘ Set value End With • ‘ Set Font size ‘ Set Font color To let Excel read a cell, use (a text-to-speech/speech-to-text engine comes built in with Windows XP, (and I am sure with Windows Vista too). This engine is the one that actually reads the data). Application. Speech. Speak Worksheets(“Sheet Name”). Cells(R, C). Value • To email the active workbook (the file currently open) to someone, use Active. Workbook. Send. Mail recipients : = "wajih@kfupm. edu. sa" To be continued… 33

34

VBA test questions • http: //www. excelitems. com/2010/12/excel-vba-interviewquestions. html • http: //macromatician. blogspot. in/2013/10/excel-vbamacros-interviewquestions. html#_methods=on. Plus. One%2 C_ready%2 C_clos e%2 C_open%2 C_resize. Me%2 C_renderstart%2 Concircled% 2 Cdrefresh%2 Cerefresh%2 Conload&id=I 0_1389263939734 &parent=http%3 A%2 F%2 Fmacromatician. blogspot. in&pfna me=&rpctoken=74810093 • http: //macromatician. blogspot. in/2014/01/excel-vbamacros-interview-questions_10. html • http: //www. globalguideline. com/interview_questions/Que stions. php? sc=VBA 35

Command Button • • Make sure your file is saved as a Macro Enabled file On the Developer tab, click Insert In the Form Controls group, click Command Button Move your cursor to the Excel worksheet – – Size the command button An Assign Macro window is displayed Notice the name of the macro Click the “New” button to the right • The Visual Basic editor appears – Between the two lines in the right window type: • Range(“A 1”). Value = “O-H… I-O” • Close the Visual Basic Editor. • Click the command button on the sheet (make sure Design Mode is deselected). 36

Ø The window on the left with the names Sheet 1, Sheet 2 and Sheet 3 is called the Project Explorer. If the Project Explorer is not visible, click View, Project Explorer. Ø To add the Code window for the first sheet, click Sheet 1 (Sheet 1). 37

Assign a Macro to the Command Button • Right click Command. Button 1 (make sure Design Mode is selected). • Click View Code. • Name the macro CB 1. 38

Macro A series of commands and functions that are stored in a Microsoft Visual Basic module • Automates your keystrokes and actions in Excel • Use these when you complete the same tasks repetitively • When you create a macro, VBA program code is automatically generated 39

Record Macro Click on Developer 40

Macro Walk Through Step 1: Delete the information in cells B 1 and B 2 Step 2: In Cell B 1, Input the following Function =CONCATENATE(LEFT(A 1, 3), ”-”, MID(A 1, 4, 3), ””, MID(A 1. 7, 4)) Step 3: In Cell B 2, Input the following Function =LEFT(A 1, 3) Step 3: See next Slide 41

Macro Walk Through Step 4: Display Message: File has been Updated! Click on View Double click on Module 1 Click on Project Explorer 42

Type this line at the beginning of the VBA Code Type these two lines at the end of the VBA Code Click on Stop Recording 43