Open both ZEMAX and Excel. Within Excel, click on Tools...Macro...Visual Basic Editor:



Then, within the VBA Editor window, click on Insert...Module:



Then copy the following text to the clipboard, and paste it into the editor:

Function zemax(access As String) As String
Dim ch As Long
Dim s As Variant
Set WordObject = CreateObject("Word.Application")
ch = WordObject.DDEInitiate("Zemax", "anystring")
s = WordObject.DDERequest(ch, access)
WordObject.DDETerminate (ch)
WordObject.Quit
zemax = Left(s, Len(s) - 1)
End Function

So that the editor looks like so:



Then close the Visual Basic Editor. Go to Excel, and in cell B3 enter the text "GetSerial" (without the quotes, but note the case-sensitive text) and then in some other cell enter "=ZEMAX(B3)" (again without the quotes). The value of your key serial number (which will be different to the one in the screenshot) will be returned:



Remember that ZEMAX must be running for this to work! This simple function allows you to enter any ZEMAX Extension dataitem as a text string in an Excel cell, and then to send that string to ZEMAX for processing. This is similar to the supplied sample Extension 'Command Line Interface', which is a VB6 program designed to provide query-and-response processing of Extension dataitems.

As a further example, open the sample file {zemaxroot}\Samples\Sequential\Objectives\Cooke 40 degree field.zmx. Then, in Excel, change cell B3 to show "OpenWindow, Ima" (again without the quotes, but with the case-sensitive syntax). Excel will show



and a new window has opened inside ZEMAX: an image analysis window!



Note: if this does not work, and Excel reports FAIL instead of OK, go to ZEMAX, click on File...Preferences, and in the Editors tab, check 'Allow Extensions to Push Lenses' ON.



Then send the OpenWindow, Ima command again.

If you want Excel (or any other external program) to be able to change the data in the editors and analysis windows of the currently open ZEMAX session, then "Allow Extensions To Push Lenses" must be checked. This control is provided to prevent accidental erasure of data in the normal ZEMAX user interface by the external program.