Available Onsite Only
About Our Onsites
Learn to build high end technical tools by combining Excel
and VBA's most powerful features.
Coupling Excel's "off the shelf" built-in
capabilities with VBA allows advanced software to be constructed that can surpass
existing software on the market. Our Excel training takes the most important Excel and
VBA topics / concepts and shows you how to build those advanced tools.
Questions? 1.805.498.7162 Customers >
Learn More >
Learn More >
Key topics covered in our Microsoft Excel-Aided engineering and science training.
Our
4-day hands-on
Microsoft Excel-Aided Engineering and Science workshop
shows you the methods and strategies of using Excel and
VBA to produce technical tools that do weeks of work in
seconds. This workshop takes the most important Excel
topics like technical worksheet layout strategies,
technical formula design, modeling strategies, numerical
analysis, data processing, dashboard construction and
Excel VBA macros and condenses them into a compact 4 day
course to get you started.
The key skills learned in our Microsoft Excel-Aided engineering & science
training.
Key Excel VBA skills learned:
- What
is VBA?
- A review of formula design,
workbook integration, formula debugging, cell naming and
basic modeling techniques in Excel
- Strategies and guidelines for engineers and
scientists to follow when naming worksheet
cells and ranges, the key to building and managing advanced Excel
models and data processing tools
- How to correctly
organize and store large amounts of data on
worksheets, table design, a key component in your Excel models
and data tools
- How to develop advanced
Excel table lookup formulas including formulas that can toggle
between tables, key capabilities for modeling and data processing
- How to construct
advanced logic in data processing tools and models on
the worksheet
- How to use ActiveX
controls with formula logic to control data
tools
and models on the worksheet
- Advanced Excel methods for using the IF, AND,
OR, IS, MATCH, INDEX, INDIRECT... functions to create advanced Excel formulas
that can think, adapt and find changing data and model
information
- How engineers and scientists
can create advanced Excel array formulas that analyze vast amounts of Excel data
- How to build
advanced Excel dashboards using formulas, logic, ActiveX
controls, Data Validation, Conditional Formatting,
drawing shapes, pictures and charts
- How to import various text
file formats into Excel and deal with less than ideal text
- How to use MS Query and
Excel tables to
link with databases and access Excel workbooks and text
files using SQL
- How to build Excel Pivot Tables and Pivot Charts
- A complete Excel VBA language
review in regards to engineering and science
- Range and cell
control for engineers and scientists using Excel VBA
- How to use worksheet
functions and VBA functions in your code for tasks like data
processing, information management, processing dates and
times, dates, file names....
- How to use Excel VBA
to automatically determine worksheet data size and
locations
- How to use
Excel VBA to create sophisticated data processing
systems that process data from multiple data sources at
once like workbooks, text files and databases
- How to use Excel
VBA to manage large scale data storage in workbooks
- How engineers and scientists
can use VBA to control userforms containing ActiveX controls and
graphics
- How to create advanced
engineering and science dashboards in Excel VBA using
charts, cells, pictures and drawing shapes
- VBA programming
strategies for communicating with engineering and
science oriented databases using DAO, ADO and SQL
- How engineers and scientists
can use Excel VBA
to create, open, clean, process and close large and
small text files
- How to communicate with
engineering and science programs written in VB.NET, FORTRAN and C languages from Excel VBA
programs
- How to control other programs
using VBA (CreateObject vs. Tools/References)
The skills you need for our Microsoft Excel-Aided engineering &
science training.
Select this Excel VBA training if you or your group have:
- Opened or saved a workbook
- Learned about worksheets, cells and cell references like A1
- Copied and pasted worksheet data
- Performed basic cell formatting tasks
- Typed data in cells and built basic formulas
- Used a worksheet function like SUM, COUNTIF...
Our
Microsoft Excel-Aided engineering & science training syllabus.
Day-1
- Basic formula design, formula
debugging, cell naming and modeling construction
techniques
- How to nest Microsoft Excel
worksheet functions, prelude to advanced formula design
for data processing and modeling
- How to construct "smart"
Microsoft Excel worksheets using Excel's logic, information, and
lookup and reference worksheet functions including VLOOKUP,
MATCH, OFFSET, SUMIF, COUNTIF, INDEX, INDIRECT, IF, AND, OR,
ADDRESS, COLUMN and ROW
- How to perform text
concatenation in Microsoft Excel worksheet formulas, used in
advanced formula design and reporting
- How to use array formulas to
build advanced worksheet formulas that determine and adapt to
changing information requirements
- How to use array formulas,
ActiveX controls, form controls, logic driven Conditional Formatting and logic driven Data Validation to create advanced
dashboards on the Microsoft Excel worksheet
- How to use the text worksheet
functions like RIGHT, MID, LEFT SUBSTITUTE, REPLACE, SEARCH and
CLEAN to analyze worksheet text
- How to create and manage
database tables as worksheets in Microsoft Excel
- How to import and parse text
files in Excel
- How to use MS Query to tie
into databases and text files
- How to create and use Microsoft
Excel Pivot Tables and Pivot Charts for reports
- How to link workbooks and
worksheets together including team integration and large scale
information transfer
Day-2
- How to use the VBA Editor and
VBA debugger
- How to use VBA projects, modules
and procedures
- How to use the VBA language
including variables, data types, constants, arrays, operators,
expressions, loops, logic decisions and calling
- Overview of commanding Excel
using VBA including a discussion of objects, properties and
methods
- How to use the Excel Visual
Basic macro recorder to record Excel tasks in VBA so you
can get key property and method settings
- How to analyze technical data on
single Excel worksheets using loops and logic
- How to use Excel VBA to load
Excel forms on the worksheet using loops, logic along with copy
and paste
- How to use Excel VBA to create
charts
Day-3
- How to analyze data in multiple
workbooks and worksheets
- How to use VBA to summarize
technical data
- How to access the Windows file
and folder system to open and close workbooks
- How to protect your VBA code
against errors
- How to use Excel and VBA to
create basic dashboards using ActiveX controls and worksheets
- How to automate Pivot Tables
with VBA
- How to create basic report
generation tools using Excel VBA, Microsoft Word and PowerPoint
Day-4
- How to use graphics, userforms,
drawing shapes, ActiveX controls and Excel VBA to create
advanced dashboards in Excel
- How to integrate ActiveX
controls and VBA with existing technical models that are formula
based
- Mixed language programming
discussion using VBA, FORTRAN, C and VB .Net
- How to create UDF's (user
defined worksheet functions) for modeling purposes that
run from the worksheet
- How to command text files
using VBA, open, close, read,...
- How to use ADO to query
workbooks and text files, large data set importation
- How to command databases using
DAO and ADO in VBA
_EMAGENIT_2019.gif)
_EMAGENIT_2019.png)
_EMAGENIT_2019.gif)
_EMAGENIT_2019.gif)
_EMAGENIT_2019.gif)
_EMAGENIT_2019.gif)
_EMAGENIT_2019.gif)
_EMAGENIT_2019.gif)
_EMAGENIT_2019.gif)