Automating the Si8000 goal seek with VBA
Application Note 190

The Si8000 Goal Seek function is a powerful tool — for example, given a controlled impedance structure's target impedance it can solve for track widths (W1, W2) when the other parameters, (Er, dielectric height and trace thickness, etc.) are provided (often dictated by manufacturing constraints).

For some applications it will be necessary to perform multiple "goal seeks" to achieve the final controlled impedance structure. We can use Excel's powerful macro language, Visual Basic for Applications (VBA) to automate the process.

For this example we'll construct a simple tapered transmission line structure (where the impedance varies linearly along the trace). We are given starting and ending target impedance values of 50 and 60 Ohms respectively and we make the decision to calculate trace widths at 100 points along the trace.

The Si8000 application includes three components, the Si8000 Quick Solver, the Si8000 workbook with its pre-built sample structures and the Si8000Expert workbook, which simply provides the user-defined functions to access the Si8000 calculation engine.

Ensure the Si8000Expert workbook is open and start a new workbook.

Enter the parameters for the chosen structure (as a starting point you can copy the parameters from
the Si8000 workbook if appropriate) as shown in the example below. For this example we use the Surface Microstrip structure with parameters in cells B4 through G4.

To cater for trace etch-back we specify W2 as W1–1. Set E4 = D4-–1.

Press Shift - F9 to calculate the impedance on this sheet; the impedance will be calculated for the parameters shown.

Goal seeking for a target impedance value

To goal seek for a specific value of impedance (say, 50 Ohms)  we can use the Excel Goal Seek... command.

Select the impedance cell (H4 in this case) and choose Tools|Goal Seek...

Use the Goal Seek... to set the impedance cell H4 to 50 Ohms by changing W1 (in cell D4).

The results for W1 and W2 are shown below.

Automating the Goal Seek process for the other values

For our example the trace begins with an impedance of 50 Ohms and needs to end up with an impedance of 60 Ohms; we therefore repeat the Goal Seek... process for W1 and W2 100 times at
0.1 Ohm increments.

We begin by copying the Excel worksheet row B4:H4 down 100 rows (highlight the cells and use 
Ctrl + the Fill Handle to copy down).

Adding the code

Use Alt + F11 to open a module sheet and enter the code below into Module 1.

Note: the code below contains no error handling, etc. and is supplied simply for illustrative purposes.

Sub GoalSeeks()

Dim StartZ As Single, EndZ As Single, ZStep As Single

StartZ = 50
EndZ = 60
ZStep = (EndZ - StartZ) / 100

Do While Not IsEmpty(ActiveCell)

ActiveCell.GoalSeek Goal:=StartZ, ChangingCell:=ActiveCell.Offset(0, -4)
ActiveCell.Offset(1, 0).Select
StartZ = StartZ + ZStep

Running the macro

The macro begins in the active cell, the first impedance value, refers to the parameters to its left and repeats the process for the cell below until it reaches the end of the block of data so choose cell H4 before you run the macro.

Click cell H4, press Alt - F8 to display the available macros, choose the macro and click Run. 

The macro goal seeks for W1 and W2 for incrementing values of Zo. The results are shown below

Clicking the Macro Edit button will allow you to edit the macro to change impedance values, number and size of steps, etc.

Graphing results

Finally, we present the results graphically, displaying Zo against W1.

The combination of the power of the Si8000 calculation engine and Excel's VBA programming
language places a potent productivity tool in the PCB design engineer's hands.