Beginning Excel VBA Training for Engineers and Scientists

Our 2-day hands-on live beginning excel VBA training for scientists and engineers demonstrates how to use Excel VBA to control key Excel tools and capabilities that are useful in engineering and science. Our Excel VBA training starts off with a full review of the VBA Editor, procedure types and the core VBA language. Our training then shows how to build various calculations (expressions) in VBA using different operator types, VBA functions and Excel worksheet functions. Building and using logic is also covered. After the programming basics have been reviewed, how to identify and control key Excel elements like worksheets, workbooks, ranges, cells, charts and pivot tables using VBA is covered throughout the training.  It also discusses how to automate key Excel tasks like formatting worksheets, copying and pasting data, adding and deleting columns and rows, sorting and filtering, grouping, removing duplicates, finding data... After Excel control is discussed, it then shows you how to use loops and logic to create powerful data analysis tools that scan worksheets and workbooks in Excel VBA. In addition to data processing, it also discusses modeling basics including worksheet layout, information transfer and custom worksheet function assembly. Last basic report generation is discussed using VBA to control Word and PowerPoint. It is the beginning Excel VBA fundamentals that engineers and scientists need to know to build useful time saving tools in Excel. Taught by an engineer.

Our  beginning Excel VBA training is held Thousand Oaks, CA. Classes start at 8:30 AM PT and run to 4:30 PM PT.  Thousand Oaks is in the vicinity of Simi Valley, CA; Oxnard, CA; Ventura, CA; Santa Barbara, CA; Carpentaria, CA; Calabasas, CA; Agoura Hills, CA; Studio City, CA; Sherman Oaks, CA; Encino, CA; Woodland Hills, CA; Westwood, CA, Santa Paula, CA; Valencia, CA; Santa Monica, CA and Los Angeles, CA. Thousand Oaks is within driving range of Ventura county, Santa Barbara county, Los Angeles county and Orange county. Anything farther, it is recommended you stay the night before. We also broadcast our beginning Excel VBA training online live all over the US, Canada, Mexico and the Caribbean. We can also broadcast to your company over the web as an onsite anywhere in the world or travel to your company for an onsite. We conduct our in person onsites in the US, Canada and Europe.

Key Excel VBA skills learned:

• What is VBA and how can engineers and scientists use it?
• Review of the Excel VBA Editor and its features for scientists and engineers
• Review of the Excel VBA language including procedures, loops, logic, variables, functions, properties, methods and objects for engineers and scientists
• How to identify objects in Excel VBA code and track them
• Review of how to build a program that meshes the Excel environment with the VBA programming language and how scientists and engineers can exploit this pairing
• How to build engineering and science calculations (expressions) in Excel VBA
• How to use and run Excel worksheet functions and VBA functions in your VBA code
• Using Excel VBA to open, save and close workbooks
• Using Excel VBA to add, delete and rename worksheets
•  Using Excel VBA to track worksheet cells and ranges including resizing data
• How to use Excel VBA  to manage worksheet information and data including inserting, deleting, moving, and copying and pasting rows, columns and cells
•  How to use Excel VBA to control range and cell formatting features like fonts, colors, borders and number formats
• How to use VBA For...Next loops, Cells, logic and expressions to process data on worksheets
• How to use VBA For...Each Next loops to find and track workbooks and worksheet
• Using Excel VBA to control key Excel tools like sorting, filtering, pivot tables, text-to-columns, removing duplicates
• How to use Excel VBA to create powerful custom worksheet functions for modeling and data processing purposes
• How to use Excel VBA to construct standardized engineering and scientific charts
• How engineers and scientists can build powerful report generation tools using Excel, PowerPoint, Word and VBA

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

• Never programmed
• Just used the macro recorder
• Not programmed in over a year
• Programmed but have not used objects before or minimally
• Used Microsoft Excel and know its operational capabilities and its tools
• Built basic worksheet formulas (=A1+A2) and used worksheet functions like SUM

Day-1

Visual Basic Editor, Module and Procedure Review

• A complete review of the Excel VBA Editor including it's windows and how to insert, delete and control modules
• What is a VBA project
• What is a Module
• What is a statement and how are they used in VBA programming
• What is a Sub procedure and how is it used in Excel VBA programming
• What is a Function procedure and how is it used in Excel VBA programming
• How to use the VBA Editor's debugging tools to correct your engineering and science VBA programs (discussed throughout the class)

Excel VBA Language and Programming Review for Engineers and Scientists

• Core Excel VBA language review including how to use variables, data types, constants, arrays, operators, expressions, loops, logic decisions and calling (discussed throughout the class)
• What is a program, how to construct on Excel VBA
• How to translate your Excel tasks into a program that couples Excel and VBA
• What is an object and how are they used to control Excel and other programs
• What is a property and how are they used to control Excel and other programs
• What is a method and how are they used to control Excel and other programs
• What is an object expression and how to create one using the command.command.command structures in Excel VBA
• How to use the Set statement to track objects in your code
• What is logic and how is it used in VBA programming
• Complete review of Excel VBA program logic (If, Select Case...) and how engineers and scientists can translate their logic tasks into VBA computer code

Creating Engineering and Science Calculations in Excel VBA (Expression and Function Overview)

• How to construct engineering and science equations in Excel VBA (expression overview)
• Definition of a numeric expression, string expression, relational expression and logic expression
• How to use relational operators like <, >, <=, >=,... to build VBA expressions that logic (IF and Select Case) can use
• How to use the And, Or and Not operators in Excel VBA to make multiple decisions in your VBA logic
• Using object properties and methods to create the conditions for your logic in VBA
• How to run VBA functions and Excel worksheet functions in VBA code, the key to building powerful engineering and science tools quickly
• Review of how to use Key VBA and Excel worksheet functions that analyze dates and times (DateAdd, DateDiff, EOMONTH, Month, Day, Year, Format,...)
• Review of how to use Key VBA and Excel worksheet functions that perform math and statistical calculations (Tan, Cos, Sin, Sqr, SUMIF, SUM, COUNTIFS, SUMIFS, AVERAGE, DEGRESS, LOG...)
• Review of how to use key Excel worksheet functions that perform data lookup operations (MATCH, VLOOKUP...)
• Review of how to use key VBA and Excel functions that perform operations on text (Left, Right, Mid, Len, Replace, CLEAN, TRIM, Instr...)

Creating and Using Arrays in VBA

• What is an array and how are they used in Excel VBA engineering and science programs
• How to declare arrays in Excel VBA
• Review of array notation, bounds and how to read and write to an array
• How to use Excel and VBA functions in your VBA code that return arrays

Workbook and Worksheet Management Using Excel VBA

• The strategy behind detaching your data from your code workbook in Excel, key to developing advanced engineering and science tools for modeling and data analysis
• Using Excel VBA to open, close and save workbooks
• Selecting workbook files to open and close in VBA using Excel's Open and SaveAs dialog boxes
• Using the Set statement to track workbooks in your VBA code
• How to use Excel VBA to add, move and rename worksheets
• How to name your workbooks and worksheets for modeling and data tracking purposes

  Using Excel VBA to Manage Ranges and Cells

• Identifying cells and ranges in your VBA code using (Range, Cells, Columns, Rows...)
• How to use Excel VBA and various functions to track resizing data on the worksheet (CurrentRegion, UsedRange, COUNTA...)
• How to use Set statements to track worksheet ranges and cells in VBA
• Locating engineering and science data on the worksheet using Find and various Excel worksheet functions in your VBA code
• Using Excel VBA to copy and paste engineering and science worksheet data (rows, columns, entire worksheets...)
• Using Excel VBA to read worksheet data, perform calculations and output the data back to a worksheet
• Using Excel VBA to add and edit worksheet cell formulas and comments
• How to use VBA to control cell and range formats like number, fonts, colors, alignments and borders
• Using Excel VBA to control cell and range names, the key to developing sophisticated engineering and science tools that upload and download information
• Using Excel VBA to control engineering and science data on a worksheet including inserting, deleting and moving cells, rows and columns
• How to use various Range, Font and Interior properties like Bold, Color, Address, Column, Row... to build relational expressions (conditions) for your VBA logic

Day-2

Analyzing Engineering / Science Worksheet Data and Arrays Using VBA For...Next loops

• Review of how to organize engineering and science data on the worksheet so it can be easily read
• Review of a VBA For...Next loop and how to use them in your VBA code
• Building and using counters inside of VBA For...Next loops
• The strategy behind using VBA For...Next loops to analyze engineering and science data on the worksheet
• How to couple the Cells property with the For...Next loop to process worksheet data
• How to write logic and expressions that operate inside of a VBA For...Next loop
• Using logic and VBA For...Next loops to fill out technical forms and tables on the worksheet
• How to add/subtract rows and columns on a report worksheet while looping
• Using a VBA For...Next loop to read and write to an array

Scanning for Engineering / Science  Data in Workbooks and Worksheets Using VBA For...Each Next loops

• Overview of how to use the VBA For...Each Next loop to loop through object collections
• Strategies for locating information on worksheets and in workbooks using For...Each Next loops
• How to combine VBA For...Each Next loops, logic and functions to search for a specific workbook
• How to combine VBA For...Each Next loops, logic, functions and key Excel methods to search for worksheets within a workbook
• Using VBA Set statements to track workbooks and worksheets inside of VBA For...Each Next loops
• How to use Is Nothing to test to see if an object was stored using Set

Managing Worksheet Models and Creating Custom Worksheet Functions (UDF)

• How to create a custom worksheet function in VBA
• How to design the argument list of a custom worksheet function
• How to read range and cell information inside of a custom worksheet functions
• Using loops, logic, variables and Excel/VBA functions in custom worksheet functions
• Using Excel VBA to mange model information
• Worksheet layout design for models (Input/Output areas, arrays,...)

Using Excel VBA to Control  Key Engineering and Science Tools in Excel

• How to use VBA to control Excel's Autofilter and Advanced filter for engineering and science purposes
• How to use Excel VBA to control the Sort tool, Remove Duplicates tool and Text-to-Columns tool
• How to use Excel VBA to control the Grouping tool

Using Excel VBA to Create Charts and Pivot Tables

• How to use beginning Excel VBA to create engineering and science charts from worksheet data
• How to use Excel VBA to update a chart's source data without rebuilding it
• Using Excel VBA to format engineering and science charts
• Building pivot tables using Excel VBA and the Macro Recorder

How to Create Simple Engineering and Science Report Tools Using Excel, VBA, PowerPoint and Word

• Overview of combining Excel, VBA, Word and PowerPoint to form report generation tools
• How to use CreateObject and Tools/References to control other programs
• How to use VBA to open PowerPoint and Word files
• Controlling basic Word and PowerPoint elements using VBA
• Transferring data, ranges and charts between Excel, PowerPoint and Word

• 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
  • Self Study.For self-study, purchase Beginning Excel VBA for Engineers and Scientists. For company onsite information, please contact us 1.866.924.6244
• Additional Training:
  • Intermediate Excel VBA
  • Advanced Excel VBA
  • Microsoft Excel Dashboards

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