This article describes how to allow programs that support Visual Basic for Applications to communicate with Zemax.
This article is also available in Japanese.
Authored By: Henning Rehn
Introduction
This article is also available in Japanese.
Visual Basic for Applications (VBA) is a variant of the now obsolete Visual Basic 6. VBA was commonly used as a programming language within the Microsoft Office family of products, and it was also available to third-party developers as a bolt-on scripting language for incorporation into third-party applications. VBA itself is now obsolete, but its use within Microsoft Office means that it is likely to be used by Zemax users for many years to come.
This article describes how to transfer data to and from Zemax using Microsoft Excel as the example VBA program. The code is provided and a simple 'hello world' demonstration is given, along with details of some specific issues when using Excel.
Communicating Between Zemax and Excel
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.
DDE and VBA
VBA communicates with Zemax over Dynamic Data Exchange (DDE). The standard way for a VBA program to call another DDE-enabled code is as follows (this example is from the VBA Help file):
channelNumber = Application.DDEInitiate(app:="WinWord", _
topic:="C:\WINWORD\FORMLETR.DOC")
Application.DDEExecute channelNumber, "[FILEPRINT]"
Application.DDETerminate channelNumber
In this case, the VBA code is calling Microsoft Word. The following code snippet:
Sub Zemax_DDE()
Dim s1 As String
Dim s2 As String
Dim s3 As Variant
Dim ch As Long
s1 = "GetName"
s2 = "anystring"
ch = Application.DDEInitiate("Zemax", s2)
s3 = Application.DDERequest(ch, s1) ' Does not work in Excel
Application.DDETerminate ch
End Sub
will call Zemax and get the name of the currently loaded file and store it in the s3 string. The author has discovered that this code will work correctly as long as it is NOT executed by the VBA in Microsoft Excel. Excel will generate an 'Error 2042' at the line s3 = Application.DDERequest(ch, s1). The reason for this is unknown, but is presumably because of some internal difference between the VBA embedded in Excel and that in other codes, including other Microsoft Office applications. This is why in the code on the previous page, we call Word.Application.
With this one fault in Excel's DDE code fixed with this workaround, the transfer of data between Zemax and Excel (and any other VBA-enabled application) is smooth and reliable.