Microsoft Excel is the most widely utilized calculation/data analysis software. Virtually all optical coating engineers rely on Excel. But do they get maximum benefit?

In this page we present ways in which Excel interfaces with FilmStar (or Scantraq). Note that examples 3-7 utilize Excel VBA. Never heard of VBA? Then start Excel and click Alt+F11. Surprised to see Microsoft Visual Basic (VBA) in the title bar? Once you discover the power of Excel VBA, you'll better appreciate how FilmStar's  support far exceeds that of other coating and spectrometer software. (We suggest you compare an ftgsoftware.com site search for 'Excel' with the same search on other coating software sites.)

1. Save Spectra as .xls

FilmStar spectra formats include .csv, .dx (J-CAMP) and .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 (Scantraq) 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.

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's too easy to paste data into the wrong columns and there are possible 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.bas' BASIC macro included with the FilmStar Free Version. A user need only click a button to run a hidden copy of Excel which computes requested quantities.

In the above example we pasted formulas into a blank sheet, but more typically previously saved Excel calculations would be utilized. In example 'SpecCalc5nm.bas' Bruce Lindbloom's Spectral Calculator computes LCH values.

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 an Excel workbook 'Run DESIGN.xls' also in the FilmStar Free Version. 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.

Optical engineers and spectroscopists who have so far managed without Excel VBA take the risk of putting their companies and possibly their careers in jeapordy. The effort involved in understanding and implementing VBA automation is more than compensated by the advantages.

7. Excel as Results Database

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.