FilmStar and Excel

Technical Issues - FilmStar and Excel

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.

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

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.

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?'

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