- Slides: 20
Piquing Interest in Excel Macros Let the Spreadsheet do the Work November 18, 2014
Introduction • Excel has so many features and it is unlikely that anybody has mastered the majority of these features or even knows what they are. • Excel also provides alternative ways of accomplishing a task and multiple ways to invoke a function. • Macros extend this capability by allowing a user to predefine a series of functions in order to complete a task. • The goal of this session is not to learn how to construct a macro. Instead, this session will demonstrate some of the macros I’ve developed over several years to get an appreciation for what macros will do. • If there is interest in learning how to construct macros, I’m happy to share. Frankly, it takes a little instruction and a lot of practice and investigation to be good at writing macros.
Why Invest Time to Learn Macros • Once defined, a lengthy or complex task can be run in seconds. • You can pass certain tasks to another staff member who has few Excel skills but is responsible for monitoring the data. • You can run it yourself without having to remember all of the steps of the process. Once you verify it works, you can have confidence that it completed all of the steps. • You can pull data from multiple databases (e. g. general ledger, student, payroll, Kronos, etc) to assemble a report or series of reports. • Quickly process a large number of transactions. • You can use other data, not currently stored in a database, to supplement the report or translate data (e. g. if a is true and b is false, translate or group an item differently).
Skill Levels • Beginning – record a macro, loop through each row of a worksheet, simple if-then statements, assign macro to a button. • Intermediate – retrieve data from a database, loop through rows of multiple worksheets, sort the data • Advanced – retrieve data from multiple databases, integrate the data, translate the data, create an outline • It takes a lot of practice to advance from one skill level to another. • There a lot of experts that post macros to the internet that you can copy and modify. • For most of the Excel functions, you can record a macro and it will store the code. You can embed that code within your macro. Examples of this might include coloring a cell, underline, bold, formulas, sorting, outlining, filtering, etc.
Show the Developer Ribbon If you want the Developer option in the ribbon … You need to follow these steps.
Beginner’s Level Example • Objective – Color groups of rows • Record macro to get code for coloring a cell • Create macro and insert recorded macro code where needed • Loop through the various rows with For/Next • For alternating groups, color the rows • Demonstrate: • • • Record a macro to paint some cells Show to integrate the recorded macro into a looping structure How to run macro from within the macro window How to step through the macro one instruction at a time. How to create a button to run the macro
Other examples – Financial Reporting • Supplies and Services Budgets • • • M D Retrieve requested budget information for ensuing year Match each budget with rationales provided by users via spreadsheet Sort the budgets by cabinet level, department, key description and object Use Excel’s outline feature to summarize the information Allow drill-downs to retrieve expenditure detail for prior years. Demonstrate: • • Show the parameters to be used in the report Show the rationales Video excerpt of macro running Review final report • How the outline was configured • Rationale • How to execute macro with keystroke combinations (Cntrl q) • Video showing drill down of expenditure detail for prior year
Other examples – Financial Reporting • Revenues, Expenditures, Transfers and Fund Balances • • • Allow user to indicate what data will be retrieved for each column Retrieve the information from the Bplus database Merge data with projections stored in separate sheets of workbook Get separate totals for cash and accrual entries Populate totals in pre-defined report structure Demonstrate: • • How the report was pre-defined The “cash vs. accrual” flag The outline showing major headings that can open to object totals How each cell has cash and accrual totals that change with the flag
Other examples – Integrity Checks • Payroll Verification • User selects the payroll to verify • Payroll records are retrieved • Using data in the Taxability and Tax. Tables sheets, the macro recomputes gross and net pay, FICA, Medicare, state and federal taxes and retirement. • Drill down allows differences to be investigated. • Demonstrate: • Totals from payroll vs. recomputed amounts • Drill down to method of calculation (net pay, federal tax, etc. )
Other examples – Integrity Checks • Leave Balances • Paste a download from Subfinder to the workbook for the current year • Have the macro format the report to complement the data to be retrieved from Bplus • Have the macro compare the leave for each person, for each day, for each leave type. • Report the results • Demonstrate: • Three separate macro buttons • Comparison report • Filter by color
Other examples • Requisitions to approve • Each board authorization for purchases is recorded. Additionally, each requisition, or group of requisitions, related to each purchase is recorded. The macro does the following: • Retrieves all requisitions in my queue for approval • Determines if the requisition is related to an approval already approved by the board. • If approved and the authorization has not been exceeded, it appears in green on the report. • If in the list for approval but the board date has not yet occurred, it appears in red on the report. • If not in the list, it is shown in a separate category on the report for my assistant to follow-up with the requester. • Demonstrate: • • • List of Board Items (Board. Auth) List of requisitions including cross-reference with board items. Highlight column G. Explain Extract sheet. Macro buttons on Control. Panel Report showing which requisitions to approve. Notice prior requisitions are also considered when determining remaining authority.
Other examples • Local Property Tax Distributions • The monthly transmittal information from our property tax payments is recorded in the spreadsheet. • For St. Louis County, settlements for prior year protests are withheld from current year distributions • The Tax. Rate. History tab shows the fund breakdowns for tax rates over the years and will be used to allocate the tax revenue by fund. • The Key/Object sheet has the appropriate account number information to code the revenue. • We added additional accounts to separately track: • Delinquent amounts by year. • Prior year settlements by year • When the macro runs, it creates the appropriate journal entry that we upload into our finance system. • Demonstrate: • • County information, tax rate history and key/object Journal entry (Items. To. Post) Macro buttons on Control. Panel Run the macro
Other examples • Tax Recoupment • As the State Tax Commission reduces valuations for prior years, re-compute the tax rate ceiling for each year and determine the one-year recoupment amount. • Demonstrate • • Show information from Annual Value – rows and columns Show sample of calculation sheet (2014) Go to Print, run macro to create pdf files. Look at files created. Describe process to • Add columns on Annual Value for new revisions • Add new calculation sheet, change the data page references and CPI • Audit certain fields to make sure nothing was changed inadvertently
Other examples • Index of information • In preparation for my current assignment, I reviewed a lot of information and looked for a way to organize the data. I could’ve used Evernote, but was more familiar with Excel and created a macro. • As I identified key requirements or deadlines, I copied them to a cell in a spreadsheet and placed tags (index items) that would later allow me to quickly retrieve the item. For deadlines, I also added a tag related to the month due. • When the macro is run, a list of tags is created, sorted and displayed in a dialog box for me to choose. • When I choose an item, I get a list of items where that tag was used. See top right of dialog box. • When I choose one of those items, I get the full text of the item in the larger box on the bottom right. • Demonstrate: • Review Deadlines and Requirements • Run the macro and show the full text.
Other examples • Mileage Chart • Each of the district buildings is listed along the top of a sheet and also to the far left. • The physical address is recorded next to each (below the column heading and to the right of the row heading) • The macro goes through each combination of buildings and makes a call to a Google Maps API which returns the distance between the two physical locations. It is surprising how you can google “Excel macro” followed by something like “google maps distance” to find a solution that you might not think Excel can solve. • Demonstrate: • Show where the physical address is stored • Show the video of the macro running.
Other examples • Facilities Projects • The District annually updates a list of facilities needs • A macro creates individual spreadsheets for each school/department with projects unique to them • The principal or department manager can add new items to the list and indicate the top 5 priority items. • When the individual spreadsheets are returned, they are place in a separate folder. Another macro updates the master spreadsheet. • Demonstrate: • Run some extracts and look at the files
Other examples • File Directory • The macro will search a specified drive for user-defined types of files (e. g. Word documents, Excel, pictures, videos, etc. ) • Lists all folders at or below the specified drive location • Lists all files with file attributes • The macro can use this to identify identical files in multiple locations on the network so they can be consolidated (same name, date modified and size) • The macro can copy a file to another location. • The macro can delete a file or group of files. • The macro can create or delete folders. • Demonstrate: • Run macro to identify folders and files
Other examples • Personal Leave Calendar • Creates a calendar of all days for any given year. • As leave is taken, I place the cursor over the absence date and invoke the macro. It asks what type of leave and then colors it appropriately. I also need to record the number of hours. • When done, I can select the “Add. Time. Used” button which will invoke a macro to total the absences and restate my balances. • In cell A 68, I can change the view from Days to Hours • Demonstrate: • • Create a new calendar Record absence Total all absences to update the report Change from Days to Hours
Other examples • Paycheck Calculator • Enter your payroll information and let the macro compute your net pay. • Demonstrate • Tax rate tables and other factors • Change some of the factors • Run macro to compute net pay