How our training can help you.
Our 2-day workshop shows you how to combine
Excel's "built-in" data abilities with VBA to build powerful
data processing tools. Using this combination to crunch data saves
build-time and project costs not to mention having a tool that's
actually ready
before the project ends. Plus, these tools can be upgraded quickly, as
in hours, not weeks.
Since VBA can tie into multiple data sources and
programs, you can design a data app that rips through 250 workbooks, 10 text
files, queries a database, creates charts, and emails out the report while you get a cup
of coffee.
Our class will show you how to do all these things
and much more at the click of a button.
Key Excel VBA data topics covered.
- Review of the VBA Editor, Excel VBA
language, and the debugger
- Using functions and VBA to
rapidly calculate dataset data and build / fill formulas
- Using Excel VBA to control data / report
workbooks and organize them in folders
- Building loops and logic to find,
format, and shape (i.e., insert, delete...columns) datasets on
multiple worksheets
- Using Excel's data tools, PivotTables,
and
VBA loops to rapidly process large datasets
- Building adaptive loops and logic to
process financial, inventory, stacked
/ broken, grouped... datasets
- Performing multi-table lookups and
calculations in your datasets using Excel VBA
- How to design effective UIs on the
worksheet to control your data tools and email your reports
- How to use VBA to build, fill,
calculate, and format various report tables and forms
- Using VBA to build / format report grade charts
and layout multiple charts on a worksheet
- How to open,
process, write, and close text files
- Using VBA, Power Query, and ADO to
integrate database, workbook, and text file data
- How to scan and process data stored in folders, open workbooks, and
on multiple worksheets
Click to enlarge, scroll < > if hidden.
Detailed training syllabus.
Available:
Public >,
Virtual >, Onsite >
How we run the class: We focus our training on what our customers need. When training begins, we analyze those needs and shift our training outline appropriately. We will stress topics or add
topics that our customers want. No two training sessions are ever the same with EMAGENIT.
Day-1
Excel VBA Language, VBA Editor,
Object, and Debugging Review (Discussed Where Needed)
- Review of using the VBA Editor
windows and debugging tools
- VBA language review including
variables, data types, constants, arrays, operators,
expressions, loops, logic decisions, functions, and calling
conventions
- Designing logic for your data
processing tasks
- Design strategies for creating and using object expressions to track objects
in your code
- Using the Set statement,
CreateObject, and GetObject to track Excel objects as well as
other program objects in your code
- How to create error handlers and
the logic to handle run-time errors in your macro code
Using VBA and Functions to
Rapidly Calculate Data and Build / Fill Formulas
- Dynamically tracking worksheet data
ranges in your macro code using MATCH, COUNTA, Range, Cells, Columns, Rows, CurrentRegion...
- Performing math and statistical
operations in your code using functions like SUM, MAX, MIN, SUMIFS,
AVERAGE, COUNTA...
- Performing date time operations in your code using
functions like Month, Year, DateDiff, DatePart, Format,
NETWORKDAYS, EOMONTH...
- Using macros to build and auto fill
formulas down or across worksheet datasets
- Using Excel VBA to build formulas
or place calculated values below or beside datasets
- Automating the Analysis Toolpak to
analyze worksheet datasets
Controlling Workbooks and Organizing Them in Folders
with Excel VBA
- The benefits of making and
managing your own folder system to store program workbooks, text
files, documents...
- Controlling workbooks in your code
using Open, Close, Save, SaveAs, and Add
- Using the Set statement in Excel
VBA to track multiple workbooks at once in your code
- Controlling the Windows folder /
file system in your code using FileSystemObject, File, Folder...
- How to test for folder / file
existence in your macro code using FolderExists, FileExists...
- How to use concatenation in your
code to build folder paths and filenames for your report / data
workbooks
Using Excel VBA to Dynamically Shape, Format,
and Find Resizing Worksheet Datasets
- How to dynamically track range
data size, position, headers, and data
subsets using
MATCH, Range, Cells, Find, CurrentRegion, Address...
- Using Excel VBA to dynamically format
resizing dataset fonts, cell colors, number formats,
alignments, borders...
- Using macros to automatically
insert, delete, and move dataset cells, rows, and columns
- How to build and loop worksheet
tables that guide VBA on what rows / columns to delete, move,
and insert
- Using loops and logic to find and format
multiple datasets on multiple worksheets
Using Excel's Data Tools with
VBA Loops to Rapidly Process Large Datasets
- How to dynamically track ranges, headers,
and position using
MATCH, Range, Cells, CurrentRegion, Find,
Address...
- Using Excel VBA to automate
AutoFilter, Advanced Filter, Sort, PivotTables, and Remove Duplicates
- Using macros to copy and paste
filtered data to a report worksheet
- Generating a report name list from
a worksheet dataset using Remove Duplicates, Sort, and VBA
- Looping through a report name list
and adding report worksheets to a report workbook in a specific
order
- Looping Excel's filters in your
code and
stacking the data on specific report worksheets
- Using loops and logic to build and
stack PivotTables on the worksheet
Building Adaptive Loops and Logic to
Process Complex Worksheet Datasets
- Overview of using loops, logic,
functions, counters, Cells, Range, Offset... to build adaptable data processing programs
- Using cell formats and data
patterns as logic triggers when processing data
- Using loops and logic
to process datasets like financial downloads, inventory, stacked
/ broken tables, multi-column grouped data...
- Detecting and inserting different formulas
in worksheet dataset breaks (i.e., text) using Excel VBA
- Performing worksheet table lookups
in your code using VLOOKUP, SUMIFS, MATCH, Cells, Offset,
Find...
- Using loops and logic to color data points
based on worksheet table lookups
- Using Excel VBA to lookup up data
in multiple worksheet tables and perform calculations
- Using loops and logic to
reorganize worksheet datasets into different configurations
Day-2
Creating, Filling, and Formatting
Report Tables and Forms Using Excel VBA
- Using MATCH,COUNTIF, COUNTA,
Find, CurrentRegion... to dynamically find worksheet table
position, headers, size, end rows...
- Using Excel VBA to create summary
report tables "on the fly" like inventory, human
resources...
- Building loops and logic to fill
in various data patterns like stacked
tables, 2-d tables, multi-column grouped data...
- Automatically formatting various
data patterns on report worksheets including data subsets
- Rules for designing pre-built worksheet
report templates to ease code complexity, mark report table
start cells
- Using loops and logic to fill out and
calculate premade worksheet forms
Designing Effective UIs to
Control Your Excel VBA Data Tools and Email Reports
- Using Data Validation, shapes,
pictures, and VBA to quickly build the interfaces that control your
Excel VBA data tools
- Using the FileDialog object in VBA to construct file and folder pickers
for your data tools
- Using shapes and VBA to create
hi-tech
status displays, path displays, and large check boxes
- Integrating your UI selections in
your code using logic
- Creating a basic Outlook macro in
Excel VBA to automatically mail your reports
Creating Professional Chart Reports with Excel VBA
- Using Excel VBA and templates to create various chart
types like pie, line, combo charts....
- Using macros to add chart elements
like data labels and gridlines
- How to use macros to format charts including coloring data points and hiding / displaying series
- How to use VBA arrays to create a chart series, a must know for emailed chart reports
- Using macros to create vertical
/ horizontal chart limit lines, curve fits, custom label
text, position labels...
- Using Excel VBA to create and align multiple charts on a report worksheet
Using Excel VBA to Open,
Process, and Close Text Files
- Automating Text Wizard
to parse and load text data directly onto a worksheet
- How to open, close, write, and
modify text files using the FileSystemObject and TextStream objects
- Using loops, logic, arrays, and functions to scan and analyze text file data and output the results to an Excel worksheet
- Cleaning and parsing corrupted text files using VBA, functions, the
worksheet, and Text to Columns
- Using loops, logic, and string
concatenation to assemble text data and write it to a text file
Large Scale Multi-Workbook,
Worksheet, and Text File Processing Using VBA
- Scanning open workbooks and
worksheets for specific datasets using For...Each Next loops, logic, and functions
- Scanning
folders for specific data workbooks and text files
using For...Each Next loops,
logic, and functions
- Designing scalable procedures that
adapt to data in n...workbooks, worksheets, and text
files
Using VBA, Power Query, ADO to
Integrate Database, Workbook, and Text File Data
- Review of ADO and how it can be used to query different data sources in Excel VBA tying them together
- Basic SQL language review and how
it is used to query, update, insert, and delete database data
- How to use ADO to query and control databases like SQL Server,
Oracle, and Microsoft Access
- How to use ADO and SQL to query and process data in workbooks and text files
- Using string concatenation to assemble SQL statements and ADO connection strings in your VBA code
- Design methods for using Power Query, Excel tables, and VBA to quickly integrate and
filter data from different sources
- Using VBA and Record Macro to
control Power Query and Excel table settings
Additional Classes:
If you need to contact us about our training.
Phone Number: 1.805.498.7162
Business Hours: 8:30 - 5:00 PM PT
You can email us at info@emagenit.com >
Contact us by form.
_EMAGENIT_2019.png)
