Laser Focus World, April 2014

This page supplements Excel extends optical coating software capabilities. Assuming you have Excel, all examples can be freely tested.

New to Excel or VBA? A search for 'Excel VBA Tutorial' leads to a number of pages, such as Excel Easy or Excel VBA Tutorial for Beginners. For courses with instructor, search 'Excel VBA classes New Jersey', etc. Online courses are also available. Excel newbies should review introductory tutorials before tackling VBA. Examples discussed below are the subject of Dave Taddeo's YouTube video FilmStar with Excel.

Examples may require that you change Excel security settings. By default Microsoft disables the Developer ribbon. Click File.. Options Customize Ribbon and add the Developer tab. You also need to adjust Trust Center settings by enabling macros and Trust access to the VBA project object model.  Please contact author Fred Goldstein if you have questions or need help.

 NEW Sep 2017 ...All FTG programs directly read and write Excel XLS/XLSX files. Excel is not required, thereby simplifying operations and enhancing security. Click here and here to learn more.

Color tolerancing files:  RandColor.xlsm   SpectralCalculator5nm.xls    ExcelRand.bas

RandColor.xlsm utilizes Bruce Lindbloom's SpectralCalculator5nm.xls to compute CIE color values from multiple spectra, in this case spectra generated from a tolerancing model in which layers are randomly varied to simulate manufacturing errors. Ultimately we want to relate layer errors to color variations.  Our thanks to Bruce for making his work freely available. 19 Jul 15: RandColor.xlsm replaces RandColor.xls; ExcelRand.bas updated accordingly.

RandColor.xlsm transfers spectra to SpectralCalculator5nm.xls and returns
color values. Both workbooks must be open within the same instance of Excel.

RandColor.xlsm's 10 spectra can be replaced with any number of columns of reflectance or transmittance values with wavelength range 380-780 x 5 nm. Our spectra were generated with FilmStar BASIC (FSBasic) program ExcelRand.bas which can be tested in the FilmStar Free Version.

SpectralCalculator5nm.xls - Yellow denotes data input cells; blue cells
contain calculated results. The workbook cannot otherwise be modified.

  Color map file:  Colors.xlsm  (Full Version)        ColorsF.xlsm (Free Version)

Setup tab specifies calculation range

Colors.xlsm (Excel 2007 or newer required) displays RGB values calculated in the background by FilmStar. This example can be tested in the FilmStar Free Version. Since Design Wavelength is varied, designs must utilize optical thickness. (Instructive exercise: modify the workbook for designs in physical thickness.) Color maps are also useful for analyzing the effects of layer errors.

Excel 2007 or newer is required as older Excel
versions do not support the full 16M color range.

Optical glass files:  OharaGlass.xls (2012)   SchottGlass.xls (2014)

Manufacturers who provide data in Excel format enable us to convert it to a form suitable for optical coating software. Fortunately Schott and Ohara have not disabled the ability to supplement their Excel workbooks, and it was straightforward to add VBA code for selecting and copying to the clipboard.

'Export' worksheet added to original Schott workbook

Disclaimer: These workbooks are provided without warranty. Users should verify results and compare with published data. Please notify FTG Software of any discrepancies. Original data: Ohara, Schott. FilmStar users can simplify data transfer by running SchottGlass.bas or OharaGlass.bas from program INDEX. Note: Issues were identified and corrected 06 May 2014. Please download and replace previous versions.

QA database files: GetFMSpectra.xlsm    Spectra.fmp12

Database software provides means to save and recall information about coating runs. That information includes spectral data. As databases are not intended for engineering calculations, we apply Microsoft Excel to the task of automatically extracting spectra for further analysis.

Excel workbook GetFMSpectra.xlsm copies spectra from FileMaker database Spectra.fmp12 and computes coating yields. FileMaker Pro version 12-16 is required; a 15 day FREE TRIAL is available. FileMaker Pro Advanced (not in free trial) adds options such as the ability to change menus. A FileMaker administrator is typically the only user with the Advanced edition.

When opening Spectra.fmp12 you are asked for Account Name [Admin (full access), User (user access)] and Password [leave blank]. Signing on as Guest grants browsing access only. Note differences in menus, with a Guest having no ability to alter the database. A User (QA technician) can create new records but not delete old ones, etc. These security features reduce the possibility of QA catastrophes.

Click for full size image

Setup options include delay (typically 0.1 sec), and pass/fail specs.
You may need to increase delay on slow computers.

With FileMaker open select the records you wish to analyze and click Load Spectra. You might, for example, want to test whether coating performance varies with technician. How does a tighter tolerance affect yield? Why FileMaker Pro? 1. Many thousands of spectra stored in a single file, 2. Easy to use, database expertise not needed, 3. Compatible with corporate 'enterprise' databases like Oracle, 4. Integration with thin film design software for analysis and reverse-engineering, 5. Supported by FTG Software.

Cited and related Excel links

General discussion: FilmStar and Excel

: Extracting illuminant data, Importing index tables, Intellemetrics FilmMaker

Coating designs: Rugate designs, Tolerancing with index variations

Multiple spectra: %R, %T vs. temperature, ARTA (Absolute Reflectance/Transmittance Analyzer), Saving multiple spectra, Reflectance vs. wavelength and layer thickness.

FilmStar BASIC-Excel interoperability: Excel VBA, FilmStar BASIC Objects


Copyright 2018 FTG Software Associates
Last updated on January 09, 2018