FilmStar and Excel

Technical Issues - FilmStar and Excel

Don't miss Excel extends optical coating software capabilities
in Laser Focus World April 2014. Click here to read the article;
click here 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 optical engineers.

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 that format.

2. Copy/Paste Data

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 FilmStar Workbook.

Applications include

DESIGN..Import theoretical optimization targets
DESIGN.. Import/export calculated spectra
DESIGN..Rugated index profiles and thin film designs
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

Applications like Mathcad, Origin and SigmaPlot are also Excel-compatible. This greatly extends the usefulness of FilmStar's compatibility.

User-friendly glass selector

3. Import Manufacturer's Data

Since Excel is so universal, manufacturers typically supply data in Excel format (*.xls). Examples include Schott and Ohara 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:

360 1.94204 6.8983E-06

370 1.93647 2.2231E-06

380 1.93144 8.2988E-07

390 1.92688 3.6167E-07

400 1.92273 1.8007E-07

420 1.91545 7.7770E-08

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)
              Exit Function
          End If
      Next i
  End Function

5. 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 possibilities.

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 COM (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 quantities.

This will not work with the pre-installed
'Click-to-Run' version of Office 2010.
Click here for further information.

More typically previously created Excel workbooks are utilized. In DESIGN macro 'SpecCalc5nm.bas' Bruce Lindbloom's freely available Spectral Calculator (zipped) (unzipped) computes LCH values. DDE can also be used.

6. Run FilmStar from Excel

In the above example, FilmStar BASIC issues Excel VBA commands. In the client-server 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 film calculations.

DESIGN BASIC subroutines (like .Calculate) and 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.

2. A 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 more.

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 learn more.

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.

8. Excel as Procedure Database

A NH manufacturer required a MEASURE BASIC program to help technicians scan numerous optical parts. Our solution uses 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?'

9. 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.

Back to Technical Issues