Use DDE in VBA code.
 

I have a ray-trace application which is supposed to support DDE. If I type something lik

=ZEMAX|ignored!"GetSurface,2"

into a cell it returns a string from the ZEMAX application which contains information on surface 2.

STANDARD,-9.070294785E-005,-3.969000000E+003,MIRROR,1.751943691E+003,-1.000000000E+000,0.000000000E+000,0.000000000E+000 ,0.000000000E+000,0.000000000E+000,0.000000000E+00 0,0.000000000E+000,0.000000000E+000,0.000000000E+0 00

It also changes the formula displayed in the editing window to something lik

='#19264'|'#23'!'GetSurface,2'

(the values change every time, and the link doesn't work next time the application is started)

This isn't very helpful, so I tried writing a VBA function to set the cell up. That doesn't work (apparently you can't change cell values from within a function)

Is there a way to execute a given string as if it was typed into a cell and return the results to a variable in VBA

I have also tried the DDE functions directly within a macro, but I couldn't get them to work.

Any help much appreciated

Chri