Dalsa Using Sherlock in Excel or other COM environments
Contents
1. l 1l MAior B Brgy x als Fs Ree eke a see we Vale BSS WN Sheet Sheet Sheets iat I i e Right Click the top command button and select Properties Change the Name to cmdConnect and change the Caption to CONNECT Change the remaining buttons to cmdDisconnect and DISCONNECT cmdStart and START cmdStop and STOP e Inthe same manner as the command buttons change the ListBox name to lboxStakeouts one ComboBox name to cboxVariable1 and the other ComboBox name to cboxVariable2 e Exit Design Mode by clicking on Exit Design Mode Close the Control Toolbox a a 5 KN Sheeti Sheet Sheets P Sherlock_XL_AppNote doc Page 5 of 12 g PHONE 877 40 FABER 877 403 2237 www faberinc com Add project reference to Sherlock H tai Microsoft Visual Basic Book1 lol xj library File Edit view Insert Format Debug Run Tools Add Ins Window Help Type a que Da B r s Aa B teed ocs gt nA NESO a e Open the Visual Basic Editor To do so mz zmz select Macro from the Tools menu then pgmesmm a Visual Basic Editor or press alt F11 a sexi Gea e From the Visual Basic editor click the energy Tools menu item and select References to bring up the VBAProject References Dialog box Note that SpDisplay 1 0 Type Library is already included as part of the Sherlock E Display added to Sheet1 earlier E e Unselected entries are arranged p alphabetically followin2. d Sherlock mode is set to Halt_Inspections ignored if already halted The Sherlock Display window on Sheet1 is disconnected from its stakeout ignored if already disconnected Sherlock object is deactivated set to Nothing The DISCONNECT button is re captioned to be the CONNECT button 6 User closes the workbook a b C Sherlock mode is set to Halt_Inspections ignored if already halted The Sherlock Display window on Sheet1 is disconnected from its stakeout ignored if already disconnected If not already done Sherlock object is deactivated set to Nothing The VBA code may be examined in the Visual Basic Editor To do so select Macro from the Tools menu then Visual Basic Editor or press alt F11 P Sherlock_XL_AppNote doc Page 3 of 12 PHONE 877 40 FABER 877 403 2237 E END AI recHvoLocies www faberinc com Tutorial Creating the workbook This tutorial will show you how to create an Excel workbook which uses the Sherlock COM methods events and properties and which includes a Sherlock ActiveX Display window for live viewing of a stakeout Open a new workbook e Close all open workbooks and open a new Excel workbook Enter Design Mode e Select the View menu then Toolbars then Control Toolbox see screenshot e The Control Toolbox may be shaped differently than this illustration You can reshape it by dragging its edges e Mouse hover over the icons in the toolbox to see pop up descripti
3. 1 z a Press STOP button 4 to change stakeout 5 view or variables 6 EA 8 gl 10 11 12 soCalStraight 13 soCalLength DISCONNECT i i 5 Display Number 16 Enter name of 2 Sherlock variables to display 1 to send to Sherlock GET Strin GET Number SETPOINT Number 25 Name ResultBent z Max Difference Ej SetpointBentDiff x 26 Values PIN STRAIGH 2 336734538 27 PIN BENT 1769361692 3 5 PIN STRAIGHT 0 657456984 PIN STRAIGH 1 821469121 PIN BENT 11 14210394 PIN STRAIGHT 2 368652922 PIN STRAIGH 1 95972566 PIN STRAIGH 2 8067 40867 PIN STRAIGHT 1 26849796 PIN BENT 9 150206857 PIN STRAIGH 0 498462976 PIN STRAIGHT 0 795038174 PIN BENT 10 94647829 PIN STRAIGHT 1 921410736 PIN STRAIGHT 1 039154916 PIN STRAIGH 1 921410736 it 4 iN Sheets Sheek Sheets lal HT e The CONNECT button is re captioned to be the DISCONNECT button 2 User clicks the START button on Sheett1 a The Sherlock Display window on Sheet1 is connected to the stakeout whose name b b is selected in step 1c Sherlock mode is set to Continuous_Inspection Every time an investigation is completed a Sherlock Run_Completed event will be raised which means that the host environment Excel in this case calls a subroutine named Sherlock_Run_Completed in the code module for Sheet1 c The START button is re captioned to be the STOP button 3 For each Sherlock Run_Completed event a The Sherlock Display window on Sheett is ins
4. PHONE 877 40 FABER 877 403 2237 acct Eee www faberinc com Application Note Using Sherlock in Excel or other COM environments Sherlock in addition to being a stand alone machine vision application is a COM server COM is an acronym for Component Object Model which is a software architecture specification designed by Microsoft that allows components supplied by independent software vendors to interoperate in a dependable regulated fashion in a Windows based environment The most common use of Sherlock s COM is within a Visual Basic Graphical User Interface There may be situations occasions when you may need to use a front end other than Visual Basic for Sherlock For example you may want to incorporate your Sherlock vision system into the commercial HMI package such as RSView or LabVIEW controlling your application Or you may want to provide a simplified front end to your operators in the form of an Excel spreadsheet This Application Note describes the use Sherlock s COM server ability within an Excel workbook The procedure will be very similar to any other COM environment and also to the procedure described in the Sherlock User Manual for including Sherlock in a Visual Basic project The main difference is that the Excel workbook environment already exists whereas you create the Visual Basic environment from scratch in the form of a Visual Basic Project Also you are very likely to already own Microsoft Excel but may not
5. ate and activate Sherlock object load last active investigation Application StatusBar Loading Sherlock last investigation Set objSherlock CreateObject Sp32 Sherlock nErr objSherlock InvestigationLoadLast Get last active investigation nErr objSherlock InvestigationNameGet FulllnvestigationName Get the investigation name Display fully qualified investigation name in StatusBar Application StatusBar Sherlock investigation at amp FulllnvestigationName Get names of all stakeouts so user can select one for display nErr objSherlock StakeoutsGet strNames For iCtr 0 To UBound strNames lboxStakeouts Addltem strNames iCtr Next iCtr lboxStakeouts ListIndex 0 P Sherlock_XL_AppNote doc Page 8 of 12 PHONE 877 40 FABER 877 403 2237 acct Eee www faberinc com Get names of all STRING and NUMBER variables so user can select a couple for display All others are ignored Variables can be of type VAR_STRING VAR_NUMBER VAR_BOOL VAR_POINT VAR_LINE VAR_ NUMBER_ARRAY VAR_BOOL_ARRAY VAR_POINT_ ARRAY VAR_LINE_ ARRAY or VAR_STRING_ARRAY nErr objSherlock VarGetInfo VarNames VarType For iCtr 0 To UBound VarNames If VarType iCtr VAR_STRING Then cboxVariable1 Addltem VarNames iCtr Elself VarType iCtr VAR_NUMBER Then cboxVariable2 Additem VarNames iCtr End If Next iCtr Enable the START button cmdStart Enabled True End Sub cmdConnect_Click Priva
6. g selected entries E Scroll down to Sp32 1 0 Type Library P mmc aura and select it by checking its checkbox FS Click OK 5032 1 0 Type Library Location Di k Bin sp32 exe Language Standard Add event handlers to the buttons e While still in the Visual Basic Editor paste the Code For Sheet1 in the VBA Code section at the end of this document80 e into the Sheet1 Code window If it is not already open double click on Sheet1 in the Project Explorer pane on the left side of the screen e Paste the Code For ThisWorkbook in the VBA Code section into the ThisWorkbook Code window If it is not already open double click on ThisWorkbook in the Project Explorer pane on the left side of the screen Add a named range to Sheet1 Seeeame aso s uma m o e Go back to the worksheet view se a ee Sheet A e Click on cell G21 A l e Select the Insert menu item n then Name then Define H ami e Enter DisplayNumber in the A dialog s name line Click OK Enter the number of variables to display before wrapping back to the top Enter 12 for now You can change it at any time j JESS euseseeeseuesuneunies MA sheets X Sheet Z Sheet 7 isi P Sherlock_XL_AppNote doc Page 6 of 12 Parer INDUSTRIAL TECHNOLOGIES PHONE 877 40 FABER 877 403 2237 www faberinc com Save the workbook and test it Ei Microsoft Excel Tutorialads G e ga yew puert Fome joos Chart
7. have access to Visual Studio This Application Note includes the workbook RunSherlock xls It will run as described in the next section connecting to Sherlock and loading the last active investigation The tutorial section following that gives instructions for creating your own workbook with code to paste into your VBA project P Sherlock_XL_AppNote doc Page 1 of 12 g i vousTRiaL Pit 2 L TECHNOLOGIES PHONE 877 40 FABER 877 403 2237 www faberinc com RunSherlock xls Workbook Operation The workbook responds to the following 6 events 1 User Clicks CONNECT button on Sheet1 a A Sherlock object objSherlock is created on Sheet1 This object is not visible but can be manipulated with VBA code in the code module for Sheet1 Sherlock object loads the last investigation that was run A list of all Stakeouts in the investigation is obtained from Sherlock A ListBox on Sheet1 is populated with the stakeout names A list of all Sherlock variables in the loaded investigation and their types is obtained from Sherlock All STRING types are added to one combo box on Sheet1 and all NUMBER types are added to two other combo boxes on Sheet1 Other variable types such as BOOLEAN are ignored REE File Edit View Insert Format Tools Data Window Help pe a question for hep Mua E X De A SQMY B S E 2l l i 100m g pa ele z u Aslam a w lE El si f Hay
8. ivate Sub objSherlock_RunCompleted ByVal bSuccess As Long _ ByVal dTime As Double Dim strNames As String Dim VariName As String var1 Value As String Dim Var2Name As String var2Value As Double Dim Var3Name As String var3Value As Double Dim NbrToShow As Integer Static iCounter As Long Dim iCtr As Integer iCtr2 As Integer Dim DispRow As Integer SpWindow1 UpdateDisplay Update Stakeout display P Sherlock_XL_AppNote doc Page 10 of 12 PHONE 877 40 FABER 877 403 2237 acct Eee www faberinc com DispRow Range DisplayNumber Row 1 If Range DisplayNumber lt gt Then NbrToShow Range DisplayNumber Else NbrToShow 12 Range DisplayNumber 12 End If Track the rolling display of current reading iCtr iCounter Mod NbrToShow Track prior reading iCtr2 iCounter 1 Mod NorToShow Update the static counter for next reading iCounter iCounter 1 Refresh display of string var 1 if specified by user If Trim cboxVariable1 Text lt gt Then VariName Trim cboxVariable1 Text nErr objSherlock VarStringGet Var1 Name var1 Value Sheet1 Range BDispRow var1 Value Cells DispRow iCtr 2 var1 Value Cells DispRow iCtr 2 Font Bold True Highlight the current reading Cells DispRow iCtr2 2 Font Bold False Un Highlight the prior reading End If Refresh display of number var 2 if specified by user If Trim cboxVariable2 Text lt gt Then Var2Name Tri
9. l Chart svt wizard Bs S ole ole olo SSS 5 isle lt isie eiais Sisleialsis eels ses soCalStraight soCalLength e Click STOP Reformatting the spreadsheet while Sherlock is updating it will result in an error e Highlight the cells containing Sherlock values e Start the Chart Wizard Insert menu then select Chart e Click Line Chart then click Finish e Resize by grabbing the sizing handles at the corners e Click the START button The chart will be updated in real time CONNECT DISCONNECT ResutBent z MaxOitlerance z 16 PIN STRAIGHT 257s PIN BENT 17 68062 PIN STRAIGHT 0 657457 ases PIN STRAIGHT 1 821469 PIN BENTI 11 1421 PIN STRAIGHT 2 353953 PIN STRAIGHT 1 959726 PIN STRAIGHT 2 006741 PIN STRAIGHT PIN BENT 9 150207 PIN STRAIGHT 0 498453 PIN STRAIGHT 0 796036 PIN BENT 10 94648 5 PIN STRAIGHT amis pa eee res wnaiaew PIN STRAIGHT 2 336735 Page 7 of 12 PHONE 877 40 FABER 877 403 2237 Eroe EA www faberinc com VBA Code Sheet1 Code Copy and paste into the Sheet1 code module of your VBA project as instructed in the tutorial section Option Explicit Public WithEvents objSherlock As Sherlock Public nErr As RET_TYPE Public strSource As String Public FulllnvestigationName As String Private Sub cmdConnect_Click Dim strinvName As String Dim iCtr As Integer Dim strNames As String Dim VarNames As String VarType As Long Cre
10. m cboxVariable2 Text nErr objSherlock VarNumberGet Var2Name var2Value Cells DispRow iCtr 5 var2Value Cells DispRow iCtr 5 Font Bold True Highlight the current reading Cells DispRow iCtr2 5 Font Bold False Un Highlight the prior reading End If Show inspect time and result Application StatusBar IIf bSuccess 1 Inspect Success Inspect Failure amp _ time amp FormatNumber dTime 2 voTrue amp msec End Sub End objSherlock_RunCompleted P Sherlock_XL_AppNote doc Page 11 of 12 PHONE 877 40 FABER 877 403 2237 acct Eee www faberinc com ThisWorkbook Code Copy and paste into the ThisWorkbook code module of your VBA project as instructed in the tutorial section Option Explicit User has asked Excel to close If not already done halt disconnect and free Sherlock first for a clean Sherlock shutdown Private Sub Workbook_BeforeClose Cancel As Boolean Dim RetCode As RET_TYPE If Not Sheet1 objSherlock Is Nothing Then Application StatusBar Closing Sherlock before closing workbook RetCode Sheet objSherlock InvestigationModeSet _HALT Sheet1 SpWindow1 DisconnectStakeout Set Sheet1 objSherlock Nothing End If End Sub Workbook_BeforeClose Contact IPD Engineer Report Author Bob McMinn IPD Applications Engineer Phone 978 670 2075 rmcminn goipd com P Sherlock_XL_AppNote doc Page 12 of 12
11. ons of the controls Click the icon in the upper left of the Control Toolbox Enter Design Mode When in design mode this icon becomes Exit Design Mode W Ble Edt Yew poet Forms Joss ate Window tib 3 es O Bope Break Preview C E Ai A BY oases E tonaren trde gt HN Sheett Sheet Z Sheet 7 i sessseresesyeNe r P Sherlock_XL_AppNote doc E Si Ma or OC Sx B22 Re A e H T J lsi TE Ek yew pomt Foma Joos Qee Window thp 0x Og anM e o o o E B e Anai omms z UA A BEB Sx BH REEDA Al amp 8 3 D E F G H J K 5 a a a a x m zis gg usuegi OP KN sheets lt o Z hot KI j wifes teady nm z Page 4 of 12 PHONE 877 40 FABER 877 403 2237 E ABER _ pamertey www faberinc com Add Stakeout Display Command Buttons and ListBoxes e While in Design Mode click the icon at lower right More Controls e Select SoWindow Class from the pop up list The items are listed alphabetically e Draw arectangle from cell A1 to cell E19 for your SherlockDisplay It can be resized later The control will be transparent while in Design Mode but is visible in Run Mode e Select controls from the Control Toolbox by clicking the control s icon once then drawing the shape on the worksheet Add 4 command buttons one List Box and two Combo Boxes as shown below G Ble C Yew iret Format Took Qaa Window tsp 2 8 x Osu 6a B f o o
12. te Sub cmdDisconnect_Click Close out Sherlock If Not objSherlock Is Nothing Then Application StatusBar Closing Sherlock nErr objSherlock InvestigationModeSet l_HALT SpWindow1 DisconnectStakeout Set objSherlock Nothing End If Clear names of all stakeouts and variables lboxStakeouts Clear lboxStakeouts ListIndex 1 cboxVariable1 Clear cboxVariable1 ListIndex 1 cboxVariable2 Clear cboxVariable2 ListIndex 1 Disable the START button and prompt for action cmdStart Enabled False Supress messages asking user to save workbook Application DisplayAlerts False P Sherlock_XL_AppNote doc Page 9 of 12 PHONE 877 40 FABER 877 403 2237 Eroe EA www faberinc com End Sub cmdDisconnect_Click Private Sub cmdStart_Click Dim strCurName As String Connect selected stakeout and start investigations strCurName lboxStakeouts Text Get name from ListBox SpWindow1 ConnectStakeout strCurName nErr objSherlock InvestigationModeSet l_CONT End Sub cmdStart_Click Private Sub cmdStop_Click Halt investigations and Disconnect stakeout If Not objSherlock Is Nothing Then nErr objSherlock InvestigationModeSet l _HALT SpWindow1 DisconnectStakeout End If TextBox1 Text Select a stakeout from the list below then press START button End If End Sub cmdStartup_Click Investigation Complete Update the stakeout display Display Set any variables specified by user Pr
13. tructed to refresh its view If the user has selected a STRING variable from combo box Variable1 the value of that variable is obtained from Sherlock and posted in a worksheet cell under the combo box P Sherlock_XL_AppNote doc Page 2 of 12 g i vousTRiaL Pit 2 L TECHNOLOGIES C PHONE 877 40 FABER 877 403 2237 www faberinc com If the user has selected a NUMBER variable from combo box Variable2 the value of that variable is obtained from Sherlock and posted in a worksheet cell under the combo box Variable3 is intended to display a setpoint a variable that sets limits for determining Pass or Fail If one has been selected by the user and it s value has been changed in its Textbox the new value is sent to Sherlock If the user has selected a setpoint variable from combo box Variable3 the new value of that variable is obtained from Sherlock and posted in a worksheet cell under the combo box 4 User clicks the STOP button on Sheet1 b C d e The Sherlock Display window on Sheet1 is disconnected from its stakeout Sherlock mode is set to Halt_Inspections User can now select a different stakeout or select different variables to display or set He can also change the number of values that are displayed User can either click Start can DISCONNECT Sherlock or can close the workbook The STOP button is re captioned to be the START button 5 User clicks the DISCONNECT button on Sheet1 a b C
14. window tep Save the workbook You might call it Tutorial xls Or you might name it after your favorite machine vision supplier ipd is spelled with all lowercase letters Click CONNECT Watch for progress messages in the status bar Click START Select a variable from a combo box and watch it display lf VBA detects an error it will raise a message box Click on the Debug button to display the line of VBA code or sometimes the VBA code module that caused the error X Microsoft Becel Tutorials G fle cd yew puert Foma Joos Qwea window tep OSE SAMY B S o EA B o L farw Jo JB zy Ar Bls x AA wwe aA k 12 A B c D e F G H 1 J KG i 2 ENA START 4 Le STOP 6 7 soTop W Bo soCalStraight soCalLength _connect_ DISCONNECT a El ResunBent z iomon E 12 PIN STRAIGHT 1 821469 PIN BENT 11 1421 PIN STRAIGHT 2 350653 PIN STRAIGHT 1 959726 PIN STRAIGHT 2006741 PIN STRAIGHT 1 208498 PIN BENT 9 150207 29 PIN STRAIGHT 1039155 E PIN STRAIGHT 1921411 31 PIN STRAIGHT 233735 E PIN BENT 17 6932 33 PIN STRAIGHT 0 657457 u 35 EM v7 E N 2 40 Al az 4 43 gt H 4 gt H sheets Seet Z sheets dal J Inspect Skxcess tine 40 53 msec Bonus Feature OSM SAKI BF o m 2 H Bal G i alr n Ar msx aar tA An example of a way to take advantage of the Series 1 268 amp SERIES Sheet1 SE Sheet 1 SE 22 E 37 1 eyo OR A 3 ests host environment is by use of the Exce
Download Pdf Manuals
Related Search