OPC Expert enables Excel to read OPC items containing 2-dimensional arrays. Excel can show the entire array (containing multiple elements), or a single array element.
Syntax: Array2D(Column, Row, 1-base, SwitchColumnRow)
Column: Integer index number of desired column. By default, first column is 1. To use 0 as the first column, set parameter “1-base” to 0.
Row: Integer index number of desired row. By default, first row is 1. To use 0 as the first column, set parameter “1-base” to 0.
1-base: Boolean specifying index of first row and column. When set to TRUE, the first index is 1. When set to FALSE, the first index is 0. This parameter is optional and is TRUE by default.
SwitchColumnRow: Boolean specifying parameters are Column and Row and not the reverse. TRUE specifies arguments are Row and Column. FALSE specifies arguments are Column and Row. This parameter is options and is FALSE by default.
Consider the following array contents:
| # | A | B | C |
| 1 | Apple | Orange | Pear |
| 2 | Bike | Car | Truck |
| 3 | Ant | Mouse | Zebra |
| 4 | Brook | Creek | River |
Table below shows an Excel examples and results
| # | Cell Contents | Cell Value |
| 1 | Array2D(0,0) | Apple |
| 2 | Array2D(1,0) | Apple |
| 3 | Array2D(0,1) | Apple |
| 4 | Array2D(2,1) | Orange |
| 5 | Array2D(3,4) | River |
| 6 | Array2D(1,6) | <blank> |
| 7 | Array2D(5,2) | <blank> |
| 8 | Array2D(2,3) | Mouse |
| 9 | Array2D(2,3,TRUE) | Mouse |
| 10 | Array2D(2,3,FALSE) | River |
| 11 | Array2D(2,3,TRUE,FALSE) | Mouse |
| 12 | Array2D(2,3,TRUE,TRUE) | Truck |
Notes:
- Example #6 shows a blank value because array only has 3 columns, and we are asking for column number 6
- Example #7 shows a blank value because array only has 4 rows, and we are asking for row number 5
OPC Classic (DA) Example
Consider we have an OPC DA server with the following information
- Computer name: MyComputer
- OPC Server ProgID: MyOpcServer.ProgID
- OPC ItemID: MyItem
- Update rate: 1000 milliseconds (we want updates as fast as once per second, but not faster)
Following are OPC DA examples using Array2D
| # | Cell Contents | Cell Value |
| 1 | =RTD(“expertrtd”,,”MyComputer”,”MyOpcServer.ProgID”,”MyItem”,,1000) | Apple, Orange… River |
| 2 | =RTD(“expertrtd”,,”MyComputer”,”opc.tcp://MyComputer:57888.ProgID”,”MyItem”,”Array2D(2,1)”,1000) | Orange |
| 3 | =RTD(“expertrtd”,,”localhost”,”MyOpcServer.ProgID”,”MyItem”,”Array2D(0)”,1000) | Apple |
OPC UA Example
Consider we have an OPC DA server with the following information
- Computer name: MyComputer
- OPC Server URL: opc.tcp://MyComputer:57888
- OPC ItemID: MyItem
- Update rate: 1000 milliseconds (we want updates as fast as once per second, but not faster)
Following are OPC UA examples using Array2D
| # | Cell Contents | Cell Value |
| 1 | =RTD(“expertrtd”,,”MyComputer”,”opc.tcp://MyComputer:57888.ProgID”,”MyItem”,,1000) | Apple, Orange… River |
| 2 | =RTD(“expertrtd”,,”MyComputer”,”opc.tcp://MyComputer:57888.ProgID”,”MyItem”,”Array2D(2,1)”,1000) | Orange |
| 3 | =RTD(“expertrtd”,,”localhost”,”opc.tcp://MyComputer:57888.ProgID”,”MyItem”,”Array2D(0)”,1000) | Apple |
Notes:
- The only difference between the OPC DA and UA examples is the OPC server reference
- Remember to enclose array element reference in quotations
- Example #3 uses a generic reference of “localhost” instead of “MyComputer”
See also: