Calling Fortran DLLs from Excel VBA
for Engineers and Scientists

Our 3-day "hands-on" Excel VBA / Fortran training shows you how to run Fortran code from Excel VBA allowing you to take advantage of the strengths of both environments. First off, our training discusses how to use Excel and VBA together to collect and process the data to be used in your Fortran DLL routines. Next our training discusses in depth how to design Fortran routines and VBA procedures that pass information between them and the detailed code that makes this happen. Last our training discusses program interface construction and Fortran data visualization using worksheets, charts, drawing shapes,...etc. Our training also discusses how to create Fortran DLLs from legacy Fortran source code, compile a Fortran DLL using Visual Studio and debug a Fortran DLL.

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

• Operated Excel's basic data processing tools like AutoFilter, Text To Columns, Remove Duplicates, Sort
• Used worksheet functions before and know they operate
• Copied and pasted worksheet data
• Typed data in cells and built basic formulas
• Created charts and used drawing shapes
• Used VBA before, do not have to be an expert
• Been exposed to the concepts of basic programming like loops, logic and variables
• Used Fortran before or have been exposed to it. Basic syntax will be reviewed

Day-1

Why Run Fortran from Excel VBA?

• The uses of User Defined Worksheet Functions (UDFs) and Sub procedures in Excel VBA
• Running Fortran routines from UDFs; powerful modeling with design optimization capabilities, powerful data analysis capabilities, arrays
• Running Fortran routines from Sub procedures; event driven application development
• Running Fortran routines from Property procedures; object driven application development
• The powerful reporting capabilities and data visualization features of Excel

Key Excel VBA Language Review Emphasizing Fortran DLL Communication

• How to use the Visual Basic Editor, its debugging tools and module types in Fortran DLL construction
• A review of the Excel VBA language syntax including objects, properties, methods, variables, data types, constants, arrays, operators, expressions, loops and logic decisions
• Overview of procedure argument design in Excel VBA; using by reference and by value to pass arguments
• Overview of Excel VBA data types that are used to pass argument data to a Fortran DLL routine
• How to create and work with VBA arrays; automatically dimensioning an array with the ReDim statement; clearing arrays; tricks to increase speed
• How to create a user defined type that can be passed to a derived data type (structure) in a Fortran DLL

Key VBA Functions and Excel Worksheet Functions Used in Excel VBA / Fortran DLL Programs

• Overview of how to run VBA functions and Excel worksheet functions in VBA, calling conventions
• How to use the C... VBA functions to convert data types, using data type coercion in VBA
• How to use the complex and matrix Excel worksheet functions in you VBA code
• How to use the Excel worksheet and VBA Date and Time functions to analyze dates and times
• How to use key Excel worksheet functions like MATCH, VLOOKUP, COUNTIF, COUNTA, SUMIF, SUMIFS, COUNTIFS, EVEN, ODD in your VBA code
• How to use UBOUND and LBOUND to figure out the bounds of Excel and VBA arrays, alternatives to these functions
• How to use key VBA functions like Split, Instr, Len, Left, Right, Replace, Mid, Chr, Format... and the & concatenation operator on VBA strings

How to Use Excel VBA to Control and Loop Through Workbooks and Worksheets That Store Data

• How to use Excel VBA to open, save, add and close workbooks
• How to use the Set statement to track your workbooks and worksheets in VBA
• How to use Excel VBA to add, move and rename worksheets
• How to use the VBA For...Each Next loop and other key objects to loop through folders looking for workbooks, the key to developing an effective data storage system in Excel
• How to use the VBA For...Each Next loop to loop through worksheets to find data and model information
• How these loops will aid in gathering data for Fortran DLLs

How to Use Excel VBA to Process Worksheet Data for Fortran DLL Use

• What is a range and how to control it with VBA using Cells, Range, Rows, Columns, CurrentRegion, ActiveCell, Find,...
• How to use Excel VBA to automatically lock onto worksheet table data that changes size, shape and position
• How to use Excel VBA to copy and paste worksheet tables and sub sections of tables
• How to use Excel VBA to find worksheet table headers using Find, Offset and Worksheet functions like MATCH and COUNTIF
• How to use Excel VBA to reorganize worksheet table data including inserting/deleting rows and columns and moving cells
• How to use For...Next loops and logic to process worksheet table data and convert it into arrays that can be passed to Fortran DLL routines
• How to convert Excel ranges and sub ranges into VBA arrays that can be passed to Fortran DLL routines
• How to use Excel VBA to read/set cell and range names and loop through them searching for specific cell values
• How to use VBA to control Excel's data features like AutoFilter, Advanced Filter, Sort feature, Remove Duplicates feature and Text-to-Columns feature
• How to tie these data features into Excel VBA programs using loops that process massive quantities of data

Day-2

How to Call Fortran DLLs from VBA Procedures

• How to create a Declare statement in Excel VBA to call a Fortran DLL Subroutine or Function routine
• How to call a Fortran DLL Subroutine from Excel VBA
• How to call a Fortran DLL Function routine from Excel VBA
• How to use argument lists to pass data between Excel VBA and Fortran
• UDF design issues for calling Fortran DLL routines
• Sub procedure design issues for calling Fortran DLL routines
• Property procedure design issues for Calling Fortran DLL routines

How to Design a Fortran Routine to be Called from Excel VBA

• How to design compiler directives for your Fortran DLL routines
• How to use the STDCALL,ALIAS and DLLEXPORT attributes to define your Fortran DLL entry points
• How to use the REFERENCE and VALUE attributes correctly in your compiler directive to specify argument passing convention
• How to data type Fortran dummy arguments to match their VBA counter parts
• How to design Fortran dummy arguments to accept VBA numbers
• How to design Fortran dummy arguments to accept VBA strings with variable lengths
• How to design Fortran dummy arguments to accept string arrays
• How to design Fortran dummy arguments to accept VBA arrays as explicit arrays, adjustable arrays and assumed size arrays
• How to design a Fortran dummy argument to accept a VBA user defined type including strings

How to Pass Different Argument Types from Excel VBA to a Fortran DLL

• How to pass a number including Booleans from Excel VBA to a Fortran DLL routine
• How to pass a numeric array from Excel VBA to a Fortran DLL routine
• How to pass a string from Excel VBA to a Fortran DLL routine
• How to pass a string array from Excel VBA to a Fortran DLL routine
• How to pass a user defined type from Excel VBA to a Fortran DLL routine
• How to pass a complex number from Excel VBA to a Fortran DLL routine

How to Pass Data from Fortran Back to Excel VBA

• How to pass a number including Booleans back to Excel VBA from Fortran
• How to pass a number array back to Excel VBA from Fortran
• How to pass a string and string array back to Excel VBA from Fortran
• How to pass a Fortran derived data type back to Excel VBA from Fortran

How to Pass Data to Fortran from Excel VBA Using Text Files

• How to assemble text files on the Excel VBA side using FileSystemObject and TextStream objects
• How to quickly assemble strings on the Excel side using key functions and concatenation strategies
• How design Fortran routines that quickly read text files into variables and arrays

How to Compile and Debug a Fortran DLL

• How to compile a Fortran DLL in Visual Studio using Lahey and Intel Visual Fortran
• How to debug a Fortran DLL from Excel VBA
• How Excel VBA reacts when it encounters an error in a Fortran DLL

Day-3

How to Create Program Interfaces to Run Your Excel VBA / Fortran DLL Programs

• When to design program interfaces on worksheets and on VBA userforms
• When to use ActiveX controls to design program interfaces versus using Excel's built-in features like cells, data validation, conditional formatting, Excel tables,...
• How to use data validation and conditional formatting to quickly create program interfaces that control your Excel VBA / Fortran programs
• How to use Excel VBA to control ActiveX controls like list boxes, combo boxes, check boxes, option buttons,... on worksheets and on userforms
• How to create program folder control using the Application object, FileDialog object
• How to use the Windows Registry to store your program data

How to Use Excel VBA to Create Technical Charts for Fortran DLL Data

• How to use Excel VBA to create chart sheets and embedded charts from data returned by Fortran DLLs
• How to use Excel VBA to generate charts that are based off of arrays and not connected to ranges, perfect for reports
• How to use Excel VBA to update a chart data without rebuilding it
• How to use Excel VBA to position charts on worksheets generating reports
• How to use For...Each Next loops and logic to loop through chart element points and series
• How to use Excel VBA to format charts (axes, data points, labels, positioning labels,...etc)

How to Create Worksheet Reports to Display Your Fortran DLL Data

• How to fill in various worksheet table configurations using Excel VBA
• How to use Excel VBA to create multiple worksheets and break report data out to them
• How to build formulas and determine their ranges automatically using Excel VBA
• How to quickly format worksheet tables using Excel VBA
• How to build Pivot Tables and Excel Tables using Excel VBA

How to Visualize Your Fortran DLL Data Using Excel VBA and Drawing Shapes

• How to use Excel VBA to command primary shapes like rectangles, circles, lines and freeforms to form technical drawings in Excel
• How to format drawing shapes and alter their text using Excel VBA
• How to use worksheet cells to help position drawing shapes
• Basics strategies for using Excel VBA to align dimension lines and values around drawing shapes