Advanced Excel VBA Training

Our 3-day hands-on live advanced Excel VBA training for scientists and engineers shows you from an engineering and science perspective 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 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. 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 this is arranged in non-standard, adjustable, or stacked formats on the worksheet. Scanning for hard to find information 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. After discussing data control, our VBA training provides an in depth review 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 dashboards on worksheets, charts and userforms with a full review of using events that apply to those elements. 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 advanced Excel VBA language review in regards to engineering and science
• Advanced range and cell control for engineers and scientists using Excel VBA
• Advanced use of worksheet functions and VBA functions in your code for tasks like data processing, information management, processing dates and times, dates, file names....
• How engineers and scientists can use advanced Excel VBA to automatically determine worksheet data table sizes and locations
• How to use advanced Excel VBA to create sophisticated data processing systems for engineering and science that process data from multiple data sources at once like workbooks, text files and databases
• How scientists and engineers can use advanced VBA to control advanced Excel features like the Data Validation feature, Conditional Formatting feature, Analysis ToolPak, Query Tables, Pivot Tables and Solver
• How engineers and scientists can use advanced VBA programming techniques to store program information in the Windows Registry
• How to control the Windows folder system using Excel VBA
• How to use advanced Excel VBA to manage large scale data storage in workbooks
• How engineers and scientists can use VBA to control advanced userforms containing ActiveX controls and graphics
• How to create advanced engineering and science dashboards in Excel VBA using charts, cells, pictures and drawing shapes
• How to use advanced Excel VBA to create event driven engineering and science programs that respond to key stroke, user action or time
• How scientists and engineers can create custom toolbars to run Excel VBA code and provide program navigation
• Advanced VBA programming strategies for communicating with engineering and science oriented databases using DAO, ADO and SQL
• How engineers and scientists can use advanced 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 (class discussion)

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 Advanced Excel VBA Language, Object and Error Trap Review for Engineers and Scientists

• How to use advanced Excel VBA to create sophisticated engineering and science tools when other languages fail
• A complete advanced Excel VBA language review for engineers and scientists including variables, data types, constants, arrays, operators, expressions, loops, logic decisions and calling conventions
• What are objects, properties and methods and how do they really 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
• How to build a class and create objects using Excel VBA class modules
• How engineers and scientists can enhance their advanced Excel VBA code by constructing error traps and logical error traps

Advanced Range Control and Worksheet Formatting for Scientists and Engineers

• How engineers and scientists can use the Cells, Range, Rows, Columns, CurrentRegion, SpecialCells, Find commands along with combined range structures like range(Cells(),Cells()) in VBA to control ranges
• How scientists and engineers can use advanced Excel VBA methods to automatically map resizing data ranges on worksheets
• How to use the Excel Find feature in advanced VBA engineering and science programs to locate cells without knowing their position
• How engineers and scientists can use advanced Excel VBA to control cell names and range names using the Names collection and For...Each Next loop, the key to uploading and downloading model/program information
• How to use advanced VBA to control worksheet formats including range number formats, fonts, alignments, borders and colors
• How to use Excel VBA to control Data Validation and Conditional Formatting, key engineering and science dashboard constructs

Advanced Workbook and Worksheet Control for Scientists and Engineers

• Advanced Excel VBA methods for adding, opening, saving, closing and naming workbooks
• How engineers and scientists can 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 Excel VBA Data Processing Techniques for Engineers and Scientists

• How to create proper engineering and science tables on the worksheet for large scale data storage
• How engineers and scientists can bring entire ranges into Excel VBA code as an array and process them internally for speed, great for processing large amounts of data
• How to use advanced Excel VBA methods to process engineering and science data on worksheets in multiple workbooks
• Advanced Excel VBA methods for processing data that includes dates, times and months

Day-2

Advanced Excel VBA Data Processing Techniques for Engineers and Scientists (Continued)

• How to use advanced Excel VBA methods to automatically construct summary worksheet tables using loops, logic and key worksheet functions
• How engineers and scientists can use loops, logic and the VBA date/time functions to automatically build various tables on worksheets
• How to use advanced Excel VBA to automatically create data processing formulas on the worksheet including array formulas
• Advanced engineering and science strategies for using VBA and worksheet functions in your code that scan for shifting table headers, do advanced logic lookups, detect if a value is present on a worksheet...

Windows Registry, Folder and File System Control for Engineers and Scientists Using Advanced Excel VBA

• Why engineers and scientists should store program information in the Windows Registry
• How to use advanced Excel VBA methods to store and retrieve program information from the Windows Registry
• 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 VBA to create massive 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 and folder names
• Advanced VBA strategies for opening and closing hundreds or even thousands of data workbooks in different folders

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 programs 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

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

• How to create userforms for engineering and science purposes in Excel VBA
• How to create ActiveX controls and graphics for engineering and science on userforms
• Advanced Excel VBA strategies for controlling userforms and their ActiveX controls in engineering and science programs
• How to use advanced Excel VBA methods to control combo boxes and list boxes on userforms
• How engineers and scientists can use ActiveX control events on userforms in VBA to create advanced engineering and science programs
• Advanced Excel VBA procedure design for launching userforms in various manners and presetting them with engineering and science information
• How to create userform based toolbars that control your Excel VBA programs and provide navigation for your projects

Day-3

Advanced Engineering and Science Dashboard Design on the Excel Worksheet

• How to control ActiveX controls on the worksheet using advanced Excel VBA methods
• 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 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 engineers and scientists can use advanced Excel VBA methods to design dashboards that morph based on user selection of different graphics
• 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

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

• How to control all pivot table elements and features using advanced 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

Advanced 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 advanced Excel VBA techniques and the worksheet
• How to get information from large text files (250,000 + rows of data) in to Excel using advanced VBA strategies

Using Advanced Excel VBA to Control Other Languages and Programs

• How to use the capabilities of VB.Net with VBA including compiling .Net COM libraries (optional, class vote)
• How to call FORTRAN DLL's from Excel VBA (optional, class vote)
• Overview of how to create report generation tools using Excel VBA, Word and PowerPoint (optional, class vote)
• How to control programs like Word, PowerPoint and Outlook using VBA (optional, class vote)
• How to access and control web pages (optional, class vote)

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 to call routines in written in FORTRAN, C and VB.NET from UDF's (optional, class vote)
• How design model interfaces that can utilize functions and also incorporate SOLVER  (optional, class vote)