Intermediate Excel VBA Training for Engineers and Scientists

Intermediate Excel VBA Training for Engineers & Scientists Description

Our 2-day hands-on live intermediate Excel VBA training for engineers and scientists focuses on how to turn Excel into an innovative data processing, data fusion and data visualization tool for engineering and science data. Our Excel VBA training starts off with showing you unique ways to control Excel cells, ranges and data tools in Excel VBA and how these elements can be used together to create high end data fusion and data analysis tools. Our Excel VBA training then focuses on how to use various loops, Excel worksheet/VBA functions, arrays and logical techniques to analyze technical data with a focus on reading unconventional worksheet data layouts, re-sizing moving data areas and locating hard to find information on a worksheet. Our training then discusses sophisticated data fusion techniques using VBA loops and logic to sift through data in multiple workbooks and worksheets from multiple folders. The data fusion discussion also covers methods for reading and writing to text files and databases and fusing that information together. Also covered is various data visualization methods showing how to use Excel VBA to visualize your collected engineering/science data in various worksheet table configurations, colored cells, drawing shapes, pictures and charts. Taught by an engineer.

Key Skills You'll Learn in Our Intermediate Excel VBA Training for Engineers & Scientists

Key Skills You'll Learn in Our Intermediate Excel VBA Training for Engineers and Scientists

Key Excel VBA skills learned:

A review of key VBA language elements during the training including variables, data types, constants, arrays, operators, expressions, loops, logic decisions, functions and calling
Full object, property and method review, how to use them and their syntax rules in VBA code
A full review of key of how to use Excel worksheet and VBA functions in your code that sum and count values; check for errors; lookup information;, clean and process text; and process dates and times
Advanced methods to identify cells, ranges, columns and rows in VBA and control them
Using Excel VBA to locate and track hard to find data on the worksheet
How to automate key Microsoft Excel data tools (autofilter, advanced filter, sort, Excel Tables, Microsoft Query and pivot tables) in VBA that are key to data fusion and data analysis techniques
Automating key Excel VBA data operations that are used in data fusion like sorting, filtering, finding, copying, pasting, inserting and deleting
How to use Excel VBA to control key Microsoft Excel objects that relate to data fusion and data visualization in Microsoft Excel
Using VBA to control the Windows folder and file system
How to use VBA to control other programs
How to use Excel and VBA to open, scan and process data in text files including hard to read text files
Using Excel VBA to access data stored on multiple worksheets in multiple workbooks
Using VBA ADO, DAO and SQL to access and control databases
Key strategies and methodologies for creating data fusion tools in VBA
How to use Excel VBA to control worksheets, charts and drawing shapes and turn them into data visualization tools

Intermediate Excel VBA Training for Engineers & Scientists Skill Requirements

Intermediate Excel VBA Training for Engineers and Scientists Skill Requirements

Select this Excel VBA training if you or your group have:

Written a procedure in VBA
Used variables, loops, functions, declarations,... in some capacity in VBA
Used and written basic logic in VBA
Used Microsoft Excel's data tools manually and know them operationally
Formatted worksheets and know Excel's basic formatting capabilities
Have used Excel's worksheet functions like MATCH, COUNTIF, SUMIF, VLOOKUP...

Intermediate Microsoft Excel VBA Training for Engineers & Scientists Topics Outline

Important: We focus our intermediate Excel VBA training on what our customers need. When training begins, we analyze those needs and shift our intermediate Excel VBA 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

Using Objects in Excel VBA

A review of critical VBA programming elements during the class including variables, data types, constants, arrays, operators, expressions, loops, logic decisions, functions and calling conventions
Definition of an object, how to use one in programming and the syntax rules for using them in your VBA code
Definition of a property, how to use them in programming and the syntax rules for running them in your VBA code
Definition of a method, how to use them in programming and the syntax rules for running them in your VBA code
How to construct object expressions using accessors in your VBA code
How to use the Set statement in VBA to track objects
How to use the VBA For...Each Next loop to loop through object collections and track individual objects

Intermediate Excel VBA for Engineers and Scientists Free Repeats

Review of Key Excel Worksheet and VBA Functions Commonly Used in Data Fusion, Data Analysis and Data Visualization

Full syntax review of how to run Excel and VBA functions in your VBA code
Using the SUMIF, COUNTIF, SUMIFS and COUNTIFS worksheet functions in Excel VBA to sum and count technical data
Review of using the imaginary and engineering functions in Excel VBA to crunch data
How to perform table lookups in Excel VBA using MATCH, COUNTIF, VLOOKUP, SUMIFS
How to use the COUNTIF worksheet function in Excel VBA to test for data existence
Using the IS VBA and Excel worksheet functions to detect errors in your data
Using the Left, Right, Mid and Len VBA functions to break text apart in VBA
Using the TRIM, CLEAN, Replace and Lcase functions in Excel VBA to condition text for use in logic
How to test for a subtext within a piece of text using the Instr VBA function
Using the Split VBA function to break data apart based on a specific delimiter
How to use the C VBA functions (Cdbl, Cdate,...) to flip data types for use in logic and expressions
How to determine date elements in Excel VBA using the Month, Day, Year and Format VBA functions
Using DateAdd and DateDiff in VBA to add or subtract days, months, years...from a specific date
How to use string concatenation to create and modify dates in your VBA code
How to use the VBA For...Next Loop and DateAdd, DateDiff and Format VBA functions to create automated calendars

Intermediate Excel VBA Training for Engineers and Scientists Online Details

Key Methods for Tracking Excel Ranges in Your VBA Code for Data Fusion, Data Analysis and Data Visualization Purposes

Why do you need to know how to assemble multiple Range accessors together in VBA code to isolate data on a worksheet
Review of using the Cells, Rows, Columns and Range accessors in your Excel VBA code
How to isolate parts of a range within another range in your VBA code by assembling multiple Range accessors together in an object expression
Using the Find accessor to locate hard to find data on a worksheet
Unique ways of using the Offset accessor to locate data in cells around cells, great for modeling and multi-row data headers
Using the CurrentRegion accessor in your VBA code to track a range of unknown size
Alternate ways of tracking ranges that change size in your VBA code
Clever ways to use range names in your VBA code to track data and flag worksheet positions

Using Excel VBA to Control Key Excel Operations and Tools Vital to Data Fusion, Data Analysis and Data Visualization

Various high-end Excel VBA methods for reading and writing to ranges (single cell values, ranges...)
Various high-end Excel VBA methods for coping and pasting worksheet data (entire sheets, specific table sections, rows, columns...)
Worksheet data management (inserting, deleting, and moving rows, columns, cells)
Using Excel VBA to control formatting for cells and various range configurations (fonts, colors, borders, number formats and alignments)
Using Excel VBA to manage Excel tables and queries
Strategies and methods for using Excel VBA to control Excel's filter tools like Autofilter, Advanced filter and Remove Duplicates
Using Excel VBA to control the Sort, Text-to-Columns and grouping tools
Using Excel VBA to create, edit and delete formulas and to determine their cell and range references
Strategies and methods for using Excel VBA to control cell and range names
Various high-end Excel VBA methods for adding and controlling Pivot Tables
How to design For...Next and For...Each Next loops that use these operations and tools

Methods for Using VBA Arrays and For...Next Loops to Perform Advanced Data Analysis

Benefits of using the VBA For...Next loop to analyze worksheet data
Strategies and methods for designing VBA For...Next loops and logic to analyze worksheet data arranged in various patterns (stop and go data, stacked tables, oddly grouped data....)
Strategies and methods for designing multiple counters and logic to track start and stop positions of data inside VBA For...Next loops
Strategies and methods for designing VBA For...Next loops and logic that deal with shifting header names, column positions and multi-row headers in your data
How to declare, write to, and read arrays in VBA
How to create dynamic arrays in VBA
How to use Excel worksheet and VBA functions that generate arrays in your VBA code
How to read worksheet ranges as arrays in VBA and process them using For...Next loops
How to use Excel VBA to build report worksheets
How to use VBA For...next loops to build worksheets from table values

Day-2

Controlling the Windows Folder and File Systems, Registry; and Other Programs Using VBA

How do these programming elements time into data visualization, analysis and fusion tools
Strategies and methods for controlling other programs and libraries in VBA with early (tools/reference) and late binding (CreateObject, GetObject)
Using the VBA Object Browser and on-line help to determine the object hierarchy of another program
Why store program information and settings in the Windows Registry
How to use VBA to store and retrieve information in the Windows Registry
Delimiting multiple pieces of program information when storing in the Windows Registry
Using the FileSystemObject in VBA to move, create, delete and rename Windows folders and files
Using VBA, the FileSystemObject and logic to detect if program files and folders exist
Strategies and methods for labeling files for use in a data fusion environment
Using the File and Folder objects in VBA to determine folder and file names
Overview of using VBA For...Each Next loops to loop through folders and files
How to deconstruct folder and file names using the Split and Instr VBA text functions

Data Fusion Methods in VBA for Looping Through Workbooks and Worksheets

What is data fusion, how can it be applied to worksheets and workbooks, and how to implement it using Excel VBA
Data fusion strategies for naming and reading workbook files in a data fusion environment
Benefits of using the Set statement to track workbooks in VBA when you open them
How to use Excel VBA to automate the Excel Open dialog box turning it into a file selector
Using Excel VBA to name and save workbooks to different folders and closing them
Using VBA For...Each Next loops, various Excel methods and properties, and logic to loop through open workbooks and their worksheets looking for data
Using VBA For...Each Next and For...Next loops, various Excel methods and properties, and logic to loop through workbooks in folders, open them and scan their worksheets looking for data

Data Fusion Purposes in VBA to Control Text Files

VBA data fusion strategies for storing data in multiple text files
VBA strategies for naming and reading text files in a data fusion environment
Using VBA and the TextStream object to open and close text files
How to read and write to a text file using VBA
Using VBA Do loops, logic and functions to scan large and small text files for key information and transfer that data to the worksheet
How to clean misaligned degraded text files and parse their information using Excel VBA

Data Fusion Methods in VBA to Control Databases

Data fusion strategies for using multiple databases along with Excel based data
SQL language review
How to use string concatenation and loops to assemble a SQL statement in your VBA code
How to use DAO to control Microsoft Access databases
How to use ADO to control SQL driven databases
How to use ADO to query and process data in workbooks and text files

How to Assemble and Use Excel VBA to Control Charts, Drawing Shapes and the Worksheet for Data Visualization Purposes

How to automatically build embedded charts and chart sheets using Excel VBA
How to use VBA to size and place embedded charts on the worksheet
How to add and change a chart's series using Excel VBA (ranges and arrays)
How to use VBA including For...Each Next loops to format various chart elements
How to use VBA to add and control Excel drawing shapes, their format and their text
Strategies for laying out visual display areas on the worksheet so VBA can fill them in
How to assemble and use these elements to make data visualization tools for data fusion programs

Intermediate Microsoft Excel VBA for Engineers & Scientists Training Details

Audience: Engineers, Scientists and Technicians
Time: 8:30 AM - 4:30 PM
# of Days: 2-days
Type: Hands-on, live, instructor taught training
Manual: 2000 + page Microsoft Excel Aided Engineering and Science manual full of Excel tips and how-to-do topics included with the training
Platform: Windows 7 - Windows 10, Mac users welcome
Excel Versions: 2007-2016, Windows and Mac
Free Repeats: Repeat your Excel training as many times as you like, no date or limit, click here for details
Examples: We review our attendees personal examples in our training instruction
Training Formats:
- Public Training Workshop
- Live Online Course. We broadcast our Excel public training as a live hands-on online courses so even though you cannot travel to us, you can still take the course as if sitting in the room with us
- Company Onsite
- For self-study, purchase Beginning Excel VBA for Engineers and Scientists. For company onsite information, please contact us 1.866.924.6244
Additional Training:
- Advanced Excel VBA
- Microsoft Excel Dashboards

Public Event / Live Online Course Signup

Click Dates|Signup

Dates | Signup

Intermediate Excel VBA Training Contact Information

Contact EMAGENIT Directly

Phone Number: 1.866.924.6244
Business Hours: 8:30 - 5:00 PM PT
email: adv_excel_question@emagenit.com

Your Contact Information

*Name:
Company:
Company Web:
Country:
Phone #:
Ext:
*e-mail:
	Contact me directly by phone when this is received

*Required information to complete form. Privacy Policy

Workshop Questions

Please fill out the box below with your workshop questions and EMAGENIT will contact you.

Request Workshop Information and Submit

	Send me on-site workshop information
	Send me public training workshop information
	Send me self-study manual information
	Send me live online course information

Before clicking the Submit button, please fill in at least your name and email under Your Contact Information. Privacy Policy