Technical Issues - Saving Multiple Spectra

While it is typical to save a single calculated or measured spectrum in a unique file, this becomes unwieldy when there are many associated spectra. MEASURE examples: 1. Life tests on solar mirrors, 2. Uniformity on large optics, 3. Multiple samples from a coating run. DESIGN examples: 1. Random error analysis, 2. Angular variations. Excel is universally installed and is (in our opinion) the file format of choice which should be used instead of proprietary or specialized formats.

This page refers to FilmStar BASIC (#4, #5) code and Excel VBA code (#6). BASIC files MultCalcXL.bas,  MultCalcXLC.bas, and YieldEnvl.bas are found in C:\Winfilm\Basic32.

Template MultCalc0.xls is in c:\Winfilm\Workbook while MultCalcXL.xlsm (Excel 2007/2010 macro-enabled workbook) is found in C:\Winfilm\Excel 2007. Users who cannot get these examples to work should contact FTG Software for assistance.

1. DESIGN Contour mode

The simplest DESIGN method is to select Contour mode in Setup.. Parameters. Evaluate and copy spectra to the clipboard (Spectra.. Copy Data <Ctrl+C>) and paste into Excel or the FilmStar Workbook. Be sure to select Contour Data.. Matrix in Spectra.. Format Options. This method only applies when each column corresponds to a different angle of incidence.

2. FSPlot data storage

The next method uses FSPlot, works with MEASURE as well as DESIGN, and does not require BASIC. Using the Plot button, add spectra to FSPlot then click Data.. Save As to save the spectra in Excel-compatible CSV format. Alternatively, click Data.. Copy <Shift+F11> and paste into Excel. For users with occasional requirements, this simple technique should be sufficient. Data can be viewed in the Formulator.

The FSPlot method can be automated as follows:

 MultCalcCSV.bas for FilmStar DESIGN
' Copyright 2011 FTG Software Associates
' ****************************************************
' Save multiple spectra in Excel-compatible CSV format

Option Explicit
Option Base 1
DefInt i-n
DefSng A-H, O-Z

Const path$ = "C:\Winfilm\Workbook\"

Sub Main
    Dim ang
    PlotClose
    PlotActivate
    For ang = 0 To 60 Step 15    ' adjust as required
        Angle = ang
        Pol = "P": Calculate
        PlotNext
        Pol = "S": Calculate
        PlotNext
    Next ang
    PlotDataSave path$ & "MultCalc1.csv"
    'PlotDataCopy  ' even simpler, paste into Excel
    Busy False     ' remove hourglass
End Sub

Select 0-100% scale or risk losing data. FSPlot only saves data within Graph Axes range.

3. FilmStar Workbook used manually

The next method relies on the ability of the FilmStar Workbook to add multiple spectra as it is activated. In the following DataType = 11 (1+2+8) indicates formatting (0-100%), looping, and one data column (%T instead of %R and %T) in DESIGN. While this can be automated with FilmStar BASIC or with a Workbook macro, we think Method #4 (below) is a better use of BASIC skills.


DataType B1, DataMarker A3

4. FilmStar Workbook with BASIC (MultCalsXL.bas)

CSV is not internationally invariant. Files saved in European format (comma decimal point) are unreadable elsewhere. In addition, CSV does not support formatting.

FilmStar can directly create single-sheet Excel files. In BASIC program MultCalcXL.bas a pre-formatted worksheet MultCalc0.xls provides a template. This template can be created in DESIGN or in Excel 5.0/95 format (supported by all newer Excel releases).


FilmStar Workbook (Excel 5.0/95) template with text and numeric formatting
No special defined names (DataType, DataMarker) are utilized.


After running FilmStar BASIC MultCalcXL.bas

The filled-in Workbook is saved with a new file name. Marking the template as read-only is recommended to prevent unwanted modification.

If 256 columns are sufficient and Excel features like charts, VBA macros, multiple worksheets etc. are not required, this is an excellent and highly reliable method for generating Excel files without MS Excel. Note that a template is not limited to formatting but can include worksheet functions.

MultCalcXL.bas code comment: Function Cell$ converts column numbers to addresses (35-> AI). This is required because the Workbook cannot directly utilize column numbers. The FilmStar Workbook is limited to 256 columns as are Excel versions prior to 2007.

5. DESIGN export to Excel (MultCalcXLC.bas)


After running FilmStar BASIC MultCalcXLC.bas using template MultCalc0.xls

Use this instead of Method #4 (above) when more than 256 columns are required (Excel 2007 or newer), if XLSX format is required, or if the Excel workbook includes multiple sheets, charts, etc.

MultCalcXLC.bas code comments: Why Macro "CALCULATE;" instead of BASIC Calculate? It appears that Calculate is misinterpreted as an Excel keyword. There is no problem when CalcPlot is utilized. Excel can be run in the background (not visible) by changing xlApp.Visible = True to False and adding xlApp.Quit before End Sub in Sub Main in order to prevent multiple hidden copies of Excel.

6. Excel import from DESIGN (MultCalcXL.xlsm)

Excel aficionados might prefer to work in Excel and have Excel retrieve spectra from DESIGN or MEASURE. Users who do not have Excel 2007/2010 should download the Microsoft Office Compatibility Pack.

Once spectra are transferred from DESIGN to Excel, the related Excel VBA macros are no longer required and may be deleted. Excel 2007 and 2010 deal with this issue via two file structures: XLSX (no VBA) and XLSM. By starting with XLSM and saving in XLSX we delete spurious macro code.


Excel 2007/2010 macro-enabled workbook

MultCalcXL.xlsm VBA code comments: Pause .2 is a puzzle! Without a delay, perhaps a message box, the program does not run correctly (ID Module error). This remains an anomaly. Note the line shapes(1).cut for deleting the Calculate button.


VBA macros deleted when saved as MultCalc2.xlsx

Advanced users: what if the final Excel model requires macros? There could be two code modules, say Module1 (imports spectra from FilmStar) and Module2 (subsequent analysis). Module1 can be selectively deleted by adding the following code as the last step. This procedure is also useful when Excel 2007 or newer is not available.

Sub Main()
    ...
    ...
    Dim vbCom As Object
    Set vbCom = Application.VBE.ActiveVBProject.VBComponents
    vbCom.Remove VBComponent:= vbCom.Item("Module1")
End Sub

Depending on Excel version, it may be necessary to adjust VBA security settings as shown here for Excel 2010 (Developer.. Macro Security.. Trust Center). Menu sequence and terminology will vary in different Office versions. If trust is not enabled, the error message upon running the above code is 'Programmatic access to Visual Basic Project is not trusted' or similar.


Enabling a VBA module to delete itself in Excel 2010

Should DESIGN and MEASURE BASIC run Excel (#5), or should Excel VBA run DESIGN and MEASURE (#6)? While each approach has merits, there are advantages in FilmStar BASIC control. Excel is a programming language and Workbooks can easily (unless precautions are taken) be wrecked. On the other hand, complex and user-friendly data entry dialogs are easier to create in Excel VBA. Advanced FilmStar BASIC users can 'borrow' dialogs from Excel. Click here for an explanation.

7. Transferring Excel spectra to FSPlot

Users may find that Excel charts are fairly complex, especially if there are a variety of wavelength ranges. A simple alternative is to copy data to the clipboard and transfer to the FSPlot Module.

Spectra are plotted in FSPlot via Data.. Paste. This function has been upgraded in DESIGN 2.61.2244 and MEASURE 2.51.1323 to convert column headers to line legends. (Headers are automatically detected by the presence of non-numeric values in leftmost columns.) These versions also support the opposite via Data.. Copy which optionally converts legends to headers. Multiple header rows are supported as shown below.

Data and headers were copied from MultCalc2.xlsx; the second 0 deg column was deleted and data limited to the 0-30 deg range. FSPlot's Bitmap/Gradient mode was used to set the border color to light green.

9. Learn by doing

The only way to master FilmStar BASIC or VBA is to develop code and/or modify existing code. Excel VBA books, online tutorials, and short courses are widely available. Here are some exercises:

  • Very simple, just to get started. Add the degree symbol '°' to angles. Looks more professional!

  • We don't need two header rows. Change to one row.

  • We don't need P and S polarization for normal incidence; modify BASIC and/or VBA code so that angle = 0 only utilizes one column.

  • Add dialog boxes to set angle range and interval. Be sure to check for invalid entries.

  • Automatically increment the file name each time a worksheet is saved. How to keep track of the last file name? Hint: Make use of BASIC Sub WriteIniString and Function IniString.

Don't forget to first back up any files that you will modify.

Back to Technical Issues