Excel Training, Excel VBA Training, Excel Software Design

Intermediate Microsoft Excel VBA Training
for Business Professionals

Intermediate Microsoft Excel VBA Training for Business
Click Dates|Signup

Our Intermediate Microsoft Excel VBA training for business shows you how to create useful data analysis, data fusion and data visualization tools using Microsoft Excel and VBA.

Intermediate Excel VBA Training Description, Requirements and Topics

Intermediate Excel VBA Training Description
Description:

Excel VBA Training Description

Our 2-day "hands-on" live Intermediate Microsoft Excel VBA training for business focuses on how to use Excel as a powerful data analysis, data fusion and data visualization tool. Our Excel VBA training starts off with a complete review of objects and how to use them in programming. Next it discusses how to use Excel VBA to command Excel's cells, ranges and data tools in ways that apply to data fusion and data analysis. It then demonstrates how to analyze data using various loops, functions and logical techniques with the focus on handling re-sizing data tables, locating hard to find headers and reading non-standard data table formats. Our VBA training then discusses how to use VBA loops and logic to perform data fusion on multiple workbooks and worksheets from multiple folders. It also discusses how to perform data fusion on multiple text files and databases fusing their information together with worksheet data. Along with combining databases, text files and worksheets, our training demonstrates how to access and control other programs using VBA. Last it shows how to use Excel VBA to visualize the collected data in various worksheet table configurations, colored cells, charts and drawing shapes. Excel for Mac users welcome.
Key Skills You'll Learn in Our Intermediate Microsoft Excel VBA Training
Key Skills:

Key Intermediate Excel VBA Skills Learned

Key Skills You'll Learn in Our Intermediate Microsoft Excel VBA Training
  • A review of the VBA language during the course including variables, data types, constants, arrays, operators, expressions, loops, logic decisions, functions and calling in relation to data tasks
  • A review of objects, properties and methods, what they really are and there syntax rules in VBA code
  • How to use and program key Microsoft Excel data tools (autofilter, advanced filter, sort, Excel Tables, Microsoft Query and pivot tables) that are key to data fusion techniques
  • Excel VBA data analysis techniques for sorting, filtering, finding, copying, pasting, inserting and deleting worksheet data
  • Review of key Microsoft Excel worksheet functions and Excel VBA functions that control text and dates
  • How to use Excel VBA to control key Microsoft Excel objects that relate to data fusion and data visualization in Microsoft Excel
  • How to use Excel VBA to access data stored in multiple worksheets and in multiple workbooks
  • How to use Excel and VBA to open, scan and process data in text files including hard to read text files
  • Basic database access in Excel VBA using ADO, DAO and SQL
  • How to command and access other programs
  • How to control the Windows folder and file system using VBA
  • Key strategies and methodologies for creating data fusion tools in VBA
  • How to use Excel VBA to fill in various table formats for your data visualization tools
  • How to use Excel VBA to control drawing shapes and charts for data visualization tools
Intermediate Excel VBA Training Skill Requirements
Required Skills:

Required Intermediate Excel VBA Skills

Select this Intermediate Excel VBA training if you or your group have: Intermediate Excel VBA Training Skill Requirements
  • 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 for Business Training Topics
Training Topics:
Important: We focus our intermediate Excel VBA training on what our customers need. When class begins, we analyze those needs and shift our workshop training outline appropriately. We will stress topics or add topics that our customers want. No two workshops are ever the same with EMAGENIT.

Day-1

Review of Tracking and Working with Objects in Your Excel VBA Code

Intermediate Excel VBA Training for Business Free Repeats Policy
  • A review of all the major VBA programming elements including variables, data types, constants, arrays, operators, expressions, loops, logic decisions, functions and calling conducted when needed
  • What is an object and how to identify them with VBA code
  • What is a property and how to run them in your VBA code
  • What is a method and how to run them in your VBA code
  • Complete Excel VBA syntax rules for typing the command.command.command structure in your code
  • Complete Excel VBA syntax rules for running properties and methods
  • How to use the Set statement to properly track objects in your code
  • How to use the VBA For...Each Next loop to loop through and track objects in your code

How to Track Cells and Ranges in Your Excel VBA Code for Data Analysis, Data Fusion and Data Visualization Purposes

  • Basic identification of cells and ranges in your Excel VBA code (Cells, Rows, Columns, Range)
  • How to combine multiple range accessors (Range, Cells, Columns...) together in your VBA code to isolate ranges within ranges
  • How to use Offset to locate cells around cells without markers
  • How to use CurrentRegion in your VBA code to track a range of unknown size
  • How to use Find to locate specific cells on the worksheet such as header cells
  • Using cell and range names in your VBA code to track data and designate worksheet positions
  • Using all these methods together to go after data on worksheets

How to Use Key Excel Worksheet Functions and VBA Functions for Data Analysis, Data Fusion and Data Visualization Purposes

  • Review of how to use Excel and VBA functions in your code
  • Parsing text data using Left, Right, Mid, Len, Instr and Replace
  • How to sum and count data in VBA using the COUNTIF, SUMIF, SUMIFS and COUNTIFS Excel worksheet functions
  • How to use the MATCH and the COUNTIF worksheet functions to locate headers and specific rows in you Excel worksheet data
  • Using the COUNTIF worksheet function in your VBA code to test for data existence
  • How to use the Split VBA function to parse data 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 use the IS VBA and Worksheet functions to detect errors in your Excel data
  • How to break dates down in Excel VBA using the Month, Day, Year and Format VBA functions
  • How to add and subtract dates using DateAdd and DateDiff
  • How to work with dates using string concatenation
Intermediate Excel VBA Training for Business Webinar Details

Controlling Key Cell and Range Operations in VBA for Data Analysis, Data Fusion and Data Visualization Purposes

  • Different ways to use Excel VBA to copy and paste worksheet data (entire sheets, specific table sections, columns...)
  • Different ways to use Excel VBA to read and set cell values (single cell values, ranges...)
  • Using VBA to manage worksheet data like deleting, inserting and moving columns, rows, and cells
  • Using VBA to control cell fonts, colors, borders, number formats and alignments
  • How to use Excel VBA to control Excel's data filters like Autofilter, Advanced filter and Remove Duplicates
  • How to use VBA to control the Sort feature, Text-to-Columns feature and grouping features
  • How to use Excel VBA to control Excel tables and queries on worksheets
  • Using Excel VBA to control formulas, names and drag n fills
  • How to automate Pivot Table construction
  • Automating these features inside of loops

Data Analysis Techniques Using For...Next Loops and VBA Arrays

  • Determining the master For...Next loop
  • Dealing effectively with shifting data header names, column positions and multi-row headers in your VBA For...Next loops
  • Using For...Next loops in various patterns to analyze worksheet data
  • Using For...Next loops to analyze complex data patterns (stop and go data, stacked tables, oddly grouped data....)
  • How to construct the logic for For...Next loops that analyze complex data patterns on the worksheet
  • How to use arrays in Excel VBA for data analysis purposes including Excel and VBA functions that generate arrays
  • How to read entire ranges off the worksheet and process as arrays in VBA using For...Next loops (speed run)
  • How to use For...Next loops to build multi-category report worksheets and workbooks
  • How to use For...Next loops to fill in sophisticated worksheet table layouts and forms


Day-2

How to Use Excel VBA to Control the Windows Registry; Folder and File Systems; and Other Programs

  • How to control other programs using early (reference) and late binding (CreateObject, GetObject) in Excel VBA
  • How to lookup and observe the object structure of another program
  • Why use the Windows Registry to store valuable program information
  • How to label files for use in a data fusion environment
  • How to store and retrieve information from the Windows Registry
  • How to use Excel VBA and the FileSystemObject to create, move, rename and delete Windows folders
  • How to use Excel VBA and the FileSystemObject to detect if files and folders exist
  • How to determine folder and file names using the Folder and File objects
  • Strategy for using Excel VBA to loop through files in a folder
  • How to use the Split and Instr VBA text functions to read your file and folder names

Using Excel VBA to Open, Analyze and Close Text Files for Data Fusion Purposes

  • Data fusion strategies for storing data in text files
  • How to label text files for use in a data fusion program
  • How to open and close text files using Excel VBA
  • How to use Excel VBA Do loops to scan large and small text files for key information
  • How to clean misaligned degraded text files and parse their information using VBA
  • How to use Excel VBA to transfer text file data to a worksheet for data fusion purposes

 Using VBA to Control and Loop Through Workbooks and Worksheets for Data Fusion Purposes

  • Data fusion strategies for storing data in multiple workbooks and worksheets
  • How to label workbook files for use in a data fusion program
  • The proper way to track workbooks in your code that you open and add
  • How to build a workbook file picker using Excel VBA
  • How to use Excel VBA to open, add and close workbooks
  • How to use Excel VBA to save workbooks
  • How to loop through open workbooks using VBA For...Each Next loops
  • How to loop through multiple workbooks in folders using VBA For...Each Next loops
  • How to loop through multiple worksheets in a workbook looking for data using VBA For...Each Next loops

How to Use SQL, ADO and DAO to Access Databases for Data Fusion Purposes

  • 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 Training Details

Training Details
Audience: Business Professionals
Time: 8:30 AM - 4:30 PM
# of Days: 2-days
Type: "Hands-on", live, instructor taught training
Manual: 2000 + page Microsoft Excel-Aided Business 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. Training covers these versions discussing differences where necessary
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 Seminar, Live Webinar, Company Onsite, and Self Study. For self-study, purchase Beginning Excel VBA for Business Professionals. For company onsite information, please contact us 1.805.498.7162
Price: Live Hands-On Public Seminar: $695.00, Live Broadcast Hands-On Public Webinar: $595.00
Follow On Training Microsoft Excel Dashboards, and Advanced Excel VBA
Public Seminar and Webinar Signup
Click Dates|Signup

Intermediate Excel VBA Training Question Form

Your Contact Information
Name:
Company:
Company Web:
Country:
Phone #:
Ext:
e-mail:
Contact me directly by phone
Contact me directly by phone
Workshop Questions
Questions:
Request Additional Workshop Information
Send me on-site workshop information
Send me self-study manual information
Send me public seminar workshop information
Send me live online webinar information

Privacy Policy

Contact EMAGENIT Directly

Our Contact Information
Phone Number: 1.805.498.7162
Business Hours: 8:00 - 5:00 PM PT
email: int_excel_vba_bus_question@emagenit.com


Site Map

Copyright © 2002-2017

EMAGENIT All Rights Reserved