Can I do a ZOS-API Interactive Extension with Excel VBA?

  • 22 September 2022
  • 4 replies


Is it possible to do a ZOS-API Interactive Extension with Excel VBA?

I’ve been able to do a standalone with examples from this forum.  This works:

    Set TheApplication = TheConnection.CreateNewApplication()
    Set TheSystem = TheApplication.CreateNewSystem(SystemType_Sequential)

This does not work (get the VBA error message “Object variable or With block variable not set” for the second line which seems to say that TheApplication is not being Set properly):

    Set TheApplication = TheConnection.ConnectAsExtension(0)
    Set TheSystem = TheApplication.PrimarySystem

I’ve also been able to do an Interactive Extension with MATLAB from the boiler plate code (similar to above) generated by OpticStudio … just can’t seem to get it to work in Excel.


4 replies

Userlevel 4
Badge +2

Hi Pat,

I am not good at Excel VBA but you may find something useful here:

ZOS-API & Excel interactive extension example- retrieve incident angles on surfaces across pupil | Zemax Community

I made a summary about the materials available on community, sorry it’s in Chinese. You can use browser to translate.

ZOSAPI Excel资料合集 | Zemax Community


Hi Yuan,

Thanks for the link.  I used the same syntax as was in the link (see below) which is nearly the same as my original post and I get the same error message.


Userlevel 4
Badge +2

Hi Pat,

I ran into some issues to reproduce your situation. I will get to you soon.



I have been able to get my VBA code to run without error messages but I’m still having issues as it doesn’t seem to be running properly in Extension mode.  I can run the code even without an instance of OpticStudio running.  It almost appears to be running in Standalone mode.

I tried using Julia’s code for the incident angles calculation and I had the same problem … the VBA codes runs without errors even when there is no OpticStudio running.

I’ve tried the same code in Matlab and I do not have this problem … it seems to be running properly in Extension mode.

Thanks for your time.