Laser Focus World, April 2014
This page supplements
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
newbies should review
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.
Color tolerancing files:
SpectralCalculator5nm.xls to compute CIE color values from multiple spectra, in this case
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:
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:
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:
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:
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
GetFMSpectra.xlsm copies spectra from FileMaker database Spectra.fmp12 and
computes coating yields.
FileMaker Pro version 12-16 is required; a
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
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
FilmStar and Excel
Extracting illuminant data,
Importing index tables,
Tolerancing with index variations
Multiple spectra: %R, %T vs.
temperature, ARTA (Absolute
Reflectance/Transmittance Analyzer), Saving multiple
spectra, Reflectance vs. wavelength and
interoperability: Excel VBA, FilmStar