Interfacing Excel VBA with Fortran for Engineers and Scientists Training

Our 3-day hands-on Microsoft Excel VBA Fortran training shows you the essential interface, data processing, and coding strategies of how to combine Excel VBA with Fortran to create powerful tools. Key topics include:

• Why run Fortran from Excel VBA? More >
• Excel VBA language review emphasizing Fortran DLL communication More >
• Must know VBA functions and Excel worksheet functions used in Excel VBA / Fortran DLL apps More >
• Controlling and looping through workbooks, worksheets, and folders for Fortran data  More >
• Using Excel VBA to process input / output Fortran data on the worksheet More >
• Calling Fortran DLLs from Excel VBA procedures More >
• Designing a Fortran Routine to be called from Excel VBA More >
• Passing different argument types from Excel VBA to a Fortran DLL including strings and arrays More >
• Passing data from Fortran back to Excel VBA More >
• Passing data to Fortran from Excel VBA using text files More >
• Compiling and debugging a Fortran DLL More >
• Creating program interfaces (UI's) for your Excel VBA / Fortran DLL apps More >
• Creating engineering and science charts with Excel VBA to display your Fortran data More >
• Using VBA to create worksheet tables, formulas, and PivotTables to display your Fortran data More >
• Visualizing your Fortran DLL output using Excel's drawing shapes More >

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 how 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? Back >

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

Excel VBA Language Review Emphasizing Fortran DLL Communication Back >

• 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
• Creating and work with VBA arrays; automatically dimensioning an array with the ReDim statement; clearing arrays; tricks to increase speed
• Creating a user defined type that can be passed to a derived data type (structure) in a Fortran DLL

Must Know VBA Functions and Excel Worksheet Functions Used in Excel VBA / Fortran DLL Apps Back >

• Overview of how to run VBA functions and Excel worksheet functions in VBA
• Using the C... VBA functions to convert data types, using data type coercion in VBA
• Performing complex and matrix operations in VBA using Excel worksheet functions
• Analyzing dates and times using the Excel worksheet and VBA Date / Time functions
• Using Key Excel worksheet functions like MATCH, VLOOKUP, COUNTIF, COUNTA, SUMIF, SUMIFS, COUNTIFS, EVEN, and ODD in your VBA code
• Figuring out the bounds of VBA arrays using the UBOUND and LBOUND VBA functions
• Using key VBA functions like Split, Instr, Len, Left, Right, Replace, Mid, Chr, Format... and the & concatenation operator on VBA strings

Controlling and Looping Through Workbooks, Worksheets, and Folders for Fortran Data Back >

• Storing Fortran data in multiple workbooks; the key to developing an effective data storage system in Excel
• Opening, saving, adding, and closing workbooks with Excel VBA
• Using the Set statement to track workbooks and worksheets in Excel VBA
• Using Excel VBA to add, move, delete, and rename worksheets
• Creating, deleting, moving, and coping folders and files with Excel VBA
• Scanning folders for workbook based Fortran data using VBA For...Each Next loops, the FileSystemObject, and logic
• Looping through open workbooks and worksheets to find Fortran data and model information using VBA For...Each Next loops and logic

Using Excel VBA to Process Input / Output Fortran Data on the Worksheet Back >

• The Excel worksheet, the perfect place to pre/post process Fortran data
• What is a range and how to control it with VBA using Cells, Range, Rows, Columns, CurrentRegion, ActiveCell, Find,...?
• Using Excel VBA to automatically lock onto worksheet table data that changes size, shape, and position
• Copying and pasting worksheet tables and sub sections using Excel VBA
• Finding worksheet table headers using Find, Offset, and Worksheet functions like MATCH and COUNTIF in Excel VBA
• Using Excel VBA to reorganize worksheet table data including inserting/deleting/moving rows, columns, and cells
• Converting Excel ranges and sub ranges into VBA arrays that can be passed to Fortran DLL routines
• Using Excel VBA to read/set cell and range names and loop through them searching for specific cell values
• Controlling Excel's data features like AutoFilter, Advanced Filter, Sort, Remove Duplicates, and Text-to-Columns with VBA
• How to tie Excel's data features into Excel VBA using loops that process massive quantities of worksheet data

Day-2

Calling Fortran DLLs from Excel VBA Procedures Back >

• Creating a Declare statement in Excel VBA to call a Fortran DLL Subroutine or Function routine
• Calling a Fortran DLL Subroutine from Excel VBA
• Calling a Fortran DLL Function routine from Excel VBA
• Using 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

Designing a Fortran Routine to be Called from Excel VBA Back >

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

Passing Different Argument Types from Excel VBA to a Fortran DLL Including Strings Back >

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

Passing Data from Fortran Back to Excel VBA Back >

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

Passing Data to Fortran from Excel VBA Using Text Files Back >

• Assembling text files on the Excel VBA side using FileSystemObject and TextStream objects
•  Quickly assembling strings on the Excel side in VBA using key functions and concatenation strategies
• Designing Fortran routines that quickly read text files into variables and arrays
• Using VBA, ADO, and SQL to query large Fortran text files and return the result to a worksheet

Compiling and Debugging a Fortran DLL Back >

• Compiling a Fortran DLL in Visual Studio using Intel Visual Fortran
• Debugging a Fortran DLL from Fortran
• Debugging a Fortran DLL from Excel VBA
• How Excel VBA reacts when it encounters an error in a Fortran DLL

Day-3

Creating Program Interfaces (UI's) for Your Excel VBA / Fortran DLL Apps Back >

• When to design program interfaces on worksheets vs. VBA userforms
• Userform design and coding strategies in Excel VBA
• When to use ActiveX controls to design program interfaces versus using Excel's built-in features like cells, Data Validation, Conditional Formatting, Excel tables,...
• Using Data Validation and Conditional Formatting to quickly create program interfaces that control your Excel VBA / Fortran programs
• Using Excel VBA to control ActiveX controls like list boxes, combo boxes, check boxes, option buttons,... on worksheets and on VBA userforms
• Creating folder and file pickers using the Application object in Excel VBA
• Using the Windows Registry to store your program data

Creating Engineering and Science Charts with Excel VBA to Display Your Fortran Data Back >

• Automating chart construction for Fortran DLL data using Excel VBA
• Using VBA arrays, not ranges, to build charts; perfect for emailing reports
•  Refreshing chart data with Excel VBA, no rebuild
• Using VBA to position multiple charts on a worksheet for reporting purposes
• Formatting charts (axes, data points, labels, positioning labels,...etc) with Excel VBA
• Creating limit lines for charts using Excel VBA
• Using VBA to color chart data points that are below or above specified limits

Using VBA to Create Worksheet Tables, Formulas, and PivotTables to Display Your Fortran Data Back >

• Filling in various worksheet table configurations using Excel VBA
• Using Excel VBA to create multiple worksheets and separate report data out to them
• Building formulas and determining their argument ranges and cell references automatically using Excel VBA
• Quickly formatting worksheet tables using Excel VBA
• Automating PivotTables, Excel Tables, and their features using Excel VBA

Visualizing Your Fortran DLL Output Using Excel's Drawing Shapes Back >

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