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.