Don't miss Excel
extends optical coating software capabilities
in Laser Focus World April 2014.
Click here to read the article;
to download examples (Reference 1 in the article).
Microsoft Excel is the most widely
utilized calculation/data analysis software. Virtually all FilmStar users have Excel. But do they get maximum benefit?
To inspire users to learn more
about Excel and especially Excel VBA (Visual Basic for Applications), we present ways in which
Excel interfaces with FilmStar.
1. Save Spectra as .xls
FilmStar spectra formats include .csv, .dx (J-CAMP), .spc (Galactic GRAMS) and .xls (Excel). While .dx and .spc are
somewhat standard in spectroscopy, these formats are not generally familiar to
FilmStar DESIGN and MEASURE save directly in binary .xls format and do
not require Excel on the computer.
Excel .csv format varies with
international Windows settings, so users should be wary of exporting in
As illustrated by the DESIGN Optimization Targets dialog, many
FilmStar spreadsheet objects are Excel
copy-and-paste-compatible. Data can also be pasted or copied from the
- DESIGN..Import theoretical optimization targets
- DESIGN.. Import/export calculated spectra
- DESIGN..Rugated index profiles and thin film
- INDEX..Import/export n,k tables, import
measured spectra for n,k calculations
- MONITOR..Import/export the monitor Worksheet
- MEASURE/Scantraq..Export measured spectra, useful for
combining multiple measurements
SigmaPlot are also
Excel-compatible. This greatly extends the usefulness of FilmStar's
User-friendly glass selector
3. Import Manufacturer's Data
Since Excel is so
universal, manufacturers typically supply data in Excel format (*.xls).
glass data. To make these workbooks easier to use we added an Export
sheet, a Select Glass dialog, and VBA macros in
SchottGlass.xls and OharaGlass.xls. These files, along with supporting
INDEX BASIC code are included in the FilmStar installer.
n values are computed
with a Sellmeier equation, while k values are derived from internal transmittance.
W,n,k tables are automatically imported into FilmStar:
The end result is that
Schott and Ohara glasses appear to be built into FilmStar.
4. Translate Designs
Designs from another program are pasted into Matl
and Thick columns. VBA function getSymbol helps
convert to FilmStar DESIGN format. Export from FilmStar would be very similar.
Function getSymbol$(ByVal Matl$)
Dim i%, t$
For i = 1 To 50
t$ = Ucase(Sh1.Cells(i + 10, 5))
If t$ = "" Then
getSymbol = "": Exit Function
ElseIf UCase(Matl$)=t$ Then
getSymbol = Sh1.Cells(i+10, 6)
Calculate in Excel
There is often need to compute specialized and
possibly proprietary values from
theoretical or measured spectra. While this might be accomplished in
FilmStar BASIC and/or the
FilmStar Workbook, Excel offers further
One can always utilize Excel by manually pasting spectra, but
this approach may not be suitable in production. It is easy to paste
data into the wrong columns and there are potential security issues.
Fortunately Excel is
(component object model) compatible and can be utilized in the
background. Since FilmStar (Scantraq) is also COM compatible, it can send and
receive Excel commands and data.
This is illustrated with the instructive 'Calculate in
Excel' BASIC macro in ..\Winfilm\Basic32. A user need only click a
button to run a hidden copy of Excel which computes requested
This will not work with the pre-installed
'Click-to-Run' version of Office 2010.
Click here for further
More typically previously
created Excel workbooks are utilized. In
Lindbloom's freely available Spectral Calculator (zipped)
(unzipped) computes LCH values.
DDE can also be used.
Run FilmStar from Excel
In the above example,
FilmStar BASIC issues Excel VBA commands. In the
model, FilmStar is the client and Excel the server.
The opposite arrangement (Excel as client) is
illustrated by Excel workbook 'RUN Design' in ..\Winfilm\Excel 97. Here
Excel runs DESIGN in the background. Since DESIGN is invisible, it might
appear that Excel has 'magically' gained the ability to perform thin
DESIGN BASIC subroutines (like .Calculate)
functions (like .Angle) act as if built into
Excel VBA. Skills learned with DESIGN are applicable to
MEASURE. Often the only difference in MEASURE or Scantraq is replacing
Calculate with Scan.
The effort involved in understanding and implementing VBA automation is
more than compensated by the advantages. Advanced users will find
FilmStar Class References to be of
great interest when connecting Excel and FilmStar.
A very prominent manufacturer utilizes Excel Workbooks as process
databases. Stack Mode lets
them optimize optical characteristics and instantly modify Workbooks
accordingly. Their Excel-FilmStar solution is called ExcelStar.
1. Sorting filters
2. R/T vs temperature
3. Absolute R/T Analyzer
7. Excel as Results Database
1. A UK coating company needed to sort
filters by center wavelength. Here FilmStar MEASURE BASIC communicates with a
PLC to move parts into the sample chamber. Filters are scanned, center
wavelengths computed, and results automatically inserted into Excel.
Korean research facility needed to measure %R/%T over wide temperature
ranges. Excel was the obvious choice for saving multiple spectra; click here to learn
3. A Dutch manufacturer of spectrophotometer accessories needed to
measure %R/%T over a wide range of incident and detector angles.
Excel was once again the obvious choice for saving the great number of
spectra. Click here to
4. A coating engineer required reflectance vs. wavelength and layer
thickness lookup tables with ~6000 columns and ~1000 rows. Fortunately
Excel 2007 increased the maximum number of columns from 256 to 16,384.
Click here to learn more and
download relevant DESIGN BASIC code.
Excel as Procedure Database
A NH manufacturer required a MEASURE
BASIC program to help technicians scan numerous optical parts. Our
Excel as a database. Each row defines a procedure; photos and screen
images are included in gMsgBox
prompts. Example: 'Does the scan resemble the one shown here?'
Excel Color Grid
Since Excel 2007, cells
are no longer limited to 256 colors. This improvement allows us to
automatically generate RGB
color grids illustrating angle dependence, tooling changes, layer
variations, etc. Click here to download the Excel workbook shown at the left.
The diagram indicates how a graded thickness deep dish reflector can
minimize color shift as a function of angle.
|Inspired by these examples? Thinking that your organization would
might from more efficient use of Excel? Contact us to discuss the
possibilities. If your company lacks in-house Excel development resources,
we'll be pleased to analyze your
requirements and provide a proposal.