Introduction to Excel VBA & Macros - sentex.net

1 Introduction to Excel VBA & Macrosreferences: VBA & Macros : Microsoft Excel 2010, Bill Jelen, Tracy Syrstad VBA & Macros : Microsoft Excel 2013, Bill Jelen, Tracy SyrstadPeter Komisar Conestoga CollegeThis note has been generated for private academic use and is not meant to be published - PK Excel users have discovered that business reports can be generatedwith great efficiency using ' visual Basic for Applications', commonly called 'VBA', the macro language that comes with Microsoft Excel . Typical User Usage of Aspects of Microsoft Excel 'Mr. Excel ', the company that authors the text book used in this course,found in a poll of 8000 readers that only 42% of 'smarter-than-average'users were using even one Excel 's top ten power features.

2 One of the text authors who gives seminars to accountants who spend thirty to forty hours on spreadsheets routinely draws 'gasps' from the audience when demonstrating how quickly some tasks can be accomplished. The point is that however familiar a user may be with Excel , there is likelya lot more that can be learned to improve the user's effective use of the application. Summary of the 'Mr. Excel ' Case StudyMonthly reports were left out of resource allocation, however one resourceful employee used Excel to create the needed report in about three hours. The employee became the company 'hero' and was asked to generatesimilar reports for all 46 departments. The employee was now swamped, spending 40 hours a month doingnothing but generating reports.

3 Mr. Excel to the RescueThe employee finds and with his help, applies a series of Macros , and reduces the 40 hour task to two button clicks and a wait of about fourminutes. VBA's Future with ExcelWhile rumors have circulated that Microsoft may cease support for VBA, Mr. Excel sees evidence of VBA being supported in Windows version ofExcel to the year 2025. VBA was removed from the Mac version of Excel 2008 and the complaints were great enough to have VBA support restored to the next version of Mac Excel . Even if VBA at some point is retired and replaced by another language, the coding skills should easily be transferable to the new language. Excel VBA VersionsThis text is the 4th edition of the book and is designed to work with Excel previous edition of the book covered code that worked with Excel 97 through to Excel 2010.

4 A great majority of the code is the same. // there is some new stuff in Chapter 12 on Pivot Tables. Chapter 28 describes what's new in Excel 2013t 2010 works with VBA and Macros 3rd between Windows and Mac Versions of ExcelA number of differences between Excel running on Mac as comparedto Excel running on Windows exist. To see the differences view the following web site. List of Differences Between Mac and Windows Excel Files to Accompany Textbook ExercisesA number of files are used in conjunction with the book can be downloaded from the following Excel Power of Excel "VBA is sitting on the desktops of 500 million users of Microsoft office and most have never figured out how to harness the powerof VBA in Excel ." Using VBA the production of any task in Excel can be sped up.

5 Two Hurdles The macro Recorder is flawed // at least when default settings are used VBA is hard or frustrating to learnThe Broken macro RecorderBy the mid 1990s, Microsoft was dominating the spread sheet migrated from using Lotus 1-2-3. The macro language was however very different. VBA is considered more powerful than the Lotus macro language, however a fundamental flaw is that the macro recorder is faulty. The macro recorder in Lotus worked fine. What worked today on Lotuswould work tomorrow. Back in 1995 Mr. Excel was a spreadsheet wizard in an office where a migration was done from Lotus to Excel . He was confronted with a macro recorder that didn't work and a macro language he didn't understand.

6 Mr. Excel found visual Basic nothing like BASIC. // the flawed macro was what inspired Mr. Excel to write the text bookSolutions the macro recorder can be made to work VBA is not as hard to learn as it first appears and is very powerful liberating as one no longer have to wait on IT to write a program redundant tedious report generation can be automatedInstalling the Developer's TabBy default the Developer Tab which provides the VBA tools is hidden. To open the option to access the developer tool do the following moves. Open File (new Backstage View) Options Customize Ribbon Select check box for Developer Tab ( in right list box) click OK Icons on Developer Tab // left to right visual Basic icon opens VBA Editor Macros icon dialog to run or edit macro Record macro icon turns on macro recording Use Relative Reference icon switches to relative from absolute recording example move down two cells from B5 (absolute) move down two cells from cursor location macro Security icon access Trust Center allow or disallow Macros to run on this computer// other features of the Developer Tab are looked at later in the Chapter.

7 Excel 2013 File TypesExcel 2013 supports four files types Excel Workbook (.xlsx) a series of XML objects zipped into a single file compact files are space savers the default type Macros cannot be stored in this file type // worry free format Excel macro -Enabled Workbook (.xlsm) like default format except Macros allowed enables easy detection if macro inhabits file // Heads up! May contain macro Excel Binary Workbook (.xlsb) introduced in Excel 2007 accommodates larger than million row grid size load quickly but more prone to corruption a few lost bits can destroy file Macros allowed Excel 97-2003 Workbook (.xls ) legacy support Macros allowed cells lost outside A1:IV65536 If opened in Excel 2003, 2007 and later features are lost// file extension formats: xls legacy + x xml, + m Macros , + b binary You May Customize to Always Save in.

8 Xlsm Format click File Options Save drop down 'Save Files in This Format' select Excel macro -Enabled Workbook (.xlsm ) click OK // See TEXT NOTE describes .xlsm phobia Author feels fear is unwarranted and that the security// setting for preventing Macros to run should be used anywayMacro SecuritySome serious viruses were spread using VBA Macros . One serious one was called the Melissa virus. Microsoft subsequently added security measures to ensure their settings can be set be set globally or on a workbook basis storing the workbooks in trusted locations workbooks stored in a trusted locations have Macros enabledAdding a Trusted Location click macro Security in Developer's tab click Trusted Locations click Allow Trusted Locations on My Network click Add New Location click Browse click parent folder of trusted location click trusted folder click OK click 'Subfolders of this Location Will Be Trusted' ( to trust subfolders ) click OK // when an Excel attachment is clicked on an e-mail the file is stored on a temporary file on C: // drive as such you do not want to globally add C.

9 \ and subfolders to the Trusted Location hat are not stored in trusted locations rely on macro settings in Excel . 'Low', 'Medium', 'High' and 'Very High' security used in Excel 2003 are renamed as follows. The setting are accessed in ' macro Settings' in the Developer Settings Disable All Macros Without Notification prevents all Macros from running no use to the macro developer ~ equivalent to old 'very high' security Disable All Macros With Notification a notification arises when a file containing a macro is opened forces user to enable or disable ~ medium security the recommended setting Disable All Macros Except Digitally Signed Macros requires a digital signing tool from a provider like VeriSign appropriate for commercially sold add-ons ~equivalent to old high security setting Enable all Macros potentially dangerous not recommended ~ like the old low security settingDisable All Macros With Notification Is

10 The Recommended SettingAs shown above this is the recommended setting. Click enableif expecting Macros in the workbook. Dismiss the Security Warning by clicking 'X' if you do not wantmacros to run in the current workbook. If disabled from running, it may be necessary to close and reopena workbook to change the settings. Mr Excel Caution: A saved workbook will remember if Macros wereenabled and will automatically enable Macros on reopening. Recording, Running & Storing MacrosInitially the macro recorder can be used to record a set of Excel actions. Select the Record macro icon and fill in the dialog. macro Dialog name // continuous characters no spaces, use meaningful names shortcut key // most lowercase letters are in use, ctrl+lc, (can type Shift + A to Z) save // recommended to save to a particular workbook in 'This Workbook' description // adds as a comment to the macro beginning date & user name not auto added anymoreIt is not recommended to use 'the Personal macro Workbook' except for general Macros .