For those who want to work with ZOS-API using Excel, here are a few advice and sample codes. You can either work in standalone or interactive extension.
Enable the communication between Excel and ZOS-API
The communication between Excel and ZOS-API is pretty straightforward once the references are enabled in the VBA environment. Click the Developer tab from Excel to access the VBA environment (if you don’t see it it has to be enabled in the Options):
Once VBA is opened, simply click on Tools > References and then select ZOSAPI and ZOSAPI_Interfaces:
Using ZOS-API with Excel: what is different?
From here, create a new Module and subroutines. There are a few caveats and cool things:
- Declare variable types with the Dim keyword (at the beginning of your Sub)
- For any method which is not assigned to a variable, treat it like a ZPL keyword where you don’t use the parenthesis to pass in the arguments (see wavelengths & fields in the example)
- Use the intellisense
- Inheritance: When a child class inherits from a parent class, 2 variables have to be defined to use the API tools, one for the child tool to set the specific properties and one for the parent tool to run & close the tool.
Sample codes
The two examples provided below are from
- Example 1 called “TestZOSAPI.zip” creates a singlet and adds a marginal ray height solve on the back focal length. It saves the file under “{Zemax}\Samples\test_from_excel.ZMX”. The file is a module. Open Visual basic to open the file.
- Example 2 called “Optimize.zip” contains an excel sheet with the module. Open the excel sheet and click on the Optimize button. It creates a singlet, then uses the Quick Focus to find the back focal length. It then optimizes the singlet. That example shows how to deal with Inheritance. It saves two files "{Zemax}\Samples\Excel_quick_focus.ZMX" and {Zemax}\Samples\Excel_optimization.ZMX".
