Wiley Excel Programming
Contents
1. 9 oI Eid Fri takia Fam Tiwi a aihe ker Mret Far Ei m Place your mouse pointer on the control Beever ees ne ies and when the mouse pointer turns into a a E urteen p TA TEE Fan four sided arrow drag the control to Saas a e change the location J Jaideyp e e Right click the control twice and then ee EA sasan click it to place it in Design mode Sarum si 3 me a oe fan oe To cancel Design mode click any cell in 1 Quantity nie Description the worksheet To remove a control place it in Design mode and then press Delete C o GQ ped A e o ped N Tj e AO e ac e V You can add the controls listed in the following table to your worksheets CONTROL CONTROL NAME DESCRIPTION Gs Button Runs an associated macro when clicked Check box Selects or deselects an option Displays a list of items for selection konno sesono m eote Ina or dca a whe he cc taco dg bar Assign Values to a Form Control fter you add a control to a form you can assign values to it For example if your worksheet contains a combo box you can assign the list of values that appear when users access the combo box Some controls enable you to define a range of valid numeric values for the control For example if you use a spinner you can define the starting value and the maximum value for the control For combo boxe2. __ a5 13 557 95 EZS izj Be a Fr Hd She el oe E q pI j To assign a macro to a button create the macro and then click the Developer tab Click Insert in the Controls group A menu appears Select the button control and then click and drag to create a button The Assign Macro dialog box appears Click the macro you want to assign and then click OK Right click the button twice and then click Edit Text on the menu that appears Type the name you want to give the button You can now click the button to execute the macro If you no longer want a macro to be assigned to a control you can right click the control twice and then click the Assign Macro option In the Assign Macro dialog box delete the macro name from the Macro Name field and then click OK Excel removes the macro assignment from the control but the macro remains as part of the workbook To remove the macro from the workbook click the View tab Click Macros in the Macros group Click View Macros The Macro dialog box appears Select the macro you want and then click Delete L IaJdeyp e e C o GQ ped A e V ped N Tj e A e ce e PD
3. amale Iuin EmirA ATES lava cp s Gow 8 rj A Ffred kA F ie i P hete D Hip repade ie al o P E Wie Feat Fatai p Mine Tock etcam teak Wes You can also run your macro ne Seat Mape a mieca by pressing the shortcut key z E a z you assigned when you 2 z Income Statement created the macro a 4 Year i Year 2 Year 3 Year 4 s Net sales 5 200 000 250 000 325 000 450 000 ro a Cost of goods sold 150 000 00 150 000 00 150 000 00 150 000 00 p Gross profit on sales 50 000 00 100 000 00 175 000 00 300 000 000 YV a Expenses 25 000 00 35 000 00 50 000 00 45 000 00 a Met income 25 000 00 65 000 00 125 000 00 255 000 00 Ti pal LB la 14 is Create and Launch a Keyboard Shortcut keyboard shortcut is a combination of keys you press to execute a command You can use a keyboard shortcut to launch an Excel macro command You can assign an upper or lowercase Key to a macro when you create it or assign one later by using the Macro Options dialog box You execute a macro keyboard shortcut by pressing the Ctrl key along with the assigned upper or lowercase key Refer to the section Record a Macro to learn how to create a macro Keyboard shortcuts are case sensitive For example Excel interprets a lowercase m and an uppercase M as two different keys To execute a macro you have assigned to a lowercase letter press Ctrl plus the letter for example Ctrl m To execute a macro you have assigned to an A
4. 2 Year 3 Year 4 a Net sales z 200 000 750 000 325 000 450 000 a Cost of goods sold 150 000 150 000 150 000 150 000 Gross profit on sales 50 000 100 000 175 000 300 000 a Expenses 25 000 35 000 50 000 45 000 a Net income 5 255 000 25 000 65 000 125 000 4 E Delete a Macro Press Alt F8 The Macro dialog box appears L IaJdeyy Delete a Maco sism Deleon Click the macro you want to delete Delete a Macro xlsm INoDecimalFormat Click Delete e e Macrosin All Open Workbooks Insert regions A message box appears asking you to confirm you want to delete the macro Click Yes Excel deletes the macro o GQ ped A e o ped ou Tj z A e a e p o There are two ways to create a macro One way is to use the macro recorder to record the steps needed to perform the action The other way is to create the steps by typing the Visual Basic for Applications VBA code into the Code window of the Visual Basic Editor VBE When you use the macro recorder Excel automatically creates the VBA code for you You can use the VBE to edit macros you have created with the macro recorder Often it is convenient to use a combination of the two methods to create your VBA code You record part of the VBA code and then you use the VBE to augment or modify your code To activate the VBE you can press Alt F11 while in Excel or click the Visu
5. 950 00 Keyboard 55 00 po 55 00 275 00 Mouse 30 00 Speakers 75 00 a l Ta l a l 5 00 d 15 545 00 l e a UT AIE rA A A E Cc D 1 Quantity ltem Description 155 2 este 2 npe w i E 5 F iard r i Speakers 75 00 puj I Subtotal Shipping Total 13 545 00 13 545 00 HE z FEILE a p a ls Click the control with the assigned macro Excel executes the associated macro inthe example Excel assigns postage to the invoice El o r Mikla Parii ta a Baers Sentind ken biel Freed Sea th lkm Tarn Faga apat amaie ETA wy Tava cpat acta r E a Sar F D APh Oy bp mepe Ean k Mie Faia A Bratni Taki a ren kanm a hint kezek ae TA eia Bi pa P Crarari PETE ETTET has kale brett ore NIE nm TEL mi Fined F A B c D ae T a S lterm Description Unit Price Total kter Price Computer 1 293 00 Manitar 260 00 Keyboard 55 00 Mouse 30 00 Speakers 75 00 Ed Ir Mahla Plini 1a a Rien Sintodrbon Birnen Pared F5 Erm Ikme Tur E View Tad cp dactail Yerda i i i Af heth T Hap rcp n 4 al Ey unem Retire tiki F 5 Pu be te ime Cock S dite patiba Tonks Lis am antey Addin CIM i a oh Hen soup Wide e mlkas Eres MW ot A un Computer 1 295 00 Manitor 260 00 Keyboard 55 00 l El Mause 30 00 m Speakers 75 00 r EEn Er 14 di 13 345 00 A 1235 lt
6. ap repede _ L a _ Eu r Ragin ekmanii F a bad me nt ime Ire aia Cai Rat Peaks Ae mast a Bai Bee sued dn Erbryitia re B AAKI al F A n F nS i 2 Income Statement J 4 Year l Year 2 Year 3 Year 4 a Met sales 250 000 00 325 000 00 450 000 00 a Cost of goods sold Sa fe 150 000 00 Gross profit on sales 300 000 00 a Expenses 45 000 00 a Net income 255 000 5 6 C Perform the steps you want to record a E E E a ea EE Cienega a Note This example changes the number MEAE iam r A A Gammon crea Esai Saas a Eh i A orn format using the following steps Click the Home tab Click the Number Group launcher Renee eee Click Accounting Set Decimal Place to 0 a _ Select dS Symbol Click OK a Net sales 8 325 000 00 450 000 00 a Cost of goods sold 150 000 00 150 000 00 150 000 00 150 000 00 Gross profit on sales 50 000 00 100 000 00 175 000 00 300 000 0 a Expenses 25 000 00 35 000 00 50 000 00 45 000 00 a Net income 25 000 00 65 000 00 125 000 00 255 000 00 Click the Developer tab See a ome S TEF y E jg fering i a Gob Uie Fee Retire tiki Yaoi bami k h up Alternatively click the Stop Recording cat a button on the status bar and skip Step 10 a A gt gt Click Stop Recording Income Statement s 4 Year 1 Year 2 Year 3 Year 4 Excel stops recording your macro Net sales 260 000 00 325 000 00 450 000
7. on sales 50 000 00 100 000 00 175 000 00 300 000 00 a Expenses 25 000 00 35 000 00 0 000 00 45 000 00 a Met income 25 000 00 65 000 00 125 000 00 255 000 00 1m 11 i 1A 1 G i 17 a0 a amna os A a HHI S Fima Mariakr F di Fral D BIJ ee rur mgle erae Daa ams hwe oraepa s og lg Jie Frocectisr Hap frepactes planpert fa ion iiaii ree T mS on aarm Houa 7 E a hi j kesesa 3 ch i A esi i g Income Statement Year 2 Year 3 Year 4 ail I a J 4 Year i A 4 a a s Net sales izj 700 000 250 000 00 325 000 00 450 000 09 Cost of goods sold 150 000 00 150 000 00 150 000 00 150 000 00 Gross profit an sales EA SRLN 300 000 00 Se 25 saeceterse L Net income QE ae m 255 000 00 Wi sit j n oz z a nm S Maama pleitak 4 s 4 i mungk fa inanma Besala dod ubon sm faba a Excel differentiates between macros listed in the Macro dialog box by placing the name of the workbook that contains the macros in front of the macro name For example Excel lists a macro named Sum_Expenses in the Personal Macro Workbook as PERSONAL XLSB Sum_Expenses If the macro Sum_Cell1s exists in both the Budget xlsm and Expenses xlsm workbooks Excel treats them as two different macros The Macro dialog box lists them as Budget xlsm Sum_Cells and Expenses xlsm Sum_Cells Unless you have your macro settings set to enable all m
8. want to use to represent the macro Click OK to close the Rename dialog box Click OK to close the Excel Options dialog box The macro appears on the new tab you created Delete a Macro ou can delete macros you no longer need by clicking the Delete button in the Macro dialog box Because the Macro dialog box only displays macros in open workbooks the workbook that contains the macro must be open before you can delete the macro The Personal Macro Workbook stores macros you want to make available to all workbooks Excel creates the Personal Macro Workbook when you choose to store your first macro in it After Excel creates the Personal Macro Workbook the workbook opens as a hidden file every time you open Excel To learn more about storing macros in the Personal Macro Workbook see the section Record a Macro If your macro is in a hidden workbook such as the Personal Macro Workbook you must unhide the workbook before you can delete the macro If you try to Delete a Macro pO Ba delete a macro from the Personal Macro Workbook prior to unhiding it Excel displays the following message Cannot edit a macro on a hidden workbook Unhide the workbook using the Unhide command You unhide the Personal Macro Workbook and other hidden workbooks by executing the Unhide command on the View tab If you unhide the Personal Macro Workbook make sure you hide it again using the Hide command on the View tab after
9. you clicked when you recorded the macro If you record using a relative reference Excel plays back the relative location of the cells you used when you recorded your macro Click Use Relative References on the Developer tab to record using a relative reference To learn more about absolute and relative references see the section Record a Macro When you save a workbook that has macros you must save it as a macro enabled workbook Excel gives macro enabled workbooks an xlsm extension Dreveduciag Berd Fresisnnncing den kikre Perel Ikan arn Uman Information about Introducing Ferel Programmina Uo oral oe eer tha LOL Bas oi aam i amr rir Pn A Cea ramicnins Bayaca at pam apy aed names any purl ol Ihi verilme Prapara ter Shanra Eroa hig iri g baana bhai teeri r 1 Usama E ERF Crach 2 renhan e eih Aah can Pie oe Vine bone hy Today 1507 Phista Ieuukykw errai coma vhe vdiha j Porereaiis yuan congo Boe H bue Eri anA Fia The Customize the Ribbon pane appears Click the down arrow and then select Main Tabs Click Developer changes to WI Click OK Q O The Developer tab appears on the Ribbon Click Record Macro to record a macro Click Use Relative References to record with a relative reference Click Macro Security to change macro security E arames lbe Hoha gnere Ti J Jaidey e e Ce aoe Ce me 1a el ET PPLE eee FE PE ore Gait gigas d ge
10. you delete the macros Hiding the workbook prevents you from making inadvertent changes to it You cannot undo the deletion process If you delete a macro by mistake you can close the workbook without saving Of course if you close without saving you will lose all the work you have done since saving Your only other alternative is to re create the macro Unhide a Workbook Click the View tab Click Unhide The Unhide dialog box appears Click the workbook you want to unhide Click OK Excel unhides the workbook You are now in the workbook you Selected to unhide Q Tj el T i Mbriurkon be E BIJ ee rur mgl erae a aes e song E EEE Bye i Lope I ha surer I 4 deed Enesa Fl iva al i A a Fj A T n F eg n z Income Statement J 4 Year 1 Year 2 Year 3 Year 4 s Net sales 5 200 000 250 000 325 000 450 000 a Cost of goods sold 150 000 150 000 150 000 150 000 Gross profit on sales 50 000 100 000 175 000 300 000 a Expenses 25 000 35 000 50 000 45 000 a Net income 5 25 000 65 000 4 125 000 4 255 000 Tt i E 14 is I 17 HH a S Gere Braken Mire Faved a FO Em x apap orale Dala aros hw Seaca sorg i Fuge Imat rave r gt t Re rois aE m ee z toon de Bini eed R L a ao Am a inih mt lopan Fall Fos sures il Beden ihme pen inae TA ertigace Mree al i a E A FA n F FE I z Income Statement J 4 Year i Year
11. 00 a Cost of goods sold 150 000 00 150 000 00 _ 150 000 00 150 000 00 Gross profit on sales 50 000 00 100 000 00 175 000 00 300 000 00 Your macro IS ready for you to USE a aa 25 000 00 35 000 00 50 000 00 45 000 00 a Net income 25 000 00 65 000 00 175 000 00 255 000 00 IAFFE A Al T n F Ft A macro you create in Excel can have a relative an absolute or a mixed reference If you use a relative reference Excel performs the macro based on a relative location For example suppose you move up two cells from cell A3 to cell A1 when creating your macro When you run your macro if you are in cell C3 Excel moves up two cells from cell C3 to cell C1 If you use an absolute reference however Excel performs the macro based on the exact cell addresses For example suppose again that you move up two cells from cell A3 to A1 When you run your macro if you are in cell C3 Excel moves from there to the cells you used when you recorded your macro That is Excel moves from cell A3 to cell A1 By default Excel creates macros with an absolute reference To create a macro with a relative reference click Use Relative References in the Code group on the Developer tab to toggle the relative reference option on To create a macro with both a relative and an absolute reference a mixed reference toggle the Use Relative References button on and off as needed as you create your macro J Jaideyp e e S OJU0D WIOJ pue sonew SUIS
12. 00 65 000 00 125 000 00 255 000 00 1 i If you do not use a macro shortcut frequently it is easy to forget the keyboard shortcut you assigned to your macro If you forget your keyboard shortcut you can view it in the Macro Options dialog box You can execute a macro by assigning the macro to a picture clip art a shape or smart art For example if you want to assign a macro to a picture you start by inserting the picture into your worksheet by clicking the Insert tab and then clicking Picture The Insert Picture dialog box appears In the Look In field select the folder in which you stored the picture you want to insert The pictures in that folder appear Click the picture you want to insert and then click the Insert button The picture appears in the worksheet Click and drag the picture to place it where you want it and then double right click the picture A menu appears Click Assign Macro The Assign Macro dialog box appears Click the macro you want to assign to the picture and then click OK Excel assigns the macro to the picture Click the picture when you want to execute the macro J Jaidey e e L GQ pad O Q N pad Qu Tj e O e e i 7 Assign a Macro to the Quick Access Toolbar ou can assign a macro to the Excel Quick Access toolbar You can execute macros assigned to the Quick Access toolbar using a shortcut key or the Macro dialog
13. Create and Launch a Keyboard Shortcut uppercase letter press Ctrl and Shift n the letter for example Ctrl Shift M If you give the same keyboard shortcut to macros in two different workbooks you may execute the wrong macro if you use the shortcut while you have both workbooks open Excel cannot discern from which workbook you want the macro You can use the Macro Options dialog box to reassign one of the conflicting macros to a new key You should also be careful not to assign the macro to a keyboard shortcut that Excel uses If you do Excel executes your macro instead of the command it created For example by default Ctrl o opens the Open dialog box If you assign o to a macro your macro overrides Excel s assignment Create a Keyboard Shortcut Press Alt F8 The Macro dialog box appears Click the desired macro Click the Options button The Macro Options dialog box appears Type the desired shortcut key Press Shift as you type to assign an uppercase key Type a description Click OK to close the Macro Options dialog box oo Click Close to close the Macro dialog box Q 3 N id Ins Controls 200 000 Noein malF ont Mgcros in This Wiorkbook _ Deseriation Accounbng format no deomal piaoss no dolar sign Insert Design a Add In pa 9 Run Dialog Year 2 250 000 Year 1 Launch a Keyboard Shortcut Se
14. Introducing Excel Programming s you probably know Microsoft Excel is an electronic worksheet you can use for a variety of purposes including the following maintain lists perform mathematical financial and statistical calculations create charts analyze your data with PivotTables locate data find trends in your data and present your data to others This book is about automating the tasks you perform in Excel by using Visual Basic for Applications VBA You can use VBA to automate those repetitive tasks you perform frequently For example if the layout of your A monthly report rarely changes you can use VBA to set up your report each month VBA is a programming language however you do not have to be a programmer to automate the tasks you perform in Excel You can also automate a task by using the macro recorder to create a macro A macro is a Introducing Excel Programming recording of the steps you want to automate You just click a button to turn on the macro recorder and begin performing the steps as you normally would Excel records each step and creates the VBA code When you finish you click the Stop Record button When you select your macro in the Macro dialog box and then click the Run button Excel plays back the steps you recorded For example if you record the steps necessary to set up your monthly report all you have to do each month thereafter is click a button and Excel automatically sets up yo
15. Macros In field to limit the number of macros listed in the Macro dialog box To see the macros in any open workbook including the Personal Macro Workbook select All Open Workbooks from the Macros In drop down list To see the macros from a specific workbook select the name of the workbook from the Macros In drop down list To see global macros stored in the Personal Macro Workbook select PERSONAL XLSB from the Macros In drop down list To run macros from another workbook the macro must be from a signed source or you must enable the macros You can set the security setting for macros See the section Set Macro Security to learn more about macro security Select the cells where you want to apply your macro Click the Developer tab Note See the task Introducing Macros to learn how to display the Developer tab Click Macros Alternatively press Alt F8 The Macro dialog box appears If your macro does not appear in the Macro dialog box click the down arrow and then Select the workbook that contains your macro Fags Awaken Bier eel G O E EmA he E cpa Procethe Map orepecte irpo C Ds mo a oo m a oe dt a MLETI Fj kr irsi ia pn ih i A ma i al z A fi F roe 1 2 Income Statement 4 4 Year i Year 2 Year 3 Year 4 a Net sales g 200 000 250 000 00 325 000 00 450 000 00 L a Cost of goods sold 150 009 090 150 000 00 150 000 00 150 000 00 Gross profit
16. Q Assign a Digital KL A as digital signature provides assurance that a Signature to a Macro workbook file that contains macros is valid and A one has altered the macros There are two types of digital signatures personal digital signatures and commercial digital signatures You can create a personal digital signature by using the Digital Certificate for VBA Projects tool or you can purchase a digital signature Refer to the section Create a Digital Signature to learn how to create a personal digital signature After you create a digital signature you must attach it to your workbook Attaching a digital signature is similar to sealing an envelope If an envelope arrives sealed you have some level of assurance that no one has tampered with its contents Use the Digital Signature dialog box to attach a digital signature The Visual Basic Editor VBE is a separate Excel module that you can use to edit your macros Assign a Digital Signature to a Macro SS 7 ii Access the _ Signature dialog box by opening the VBE The Digital Signature dialog box lists valid certificates You can use the Digital Signature dialog box to view certificates and to select the one you want to use Unless you have on your computer a valid digital signature certificate for the signature used to signa macro Excel removes the digital signature when you modify a macro and you must reattach it I
17. See Chapter 2 to learn more about the VBE Click the proper module to access your macros or the VBA code you have written O Type or edit your code here Ea Press Alt F11 to return to Excel The VBE returns you to Excel Created by Denise Etheridge Purpose Calculate annual bonus ction Bonus Salary Percent ultiply Salary times annual percentage Bonus Salary Percent Approved by KP End Function J Jaidey e e I hee Mirah way Faced Baigi ea igaprbany blir ewe EY Far F bd Ss avid oe Fey spe Geen Feral th ikm Int Fag Layout amalie Iuin Enns By fred Tava cpat amebi E E Yiee ry fig of SP rpete EP Map crepe Flared iL t RA Wie Fa tive SIE TEI vice cece atin oaks BE Saas SBS Aur pels Fe Fa ie Toke GEE vaol kame Adii COP kumt Do ENE a Crarari B r ErrEEN i T mk Er heal caps ore Hg a3 Hadr AIE TA renak kasi mucap Al in SJonuo WIog pue sonew Susn You can also use Microsoft Visual Studio Tools for the Microsoft Office System Visual Studio to develop programs for Microsoft Office products With Visual Studio you can write in languages such as Visual Basic NET Visual C and Managed Extensions for C Visual Studio is not part of Microsoft Office you must purchase it Microsoft supports both VBA and Visual Studio Visual Studio is more difficult to learn than VBA and setting up and using Visual Studio is much more difficult than setting up and
18. Toolbar Click the Customize Quick Access Toolbar button and then select More Commands The Excel Options dialog box appears Click the down arrow and then select Macros Click the down arrow and then select the workbook in which the button Should appear Click the macro you want to assign to the Quick Access toolbar Click Add The macro appears in the box on the right Click Modify Q Bi CELEIT tiana Mov tothe faik Aire Totatikon Winen Prrd r EI e eean nbe TE E cpa JR hra AF hete F Map repedc 5 Hi Pr 4 BPS anim tabs Gf ba 5 P ae asm Income Statement E nam CARESTIE Ye Year Z Year 3 Year 4 a Netsalegs tirmizeeni o 4 750 000 325 000 450 000 Cost of guecrsere 150 000 150 000 150 000 150 000 Gross profit on sales 50 000 100 000 175 000 300 000 Expenses 25 000 35 000 50 000 45 000 Net incame 25 000 00 65 000 00 125 000 00 255 000 00 ib aves ar 3 etn Jud inn ed fan z Distants 2 Syra Quit Bonus Terba bakea he T ben The Modify Button dialog box appears A 3 E Click the button you want to use to Mm E Tarhan a ees ud a Tao rt represent your macro a ett D i Click OK to close the Modify Button ma cn dialog box mat HHEH K E E E ee E C i A EH V Click OK to close the Excel Options sarea e 0 BO tg dialog box sousee su rar pre GQ Fim Bod DFA ii O e e V 2 Syra ukt s Teer ek
19. acros Excel checks all documents you open for macros See the section Set Macro Security for more information If you have files that you do not want Excel to check you can store them in a trusted location Click the Developer tab Click Macro Security in the Code group The Trust Center appears Click Trusted Locations The Trust Location pane appears Click Add New Location The Microsoft Office Trusted Location dialog box appears Enter the path to the trusted location Click OK HHI r Rina Wariaake Meer Fir A u An O Click the name of the macro Erm lkm Trur Figa ipat amale Duis Ermira View Ta cp 5 7 ow 8 A Yei pi i H 7 hret i Hpinpde Eae EL you want to run a nA Beater etl z po ze Dnig1 Bae cp US aise am eee mires P oat Q Boalt uel Haze Hm halen Erbrsh Ju Arz z z a T Mo Le ll KIincd F Click Run a a z 2 Income Statement beh a ee 4 Year l Year 2 Year 3 Year 4 a Net sales lj 200 000 250 000 00 325 000 00 450 000 00 C a Cost of goods sold AAAA 150 000 00 150 009 00 150 000 03 V Gross profit on sales SC _ Eee 1 00 TOUR pi o a Expenses 25 ae 00 00 a Net income aman 00 55 000 00 ie mn ai nu Lon 2 ra wW la a SAMA unl Marras li hatia i maaka ha o m Que bana g dame dbo inkle sap V Mar rall o a Sel o d i i pI Ell o gt r ins Mariaka Kerran ved m El am Excel runs the macro th Ikem Tar Faga yat
20. al Basic button on the Developer tab If you create your macros using the macro recorder Excel defines each macro you create as a procedure and stores each procedure in a module The VBE lists modules in the Project Explorer under the workbook in which they are located Add a Form Control to a Worksheet ou can add controls to a worksheet to make it easier to enter data Form controls can aid users who are not familiar with Excel and can increase the accuracy of data entry by limiting the options a user has For example you can add check boxes to your worksheet so your worksheet looks like a paper form You can also add a combo box from which users can select an entry Excel provides nine controls you can add to a worksheet You add controls by selecting the control you want from the Form Controls menu After you add a control you can adjust its size by dragging the side or corner handles When you add a control or when you right click a control twice and then click the control you are in Design mode Add a Form Control to a Worksheet In Design mode you can modify the properties and size of the control but you cannot test its functionality When you place a control on a worksheet it sits on top of the worksheet You can size it so it appears to be located in a cell but controls are separate from cells You can place controls anywhere on the worksheet A control can cover any portion of a cell or range of cells Aft
21. amed OptionButton1 If Add a Macro to a Form Control you create a macro for the option button Excel gives the macro the name OptionButton1_Click Every time you add a new control Excel gives the control a unique name by adding a sequential number to optionButton for example Optionpgutton2 Click Option Bubbone Click and so on The portion of the macro name following the underscore character corresponds to an action commonly referred to as an event For example with an OptionButton control the user clicks the radio button to select the option so the event is Click If you create a macro for a combo box control Excel assigns Change to the name of the event because you want to execute the macro when the value of the control changes The event extension tells Excel to monitor the control and execute the macro whenever a user clicks the control Right click your control twice A menu appears Click Assign Macro The Assign Macro dialog box appears Excel assigns a default macro name for the selected control Click Record The Record Macro dialog box appears Click OK Record your macro Note See section Record a Macro to learn how to record a macro a Iwa cp acta Tom OF bap epee aik iL fore Beena ay E Sli Se ee Ee eee aes Unit Price Total ltem Price l ml 260 00 260 00 Computer 1 295 00 J 30 00 60 00 Manitor 260 00 Mank Tab wea E 1 295 00 12
22. ars Click Choose The WBA project is currently signed as Certificate name No certificate Sign as Certificate name Denise Etheridge L IaJdeyy e e The Select Certificate dialog box appears Select the certificate you want to use Note See the section Create a Digital Signature to learn how to create a digital signature 1 Denise E Code Signing None 1 1 2016 5 Click the signature you want to apply Click OK to close the Select Certificate dialog box Click OK to close the Digital Signature dialog box e We ped A e o pad N Tj z A e a e pi o me currently signed as Certificate name No certificate Excel attaches the digital signature to your workbook ane Certificate name Denise Etheridge Remove i Choose Note To return to Excel press Alt Q If you have Macro Security enabled Excel displays the You can use the Microsoft Office Security Options Trust Bar below the Ribbon when you open a workbook dialog box to select the security option you want On containing a signed macro You can modify the the Trust Bar click Macros Have Been Disabled workbook but you cannot use the macros If you trust Security warning options appear Click the Enable that the document is safe you can click the Enable Content button A menu appears Click Advanced Content button on the Trust Bar to enable the macros Options Th
23. box however using the Quick Access toolbar means you can access the macros by simply clicking the appropriate button When you add a button to the Quick Access toolbar you can specify whether it should appear on the toolbar of all Excel workbooks or only on the Quick Access toolbar in the workbook you specify By default the button appears in all workbooks If you have placed your macro in the Personal Macro Workbook you probably want your macro button to appear in all workbooks because the macro is available to all workbooks If your macro will be available only to a single workbook your macro button should appear only on the Quick Access toolbar for that workbook You use the Customize the Quick Access Toolbar page of the Excel Options dialog box to add a macro button to the Quick Access toolbar You can use the Modify button to specify the button you want to use to represent your macro You can specify where on the Quick Access toolbar your button appears and whether the Quick Access toolbar appears above or below the Ribbon You can click the Reset button to return the Quick Access toolbar to its default state Deleting a macro does not remove the macro button from the Quick Access toolbar When you press the button for a deleted macro you receive an error message Use the Remove button on the Customize the Quick Access Toolbar page of the Excel Options dialog box to remove a macro button Assign a Macro to the Quick Access
24. d places a numeric value representing the control selection in the linked cell EHI r Enip irra Fom famia Manrara aH am oth en Iran Fuga apat amale Iuin EmA Mme cawepar acta r E f g aae A harethe kip impide npert ls am Au Fela P RK om uer E Benue e i a bobs etcratt oe rant peeps EELE 1m dn AIE TA iff ail ea J Jaideyp Item Price Computer 1 295 00 Moniteer 260 00 Keyboard 55 00 Mouse 30 00 Speakers 75 00 El a r amp ain der eo Foon Coad Minne Far a Erm likme mur Faga ipat amale EmA we Tava cpat acta e E Yei fi Af hte T Hap rcp aki L Bete Retire tiki E l T i t ime Cock S dite Tatib Tonks ease a ving CIN Dai amm aH ro Ceoururi are haa nip ah Ik Reet cuts ere Cae amm nar mi Moca ay ia E Cia E a H I Eas L sen tem Description Unit Price Total Item Price 2 eke i Computer 1 295 00 a Oo Monitor 260 00 Keyboard 55 00 Mouse 30 00 Speakers 75 00 EH Sapa o ia Sl y Fg 4 FT Example Computer S195 Monitor 995 Keyboard S55 hen working with a value selected from a list box or combo box control you may want to use the value in the linked cell to set the value of another cell For example assume you have the following Excel list in cells H2 14 You can use the INDEX function to determine the price based on the equipment selection For example if the user selects Monitor from the control Excel places a value of 2 in the linked cell I
25. e Microsoft Office Security Options dialog in the workbook The workbook then becomes a trusted box appears Click Help Protect Me from Unknown document and you will not need to enable the Content Recommended to disable the macros click workbook again Enable the Content for This Session to enable the macros for one session or click Trust All Documents from This Publisher to add the macro publisher to the Trusted Publisher list Excel does not display a warning when you open workbooks with macros if the publisher is on the Trusted Publisher list pO oe acros enable you to quickly perform tasks that would normally take multiple steps When you run a macro Excel replays the steps you recorded when you created the macro You can run any macro located in any workbook as long as the workbook in which the macro is located is open To run a macro you can press the shortcut key you assigned when you created the macro or you can select the macro from the Macro dialog box When you create a macro you can choose to store it in one of three locations the current workbook a new workbook or the Personal Macro Workbook By default the Macro dialog box lists all the macros in open workbooks If a macro is stored in the Personal Macro Workbook the macro opens as a hidden file each time Run a Macro you open a file By default the files in the Personal Macro Workbook always appear in the Macro dialog box You can use the
26. e hT Ekon lat The button appears on the Quick Access ia mi h E a ARA E Tj toolbar MoM a z ae Si Ban A S Click the button to execute your macro z Excel executes the macro Income Statement 3 4 Year l Year 2 Year 3 Year 4 et a Net sales 5 200 000 350 000 325 000 450 000 O a Cost of goods sold 150 000 150 000 150 000 150 000 p Gross profit on sales 50 000 100 000 175 000 300 0000 V al Expenses 25 000 35 000 50 000 45 000 a Net income 25 000 65 000 125 000 255 000 You can also assign a macro to a custom Ribbon tab Right click the Microsoft Office Ribbon A menu appears Click Customize the Ribbon The Excel Options dialog box appears with the Customize the Ribbon pane selected Click the down arrow next to the Choose Commands From field and then select Macros Click the down arrow L next to the Customize the Ribbon field and then select Main Tabs Click the New Tab button Excel creates a new tab and a new group Click New Tab Custom and then click Rename The Rename dialog box appears Type the name you want to give the tab and then click OK Click New Group Custom and then click Rename The Rename dialog box appears Type the name you want to give the group and then click OK Click the macro you want to add to the custom Tab and then click the Add button Excel places the macro in the Main Tabs box Click Rename The Rename dialog box appears Click the symbol you
27. er you add a control to a worksheet you can assign it values See the next section Assign Values to a Form Control to learn how Form control options are located on the Developer tab See the section Introducing Macros in this chapter to learn how to display the Developer tab Click the Developer tab Click Insert The Form Controls menu appears Click to select the control you want Click and drag the mouse pointer to create the control Click and drag the handles on the sides and corners to adjust the size T n P i Erm lkm irur arale Tuis EmA Vee Ira cp Yeri i ap scat EJ D Map cpa H rperi a Ez Ah Felt Bune ZEEE i ernim Tonks Bea pa Ye ta Addin COA EEPE f Ccara Ba Sha eee zs Jaiz Hm ikang Er testi ia Tere amr 3 I r i 1 Quantity Item Description 9 wa g Price Total F gead e ql Gi ic t EEN ni ii te 15 14 is ih i 246 Breall Sa Se Pd d E m EHI r Wahl Fen Connal a Mobeko biirr For A aroo reat th lkm Turn Page pat at Euis EmirA Ve Ta Cpa EP Mapped plored l WE be Yfed ry Tae P SP acetic il al E3 AUi Feita Fatemi c 5 a b Wize cece E i brain ma Beas Dy vaol bame diire CIFA kumt Duga EERE Corer Baal han umin a Made Were A kerea an O O O amm ren BI Paed Deep linen o E a A S Pc H I n 1 Quantity Item Description Unit Price Total
28. f you are not sure if a workbook has a digital signature you can check the signature by reviewing the Digital Signature dialog box If a workbook has a digital signature the name of the signature appears in the Certificate Name field If you click the Remove button in the Digital Signature dialog box Excel removes the digital signature Click the Developer tab Note See the section Introducing Macros to learn how to display the Developer tab Click Visual Basic in the Code group The Visual Basic Editor appears Click Tools gt Digital Signature IHS BEI e ru daiga a Wed Sayaatiar Mi ou 0 riat Fm l lipat mat Coie Ermira ewe gc F Ea Pe bee se E e ib Yaoi bame 5 Adii COA ki dek Daryn EELE Crearari nip ilin hal rina 5 B A zaak F E A z fi F Fo z Income Statement J 4 Year i Year 2 Year 3 Year 4 5 Net sales 250 000 00 325 000 00 450 000 00 a Cost of goods sald 150 000 00 150 000 00 150 000 00 150 000 00 Gross profit an sales 50 000 00 100 000 00 175 000 00 300 000 00 a Expenses 25 002 00 35 000 00 50 000 00 45 000 00 a Net income 25 000 00 65 000 00 125 000 00 255 000 00 ay Sapa riral DollarSignFort af VSD oped Prange 3 Ery darru DollarsSignForma Accounting format no decimal places with dollar sign Keyboard Shortcut Ctrl m Selection NumberFormat 0 j 0 The Digital Signature dialog box appe
29. f you want users to find the cost of the selection you type a formula similar to the following assuming that C2 is the linked cell Example INDEX SH22 S124 e2 2 When the user selects Monitor the INDEX function returns 995 The INDEX function actually creates an array of the Excel list and uses the control selection to determine which element in the array to return The function uses three arguments Array Row_num and Column_num See the file Form Control Example xlsm which you can download from the Web site for this book to see an example e e C o GQ ped A e o ped a n rj e AO e o e DA CY N Add a Macro to a Form Control pO oe ou can use macros to automate the tasks you perform in Excel You can assign a macro to any form control on a worksheet For example if a user clicks a button control you can have Excel execute a macro You can create one macro for each control on a worksheet You create a macro either by recording a series of keystrokes or by writing a Visual Basic for Applications VBA procedure When you select the Assign Macro menu option Excel automatically creates a new macro name by using the name of the control followed by an underscore and an event name such as _Click Excel assigns the control name to the control when you add it to a worksheet For example the first optionButton control that you add to a worksheet is n
30. g gt F 5 z z 4 S O1JUOD WIOJ pue sonew SUISQ Crtani Mom ie a betiabe ing bie irsaban Wraak Fare a R Figa Laut arale Euin EmA Yje r b a Af hte i Hap repaired cis k Eg Gob Uie Fee Retire tiki z Poe Doce Fi brain Peho A mn 2 dp eai t He e View iad Cpt E Crearari 1 ikalsy jaa Tj kr besdi taha ere AGE TA FI merap MOTETA Because of problems with macro viruses by default Excel disables all macros when you open a workbook You can read the file but you cannot execute the macros You can click Macro Security on the Developer tab to change the default setting or you can click the Enable Content button that appears when you open the workbook to enable the macros To learn more about macro security see the sections Set Macro Security Create a Digital Signature and Assign a Digital Signature to a Macro in this chapter To save a workbook that has macros click the File tab A menu appears Click Save As The Save As dialog box appears Locate the proper folder Type a filename in the File Name field Select Excel Macro Enabled Workbook xlsm in the Save As Type field Click Save Excel saves your workbook as a macro enabled workbook and gives the workbook an xlsm extension Set Macro Security A ah coe ecause of increasing problems with computer viruses specifically macro viruses
31. ign a linked cell Enter the number of items in your drop down list The value associated with your selection appears in the linked cell Click OK 22 i Baun Valin fa Bien Srwibska Brea For r D E E Bik Wms th Ike iran Page pat ani Coie Erra ey Iwa cp amotai Termit tal Yiee Fg g ga kha SP rrethe T Map crepes plored iL i l b 7 35 Ffu Fete Feline tiki ize ZEEE Ge ai kat Peaks Beas vaol kame r fddire Oni kumt Digi EERIE pm E Ba Han nid S Jade Hami hal eee mou Acumip k AGE TA LLES TPI Kincdp eplin th i A e 5 La a lejl c 1 Quantity tem Description Unit Price Total Item Price 7 Computer 1 295 00 te tuum Monitor 250 00 i a Keyboard 55 00 ae Mouse 30 00 HENS Speakers 75 00 E Formal iarl e p 5 aie ie a e e EA tS feen alam ba a Baers Samir ika birrat For el ery Tae a FO EE e rur agape emale Daa ee ecacpa derek Terme song D E aurama S G k a TBD ea gece 0b Perel bame Aui Addire Bate kent ailas CTT LERLE Bj ve RAS onein amo o anm ai hinoi eplin th i E a Ba ES Se E E S H I Ug 1 Quantity tem Description Unit Price Total Item Price 2 a J Computer 1 295 00 4 Maniter 260 00 4 Keyboard 55 00 E Mouse 39 90 n Speakers 75 00 2 VW aal Fe 15 it i Ih 17 pi E a ae a Click the down arrow and then select the desired control value Excel selects the value an
32. lect the cells where you want the macro to execute Press Ctrl and the shortcut key The macro executes Repeat Steps 1 and 2 to execute the macro again Note In this example the macro removes the decimal places EHI r Tirar aed anail a Prylar Shrrtinaban Brrriat Fae F D Erm lkm Turn Faga apat amaie Iuin En wy Tava cpat sB ow 8 all Po Pacord Agra F g Meni 22 SP ht Cj Hap rcpade Hi arpert aT al a Goa Wie Relive Fetore tiki Ca Ea Fs bie Buz Care Fi brain Faha Peas vuol Want r fdr oon et dga FENE og rae Cecurari Ba dh Han adp a Mae f Ue 1 Jia wra SAE rA rer Ti CE E i A a a F a A i n F F z I 2 Income Statement J 4 Year 1 Year 2 Year 3 a Met sales 200 000 750 000 4 225 000 a Cost of goods sold 150 000 00 Gross profit on sales 50 000 00 a Expenses 25 000 00 a Net income 25 000 00 65 000 00 125 000 00 EH a S amp Firar asd Daaih a Prylar Shrrtinabar Wirral face ee BIJ e rur mgt erae Daa ams e eap sorg F Yerdan 3 Frngetite GR bbp crepes Gplarpert l a na Fatemi Si ie be w ae a am Tadu a EA Bai Sues r diis Hais Dee A LET Tere E vi A aa i a a a A ia n F EE t 2 Income Statement J 4 Year 1 Year 2 Year 3 Year 4 a Net sales 700 000 5 250 000 4 325 000 450 000 a Cost of goods sold 150 010 150 000 150 000 150 000 1 Gross profit an sales 50 000 100 000 175 000 300 000 a Expenses 25 000 35 000 50 000 45 000 a Met income 25 000
33. ou plan to distribute your workbook to others you should consider acquiring a commercial digital signature When you use a commercial digital signature the digital ID attaches to the macro and Create a Digital Signature 00000 9 Click the Start button Click All Programs Click Microsoft Office Click Microsoft Office Tools Click Digital Certificate for VBA Projects The Create Digital Certificate dialog box appears Type the name you want to give your certificate Click OK Excel creates a Personal Digital Certificate J Jaidey e e remains with it if anyone alters the macro Excel notifies the user that the macro should not be trusted The most common provider of commercial digital certification is VeriSign Inc To obtain a commercial certification you must submit an application and pay a fee You can find out more at www verisign com To view the certificates in your Personal Certificate store open Windows Internet Explorer On the Internet Explorer menu click Tools and then click Internet Options The Internet Options dialog box appears Click the Content tab Click the Certificates button The Certificates dialog box appears Click the Personal tab All of your personal certificates appear S OUO0D WIOJ pue sonew SuUIsSn ESO OSS tok ee au idl treats Magia T errie bce minal ged de veils dl ban je sec reales hee Thane ol carte dee oot erp a der z ogtel codices Th ba ger
34. s and list boxes you can place the options associated with the control in a range of cells For example if you use a combo box you tell Excel the list of values used by the control by entering the range of cells containing the values The values can be located on another worksheet or even in another workbook as long as Excel can access the workbook when users view the worksheet that contains the control Assign Values to a Form Control You can link a cell to a control If you link a cell to a control whatever value users select when utilizing the control becomes the value in the linked cell If you use a combo box control or list box control the value in the linked cell is a number that represents the user s selection Excel assigns the number based on the position of the selected value in your list If the list is Computer Monitor Keyboard and the user selects Monitor the linked cell receives the value 2 because Monitor is second in the list With a control such as a check box you can tell Excel whether you want the option initially selected or unselected Both options selected and unselected have an associated value Right click the control twice A menu appears Click Format Control The Format Object dialog box appears Click the Control tab The available fields depend on the control type This example uses a combo box ay Enter the range that lists the valid values Enter a cell to ass
35. signature by using the Digital Certificate for VBA Projects tool or you can obtain a digital certificate from a commercial certification authority For more information on the Digital Certificate for VBA Projects tool see the next section Create a Digital Signature Set Macro Security Macro Security Click the Developer tab Note See the section Introducing Macros to learn how to display the Developer tab Click Macro Security The Trust Center dialog box appears Click a macro security setting changes to Click OK Excel changes your macro security setting Q Ell s r Fonta Wian Fud E i om BIJ ee rus mgp erae aa as vw c song Dia ferdi AP hret J Hap rep aT eal re nage ne Pateni E 2 bi Fose zre J a cat en teta Fe D zaol bante ut ays EELE Cearari Ba dh Han as wra ABE TA Fi KIncd sp al ma mA E E H re a ee L M a Th Dhvable all moues m Site a da robbal mars p stew eaedbe prir ETE mes ar rug Create a Digital Signature f you create a workbook that contains macros you should consider using a digital signature A digital signature provides assurance that no one has altered the macro You can create a personal digital signature by using the Digital Certificate for VBA Projects tool Digital signatures created with the Digital Certificate for VBA Projects tool work only on the computer on which the digital signature was created If y
36. the default Excel macro security setting disables all macros when you open a workbook and enables you to decide on a case by case basis whether you want to enable them This is true whether you created the macros or someone else created them You can change the Excel macro security by choosing one of four options The Disable All Macros without Notification option disables all macros This option does not provide you with any security alerts to let you know macros exist The Disable All Macros with Notification option is the default setting It notifies you if macros are present so you can enable them on a case by case basis The Disable All Macros except Digitally Signed Macros option disables all macros except those digitally signed by a trusted publisher If the publisher has digitally signed the macro but you have not opted to trust the publisher you can enable the macro or trust the publisher See the Extra section of Assign a Digital Signature to a Macro in this chapter to learn how to trust a publisher The Enable All Macros Not Recommended Potentially Dangerous Code Can Run option enables you to run all macros Because potentially dangerous code can run Microsoft does not recommend this option Changes you make to macro security in Excel do not change the macro security in other Office programs Macro creators use digital signatures to verify the safety of the macros they create You can create a digital
37. u want to use with multiple files If you store your macro in a New Workbook you can use the macros whenever that workbook is open Use the This Workbook option if you want to store your macro in the workbook in which you are currently working Click the Developer tab Note See the section Introducing Macros to learn how to display the Developer tab Alternatively click the Record Macro button on the status bar and skip Step 2 Click Record Macro The Record Macro dialog box appears Type the name you want to give your macro Type the shortcut key you want to assign to your macro Press Shift as you type to assign an uppercase key Click the down arrow and then select the workbook in which you want to store your macro Type a description of your macro Click OK Q hpp H rpei iL brati kn Maho ELJ kaci fant aoe amanea MAO GE am mm nears E aana o Eigra Bi A scr f F o A Z fi F FE l z Income Statement J 4 Year i Year 2 Year 3 Year 4 s Net sales 250 000 00 325 000 00 450 000 00 a Cost of goods sold 150 000 00 150 000 00 150 000 00 150 000 00 Gross profit on sales 50 000 00 100 000 00 175 000 00 300 000 00 a Expenses 25 000 00 35 000 00 50 000 00 45 000 00 a Net income 25 000 00 65 000 00 125 000 00 255 000 00 THET BI oe rur ragya arale Tuis Erra ahe Iwa Cpa 5 7 2P 8 trane li af hete ER
38. ur report With VBA you can do more than just create macros You can use VBA to edit macros create new functions create custom applications and create add ins For these tasks you must learn the VBA programming language This book teaches VBA It is based on Office 2010 Code you write for Office 2010 may not be compatible with earlier versions of Excel Click the Developer tab Note See the section Introducing Macros to learn how to display the Developer tab Use the options in the Code group to automate your tasks Click either of these Record Macro buttons to record a macro Note See the section Record a Macro for more deiails Click Macros to run a macro Note See the section Run a Macro for more details Use the options in the Controls group to add check boxes fields and other form controls to your worksheet Use the options in the XML group to work with XML Click Visual Basic or press Alt F11 Excel moves to the Visual Basic for Applications Editor VBE Epa Ta af hete Gj Map crepes lore aT ae Zart Qa brania ma Beas P peels wilh sani Tj krri a me AGG TA D E eee hoe E el L m Nz i upat amalie Duin Eran By fe Tava cpat aetati s Boe 8 fig Ta ao EJ ei mepa iL Ao Doce Brat bon Fock Ep aa kumt Dadi LERLE a E eddie bair Hm ang one oe rena TI ae Ce Se co E W Use the VBE to write and edit code Note
39. using VBA However Visual Studio offers better security a more sophisticated development environment and built in Web services Introducing MET gets po oe ou can use macros to automate many of the tasks you perform in Excel For example if you frequently format your data in a particular way you can use Excel s macro recorder to record the steps you use to format your data You can then play back the recorded steps whenever you want to apply your format Most of the commands you can execute in Excel you can also record and play back The commands you use to create and execute macros are located on the Developer tab By default the Developer tab does not display in Excel To display it you must select Developer in the Customize the Ribbon pane in the Excel Options dialog box You begin recording macros by clicking Record Macro on the Developer tab or by clicking the Record Macro button Introducing Macros Click the File tab A menu appears Click Options The Excel Options dialog box appears Click Customize Ribbon E Way Se on the status bar Both options open the Record Macro dialog box For detailed instructions on how to use the Record Macro dialog box see the section Record a Macro When you record a macro you can record it using an absolute reference or a relative reference If you record using an absolute reference when Excel plays back your macro it plays back the exact cells
40. y Jen vil eos a ty wage riea do mas o dele ri ll aa CORET webpages uses ee deme ede wads seh beads Pood alread Ali heh totaal eae ee rll Record a Macro E F Bua macro enables you to automate common tasks You can use a macro to record most of the series of commands you can execute in Excel For example if you frequently apply a certain format to your worksheet you can record the steps for creating the format and then play them back each time you want to apply the format Clicking the Macro Recorder button opens the Record Macro dialog box You can use the Record Macro dialog box to name your macro assign your macro to a shortcut Key and tell Excel where you want to store your macro You can name your macro anything you want however the name must start with a letter only contain letters numbers and underscores and not contain any spaces Record a Macro You can assign any upper or lowercase letter to act as the shortcut key In the Record Macro dialog box the Store Macro In field tells Excel where to store your macro You can choose to store your macro in the Personal Macro Workbook a New Workbook or This Workbook Use the Personal Macro Workbook option if you want to make your macro available to all Excel files After you have stored at least one macro in the Personal Macro Workbook the workbook opens whenever you open an Excel file Use the New Workbook option if you have specialized macros that yo
Download Pdf Manuals
Related Search