# Create Loops in Spreadsheet

For certain applications, it can be necessary to repeat the actions of some functions several times during a single acquisition Acquisition is the process or result of the vision system acquiring a new image. and job A job is a user-configured program in In-Sight Spreadsheet consisting of functions and designed for specific applications. You can create and edit jobs using the In-Sight Spreadsheet interface, and load them on a vision system for deployment in a live environment. execution. To achieve this, you can construct jobs using a loop of cells.

You can use the following Clocked Data Storage functions to create cell loops and manage their output:

- Repeat

- RepeatUntil

- StoreData

- PlotData

The Repeat and RepeatUntil functions specify the range of cells in the loop and the number of repetitions. The RepeatUntil function Functions are tools that are available in Spreadsheet for processing and analyzing acquisitions or other results. You can add functions to your Spreadsheet job to create tool chains and produce results for specific applications. also allows you to specify an exit condition for the loop to end before the specified maximum number of repetitions.

The StoreData function allows you to collect the data for each repetition in the loop. The PlotData function plots the graphics for each stored Data structure.

## Example

In this example, the task is to inspect pin connections for the proper height tolerance. The job contains functions that locate the position of the pins and count the number of pin connectors. Each pin connector needs to be measured to verify that it is within the specified tolerance. Instead of creating individual functions for measuring each pin connector, you can create a loop of cells to simplify the Spreadsheet job.

This example demonstrates the following:

- How to use the Repeat, RepeatUntil and StoreData functions to construct loops

- How to use the ExtractBlobs, SortBlobs and Caliper functions to identify a feature of interest and inspect it for height and width tolerances

- How to create a Custom View displaying the graphical elements of the inspection and statistical data about the pin connectors

This example uses a sample job and sample image set that is available on your local drive with your In-Sight installation in the following directory:

    C:\ProgramData\Cognex\In-Sight\In-Sight\25.3.0\SampleJobs\Repeat

### Load the Sample Application and Images

1. Connect to your In-Sight vision system or emulator, and open Spreadsheet.

2. Select Open Job from the File menu, and navigate to the directory where the sample job is installed.

3. Open the demo connector pin inspection.jobx file.

4. After the job loads, click on the Select the Record/Playback directory button in the bottom left corner of the Filmstrip.

5. In the browsing dialog, select the directory where the sample job is installed to load the images contained by the directory into the Filmstrip.

### Count, Locate, and Measure the Pin Connectors

The first step in this application is identifying and locating the pin connectors. The job achieves this by using the ExtractBlobs and SortBlobs functions. The ExtractBlobs function inspects the image to determine the number (roughly 204 in each inspection) and position of each pin connector. The SortBlobs function then determines the location of each pin connector, relative to a fixture A fixture is a coordinate location on the image that keeps the tool in the place determined by the fixture..

After the Blob A blob is a group of pixels on the image that form a distinct pattern, typically used to represent objects, features, or areas of interest in object detection and segmentation tasks. tools count and locate the pin connectors, the Caliper function determines the width and height of each pin connector in pixels. Using the Caliper function output, the job then puts the results through an InRange logic function to determine whether or not the pin connector is within a 0.500 tolerance.

The following procedure takes you through how the pin connector measurement section of the sample job is constructed:

1. Configure the Blob tools to count and locate the individual pin connectors:

   1. Open the property sheet for the ExtractBlobs function by double-clicking on the Blobs data structure in cell A6.

   2. You can see that the Region parameter is set to the entire image area, as the pin connector position varies from image to image and a fixture is not necessary for quick blob analysis.

   3. The Number to Sort parameter is set to 500. To speed up the performance of the tool, lower the number to 250.

   4. For further performance enhancements, you can set the Color: Blob parameter to white, and the Color: Background parameter to black.

   5. Set the Area Limit: Min parameter to 10, and the Area Limit: Max to 100. This prevents any extraneous object that may appear in the image from being classified as a pin connector.

   6. Click the OK button to close the ExtractBlobs property sheet.

   7. Next, open the property sheet of the SortBlobs function by double-clicking on the Blobs data structure in cell A7.

   8. You can see that the Blobs parameter is set as an absolute reference to the Blobs data structure returned by the ExtractBlobs function in cell A6.

   9. To speed up the performance of the tool, set the Number to Sort parameter to 250.

   10. You can see that the Sort By parameter is set to grid - X, then Y, from Fixture origin.

   11. Click the OK button to close the SortBlobs property sheet.

2. Configure the Caliper for horizontal and vertical measurements:

   1. Open the property sheet for the horizontal Caliper function by double-clicking on the Edges data structure in cell G13.

   2. You can see that the Fixture parameter is set as an absolute reference to cells E13 and F13.

      Cell E13 contains the results of the SortBlobs function, providing the Row and Column pixel coordinates for the individual pin connector.

      Cell F13 contains the GetLoopCount function, which determines which indexed pin connector the job is inspecting based on the position in the loop.

   3. The Region parameter is configured around the width of bottom-right pin connector.

   4. The Edge Mode parameter is set to Edge Pair, with the first edge being the left-hand side of the pin connector, and the second edge being the right-hand side of the pin connector.

   5. The Edge Distance parameter is set to 9, the maximum pixel distance of the width of the pin connector.

   6. Click the OK button to close the property sheet of the horizontal Caliper function.

   7. Next, open the property sheet for the vertical Caliper function by double-clicking on the Edges data structure in cell L13.

   8. You can see that the Fixture parameter is also set as an absolute reference to cells E13 and F13.

   9. The Region parameter is configured around the height of the bottom-right pin connector.

   10. The Edge Mode parameter is set to Edge Pair, with the first edge being the top of the pin connector, and the second edge being the bottom of the pin connector.

   11. The Edge Distance parameter is set to 8, the maximum pixel distance of the height of the pin connector.

   12. Click the OK button to close the property sheet of the vertical Caliper function.

3. Create the Spreadsheet logic for each Caliper tool to calculate whether the measured results are in range:

   1. The GetEdgeDistance functions extract the distance between the edge pairs returned by the Caliper functions.

   2. The EditInt functions define the acceptable ranges for width, height, and tolerance.

   3. The InRange functions calculate whether the distances extracted by the GetEdgeDistance functions are within the range defined by the EditInt functions.

   4. The Stringf functions puts out a text string containing a floating point value with two decimal places, indicating how many pixels outside the range the distance measurement is.

   5. Two PlotLine functions draw the found edges returned by the Caliper functions, and a PlotString function draws any defect information that is put out by the Stringf function.

### Construct the Loop

Without loops, the process of counting, sorting and measuring all 204 pin connectors would require configuring 408 Caliper functions to account for both height and width, and a table returned by the SortBlobs function for all 204 found pin connectors with associated GetRow, GetCol and PlotLine functions. Looping greatly reduces the amount of space required to program the Spreadsheet, and also reduces the complexity and amount of time needed to construct.

Creating a loop involves storing the data for each iteration of the internal loop, and graphically representing the data of the accumulated inspections within the loop. During the inspection, the PlotLine function draws the edge output by the Caliper function, and a PlotString function creates an error message if necessary. In order to capture all of the graphical elements of the repeated inspections, a StoreData function is associated with each of the PlotLine and PlotString functions, and the StoreData functions are placed within the cells that constitute the loop.

In this example, the PlotLine function outputs its graphical data for each inspection into the StoreData function, which accumulates and stores the outputs for each pin connector. Without the StoreData function, only the last inspected pin connector would be displayed. At the end of the loop, the StoreData function outputs its accumulated data to a PlotData function, which generates the graphics from the accumulated loop.

The following procedure takes you through how the loop section of the sample job is constructed:

1. Configure the PlotLine, PlotString, and StoreData functions:

   1. Open the property sheet for the PlotLine function by double-clicking on the Plot data structure in cell J13.

   2. You can see that the Line parameter has been constructed with dynamic arguments inside the Row and Column coordinates, using GetRow and GetCol functions to extract the edge data from the Caliper function and graphically draw the horizontal edge of the pin connector.

   3. The Color parameter has also been constructed with a dynamic argument using the result output in cell I13, which alternates between 0 and 1 based on the results of the Caliper function (0 for no edge and 1 for an edge), to determine if the color displayed is red or green.

   4. The Show parameter is an absolute cell reference to cell J1, which is a CheckBox function that controls whether or not the line is displayed by the Custom View.

   5. Each of the additional PlotLine and PlotString functions for the horizontal and vertical Caliper functions are constructed in the same way.

   6. Click OK to close the property sheet for the PlotLine function.

   7. Open the property sheet for the StoreData function by double-clicking the Data data structure in cell K13.

   8. The Event parameter is an absolute cell reference to the Image data structure in cell A0, containing the AcquireImage function. This means that the function executes every time an image is acquired.

   9. The Data parameter is a cell reference to the Plot data structure returned by the PlotLine function in cell J13. This means that the StoreData function captures all of the graphical display data output by the PlotLine function during the execution of the loop.

   10. The Number of Steps parameter is an absolute cell reference to the GetNFound Blobs function in cell D6, which extracts the total number of blobs found from the ExtractBlobs function. This number determines how many times the StoreData function accumulates data.

   11. Each of the additional StoreData functions for the horizontal and vertical Caliper functions are constructed in the same way.

   12. Click OK to close the property sheet for the StoreData function.

   13. Open the property sheet for the PlotCircle function by double-clicking on the Plot data structure in cell R13. This function draws a circle over any pin connectors that fail.

   14. You can see that the Circle parameter has been constructed with dynamic arguments inside the Row and Column coordinates, using GetRow and GetColfunctions to extract the edge data from the Caliper function, and dividing the extracted data by two to extrapolate the coordinates for the origin of the circle to be drawn when a pin connector fails the inspection.

   15. The Show parameter uses a dynamic argument using two Mathematics functions to determine whether or not the circle needs to be drawn.

   16. The StoreData function in cell S13 is configured to capture the data output by the PlotCircle function in cell R13.

2. Configure the Repeat and GetLoopCount functions:

   1. Select the Loop data structure returned by the Repeat function in cell B13. You can configure the Repeat function directly in the cell or formula bar on the Job Edit toolbar. With the Loop data structure selected, the formula for the Repeat function is visible in the formula bar.

   2. The Event parameter is an absolute cell reference to the Image data structure in cell A0, which contains the AcquireImage function. This means that the function executes every time an image is acquired.

   3. The Iterations to Repeat parameter is an absolute cell reference to the GetNFound function in cell D6, which extracts the total number of blobs found from the ExtractBlobs function. This number determines how many times the functions executes.

   4. The Cells to Repeat parameter is configured to encapsulate the cells from D13 to S15.

   5. Select the GetLoopCount function contained in cell D13. This function keeps a count of the loop and determines which indexed pin connector is being inspected.

   6. The "Blob Results" output in cells E13 (GetRow) and F13 (GetCol) is constructed to reference the SortBlobs function for their position, and the GetLoopCount function for their index.

3. Configure the PlotData functions:

   1. Open the property sheet of the PlotData function by double-clicking the Plot data structure in cell A19.

   2. The Data parameter is set as a relative reference to the Data data structure returned by the StoreData function in cell K13. The PlotData function constructs the graphical display based on the data stored in the StoreData function.

   3. Each of the StoreData functions contained in the loop have an associated PlotData function to draw the graphics accumulated during the loop.

### Display the Custom View

You can create a Custom View for operator interaction during the job run time. This functionality allows the operator to potentially adjust the Nominal Width or Height values, the Tolerance values, and whether or not certain results are visualized.

Using Controls functions such as EditInt and CheckBox functions allows operators to easily interact with the job at run time.

The following procedure takes you through how the Custom View of the sample job is constructed:

1. Configure the Controls functions for the Custom View:

   1. Select the EditInt function in cell G1. This EditInt function, as well as the EditInt functions in cells G2, H1 and H2, is used as an input for the InRange functions in cells in I13 and N13. These Controls functions calculate whether or not the distance returned by the GetEdgeDistance function is within tolerance.

   2. Select the CheckBox function in cell J1. This CheckBox function is used as an input to the PlotLine functions in cells J13 and J14. This Controls function determines whether or not the horizontal results of the Caliper function are displayed.

   3. Select the CheckBox function in cell J2. This CheckBox function is used as an input to the PlotLine functions in cells O13 and O14. This Controls function determines whether or not the vertical results of the Caliper function are displayed.

   4. Select the CheckBox function in cell N1. This CheckBox function is used as an input to the PlotCircle function in cell R13. This Controls function determines whether or not to display a red circle over defective pin connectors.

   5. Select the CheckBox function in cell N2. This CheckBox function is used as an input to the PlotString functions in cells J15 and O15. This Control determines whether or not to display the value of the Caliper function as a numeric text string.

2. Configure the Custom View display:

   1. From the Edit menu, select Custom View Settings to open the Custom View Settings dialog.

   2. The Cell Range setting defines the range of cells that includes the Controls functions described in the Configuring the Controls Functions for the Custom View section of this procedure.

   3. Click the Move/Resize button to enter interactive graphics mode, where you can change the position of the Controls functions in the image overlay.

   4. Click the OK button to close the Custom View Settings dialog.

   5. From the View menu, select Custom View to display the Custom View user interface. This is the user interface that the operator interacts with during the job run time.

   6. To simulate run time, click the Play button in the Record/Playback toolbar. This starts the playback of the images in the Playback folder, simulating job execution during run time.