automating excel with macros
TRANSCRIPT
Automating Excel With Macros
Course 4612
4612/CN/A.2/504/A.1
Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
Ā© LEARNING TREE INTERNATIONAL, INC.All rights reserved.
All trademarked product and company names are the property of their respective trademark holders.
No part of this publication may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, electronic, mechanical, photocopying, recording or otherwise, or translated into any language, without the prior written
permission of the publisher.
Copying software used in this course is prohibited without the express permission of Learning Tree International, Inc. Making unauthorized copies of
such software violates federal copyright law, which includes both civil and criminal penalties.
Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
Guide to Learning Tree International Excel Courses
Course 1361: Excel Introduction (3 days)
Course 4610: Excel Workbooks (1 day)
Course 195: Power Excel, Analyzing Data (2 days)
Course 4611: Excel PivotTables (1 day)
Course 1362: Power Pivot for Excel (2 days)
Course 4612: Excel Macros (1 day)
Start
Course 209: VBA Programming Introduction (4 days)
Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
Common Excel Activities
Data
SageExcel
Text FilesExcel
SQL ServerOracle
HR
Sales and Marketing
Accounting
IT
Administration
SAPPeopleSoft
ExcelAccess
Validate/modify data
Analyze data
Create reports
Create dashboard
Create invoices
E-mail and print
Publish to SharePoint
Workbook activities
Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
Course Objectives
In this course, you will develop the skills to
Create macros to automate saving data into multiple worksheets
Execute macros via macro buttons, toolbar buttons, and keystrokes
Examine code statements to understand VBA principles
Interpret and troubleshoot code in the Visual Basic Editor
Modify and enhance a macro
Incorporate code snippets taken from the web into your own solutions
Identify and extract values from cells in VBA
Customize a macro and manage information using variables
Design user-friendly interfaces with form controls
VBA = Visual Basic for Applications
Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
Course Contents
Introduction and Overview
Chapter 1 Identifying Workbook Processes for Automation
Chapter 2 Automating Processes With Macros
Chapter 3 Extending Macro Functionality With Variables and Controls
Chapter 4 Course Summary
Next Steps
After-Course Activities
Practice Practice Practice
Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
The Course Layout
Hands-on exercisesā¢ To help master conceptsā¢ Based on real-world scenariosā¢ Some are instructor-led
Do Now exercisesā¢ Short exercisesā¢ Typically instructor-led
Demosā¢ Solely performed by the instructorā¢ Excel features to be aware of
Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
The Exercise Setup File
Shortcut to exercise file
Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
Access to Command Notation in Exercises
Example: If you see Home | Alignment | Wrap Textā¢ Means go to the Alignment group in the Home tab and select the Wrap Text
command
1
2
3
Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
A Snapshot of Microsoft Excelās History
This course ā¢ Runs on Microsoft Excel 2013
100 percent of the course material applies to all versions
OLAP = online analytical processing XML = extensible markup language
Excel 97: Major upgrade from Excel 95
Excel 2000: Minor upgradePivotChart reports, OLAP source data
Excel XP (2002): Minor upgradeXML support, Smart Tags, improved Audit tools
Excel 2003: Minor upgradeXML files, List features, upgraded calculations
Excel 2007: Major upgradeRibbon introduced; interfacerevamped; faster calculations; charts
Excel 2010: Minor upgradeSlicer; sparklines; charts; Power Pivot; Power Query
Excel 2013: Major upgradexVelocity engine; Relationships; Power View; Power Map
Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
Prerequisite Knowledge
Let your instructor know if you do not know how to do any of the following:
Selecting, moving, and copying cells ā¢ Using Cut/Copy and Paste or AutoFill
Recognizing mouse shapes and their function
Resizing columns
Inserting an Excel sheet
Filtering in PivotTables
Implementing calculations in Excel using functions ā¢ Such as SUM/IF/VLOOKUP
Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
Excel Features Used in the Course Examples
AutoFilterā¢ Automate creating a summary table using different filters
PivotTablesā¢ Automate changing the report filter for multiple PivotTables
Functionsā¢ The INDEX function is used in an exampleā¢ Course 195 can help you with interpreting functions
Chartā¢ Automate updating a chart with different sets of values
Let your instructor know if you are concerned about any of the above
Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
Are You in the Right Course?
Do the course objectives meet your needs?ā¢ No
ā Tell your instructor nowā He or she can help you find the right course
ā¢ Yesā Ensure that the instructor has your questionnaire
Identifying Workbook Processes for Automation
Chapter 1
21-Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
Chapter Objectives
In this chapter, you will learn how toā¢ Describe the purpose of an Excel macroā¢ Recognize processes to automateā¢ Identify automation problems within inherited workbooks
You will also become aware of Excel features that can enhance productivity
31-Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
DemoWhat Is an Excel Macro?
Set of instructions written in Visual Basic for Applications (VBA)
Created byā¢ Recording a sequence of actionsā¢ Manually writing code from scratch
From 2007: must be saved in macro-enabled workbook (.xlsm)
Used toā¢ Combine a series of actions into a single stepā¢ Automate repetitive actionsā¢ Improve functionality of a workbook modelā¢ Reduce possibility of human errorā¢ Store complex sequences instead of
relying on an individual
41-Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
Identifying When a Macro Is Required
Repetitive: Is there a task you are doing daily, weekly, or monthly?ā¢ Importing a dataset daily into a workbook
ā Ensuring that calculations are correctā Ensuring that related charts and PivotTables are updated
Usability: Do others have to use the workbook model that you created?ā¢ User should be able to see
ā Cells and/or areas where input is requiredā Key performance indicators
Complex: Is there a task that requires a number of steps?ā¢ Example: importing data, splitting it into different
categories, removing irrelevant rows of data, formatting numbers, then creating invoices, paymentschedules and remittances
51-Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
Identify Excel Tasks That You Do
1. Repetitive: Is there a task you are doing daily, weekly, or monthly?
2. Usability: Do others have to use the workbook model that you created?
3. Complex: Is there a task that requires a number of steps that you perform infrequently?
I wonder if a macro would be best suited to complete this task?
61-Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
Need to change the filter of several PivotTables to the same itemā¢ Versions previous to 2010 need a macro
Every week, I import a date-stamped CSV file into Excel and then have to tidy up the tableā¢ If filename is always the same
ā Set up data connections and functionsā¢ If filename varies
ā Record a macro that applies to one file, then modify macro
Need to log changes made to a formā¢ Record a macro, then modify to improve its capabilities
Need to extract a sequence of entries from a dataset to create a summaryā¢ Function knowledge must be goodā¢ Many revert to using macros
Can cause problems if dataset grows
Excel Processes to Automate
CSV = comma-separated value
71-Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
Features That Automate Common Workbook Tasks
Excel contains features that automate many common tasksā¢ Functionsā¢ Conditional formattingā¢ PivotTablesā¢ Stylesā¢ Tablesā¢ Microsoft Query/data connections
Use these features when practical instead of macros!
4610
1361
4611
195
1362
81-Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
Macros or Functions?
1. From the Exercise Setup workbook | Do Now and Demos, open the workbook called TruckDemoMacro
2. Examine the Truck Plan Macro worksheet. Click the āCreate Summary For Allā button to play a macro providing a summary in a tablea. What are the macroās limitations?
b. What if the dataset expands?
3. Examine the other worksheet to see a function providing a summarya. What are the limitations of the function?
b. What if the dataset expands?
4. How can the design of the worksheet that uses a macro be improved?
91-Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
Macro Dangers
Maintainability and usability issuesā¢ Poor design principles
ā Calculations are slow/incorrect
Broken macrosā¢ Logic-related
ā Worksheet names changedā Cell references changed
ā¢ Incorrect dataset referencesā¢ Changed cell references to
values
101-Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
The Visual Basic Editor (VBE)
For some users, VBE = Very Bad Environment!
111-Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
Chapter Summary
In this chapter, you have learned how toā¢ Describe the purpose of an Excel macroā¢ Recognize processes to automateā¢ Identify automation problems within inherited workbooks
You have also became aware of Excel features that can enhance productivity
121-Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
1. What is a macro?A. Special tool that automates large tasksB. Special tool that automates a datasetC. A recording of a sequence of actions that can be played back as if they were
one actionD. Written in a language called Visual Basic for Application
2. What extension can a workbook with a macro have?A. .xlsxB. .xlsmC. .xltxD. .xls
3. What should you consider before creating a macro?A. Check the web/forums for Excel tools/functions that might automate tasksB. Anyone can support a macroC. A macro should always be the first choiceD. A macro should be the last choice
Chapter Review
Automating Processes With Macros
Chapter 2
22-Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
Chapter Objectives
In this chapter, you will learn how to
Create macros to repeat a series of user actions
Make macros easily accessible to the end user
Identify the basic principles of a macro procedure
Decipher macro code in the Visual Basic Editor
Recycle code from the web into your own solution
Enhance code to save newly created data with the Offset VBA property
32-Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
Chapter Contents
Creating Macros Hands-On Exercise 2.1
Exploring How a Macro Works
Instructor-Led Exercise 2.2
Recycling Code/Offset Property
Hands-On Exercise 2.3
42-Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
How to Record a Macro
Creating a macro is like using a recording device1. Plan steps of macro
ā All actions in Excel are recorded (see next slide)
2. Prepare to record new macroā Select View | Macros | Macros
| Record Macroā Fill in the options
3. Click OK to begin recording process4. Perform actions identified in Step 15. Stop recording6. Play back recording
Stop Recordingappears on Status Bar
In 2003: ā¢ Select the menu options Tools | Macro | Record New Macroā¢ Stop recording by clicking the stop button on the Stop Recording Toolbar
52-Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
What Is Recorded
Excel records all actions within the application, such asā¢ Typing textā¢ Any special keys you use; e.g.:
ā Arrow keysā Function keysā Key combinations; e.g. <Ctrl><C>
ā¢ Clicking and scrolling with mouseā¢ Selecting a range or an objectā¢ Selecting characters in a cell
What is not recorded:ā¢ Any keystrokes or mouse actions
outside of Excel
209
Validate/modify data
Analyze data
Create reports
Create dashboard
Create invoices
E-mail and print
Publish to SharePoint
Workbook activities
62-Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
Macro nameā¢ Must start with a letterā¢ Can contain letters, digits, and underscores
ā No spaces ā¢ Up to 255 characters
ā Use initial capitalization for readabilityā¢ Cannot be a reserved word
ā Blue words in VBA
Which of these names is valid?
ā¢ Create Monthly Report ___________________
ā¢ 2ndQuarterlyTotal ___________________
ā¢ UpdatePivotTableFilter ___________________
Filling in the Record Macro Dialog Box
72-Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
Filling in the Record Macro Dialog Box
Shortcut keyā¢ Assign a shortcut key to run macro
Store macro inā¢ This Workbook/New Workbook
ā Macros available when workbook is openā¢ Personal Macro Workbook
ā Macros available when Excel launched
Descriptionā¢ Fill in to indicate what macro does
82-Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
Running the Recorded Macro
Select View | Macros | Macros | View Macrosā¢ Select the macro to execute, then click the Run button
Alternatively, run the macro from aā¢ Shortcut keyā¢ Button from Forms toolbarā¢ Quick Access Toolbar buttonā¢ Ribbon command (2010/2013)
In 2003: ā¢ Select the menu options Tools | Macro | Macros
92-Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
1. In a new workbook, start the macro recorder
To create a new workbook, press <Ctrl><N>
Macro recorder: View | Macros | Macros | Record Macro
2. In the Record Macro dialog box:a. Macro name = MyName; Shortcut key = <Ctrl><Shift><M>
3. Record entering your name in the active cell
4. Stop recording macro: Click Stop button in Status Bar
5. Play back macro using shortcut key:a. Select any cell on any worksheet and test macrob. In a new workbook, test that macro worksc. Save workbook containing macro and close. Test if macro still works!Create a macro that enters your name in any cell and is always available
Store the macro in the Personal Macro Workbook. You will be prompted to save this workbook when you close Excel
Macro to Enter Your Name in Any Cell Do Now
102-Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
Displaying the Developer Tab (Excel 2007 and later)
Contains tools specifically for developing workbooks
In 2010 and later:1. File | Options2. From Customize Ribbon
tab, select Developer
In 2007: 1. Office button | Excel Options2. From Popular tab, select āShow Developer tab in the Ribbonā
112-Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
Assigning a Macro to a Button From Form Controls
1
2
3
4
In 2003: ā¢ Create a Form button by using the Forms toolbar
Developer | Controls | Insert
122-Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
Assigning a Macro to a Form Button
1. From Ribbon, select Developer | Controls | Insert
2. From Form Controls, click the Button control and draw on worksheet
3. Release mouse button to display Assign Macro dialog box
4. Select macro to run, and click OK
5. Enter a descriptive caption on buttonā¢ Just start typing!
6. Deselect button by clicking a worksheet cell
7. Click button to run macroā¢ Mouse shape is a hand
In 2003: ā¢ In place of Step 1 above, display the Forms toolbar: View | Toolbars | Forms
132-Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
Modifying a Form Button
Right-click buttonā¢ Selects it and displays a shortcut menuā¢ Change button captionā¢ Assign a different macro to the button
Press <Ctrl> while clicking to select form button without shortcut menu
142-Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
Chapter Contents
Creating Macros
Hands-On Exercise 2.1 Exploring How a Macro Works
Instructor-Led Exercise 2.2
Recycling Code/Offset Property
Hands-On Exercise 2.3
152-Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
This course is AdaptaLearn Enabledā¢ Web technology has allowed us to produce ādynamic documentsā
Steps and hints can be āfoldedā in and out to give increasing levels of detail, providing you with a tailored experienceā¢ Suitable to your personal background and work styleā¢ Answers to questions also can be folded and unfolded
AdaptaLearn also provides:ā¢ Simplified ācopy and pasteā of solution code
ā Decreases need to search through files on the course load
ā¢ Internet links to other helpful informationā¢ Video playback for especially difficult actionsā¢ Ability to use outside of class for continued reference and practice
AdaptaLearn is easy to useā¢ For your convenience, a paper copy of the Exercise Manual is also provided
ā All detail levels are shown (hints and answers are unfolded)
AdaptaLearnā¢ Enabled
162-Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
Do Now
Letās try it now1. Launch AdaptaLearn by double-clicking its icon on the desktop2. Move the AdaptaLearn window to the side of your screen, or minimize it
so it is a thin strip across the top or bottomā Leave room for a work area for your development tools
3. Select the first exercise from the exercise menu4. Use <Ctrl><mouse_wheel> to change the font size as desired5. If your screen is small or you want to maximize the AdaptaLearn window,
use <Alt><Tab> to toggle back and forth6. A folded area is introduced in blue text
ā Click the text to see how folds work7. A copy-and-paste area is in a blue box
ā Hover over the box (but donāt click on it) and press <Ctrl><C>
Using AdaptaLearnā¢
172-Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
The Exercises
Access through the Exercise Setup program when described
Written as follows:ā¢ Objectivesā¢ Business case to solveā¢ Major Step
1. Main action2. Main action described
a. Sub-step to break down the action you are learning to perform
Checkpoint Hint WarningQuestion Information
Congratulationsāwhat features you have learned to use
Bonusāextra exercises (not all students will have time to complete)
ā¢ Stopāend of the main exercise or bonusSTOP
182-Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
Hands-On Exercise 2.1
In your Exercise Manual, please turn toHands-On Exercise 2.1: Easing Repetitive Tasks
Main exercise Dan would like to use an AutoFilter process to analyze his truck configuration. However, he is taking a long time to do the task. You will help him create a macro to provide a summary for truck A.
Bonus ā¢ Record a macro that will create a summary table for all the different trucks
ā¢ Assign a macro to a toolbar icon
192-Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
1. What extension can a workbook with a macro have?A. .xlsxB. .xlsmC. .xltxD. .xls
2. How can a macro be invoked?A. Assign a macro to a drawing shapeB. Assign a macro to a form controlC. Assign a macro to the Quick Access ToolbarD. Assign a macro to the Ribbon
3. Where are the form controls?A. In the Drawing shapes in the Insert tabB. In the Developerās tabāactivated in File | Options | Customize RibbonC. In the View tab, Macros commandD. In the Home tab, Form controls
Review Quiz
202-Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
Chapter Contents
Creating Macros
Hands-On Exercise 2.1
Exploring How a Macro Works Instructor-Led Exercise 2.2
Recycling Code/Offset Property
Hands-On Exercise 2.3
212-Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
What are the problems with recording long macros?ā¢ Especially with repetitive steps?
Problem With Recording Summary For All Trucks
222-Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
Taking Your First Steps in VBA
Recorded macrosā¢ Good starting point to learn VBAā¢ Limited to only user actions
Modify existing VBA to extend a macroās capabilitiesā¢ Interacting with userā¢ Automating with other applicationsā¢ Performing similar actions multiple times
Result of recording a macro = VBA
232-Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
Exploring the Visual Basic Editor (VBE)
Development environment hosted within Excel
Appears as a separate program when launched
242-Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
Reference
Projectā¢ A collection of modulesā¢ Is the workbook!
Modulesā¢ Contains procedures, including macros
Macro or procedureā¢ Macro: is a type of procedure
ā Often recordedā¢ Procedure: describes a general programming component
ā Used to enhance macros
Statementā¢ Normally a single line of codeā¢ Space underscore ( _ ) continues a single logical line onto a second physical
lineā¢ Syntactically complete unit expressing an action, declaration, or definition
Jargon Buster: Working With Code
252-Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
Debugging: Locating the Macro in the VBE
262-Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
Remember: The end result of recording a macro is VBA code
How do you display the Macro dialog box?
How do you locate the macro associated with a button?
Clicking Step Into will display VBE and go into break modeā¢ Yellow highlighting indicates line to be executed
Clicking Edit will display VBEā¢ Shows the VBA code behind macro
Debugging: Locating the Macro VBA in the VBE
Press <F8> to execute highlighted line of code and move to next line of codePress <F5> to continue a macroās execution until the end
272-Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
Debugging: Examining How a Macro Works
To troubleshoot, display both User Interface (UI) and VBE
282-Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
Chapter Contents
Creating Macros
Hands-On Exercise 2.1
Exploring How a Macro Works
Instructor-Led Exercise 2.2 Recycling Code/Offset Property
Hands-On Exercise 2.3
292-Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
Instructor-Led Hands-On Exercise 2.2
In your Exercise Manual, please turn toInstructor-Led Hands-On Exercise 2.2: Examining Macros
Main exercise Instead of having one macro per truck summary, we will look at summarizing all the truck configurations with one macro.
Bonus The truck workbook was created in Excel 2003 and is now being used in a later version of Excel. When the macro is run, an error occurs. You will fix this.
302-Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
What two lines show the beginning and the end of a macro?
Name some reserved words. How do you know they are reserved?
Which lines of code are not executed? (These are called comments)
How do you refer to a specific cell(s) in a worksheet?
Understanding the Workings of a Macro
312-Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
Chapter Contents
Creating Macros
Hands-On Exercise 2.1
Exploring How a Macro Works
Instructor-Led Exercise 2.2
Recycling Code/Offset Property Hands-On Exercise 2.3
322-Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
QuizWorking With Code Snippets
You find the following code snippet on the web to automate importing a text file into an Excel worksheet in the current workbook.
Where do you put this code to run it?
What two lines do you need to add to the code to test it and to see whether it works?
Const csPath As String = "C:\Test\Example.csv"Dim ws As Excel.WorksheetSet ws = Excel.ActiveSheetWith ws.QueryTables.Add("TEXT;" & csPath, ws.Cells(1, 1)).FieldNames = True.AdjustColumnWidth = True.TextFileParseType = xlDelimited.TextFileTextQualifier = xlTextQualifierDoubleQuote.TextFileConsecutiveDelimiter = False.TextFileCommaDelimiter = Trueā***This array will need as many entries as there will be columns:.TextFileColumnDataTypes = Array(xlTextFormat, xlTextFormat).RefreshEnd With
332-Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
Recycling Macros
Separate units of functionality into separate proceduresā¢ Aids manageability and reusability
Example:
ā¢ Open file from a folder into ExcelImport CSV File
ā¢ Remove spurious characters from text and convert to tableTidy Up
ā¢ Refresh associated PivotTablesRefresh PivotTables
ā¢ Print required reportPrint Report
342-Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
DemoCalling Procedures
Each procedure performs an actionā¢ Can execute procedure
individually via interfaceā¢ Can execute procedure
in another procedure
Useā¢ Procedure name
ā ImportCSV
ā¢ Call <ProcedureName>ā Call ImportCSV()
352-Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
Expression.Offset(RowOffset, ColumnOffset)
ā¢ RowOffset = Number of rows to offset byā¢ ColumnOffset = Number of columns to offset by
What cell is being referred to in the following?
ā¢ Range("A1").Offset(2,4)
ā¢ Range("A1").Offset(0,4)
ā¢ Range("D6").Offset(-3,0)
The Offset VBA Property
Offset allows you to omit the row or column offset when it is zero. The last two code snippets can be rewritten as Range("A1").Offset(,4) and Range("D6").Offset(-3)
Note: A comma is required when the RowOffset is omitted.
362-Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
Chapter Contents
Creating Macros
Hands-On Exercise 2.1
Exploring How a Macro Works
Instructor-Led Exercise 3.2
Recycling Code/Offset Property
Hands-On Exercise 2.3
372-Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
Hands-On Exercise 2.3
In your Exercise Manual, please turn toHands-On Exercise 2.3: Tracking New Records
Main exercise The project managers would like you to help them log projects and their respective resources into different tables when the projects are saved.
Bonus You will include code that was taken from the web and use it in your own solution to save data into CSV format.
382-Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
Chapter Summary
In this chapter, you have learned how to
Create macros to repeat a series of user actions
Make macros easily accessible to the end user
Identify the basic principles of a macro procedure
Decipher macro code in the Visual Basic Editor
Recycle code from the web into your own solution
Enhance code to save newly created data with the Offset VBA property
392-Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
QuizChapter Review
1. When the macro recorder is switched on, what tasks are recorded?
2. Where do you edit the code for a macro?
3. What function key can be used to a. Single-step through code?b. Execute code to the end?
4. You find code on the web. If you want to run it in your own solution, what do you need to do?
Extending Macro Functionality With Variables and Controls
Chapter 3
23-Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
Chapter Objectives
In this chapter, you will learn how to
Create variables in VBA
Identify common variable errors
Incorporate variables into existing macros
Capture user inputs with form controls
Connect controls to the worksheet
Create user interactive forms
33-Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
Chapter Contents
Manipulating Information With Variables
Hands-On Exercise 3.1
Creating Interactive Interfaces
Hands-On Exercise 3.2
43-Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
Reference
Named location in memoryā¢ Temporary storage area for informationā¢ Contents changed in code
Used to make macros more dynamicā¢ In statements and expressions
Naming rules of variables are identical to macro naming rules
Similar to named rangesā¢ Can contain variable information
ā Changed by changing contents of named cell(s)
ā¢ Can be used in formulas
Jargon Buster: Variables
Variable
Variable
Variable
Variable
Variable
53-Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
How to Create a Variable
Create by dimensioning or declaring the variableā¢ Dim <VariableName> As <DataType>
Declaring variables as a specific data type providesā¢ Type of information/range of values to useā¢ Required amount of space in memory
63-Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
ReferenceData Types: Simplified Overview
Name Prefix* Description Range RAM Initial value
Byte byt Numeric, Integer 0 to 255 1 0
Boolean bln Binary True or False 2 0
Integer int Numeric, Integer ā32,768 to 32,767 2 0
Long lng Numeric, Integer ā2,147,483,648 to 2,147,483,647 4 0
LongLong** llg Numeric, Integer -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 8 0
Single sng Numeric, any +/ā 1.4 * 10ā45 to +/ā 3.4 * 1038 4 0
Double dbl Numeric, any +/ā 4.9 * 10-324 to +/ā 1.8 * 10308 8 0
Currency cur Numeric, any ā922,337,203,685,477.5808 to 922,337,203,685,477.5807 8 0
Date dtm Date and Time Jan 1, 100 to December 31, 9999 8 0
Object obj Any Object Any object reference
String str Text 0 to 2 billion (fixed length: 64K) 1/char āā
Variant vnt Any of the above Any Min 16 Empty
*Follows the Leszynski naming convention**Only available to 64-bit Office 2010/2013
73-Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
Examples of Working With Variables
Assigning values to variables/writing to variables:
Dim intPalletTotal As IntegerintPalletTotal = Range(āF432ā).Value
Dim strPivotFilter As StringstrPivotFilter = Range(āA2ā).Value
Extracting/reading contents of variables:
Range(āB2ā).Value = intPalletTotalMsgBox strPivotFilter
83-Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
1. In a new workbook, create a new macro called VariableTest
To create a new workbook press <Ctrl><N>
Select View | Macros | Macros. In the dialog box, enter the macro name and click Create
2. In the macro, create the following code:a. Create a byte variable called MyHappyVariableb. Assign the variable the contents of cell A1c. Put the contents of the variable into cell C1
3. Run the code with the tabulated information and enter what happens
Instructor-Led: Data Types and Variables
Value in cell A1 What happened when you ran the macro?
255
256
aaa
Do Now
93-Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
4. Remove the data type from the code, then rerun the code with the different tabulated information and record what happens
If no data type is specified, the variable is assigned a default data type. What do you think is the default data type?
Instructor-Led: Data Types and Variables
Value in cell A1 What happened when you ran the macro?
255
256
aaa
Do Now
103-Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
Default data type = Variantā¢ Holds any type of value or objectā¢ Slow compared to other variable
What is the declaration statement to create a variant variable?
______________________________________________________________
You can declare several variables on the same lineā¢ Must give each variable a data typeā¢ Dim intPalletTotal, intTruckWeight As Integer
ā Makes intPalletTotal a variant
Specify smallest data type, but leave room for growthā¢ Improves performance of large program using many variables
Exploring the Variant Data Type
113-Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
QuizWhich Data Type?
Based on existing data, how would you declare a variable for column B?
Based on existing data, how would you declare a variable for column C?
123-Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
QuizMaking a Macro Dynamic
You have to automate changing the Report Filter for two PivotTables to the same value as in cell A2
How do you find out what code is required to change the country used in each PivotTable filter?
133-Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
QuizMaking a Macro Dynamic
What code modifications need to be made to extract the value from cell A2 in order to use the value in the filter for each PivotTable?
143-Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
Chapter Contents
Manipulating Information With Variables
Hands-On Exercise 3.1 Creating Interactive Interfaces
Hands-On Exercise 3.2
153-Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
Hands-On Exercise 3.1
In your Exercise Manual, please refer to Hands-On Exercise 3.1: Manipulating Code With Variables
Main exercise You will record a macro that changes the page filter for multiple PivotTables, then edit the macro to incorporate variables.
Bonus You will use variables to modify versions of the macros you created previously.
163-Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
Variables can be implicitly createdā¢ Causes errors in the process if inconsistent
What do you think will appear in the report filter for each PivotTable?
Potential Problems With Variables In Code
' 3a. Create a string variableDim strPivotFilter As String' 3b. Assign the contents of A3 to the variablestrPivotFilters = Range("A3")' 3c. Change the code to use the string variableActiveSheet.PivotTables("PivotTable1").PivotFields("COUNTRY").CurrentPage = _
strPivotFiltersActiveSheet.PivotTables("PivotTable2").PivotFields("COUNTRY").CurrentPage = _
strPivotFilter
173-Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
In existing modules, add Option Explicit at the top of module
Before a module is created, switch on Require Variable Declarationā¢ Tools | Options | Require Variable Declaration
Preventing Undeclared Variables
183-Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
Preventing Undeclared Variables
193-Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
Chapter Contents
Manipulating Information With Variables
Hands-On Exercise 3.1
Creating Interactive Interfaces
Hands-On Exercise 3.2
203-Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
Extending Macro Solutions With User Interfaces
Control user interaction with the workbookā¢ Display limited choices to userā¢ Example: Control a dashboard on a worksheet
Consolidate user inputsā¢ Directs user to inputs that need to be modifiedā¢ Can use worksheets, dialog sheets, and UserForms (VBE)
Form Controls on a dialog sheetForm Controls on a worksheet
213-Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
Adding Controls for User InputAdvantages Disadvantages
Form Controls on Worksheets
ā¢ Easy to use ā¢ Place controls directly on the
worksheet (input worksheet)ā¢ Controls can have a macro
assigned to them
ā¢ Can make worksheet cluttered
ā¢ User has to switch back to input worksheet to make changes
Form Controls on Dialog Sheets
ā¢ Easy to useā¢ Place controls directly on
dialog sheetā¢ Controls can have a macro
assigned to themā¢ Can be displayed in any
worksheetā¢ Can be easily hidden
ā¢ Knowledgeable user can unhide dialog sheet and make unwanted changes
ActiveX Controls on UserForms (VBE)
ā¢ Can be displayed in any worksheet
ā¢ User must know how to use VBE to make changes
ā¢ Must be created in the VBEā¢ Requires more knowledgeā¢ Requires more code
209
223-Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
Form Controls vs. ActiveX Controls
Form controlsā¢ Simpler set of propertiesā¢ Used only on worksheets and dialog sheets
ActiveX controlsā¢ Complex set of propertiesā¢ Used only on UserForms (VBE) and worksheets
ā Not in dialog sheetsā¢ Course 209, VBA Programming Introduction
Ā®
209
233-Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
Working With Form Controls
Add a controlā¢ Click control in toolbox and draw it on dialog sheet/worksheet
Move a control by selecting and dragging itā¢ May have to right-click to select it first
Size a control by dragging one of its sizing handlesā¢ Cursor changes to a double-sided arrow
243-Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
Option Buttons and Checkboxes
Option buttonsā¢ Only a single selection is allowedā¢ Use group box to create multiple option button areasā¢ Values start from 0 and correspond to option button
selected
Checkboxes allow the user to specify conditionsā¢ Any number of boxes can be selected simultaneouslyā¢ Values can be TRUE, FALSE, #N/A
253-Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
List Controls List box
ā¢ Select one or more itemsā¢ Populate using Input Range fieldā¢ Link to a cell using Cell Link field
Combo boxā¢ Select one item onlyā¢ Has similar properties to list boxā¢ Displayed as a drop-down list box
263-Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
Modifying Control Properties
1. Right-click control and select Format Control
2. Modify appropriate fields
273-Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
Control Properties
Cell Link property is a cell that is linked to the controlā¢ Changing control state changes linked cell value
Changing the linked cell value changes the control stateā¢ Cells linked to checkboxes can be set to TRUE, FALSE, or #N/Aā¢ Cells linked to option buttons have a corresponding number value
ā Starting with zeroā¢ Cells linked to list boxes have a number corresponding to selected value
283-Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
Adding a Dialog Sheet
1. Right-click an existing sheet tab to display shortcut menu
2. Select Insert from the shortcut menu to display the Insert dialog box
3. Select the MS Excel 5.0 Dialog icon and click OK
293-Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
Working With the Dialog Sheet
Dialog sheet supports Form Controlsā¢ Used to add controls to the dialog sheet
Test dialog sheet by selecting Run Dialog iconā¢ Can record pressing Run Dialog in a macro
303-Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
Chapter Contents
Manipulating Information With Variables
Hands-On Exercise 3.1
Creating Interactive Interfaces
Hands-On Exercise 3.2
313-Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
Hands-On Exercise 3.2
In your Exercise Manual, please refer to Hands-On Exercise 3.2: Creating Interactive User Interfaces
Main exercise You will examine how controls can be used to simplify the way a worksheet works.
Bonus You will design a dialog sheet to capture user specifications.
323-Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
Next Step
Course 209ā¢ VBA Programming Introductionā¢ Introduces Visual Basic for Applications (VBA) programming
ā No programming knowledge required
Course 971ā¢ Building Applications with Microsoft AccessĀ®: Hands-Onā¢ Automates Access database solutions with macros and VBA
ā Access knowledge to the level of 970 is assumed
209
333-Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
Chapter Summary
In this chapter, you have learned how to
Create variables in VBA
Identify common variable errors
Incorporate variables into existing macros
Capture user inputs with form controls
Connect controls to the worksheet
Create user interactive forms
343-Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
QuizChapter Review What is the default data type for a variable?
What are two typical errors you will see when variables are used in code?
How do you know what data type to use?
Where do you find controls to add to a worksheet or dialog sheet?
How do you use controls on a:ā¢ Worksheet?ā¢ Dialog sheet?
Course Summary
Chapter 4
24-Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
Course Summary
In this course, you have developed the skills to
Create macros to automate saving data into multiple worksheets
Execute macros via macro buttons, toolbar buttons, and keystrokes
Examine code statements to understand VBA principles
Interpret and troubleshoot code in the Visual Basic Editor
Modify and enhance a macro
Incorporate code snippets taken from the web into your own solutions
Identify and extract values from cells in VBA
Customize a macro and manage information using variables
Design user-friendly interfaces with form controls
34-Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
Guide to Learning Tree International Excel Courses
Course 1361: Excel Introduction (3 days)
Course 4610: Excel Workbooks (1 day)
Course 195: Power Excel, Analyzing Data (2 days)
Course 4611: Excel PivotTables (1 day)
Course 1362: Power Pivot for Excel (2 days)
Course 4612: Excel Macros (1 day)
Start
Course 209: VBA Programming Introduction (4 days)
44-Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent.
Related Learning Tree Courses
1-DAY BOOT CAMP Courses:ā¢ Creating and Designing Excel WorkbooksāCourse 4610ā¢ Excel PivotTables: Building DashboardsāCourse 4611
Multi-Day Courses:ā¢ Microsoft Excel IntroductionāCourse 1361ā¢ Power Excel: Analyzing Data to Make Business DecisionsāCourse 195ā¢ Power Pivot for Excel: Mining Data for Business IntelligenceāCourse 1362
For more information on these and other related courses, please visit: LearningTree.com/MicrosoftOffice
Bonus Session
Appendix A
2Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent. AppA-
Create a macro that refers to a cell within a workbook to open up a specific workbook
Implement a drop-down list box that displays a list of workbooks that can be opened, and use the code from the previous macro to open the workbook that is selected from the drop-down
You can use the INDEX() function to identify the selection. See the screenshot below for an example
Bonus Exercise A.1
After-Course ActivityāSandbox
Appendix B
2Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent. AppB-
You will create several macros that will create a report from a CSV file and then save the file with the same name, but as an .xlsx into a āsaveā folder. Many of the macros will need to be modified to make them more dynamic.
1. Create a macro that refers to a couple of cells that indicates a folder and a CSV file that is to be imported into the workbook (see screenshot below)
2. Create a second macro that applies a table format to the data as specified by a cell value in the worksheet (see screenshot below)
3. Create a third macro that creates a PivotTable report of the data showing how much revenue each product has brought into the company. Display the PivotTable report as a pie chart
After-Course Sandbox: Streamlining a Process
3Ā© Learning Tree International, Inc. All rights reserved. Not to be reproduced without prior written consent. AppB-
4. Create a fourth macro that deletes the worksheet that contains the input
5. Create a final macro that saves a workbook as a .xlsx fileYou do not want to save the current workbook as a .xlsx; otherwise, it will remove your macro code!
6. Find code on the web to extract the filename of the CSV, then use the filename as the workbook name
7. Combine the macros so that they run from one procedure
8. Create a drop-down list box of table styles you would like the āuserā to select. You can use the INDEX() function to identify the selected item. (An example has been provided in the screenshot)
Hover your mouse over the table styles to find out a list of styles available
After-Course Sandbox: Streamlining a Process