Software: Thin-Film Coating Design: Excel extends optical coating software capabilities

FRED GOLDSTEIN

Microsoft Excel is a widely utilized spreadsheet program that can enhance the utility of optical thin-film coating design software in diverse ways, from familiar copy/paste data exchange through process automation as client and server.

Examples discussed herein and available for download¹ demonstrate how Excel provides the means to evaluate layer errors, visualize angle shifts, generate rugate and other apodized designs, import dispersion data from diverse sources, automate measurements, and extract spectra from quality-assurance coating parameter databases. In short, Excel offers a universal bridge between thin-film software and other applications, enabling coating engineers to economically maximize their effectiveness.

Copy/paste

The simplest and most familiar way to transfer data to and from Excel is via clipboard copy/paste. Data entry grids throughout FTG's FilmStar optical thin-film modeling software (see http://www.ftgsoftware.com/fsindex.htm) are therefore in TAB-separated Excel format. This includes dispersion data, calculated and measured spectra, optimization targets, designs, optical monitor worksheets, and wavelength lists.

A coating engineer may need to generate optimization targets based on a mathematical model. The procedure is to copy the target grid to the clipboard and paste into Excel. Using Excel functions, the engineer generates new targets as required. Finally, modified spreadsheet cells are copied and pasted back into the target grid. In this way, off-the-shelf software is readily modified for specialized requirements.

Excel provides universal connectivity. You may, for example, require dispersion data that can only be found in graphs. To utilize this data, scan the graph and extract W, n, k (wavelength, complex refractive index) with a digitizing program such as Dagra or DigitizeIt that exports plot coordinates in Excel format. Copy the table and paste it into any thin-film software. The same technique is useful for extracting illuminant data (intensity vs. wavelength) from published curves.

Quality assurance (QA) spectral plots, such as %T (transmission) vs. wavelength, are traditional but often inadequate as optics buyers increasingly demand results in a form that can be analyzed. Spectra can be supplied in CSV (comma separated values) or XLS (native Excel) format. Coating suppliers can assume with near 100% certainty that their customers use Excel.

VBA

While much can be accomplished with Excel spreadsheet functions, far more is possible through Visual Basic for Applications (VBA), a programming environment included in Microsoft Office applications. Anyone unfamiliar with VBA will benefit from the following 'my first program' exercise: open Excel; press Alt+F11; click Insert.. Module; type three lines Sub Main() | MsgBox "Hello world!" | End Sub; click Run.. Run or press F5. You should now see a box with the message 'Hello world!' and an OK button.

This brief exercise shows how VBA fits into Excel and indicates where to find code in Excel workbooks. As you dig deeper into VBA you learn that Excel can act as both client and server using Component Object Model (COM). This means that Excel (server) can run invisibly in the background under control of optical coating software (client) and vice versa. This makes it possible to simplify and automate complex procedures.

Color tolerancing

FSBasic is FilmStar's COM-compatible programming language similar to VBA. We establish a reference to Excel and evaluate Excel functions remotely. This takes place with or without Excel being visible. An example is given in FSBasic program ExcelRand.bas1 that saves tolerancing data in Excel workbook RandColor.xls.¹ Assuming random thickness errors, FSBasic repeatedly calculates spectra and transfers results to Excel columns.

Ultimately, users want to create scatter plots in color space, so the next task is to compute color values for each spectrum. Since this is a major effort, you can search the Internet for "CIE color calculations" and find Bruce Lindbloom's freely available Excel spectral calculators. These compute a plethora of CIE quantities, saving hours of work. You only need to transfer spectra from your workbook to Lindbloom's and copy back results. To see how this works, open RandColor.xls and SpectralCalculator5nm.xls¹ simultaneously. Click 'Get CIE' in RandColor and scroll down to view color values. The workbook can be used with any optical thin-film software that can copy multiple spectra to the clipboard or save in CSV format (see Fig. 1).

Color maps

Optical glass

Optical glass manufacturers Schott (Jena, Germany) and Ohara (Oyama, Japan) publish Excel workbooks containing dispersion information, including Sellmeier coefficients and internal transmittance. By adding a new 'Export' worksheet and VBA code to their workbooks, we can generate the W,n,k tables required by optical thin-film software. The FTG-augmented workbooks OharaGlass.xls¹ and SchottGlass.xls¹ include a glass selector dialog. The final table can be pasted into any coating software.

Coating designs

FilmStar added the 'Stack Mode' design format in 2012 to interface with a user's Excel-based manufacturing process. This mode supports 32K separate layers (repeated groups allowed) and 32K dispersive materials in a format ideal for modulated designs in which layers and/or indices vary mathematically. Examples include broadband high reflectors where each layer is a multiple of the previous layer, rugate designs simulated by thin layers of varying index, and designs based on thickness modulation.² The ability to transfer designs back to Excel lets users study changes after refinement.

Coating designs are often converted to optical and/or crystal monitor run-sheets that, if Excel-compatible, can be exported or modified as necessary. One example is Intellemetrics' (Paisley, Scotland) FilmMaker optical monitor software that imports dispersion data and monitor settings in Excel CSV format.

Multiple spectra

Spectral measurements are crucial for index determination, reverse engineering, and quality assurance. As developers of data acquisition software for PerkinElmer (PE; Waltham, MA) and other spectrophotometer brands, we have found Excel to be especially useful. In addition to being an ideal repository for multiple spectra, Excel offers automation possibilities through VBA control of auxiliary devices.

A recent application involved measuring optical spectra over a wide temperature range. A hot and cold stage manufactured by Instec (Boulder, CO) was used in conjunction with a PE Lambda 950 spectrophotometer. Instec provided a dynamic linked library (DLL) enabling remote temperature setting. This was utilized in an executable that can run standalone for testing and remotely as server. Following each scan, spectra are transferred to Excel. The workbook resembles the color-tolerancing example given previously with temperature indicated in the top row.

Similar Excel-based automation projects include circular variable filters, XY stages for mapping uniformity, and spectrophotometer multi-angle accessories in which incident and detection angles vary. The cost of software development, usually requiring 1–2 days, is quickly compensated by labor savings.

Quality-assurance database

While the projects mentioned previously require automation, most facilities deal with one coating run at a time, measuring one or a few witness samples. Information about each run is (or should be) entered into database software where each record includes fields such as work order, coating type, customer, chamber, materials, and technician, among others. This provides future opportunities to quickly retrieve information according to multiple criteria.

In addition to summary quantities such as averages and color values, records should include or point to original measured spectra. An engineer can select a group of coating runs and export the spectra to Excel for analysis. It is then possible, for example, to compare different coating chambers or the same chamber over a period of time. Spectra are typically stored in ASCII format and one can easily implement VBA code which transfers data to adjacent Excel columns for analysis.

FileMaker Pro from FileMaker (Santa Clara, CA) is an ideal database for coating departments because it is easy to use, is COM compatible, supports container fields, and has numerous security features. 'Easy' means readily supported by engineers who are not database experts. 'COM compatible' enables FileMaker operations to be automated with FSBasic or Excel VBA. 'Container fields' make it possible to embed many spectra in a single file. 'Security' allows an administrator to assign privileges based on passwords; this makes it impossible, for example, to artificially 'improve' measured results.

An online example is offered by FileMaker database Spectra.fmp12¹ and Excel Workbook GetFMSpectra.xls.¹ Select records with FileMaker's Find command and then click Load Spectra in Excel. Excel locates the first record, exports the spectrum (JCAMP-DX format) to a temporary file, opens that file, and transfers data to the next column, repeating for all records. Coating engineers who can select and analyze data are able to compensate for systematic changes, decide when an apparatus requires maintenance, and predict costs.

This article discusses several possibilities for extending optical coating design and measurement software with Excel. Furthermore, there are many Excel-compatible applications (such as Mathcad and LabVIEW) and add-ins (search the Internet for "SPC Excel") that can benefit optical coating facilities. Anyone enthusiastic about improving Excel proficiency will find any number of online tutorials, books, and short courses available.

REFERENCES

1. See http://www.ftgsoftware.com/LFW2014.htm.

2. Zhang et al., "Thin-film thickness-modulated designs for optical minus filter," Appl. Opt., 52, 23, 5788–5793 (2013).

Fred Goldstein is president of FTG Software Associates, P.O. Box 579, Princeton, NJ 08452; e-mail: [email protected]; www.ftgsoftware.com.