Advanced Excel VBA Training

Our 3-day hands-on advanced Excel VBA training for scientists and engineers shows you how to build powerful data analysis, data integration, dashboard and modeling tools using Excel VBA. Our VBA training starts off with a complete review of the VBA language where needed, how to use objects in VBA and how to create them using Class modules. Next it discusses advanced command and control of ranges, cells, worksheets and workbooks using Excel VBA, and how these methods can be used to build sophisticated data analysis, modeling and dashboard tools. Using VBA to automate Pivot Tables and queries is also covered.

After covering range control, our training discusses how to read various types of data using advanced loops and logic and how to process, concatenate, clean, parse and replace data using a variety of different worksheet and VBA functions. Our training then explores how to control the file and folder system of Windows and loop through folders looking for various files. Also covered are advanced data processing methods for reading technical data that is arranged in non-standard, adjustable, or stacked formats on the worksheet. Scanning for hard to find data on worksheets such as non-standard, double rowed data headers is also covered. In addition to scanning worksheet data, our VBA training also covers how to read and write to databases and text files integrating their data with information stored on worksheets. Advanced table lookups between worksheets is also covered. Advanced chart reports is also covered.

After discussing data control, our VBA training provides an in depth discussion of how to use and control ActiveX controls on userforms and worksheets using VBA and events. It then shows how to put it all together and design, build and program advanced worksheet dashboards and visual interfaces using cells, charts, ActiveX controls, shapes, pictures and events. Our training also discusses how to build advanced engineering and science models using functions and how to automate Solver and build model batch processing systems. Last, how to control other programs like PowerPoint, Word and interface with other languages like VB.Net, FORTRAN and C is covered.

Key Excel VBA skills learned:

• A complete review of the Excel VBA language, its Editor and objects, properties and methods in regards to engineering and science
• How to use CreateObject and GetObject to control external engineering/science programs and libraries
• How to use VBA to build and control complex range configurations, worksheet names and data validation
• Advanced Excel VBA methods for determining resizing ranges and worksheet table start/stop points
• How to use VBA to automatically build worksheet formulas and figure out their ranges, a must know for reports and modeling problems
• Advanced VBA methods for opening, reading and editing text files
• Advanced VBA methods for opening and tracking multiple workbooks and adding and tracking multiple worksheets
• How to design efficient VBA loops that scan various data configurations and process them
• Advanced use of worksheet functions and VBA functions in your code for data tasks like processing, parsing, cleaning, searching, date manipulation,....
• How to perform advanced string concatenation tasks in VBA
• How to use advanced VBA to analyze complex worksheet table patterns
• How to loop through multiple workbooks and worksheet looking for data and processing it
• How to use VBA to store, edit, read and delete vital program information in the Windows Registry
• How to create, move and delete Windows folders and files using VBA
• Advanced Excel VBA methods for chart report creation and formatting
• How to use Excel VBA to create and layout mutli-chart report worksheets
• How to create and control userforms and ActiveX controls for engineering and science purposes
• Advanced VBA methods for loading and controlling list boxes and combo boxes
• How scientists and engineers can create custom userform based toolbars to run Excel VBA code and provide program navigation
• How to create advanced engineering and science visual interfaces in Excel using VBA, events, worksheets, charts, pictures and drawing shapes
• Advanced Excel VBA methods for creating VBA procedures that run at a specific time or key combination
• How scientists and engineers can use advanced VBA to control the Analysis ToolPak, Query Tables and Pivot Tables
• How to use DAO, ADO and SQL to control with engineering and science oriented databases, text files and worksheet tables
• How to communicate with engineering and science programs written in VB.NET, FORTRAN and C languages from Excel VBA programs
• How to control Word, PowerPoint, Outlook and web pages using VBA
• How to create advanced user defined worksheet functions for modeling purposes
• How to automate Solver and perform trade studies on models using VBA

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

• Used the Excel VBA language before
• Used variables, loops and logic before and know they operate
• Used objects, properties and methods before in code to control a program
• Used Excel drawing shapes and built charts manually
• Built basic worksheet formulas (=A1+A2) and used worksheet functions like SUM, Match, COUNTIF, COUNTA...
• A desire to understand VBA from a nuts and bolts perspective

Day-1

Full Excel VBA Language, Object and Error Trap Review for Engineers and Scientists

• How Excel VBA can be used to create advanced engineering and science tools
• A complete Excel VBA language review for engineers and scientists including variables, data types, constants, arrays, operators, expressions, loops, logic decisions and calling conventions
• Advanced ways to use the VBA Editor for debugging and viewing purposes
• A review of objects, properties and methods and how are they used to control a program
• Why scientists and engineers should use complete object expressions in their VBA code (i.e. command.command.command) to control Excel and other programs
• Advanced strategies for using the Set statement to track objects in your VBA code
• How engineers and scientists can enhance their advanced Excel VBA code by constructing error traps and logical error traps
• How to use CreateObject and GetObject to command other programs and libraries

How to Use Excel VBA to Control Complex Ranges, Names and Data Validation in Engineering and Science

• How engineers and scientists can use the Cells, Range, Rows, Columns, CurrentRegion, SpecialCells, and Find commands in VBA to command various range configurations
• Excel VBA techniques for both creating and tracking various complex range configurations, sub ranges, worksheet headers, and resizing data ranges
• Advanced VBA techniques for inserting/deleting data in complex range patterns and for determining worksheet table start and stop points
• How to use VBA to build engineering and science formulas automatically including array formulas and figure out their cell references and ranges, great for rapid modeling programs
• How to run key worksheet functions in your VBA code and dynamically determine their ranges
• How to use Excel VBA to apply number formats, fonts, alignments, borders and colors... to various complex range configurations
• How to use Excel VBA to control Data Validation and its settings, great for engineering and science models, forms and dashboards
• Review of how to use cell names on the worksheet for various tracking purposes
• How to control names in Excel VBA and adjust their settings with VBA
• How engineers and scientists can use the Excel Names collection and For...Each Next loop to develop model and program uploads and downloads
• How to use Excel VBA to control engineering and science pivot tables and query table ranges

Advanced Workbook and Worksheet Control for Scientists and Engineers

• Advanced Excel VBA methods for adding, opening, saving, closing and naming workbooks
• How to create Add-In workbooks and use them in advanced Excel VBA program construction
• How to use advanced Excel VBA logic to add, move and rename report worksheets dynamically
• How scientists and engineers can use the FOR...Each Next loop in VBA to search for open workbooks and worksheets based on their characteristics
• How engineers and scientists can use advanced logic to detect if For...Each Next loops have found specific workbooks and worksheets
• How to create workbook level events in advanced Excel VBA that run code when a workbook is opened, saved, closed...

Advanced VBA Text File Control Techniques for Engineers and Scientists

• How engineers and scientists can open and close text files using Excel VBA
• Advanced Excel VBA methods for scanning text files for specific data
• How scientists and engineers can clean misaligned, poorly assembled text files and parse their information using the worksheet and VBA
• How to use different string concatenation techniques in VBA to assemble text files from various data sources
• How to get information from large text files (250,000 + rows of data) in to Excel using advanced VBA strategies

Advanced Excel VBA Techniques for Processing and Correcting Engineering and Science Data

• How to create proper engineering and science tables on the worksheet for large scale data storage
• Engineering and science methods for reading worksheet data efficiently, the key to developing advanced models, data apps and dashboard displays
• How to use For...Next loops and logic to efficiently access and process standard engineering and science worksheet tables
• How to parse, interpret and fix the engineering and science worksheet data using the Trim, Clean, Left, Right, Mid, Replace, Instr and Split functions
• How to use the VBA IS functions, Nothing and logic to determine if your data has errors
• How to work with dates in VBA using the Month, Day, Year, Weekday, Format, DateDiff, DateAdd,... VBA functions
• Advanced VBA methods for combining text, string concatenation, using loops, logic and various functions
• Converting worksheet data into different data types using the C functions (Cdate, Clng) and the data type declarations
• How to import a range as an array in your Excel VBA code to speed up data processing programs, speed run
• Advanced VBA programming techniques for dealing with arrays, dynamically dimensioning arrays and the ArrayList object

Day-2

Analyzing Data in Complex Worksheet Table Patterns and Multiple Workbooks

• How to use VBA loops, logic and functions to analyze engineering and science data arranged in complex table patterns (stacked tables, fragmented tables, stepped tables...)
• Advanced VBA engineering and science strategies for tracking shifting table headers, performing advanced logic lookups, detecting if a value is present on a worksheet,...
• Advanced VBA techniques for finding and processing data on multiple worksheets in multiple workbooks
• How to use the Sort, Remove Duplicates and Filter tools inside of VBA For...Next loops to speed up data processing and report building
• How to use advanced Excel VBA methods to automatically construct summary worksheet tables using loops, logic and key worksheet functions
• How to use For...Next loops, logic, names and the VBA date/time functions to automatically build various report output tables and forms

Windows Registry, Folder and File System Control for Engineers and Scientists

• Why engineers and scientists should store program information in the Windows Registry
• How to use advanced Excel VBA methods to store, retrieve, edit and delete Windows Registry values
• How engineers and scientists can use advanced VBA to create, move, rename and delete Windows folders, great for model information and data management
• How to use advanced Excel VBA methods to detect if folders and files exist on a user's computer
• How to use Excel VBA to create data storage systems that manage Windows files and folders
• How scientists and engineers can use the VBA Split and Instr text functions to read file names, folder names and Windows registry values

Advanced VBA Methods for Controlling Windows and Excel's Built-In Dialog Boxes

• How scientists and engineers can use built-in dialog boxes from Excel and Windows to pick Excel VBA program folders and files
• How engineers and scientists can use the GetOpenFileName and GetSaveAsFileName functions to control Excel's Open and Save As dialog boxes, great for modeling and data processing programs
• How to use advanced Excel VBA to control Window's Browse for Folder dialog box when creating program interfaces
• How to develop advanced VBA program logic that set file and folder paths first, then use this information later after it has been set
• How engineers and scientists can use various dialog boxes to create advanced program interfaces that control program information access

How to Create Advanced Chart Reports Using Excel VBA

• Review of how to create engineering chart sheets and embedded charts using Excel VBA
• Advanced chart formatting techniques using Excel VBA including limit lines and curve fits
• How to use Excel VBA to add, edit and delete a chart's data series
• How to use Excel VBA to manually create a chart series using arrays instead of ranges, great for emailed reports
• How to use VBA For...Each Next loops to loop through series, legends and data points formatting them (make invisible, color a specific point, color points that are negative, grey out...)
• How to write Excel VBA code that automatically creates and arranges multiple charts on a report worksheet

Advanced Excel VBA Userform and ActiveX Control Construction Techniques for Engineers and Scientists

• Overview of how to use userforms and ActiveX controls for engineering and science projects
• How to create and layout ActiveX controls and pictures on userforms
• How to use VBA to control scroll bars, spin buttons, check boxes, option buttons and image controls on userforms
• How to use VBA to control text boxes on userforms and error check their typed data
• How to use VBA to control list boxes and combo boxes on userforms
• How to load data into list boxes and combo boxes using ranges, VBA arrays and other Excel data tools like Sort and RemoveDuplicates
• How to use ArrayList object in Excel VBA to collect and sort data from worksheets, text files and databases for use in list boxes and combo boxes
• How to Use VBA to control the format of data inside of list boxes and combo boxes
• How to create and read multi-column list boxes and combo boxes using Excel VBA
• How to create multi-select list boxes on userforms and command them using VBA
• How to create a list box on a userform in VBA that fills in another list box using events
• What is the difference between a modal and non-modal userform in VBA
• Designing VBA procedures that launch (show) and preset ActiveX controls values on userforms
• How to create event procedures for ActiveX controls on userforms
• How to design a floating toolbar that runs program elements using userforms and events in VBA
• Designing VBA code that shows the toolbar when a key combination is pressed
• How to create ActiveX control events in your Excel VBA code that trap key strokes and movement on a userform
• What is an Excel Add-In and how can they be used to house userforms

Day-3

Advanced Engineering and Science Visual Interface Design on the Excel Worksheet

• How to create and control ActiveX controls on the worksheet using advanced Excel VBA methods
• How to pull all of Excel's capabilities together to build dashboards and program interfaces
• How to construct engineering and science programs that respond to worksheet events like typing, clicking, double clicking and right-mouse clicking
• How engineers and scientists can use VBA to control charts, pictures and drawing shapes turning the worksheet into a sophisticated technical display system
• How to position and control drawing shapes and pictures using Excel VBA
• How to create an advanced worksheet navigation system using graphics on the worksheet and VBA
• How scientists and engineers can launch userforms using various worksheet, workbook and application level events in VBA
• How to use advanced Excel VBA methods to design dashboards that change shape and content based on user selection
• How to create engineering and science dashboards that update at a certain time or run based on pressing a hot key combination assigned by VBA
• How to create your own tab and buttons in the Excel Ribbon

Advanced Excel VBA Strategies to Control Excel's Advanced Data Features

• How to control all Pivot Table elements and features using Excel VBA methods
• How to run the Analysis ToolPak features and integrate them into engineering and science programs using advanced Excel VBA methods
• How to control Excel tables and queries using advanced VBA

Using SQL, ADO and DAO in Engineering and Science Programs to Control Data Sources

• 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, text files and worksheet tables

Using Advanced Excel VBA to Control Other Languages, Programs and Web Pages

• How to use the capabilities of VB.Net with VBA including compiling .Net COM libraries
• How to call FORTRAN and C routines from Excel VBA
• Hoow to create report generation tools using Excel VBA, Word and PowerPoint
• How to access and control web pages

Developing Advanced Engineering and Science Models Using Excel VBA

• How to construct custom user defined worksheet functions (UDF) in VBA that that calculate advanced equations and logic
• How to construct custom user defined worksheet functions (UDF) in VBA that accept both scalar and array values
• How to design UDF's that calculate advanced numerical analysis techniques (optional, class vote)
• How design model interfaces that can utilize functions and also incorporate SOLVER  (optional, class vote)
• How to use Excel VBA to run Solver and run model batch reports  (optional, class vote)