Solved

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

  • 22 September 2022
  • 6 replies
  • 274 views

Badge

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.

Thanks.

icon

Best answer by Sandrine Auriol 5 October 2022, 10:28

View original

6 replies

Userlevel 5
Badge +3

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

Badge

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 5
Badge +3

Hi Pat,

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

Badge

Yuan,

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.

Userlevel 5
Badge +3

Hi Pat,

Sorry I still have problem in finding my ZOSAPI.dll in Excel...

 

Userlevel 6
Badge +2

Hi @Patrick.Cronkite 

I tried the excel sheet from this example: 

When I open OpticStudio, click Interactive Extension then click the Connect button of the macro. It connects.

If I close OpticStudio, click the Connect button of the macro then I get an error message as below.

 

 

Could you just run this test?

I can see that your code is very similar but just trying to understand what is different.

Reply