Advanced Excel VBA Training Outline for Engineers and Scientists

Advanced Excel VBA Skills You Learn

• How to use advanced Excel VBA language features

• Advanced range, worksheet and workbook control using VBA

• Advanced use of worksheet functions and VBA functions in your code for tasks like data processing, information management, tracking time, dates, file names...

• How to command the Windows folder and file system and the Windows Registry with VBA

• Advanced data processing techniques for engineers and scientists using Excel VBA including scanning unknown multiple worksheets, workbooks and folders for data

• Advanced Microsoft Excel VBA programming techniques for creating event driven engineering and scientific applications in Microsoft Excel including running procedures at a given time

• Advanced interface construction using ActiveX controls, userforms and VBA

• Advanced dashboard construction for modeling and data analysis purposes

• How to integrate ActiveX controls and VBA with existing formula based models

• How to create Excel toolbars and make buttons on the ribbon

• How to construct advanced custom worksheet functions (UDF) using VBA

• Advanced VBA programming strategies for communicating with engineering and science oriented databases

• Advanced Excel VBA programming tips for communicating with FORTRAN, VB DLLs and PERL

Included in Our Beginning Excel VBA Workshop

• Free Repeats

• 1800+ page manual filled with key Excel strategies, examples and tips

• Hands-on training

• Personal questions answered

Audience Prerequisites

• Excel users with a good understanding of Microsoft Excel and basic knowledge of the VBA language. If you do not have any knowledge, we will ramp you up quickly as long as you have programmed before.

• Our workshop is intended for all types of engineering and science professionals.

Workshop Training Choices

Available:  Public, Webcast, Onsite, Self-Study Manual

• Public, Webcast and Self-Study signup buttons below

• For onsite information or if you have any questions, please contact us 1.805.498.7162 or 1.805.558.9277.

Advanced Microsoft Excel VBA Training Focus

The Microsoft Excel VBA has the capability to construct sophisticated engineering and scientific tools that integrate seamlessly with the Microsoft Excel environment. Using advanced VBA programming strategies and Excel construction, sophisticated applications can be constructed in Microsoft Excel that command, organize and analyze engineering and scientific data, automatically chart engineering and scientific data, generate reports, talk to other applications integrating their data, perform engineering and scientific modeling, integrate FORTRAN routines... the list is endless. Many companies are turning to constructing technical applications in Excel due to the lower cost, lower development time and because of Excel's exceptional abilities. This workshop discusses the advanced uses of Excel VBA in the engineering and science disciplines.

Advanced Excel VBA Workshop Outline

In this 3-day "hands-on" workshop you will learn:

Day-1

• A complete VBA language review including the advanced use of loops, logic, variables, arrays and expressions in your code

• A complete review of what a program object, property and method are and how to use them to control Excel, your projects and other technical programs

• How to create objects, properties and methods using class modules in VBA

• Advanced control of worksheet ranges, worksheets and workbooks using Excel VBA

• How to design VBA code to adapt to changing data size and model parameter information

• How to extract and fill technical form and table data using VBA

• How to use advanced worksheet functions and VBA functions in your code for tasks like data processing, information management, tracking time, dates, file names...

• Advanced VBA techniques for automatically finding information on worksheets for data processing and modeling purposes

• How to command the Windows folder and file system to create, copy, move and delete items using VBA

• How to command the Windows registry to store and retrieve program information using VBA

• How to scan and process information and data in multiple worksheets, workbooks and folders

• Advanced looping and object variable techniques for accessing and tracking massive amounts of data

• How to control advanced technical charts and pivot tables with VBA

Day-2

• How to create events for action driven applications in Microsoft Excel including running VBA code when a workbook opens, closes and when a value is entered on a worksheet

• How to set procedures to run at a specific time and assign procedures to run when certain hot keys are pressed

• How to develop engineering and science interfaces on the worksheet using ActiveX controls and Excel VBA

• Overview of using userforms to create engineering and science interfaces

• Advanced Excel VBA userform design tips

• How to build controls and graphics on userforms that create a high tech look and feel

• How to command userforms and their ActiveX controls with VBA

• How to load and utilize combo boxes and listboxes on userforms

• How to create toolbars to control your technical applications and provide navigation for them

• How to control Excel’s ribbon system with VBA

 Day-3

• How to read technical text files using VBA. Large text file importation (i.e. 900,000 rows, 100 columns) strategies discussed

• How to combine shapes, pictures, userforms, ActiveX controls, worksheets and VBA to create advanced technical dashboards in Excel

• How to integrate ActiveX controls and VBA with existing formula based models

• How to create advanced custom worksheet functions for modeling and data analysis purposes

• Advanced setup and control of large models and their formulas using Excel VBA

• Advanced command of Microsoft Word and PowerPoint using Excel VBA (report generation tools)

• How to command external databases, workbooks and text files using DAO, ADO / OLE DB in VBA

• Mixed language programming discussion using VBA, FORTRAN, C and VB .Net (Optional based on attendees needs)

Signup, Pricing and Self-Study

The Public and Webcast buttons below will lead you to our training schedule page listing all possible dates, prices and signups for those training categories. If you are unsure what a webcast is click here. The Self-Study button will lead you to our manual page where you can get the manual pricing and the manual outline. The manual comes with the class.

 

 

 

Full VBA Review; Complete Object, Property and Method Discussion; Class Module Construction

How to Locate, Track, Extract and Fill Data in Advanced Forms and System Models Using VBA

Advanced Range, Worksheet, Workbook Control; Multi Workbook/Worksheet Data Processing; Windows Folder/File Management Using VBA

Advanced Multi Chart Construction, Microsoft PowerPoint Control and Microsoft Word Control Using VBA

Advanced Dashboard Construction Using Userforms, Shapes, Worksheets, ActiveX Controls, Events and VBA

Advanced Model Construction Techniques Using ActiveX Controls, VBA, Pictures and Custom User Defined Worksheet functions

How to Mix Formula Based Analysis Tools with VBA, Userforms, Shapes, Worksheets and ActiveX Controls

Advanced VBA Techniques for Downloading Data from the Web and Databases