Technical Issues - Workbook Tutorial II - Multiple Spectra

The FilmStar Workbook can evaluate and optimize functions of multiple spectra. This is accomplished in two ways: 1. Manually by including +2 in DataType, 2. Workbook macro. A possible application is a beamsplitter coating where Tp/Ts is computed by averaging over angles and weighting functions.

Suggestion: The best way to improve Workbook skills is to
reproduce the examples below, not just read them.


In the Flash video below we started with a new sheet (File..New) and defined $B$2 as DataType and $C$1 as DataMarker. Setting DataType=3 (+2) enables looping wherein spectra are pasted in adjacent columns as one activates the Workbook window by clicking or pressing <F3>. In the following we evaluate our optimized BBAR at 0, 30, 60 degrees, activating the Workbook after each calculation. Please try this yourself. One thing you will quickly discover is that you will fill up the worksheet as you repeatedly activate the Workbook.

Click Evaluate..Reset Data Loop <Ctrl+F8> to restart data pasting at the left. You might also wonder why we paste %T values when only interested in %R? Change DataType to 11 (+8 sets One Column mode) and try again.

There is a major problem with manual looping. After accidentally clicking the Workbook window one might find formulas overwritten by spectral data. Looping (DataType +2) is thus best utilized for quick analyses. This is no limitation since, as seen in the Flash video, a macro will paste data in multiple columns even when manual looping is off.

Please add another defined name to the worksheet: highlight cell $D$1 and hit <F6> to add defined name Macro. The cell should turn yellow. You should also set DataType=1 to cancel manual looping.

A Workbook macro has extremely powerful possibilities. The macro is run during each optimization or tolerancing iteration. It is possible to modify the film design during optimization or optimize two designs at once. Users who understand macros can address issues that would otherwise involve major software development.

A macro can include Macro Commands (Help: DESIGN Reference.. Tools Menu.. Macro Reference) or BASIC code (Help: DESIGN Reference.. FilmStar BASIC.. FilmStar DESIGN Extensions). There are two BASIC options: running a .bas file with BasRun or directly running code with BasExec. BasExec is best suited to simple programs; note that it is not necessary to add Sub Main and End Sub. Test these options by pasting the following into cell Macro:

1. Angle 0 R;Calculate;Angle 30 R;Calculate;Angle 60 R;Calculate

2. BasExec Pol$="R"|Angle=0:Calculate|Angle=30:Calculate|Angle=60|Calculate

3. BasRun c:\Winfilm\Basic32\Angle0-60.bas
' Program Angle0-60.bas  Save in c:\Winfilm\Basic32
Sub Main
    Pol$ = "R"
    Angle = 0: Calculate
    Angle = 30: Calculate
    Angle = 60: Calculate
End Sub

An example, developed in response to a user-request, is illustrated in BeamSplit.zip where we optimize coating performance according to integrated quantities. Unzip and copy files as indicated below; adjust directories if not using default FilmStar paths.

FilmStar Workbook BeamSplit.vts c:\Winfilm\Workbook
FILM Archive BeamSplitInit.faw c:\Winfilm\Designs
Graph Axes TSdivTP.axw c:\Winfilm\Data
BASIC Program BeamSplitEx.bas c:\Winfilm\Basic32

The task is to design a polarizing beam splitter taking into account angle range and a wavelength-dependent weighting function. DataType=8 specifies Single Column mode since only transmittance is required. Named cell Macro ($B$4:$B$13) includes multiple rows for legibility. The Objective ($B$23=$B$20/$B$17) is the quantity to be minimized. The initial design was the user's attempt to optimize a design with %T targets.

Open <F12> FILM Archive BeamSplitInit.faw. Press <F3> to activate the Workbook; open BeamSplit.vts. Click Optimize.. Optimize <Ctrl+O> and watch the proceedings. Repeat optimization a few times until there is no further improvement. As plotted below the objective (merit function) was lowered by a factor of 13400%.

Once things are working as expected, advanced users can automate the process and automatically plot the graph shown above. Click Tools.. BASIC Editor <F6>; open BeamSplitEx.bas, click the Start icon or press <F5>. Where did the Workbook go? The macro utilizes the Hidden Workbook for 2x or greater speed improvement:

WbOpenHidden "C:\Winfilm\Workbook\BeamSplit.vts"

Change to WbOpen to display the Workbook during optimization. An astute user (that means you) will wonder how to plot TS/TP. This is accomplished through a User-Defined Function. Functions are defined in the dialog shown below accessed via buttons in Setup.. Parameters or Optimize.. Targets. Click Test to verify that the formula is correct. This is the same Calculator as in Evaluate.. Calculator <Ctrl+X>. The Description then appears in the Plot pull-down in the Setup Parameters dialog or Type pull-down in the Optimization Targets dialog.