Example |
  
|
Example |
|
OLE example: Excel worksheet computes technical data for different layer thicknesses
This example shows a rather common task: You want to investigate how some technical values of a coating depend on the individual layer thicknesses. What you need to do is to compute for each set of thickness values all the wanted quantities and collect them in appropriate tables. You can do this quite easily with Excel which provides the required table structures, a macro language to express your wishes and the ability to control CODE as OLE automation server.
Before you start the Excel part of the work you have to setup a CODE configuration containing a complete model (optical constants, definition of the layer stack, definition of the spectra that are to be computed). Select those model parameters as fit parameters which shall be modified from Excel. In our example these are three layer thicknesses.
The Excel file containing the prepared macros (see below) looks like this (in the beginning):
The cell named configuration_file will be used by the macro that starts CODE to load the CODE configuration that we are going to work with. To start CODE and load the configuration you can use the CODE menu item WCD|Start and load configuration which has been added to the Excel sheet. After the configuration loading the Excel enters the names of the CODE fit parameters and the CODE technical values in the first column called WCD names. Now the sheet looks the following way:
There are 3 fit parameters (thicknesses) and 9 technical values. In the second column you can enter comments that describe the quantities in your way. For example, you could place here internal terms for the layers or quantities of interest:
Now it's time to enter the values for the thicknesses that you want to include in your computation. Give a name (which can be just a number) for the set in the cells to the right of the term Comments and enter below the parameter values. The macros that performs the computation looks if there is a name in the third row of the worksheet. If so, it expects thickness values below that name. The macro continues to work through the columns from left to right until it cannot find a name anymore, i.e. if it reaches an empty name cell. For large series of values it would be reasonable to write a short VisualBasic macro that creates the input values for you. A prepared worksheet could be this one:
Now execute the computation macro by the menu item WCD|Compute data and watch how Excel fills up the table:
The Visual Basic macros used in this example:
' The wcd object is the global object representing the Windows Coating Designer OLE server
Dim wcd As Object
' This macro creates the wcd object and loads the configuration file that is specified in the
' Excel worksheet "colors" (cell named "configuration_file")
' This configuration must have been created before for the layer stack that is going to be inspected
' _______________________________________________________________
Sub start_wcd()
'Create the wcd object
Set wcd = CreateObject("code.colors")
'Load the configuration file
wcd.configuration_file = Range("colors!configuration_file").Value
'Show the WCD program on the screen
wcd.Show
row_offset = 2
'Get the number of fit parameters (i.e. layer thicknesses) from WCD
no_paras = wcd.number_of_fit_parameters
'Get the number of integral values (e.g. color coordinates) from the WCD list of integral values
no_tec_values = wcd.number_of_tec_values
'Enter the names of the fit parameters and integral quantities to be computed into the Excel worksheet
'in column 1 (A)
For i = 1 To no_paras
Range("colors!origin").Offset(row_offset + i, 0).Value = wcd.fit_parameter_name(i)
Next i
For i = 1 To no_tec_values
Range("colors!origin").Offset(row_offset + no_paras + 1 + i, column_count).Value = wcd.tec_value_name(i)
Next i
End Sub
' _______________________________________________________________
'This macro deletes the OLE server from memory
Sub delete_wcd()
wcd.prepare_shutdown
Set wcd = Nothing
End Sub
' _______________________________________________________________
'This macro displays the WCD main window if it was hidden
Sub show_wcd()
wcd.Show
End Sub
' _______________________________________________________________
'This macro hides the WCD main window
Sub hide_wcd()
wcd.Hide
End Sub
' _______________________________________________________________
'This macro computes the wanted integral quantities
'
Sub compute_values()
Dim fitpara_count As Integer
Dim tecdata_count As Integer
Dim no_paras As Integer
Dim no_tec_values As Integer
row_offset = 2
' Get the number of fit parameters from WCD
no_paras = wcd.number_of_fit_parameters
' Get the number of integral quantities from WCD
no_tec_values = wcd.number_of_tec_values
column_count = 2
' Check if there is another set of thicknesses to process
While Range("colors!origin").Offset(row_offset, column_count).Value <> ""
'If so take the values for the fit parameter from the Excel worksheet
For i = 1 To no_paras
'If the fit parameter is a layer thickness (fit parameter mode =2), convert the value in nanometers used in the Excel sheet to microns which is the
'unit expected by WCD
If wcd.fit_parameter_mode(i) = 2 Then
wcd.fit_parameter_value(i) = 0.001 * Range("colors!origin").Offset(row_offset + i, column_count).Value
Else
wcd.fit_parameter_value(i) = Range("colors!origin").Offset(row_offset + i, column_count).Value
End If
Next i
'Recompute everything in WCD with the new set of parameters
wcd.update_data
' Get from WCD the new values of the integral quantities
For i = 1 To no_tec_values
Range("colors!origin").Offset(row_offset + no_paras + 1 + i, column_count).Value = wcd.tec_value(i)
Next i
'Proceed to the next column
column_count = column_count + 1
Wend
End Sub