How our training can help you.
Our 3-day workshop shows you how to harness the
full power of Excel and VBA to make advanced UI driven apps. Both Excel and
VBA have amazing user interface capabilities that can help you rapidly design
interactive apps that will solve your toughest problems.
Coupling these interface capabilities with VBA's
ability to control forms, models, databases,
workbooks, tables, charts... will allow you to develop
inexpensive apps that rival or surpass industry software.
Imagine being able to develop a tool that allows a
user to select data folders, what data to process, and then at the click
of a button scan hundreds of workbooks and text files summarizing the
data, charting it, and then emailing the report. All of this is possible
and much more.
Join us and let us show
you how to unleash Excel VBA's full potential and design the right Excel tool that gets the job done.
Key advanced Excel VBA topics covered.
- Review of the VBA Editor, Excel
VBA language, debugging, and error traps
- Controlling the Windows folder system to
store report and data workbooks
- Advanced data processing methods using VBA,
Excel's data tools, and PivotTables
- Advanced worksheet data processing and
report generation using Excel VBA loops and logic
- Processing data in multiple folders, multiple
workbooks, and on multiple worksheets
- How to use VBA to build / format charts
and layout multiple charts on a worksheet (chart report)
- Using Excel VBA to create, write, read,
parse, and clean text files
- Creating UIs for your Excel tools using
the worksheet, ActiveX controls, Ribbon, and file / folder Pickers
- Creating UIs for your Excel tools using userforms, ActiveX controls, events, and VBA
- Creating advanced dashboards on the
worksheet using charts, shapes, events, ActiveX controls, and VBA
- Creating and managing advanced model and
analysis problems with Excel VBA
- Using SQL, ADO, and UIs to query and
integrate databases, workbooks, and text files
- Using VBA to control Outlook emails and
Word / PowerPoint for report generation purposes
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 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
VBA Editor, Excel VBA Language,
and Object Review (Discussed Where Needed)
- Review of VBA programming elements
including variables, data types, constants, arrays, operators,
expressions, loops, logic decisions, functions, and calling
conventions
- Advanced use of the VBA Editor windows
and debugger
- Handling run-time errors in your
VBA code using error handlers and logic
- Using the CreateObject and
GetObject functions along with the Set statement to create / track
multiple objects in your code
- How to create objects using Class modules, Subs, Functions, Properties, and public variables
Advanced Workbook and Folder
Control Using Excel VBA
- Why store data in multiple workbooks and use Excel VBA to integrate their data?
- Using VBA to access and
control the Windows folder / file system using FileSystemObject,
File, Folder...
- Using Open, Close, Save, Add, and
SaveAs to control your workbooks
- Testing for folder and file
existence in your macro code using FolderExists, FileExists...
- Using string concatenation in your
code to
build folder path and file names for your report / data workbooks
Advanced Report
Methods Using VBA, Excel's Data Tools, and PivotTables
- Using VBA, loops, and logic to
control PivotTables and PivotCharts
- Using VBA, loops, and logic to control
AutoFilter, Sort, Remove Duplicates, and Advanced Filter
- Using
string concatenation to build filter logic on the fly inside
macro loops
- Using Remove Duplicates in your
macro code to filter report worksheet names from your data
- Using loops and logic to build
report workbooks / worksheets to store your filtered data
- Using macros to copy, paste, and
stack filtered data on report worksheets
Advanced Worksheet Data
Processing Using Excel VBA Loops and Logic
- Using Range, Cells, Offset,
CurrentRegion, Find, Match... to dynamically track worksheet tables, headers,
position, and size
- Looping and logic strategies for
processing bank, quick books, inventory, human resources...
downloads
- Automatically inserting worksheet
rows and columns in your data and filling them with
formulas or values
- Performing single and multi-table
lookups in your worksheet data and outputting the results to
specific data rows and columns
- Using loops and logic to delete and move
worksheet data rows, cells, and columns
- Creating and tracking multiple
report workbooks / worksheets in your code and outputting data to
them in various patterns
VBA Methods for
Creating Text Files and Reading Text File Downloads
- Using VBA, loops, and logic to
control Text Wizard and parse / stack / process text file data
on a worksheet
- Using the FileSystemObject and
TextStream object to open, read, write, modify, and close text files
- How to use loops and string concatenation
to assemble and write text data to text files
- How to use loops, logic, and
arrays to access and load specific text file data onto a worksheet
- Using macros to clean and parse text file data using various functions,
the worksheet, and Text to Columns
Searching for Data in Multiple Workbooks, Worksheets, and Folders Using VBA
- Searching open workbooks for a specific data set using For...Each Next loops, logic, and functions
- Searching multiple worksheets for a specific data set
using For...Each Next loops,
logic, and functions
- Searching
multiple folders for specific data workbooks
using For...Each Next loops,
logic, and functions
- Designing scalable procedures that adapt to
n... number of workbooks, worksheets, text
files...
Advanced Chart
Report Generation Using Excel VBA
- Review of creating various
Excel chart types like pie, line, column, xy scatter, combo...
in Excel VBA
- Formatting charts, creating /
deleting chart elements, and loading chart templates with VBA
- Using macros to add, edit, and delete an existing chart's data series
- Using VBA arrays to create a chart
series, great for emailing chart reports
- Using macros to create chart limit
lines and curve fits
- Using macros to layout multiple charts on a report worksheet
(i.e., chart report)
Day-2
Using VBA to Store Program Settings in the Windows Registry
- Why store your program settings in the Windows registry
and not in your code workbook?
- Using GetSetting, SaveSetting, and
DeleteSetting to write and retrieve program settings in the Windows Registry
- How to use delimiters in your code to stack
array information in the registry and use Split to retrieve it
Creating Advanced UIs with Worksheets,
Controls, Events, File / Folder Pickers, and Ribbon
- Why create user interfaces (UIs) on the worksheet?
- Creating buttons, shapes, Data
Validation, and ActiveX
controls on the worksheet to control your
Excel VBA tools
- Using macros to control and read ActiveX controls
and Data Validation on the worksheet
- How to build various file / folder pickers that steer
your macro code to the right file or folder to use
- How to create a Ribbon tab,
its controls, and the control event procedures to run your tools
- How to use worksheet events like
Calculation, BeforeRightClick, and BeforeDoubleClick to control
worksheet UIs
Developing Advanced UIs Using VBA Userforms, ActiveX Controls, and Events
- How to use modal and non-modal userforms
to construct advanced UIs
- How to create ActiveX controls and
event procedures on userforms and control them with VBA
- Using ranges, VBA arrays,
AutoFilter, Sort, ArrayList object, and Remove Duplicates to load data into list boxes and combo boxes
- Using macros to format data inside of
userform list boxes and combo boxes
- How to control multi-column and
multi-select list
boxes and combo boxes in your macro code
- How to create a userform list box
that fills in another list box
- Designing procedures that launch
userforms and preset their ActiveX control values
- Using userforms and buttons to quickly create
floating toolbars that run your Excel VBA tools
Assembling Advanced Worksheet
Dashboards Using Excel VBA
- Review of various dashboard types,
scorecards, metrics, benchmarks, and KPIs
- How to use charts, shapes, cells,
pictures, and VBA to build dashboard displays
- Using Data Validation, ActiveX
controls, cells, and VBA to build dashboard controls
- How to turn shapes and pictures
into various hi-tech controls for dashboards
- Using VBA to create, format, write
to, move, and delete shapes and pictures on the worksheet
- Designing logic in your macro code
that reads and implements dashboard UI selections
- Designing macros that gather data
and build tables for dashboard charts
- When to use userform based UIs
in your dashboards and how to integrate them
Constructing Advanced Project
Status and Calendar Tools Using Excel VBA
- Why use workbooks and the
worksheet to build project status tools?
- Looking up status table headers and rows in
your code using MATCH, COUNITIF, Find, Row, Column...
- Simple worksheet strategies to track project
status info in team workbooks
- Writing loops and logic to process
project data from multiple workbooks and worksheets
- Writing loops and logic to fill
and color the data in your status dashboard
- How to access Microsoft Share
Point in your macro code
- Using Excel VBA to create and fill
monthly and 2-week calendars with data
Day-3
Creating and Managing Advanced
Model and Analysis Problems with Excel VBA
- Using ActiveX controls, Data
Validation, and names on the worksheet to layout your problems
- Using userform UIs to control
problem inputs on single and multiple worksheets
- Using VBA to manage
cell and range names
- Using Excel VBA to create and edit
existing problem formulas based on UI selections
- Using loops and logic to collect
problem information, calculate it, and output it to
worksheets
- Creating upload and download
macros that read and store your problem information in external
workbooks
- Using VBA to create worksheets
from templates and tie their formulas / information back into
the problem
Advanced Report Generation Using Excel VBA
- How to use VBA to add report
workbooks / worksheets, name them, and track them
- Using macros to create
and track report worksheets from pre-made templates
- Using cell names in your
macro code to track worksheet output areas
- Designing loops and logic to build
report worksheets like revenue / expense, various balances,
inventory...
- Using macros to format, perform
calculations, and build formulas on your report worksheets
- Designing loops and logic to fill
in worksheet forms
Using SQL, ADO, and UIs to
Query / Integrate Databases, Workbooks, and Text Files
- SQL language review for querying,
inserting, updating, and deleting data
- How to automatically build SQL
statements and connection strings in VBA
- How to use ADO and SQL to update,
insert, delete, and query database data
- How to use ADO and SQL to query a
workbook table or text file and return a recordset to the worksheet
- Combining ADO, SQL, loops, and
functions to integrate
data from different data sources
- Automating Power Query and
Microsoft Query to query / integrate data from multiple sources
- Using worksheet and userform based
UIs to write SQL, control ADO, and display recordset results
Report Generation Using Word,
PowerPoint, Outlook, and Excel VBA
- Why use Excel VBA
to automate PowerPoint, Word, and Outlook?
- The basics of automating Word
- The basics of automating PowerPoint
- Using Excel VBA to send and read
emails in Outlook
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.