Online Forums

Technical support is provided through Support Forums below. Anybody can view them; you need to Register/Login to our site (see links in upper right corner) in order to Post questions. You do not have to be a licensed user of our product.

Please read Rules for forum posts before reporting your issue or asking a question. OPC Labs team is actively monitoring the forums, and replies as soon as possible. Various technical information can also be found in our Knowledge Base. For your convenience, we have also assembled a Frequently Asked Questions page.

Do not use the Contact page for technical issues.

Read multiple values in VBA

dani.hartman@metronik.si
Topic Author
Offline
Senior Member

06 Feb 2018 10:05 #5967 by dani.hartman@metronik.si

Replied by dani.hartman@metronik.si on topic Read multiple values in VBA

It works!!!

Thank you very much for your help.
Best regards.

Please Log in or Create an account to join the conversation.

support
Offline
Moderator

04 Feb 2018 10:32 #5943 by support

Replied by support on topic Read multiple values in VBA

Your code does not work because the line

Code:

Dim Client As EasyUAClient

should rather be:

Code:

Dim Client As New EasyUAClient

Attached is a working Excel sheet that connect to our sample server.

The code looks like this:

Code:

Private Sub Workbook_Open()
    ' Create EasyOPC-UA component
    Dim Client As New EasyUAClient
    
    Dim ReadArguments1 As New UAReadArguments
    ReadArguments1.EndpointDescriptor.UrlString = "http://opcua.demo-this.com:51211/UA/SampleServer"
    ReadArguments1.NodeDescriptor.NodeId.expandedText = "nsu=http://test.org/UA/Data/;i=10845"
 
    Dim ReadArguments2 As New UAReadArguments
    ReadArguments2.EndpointDescriptor.UrlString = "http://opcua.demo-this.com:51211/UA/SampleServer"
    ReadArguments2.NodeDescriptor.NodeId.expandedText = "nsu=http://test.org/UA/Data/;i=10853"
 
    Dim ReadArguments3 As New UAReadArguments
    ReadArguments3.EndpointDescriptor.UrlString = "http://opcua.demo-this.com:51211/UA/SampleServer"
    ReadArguments3.NodeDescriptor.NodeId.expandedText = "nsu=http://test.org/UA/Data/;i=10855"
 
    Dim arguments(2) As Variant
    Set arguments(0) = ReadArguments1
    Set arguments(1) = ReadArguments2
    Set arguments(2) = ReadArguments3
 
    ' Read nodes and obtain results.
    Dim results() As Variant
    results = Client.ReadMultiple(arguments)
    
    Dim ReadResult1 As UAAttributeDataResult: Set ReadResult1 = results(0)
    Dim ReadResult2 As UAAttributeDataResult: Set ReadResult2 = results(1)
    Dim ReadResult3 As UAAttributeDataResult: Set ReadResult3 = results(2)
 
    ' Display the results.
    Range("A1") = ReadResult1
    Range("A2") = ReadResult2
    Range("A3") = ReadResult3
    
End Sub

Best regards

Attachments:

The following user(s) said Thank You: dani.hartman@metronik.si

Please Log in or Create an account to join the conversation.

dani.hartman@metronik.si
Topic Author
Offline
Senior Member

03 Feb 2018 18:16 #5942 by dani.hartman@metronik.si

Replied by dani.hartman@metronik.si on topic Read multiple values in VBA

Thank you for examples.

I've tried to do the similar before - I used the code from other examples but I always got somekind of error.

Now I tried the code from your VB6 example, which is almost like VBA, but when it tries to read (Client.ReadMultiple(arguments)) I get an error:

Run-time error '91'
Object variable or with block variable not set

There is my sample code below. Could you please give me a hint what am I doing wrong

Sub Multipla()
Dim Client As EasyUAClient

Dim ReadArguments1 As New UAReadArguments
Dim ReadArguments2 As New UAReadArguments
Dim ReadArguments3 As New UAReadArguments

If OPCUAServer = "" Then
ReadOPCUAProps
End If

ReadArguments1.EndpointDescriptor.UrlString = OPCUAServer
ReadArguments1.NodeDescriptor.NodeId.expandedText = OPCUATagPrefix & "dbAnalogSensors.TIC_01_29.ScaledMax"

ReadArguments2.EndpointDescriptor.UrlString = OPCUAServer
ReadArguments2.NodeDescriptor.NodeId.expandedText = OPCUATagPrefix & "dbAnalogSensors.TIC_01_30.ScaledMax"

ReadArguments3.EndpointDescriptor.UrlString = OPCUAServer
ReadArguments3.NodeDescriptor.NodeId.expandedText = OPCUATagPrefix & "dbAnalogSensors.LI_01_37.ScaledMax"

Dim arguments(2) As Variant
Set arguments(0) = ReadArguments1
Set arguments(1) = ReadArguments2
Set arguments(2) = ReadArguments3

' Obtain values. By default, the Value attributes of the nodes will be read.
Dim results() As Variant
results = Client.ReadMultiple(arguments)

End Sub

Please Log in or Create an account to join the conversation.

support
Offline
Moderator

02 Feb 2018 19:58 #5938 by support

Replied by support on topic Read multiple values in VBA

Hello,

you are right that this example is missing - it is simply due to the sheer amount of combinations we would have to write.

There are, however, related examples that may help you.

First, there is an example for reading multiple values in Excel VBA - but for OPC Classic. It is in file ReadAndDisplayMultipleValues.xls, and looks like this:

Code:

Private Sub Workbook_Open()
    ' Create EasyOPC-DA component
    Dim Client As New EasyDAClient
    
    ' Define OPC item IDs
    
    Dim ReadItemArguments1: Set ReadItemArguments1 = New DAReadItemArguments
    ReadItemArguments1.ServerDescriptor.ServerClass = "OPCLabs.KitServer.2"
    ReadItemArguments1.ItemDescriptor.ItemID = "Simulation.Random"
    
    Dim ReadItemArguments2: Set ReadItemArguments2 = New DAReadItemArguments
    ReadItemArguments2.ServerDescriptor.ServerClass = "OPCLabs.KitServer.2"
    ReadItemArguments2.ItemDescriptor.ItemID = "Trends.Ramp (1 min)"
    
    Dim ReadItemArguments3: Set ReadItemArguments3 = New DAReadItemArguments
    ReadItemArguments3.ServerDescriptor.ServerClass = "OPCLabs.KitServer.2"
    ReadItemArguments3.ItemDescriptor.ItemID = "Trends.Sine (1 min)"
    
    Dim ReadItemArguments4: Set ReadItemArguments4 = New DAReadItemArguments
    ReadItemArguments4.ServerDescriptor.ServerClass = "OPCLabs.KitServer.2"
    ReadItemArguments4.ItemDescriptor.ItemID = "Simulation.Register_I4"
    
    Dim arguments(3) As Variant
    Set arguments(0) = ReadItemArguments1
    Set arguments(1) = ReadItemArguments2
    Set arguments(2) = ReadItemArguments3
    Set arguments(3) = ReadItemArguments4
        
    ' Read item values
    Dim valueResults()
    valueResults = Client.ReadMultipleItemValues(arguments)
    
    ' Display the item values
    For i = 0 To 3
        Range("A1").Offset(i, 0).Value = valueResults(i).Value
    Next i
    
End Sub

Second, there are examples for reading multiple UA values in VB 6, which should be very close to VBA. They are under VB\VB60\UADocExamples, in EasyUAClientForm.frm; you can open it with a text editor, without having VB6. For convenience, here are they:

Code:

Rem This example shows how to read the attributes of 4 OPC-UA nodes at once, and display the results.

Private Sub ReadMultiple_Main_Command_Click()
    OutputText = ""

    Dim ReadArguments1 As New UAReadArguments
    ReadArguments1.EndpointDescriptor.UrlString = "http://opcua.demo-this.com:51211/UA/SampleServer"
    ReadArguments1.NodeDescriptor.NodeId.expandedText = "nsu=http://test.org/UA/Data/;i=10853"

    Dim ReadArguments2 As New UAReadArguments
    ReadArguments2.EndpointDescriptor.UrlString = "http://opcua.demo-this.com:51211/UA/SampleServer"
    ReadArguments2.NodeDescriptor.NodeId.expandedText = "nsu=http://test.org/UA/Data/;i=10845"

    Dim ReadArguments3 As New UAReadArguments
    ReadArguments3.EndpointDescriptor.UrlString = "http://opcua.demo-this.com:51211/UA/SampleServer"
    ReadArguments3.NodeDescriptor.NodeId.expandedText = "nsu=http://test.org/UA/Data/;i=10304"

    Dim ReadArguments4 As New UAReadArguments
    ReadArguments4.EndpointDescriptor.UrlString = "http://opcua.demo-this.com:51211/UA/SampleServer"
    ReadArguments4.NodeDescriptor.NodeId.expandedText = "nsu=http://test.org/UA/Data/;i=10389"

    Dim arguments(3) As Variant
    Set arguments(0) = ReadArguments1
    Set arguments(1) = ReadArguments2
    Set arguments(2) = ReadArguments3
    Set arguments(3) = ReadArguments4

    ' Instantiate the client object
    Dim Client As New EasyUAClient

    ' Obtain values. By default, the Value attributes of the nodes will be read.
    Dim results() As Variant
    results = Client.ReadMultiple(arguments)

    ' Display results
    Dim i: For i = LBound(results) To UBound(results)
        Dim Result As UAAttributeDataResult: Set Result = results(i)
        OutputText = OutputText & "results(" & i & ").AttributeData: " & Result.AttributeData & vbCrLf
    Next
End Sub

Code:

Rem This example shows how to read the Value attributes of 3 different nodes at once. Using the same method, it is also possible
Rem to read multiple attributes of the same node.

Private Sub ReadMultipleValues_Main_Command_Click()
    OutputText = ""

    ' Instantiate the client object
    Dim Client As New EasyUAClient

    Dim ReadArguments1 As New UAReadArguments
    ReadArguments1.EndpointDescriptor.UrlString = "http://opcua.demo-this.com:51211/UA/SampleServer"
    ReadArguments1.NodeDescriptor.NodeId.expandedText = "nsu=http://test.org/UA/Data/;i=10845"

    Dim ReadArguments2 As New UAReadArguments
    ReadArguments2.EndpointDescriptor.UrlString = "http://opcua.demo-this.com:51211/UA/SampleServer"
    ReadArguments2.NodeDescriptor.NodeId.expandedText = "nsu=http://test.org/UA/Data/;i=10853"

    Dim ReadArguments3 As New UAReadArguments
    ReadArguments3.EndpointDescriptor.UrlString = "http://opcua.demo-this.com:51211/UA/SampleServer"
    ReadArguments3.NodeDescriptor.NodeId.expandedText = "nsu=http://test.org/UA/Data/;i=10855"

    Dim arguments(2) As Variant
    Set arguments(0) = ReadArguments1
    Set arguments(1) = ReadArguments2
    Set arguments(2) = ReadArguments3

    ' Obtain values. By default, the Value attributes of the nodes will be read.
    Dim results() As Variant
    results = Client.ReadMultipleValues(arguments)

    ' Display results
    Dim i: For i = LBound(results) To UBound(results)
        Dim Result As ValueResult: Set Result = results(i)
        OutputText = OutputText & "Value: " & Result.value & vbCrLf
    Next

    ' Example output:
    '
    'Value: 8
    'Value: -8.06803E+21
    'Value: Strawberry Pig Banana Snake Mango Purple Grape Monkey Purple? Blueberry Lemon^
End Sub

Code:

Rem This example shows how to read the attributes of 4 OPC-UA nodes specified by browse paths at once, and display the results.

Private Sub ReadMultiple_BrowsePath_Command_Click()
    OutputText = ""

    Dim BrowsePathParser As New UABrowsePathParser
    BrowsePathParser.DefaultNamespaceUriString = "http://test.org/UA/Data/"

    Dim ReadArguments1 As New UAReadArguments
    ReadArguments1.EndpointDescriptor.UrlString = "http://opcua.demo-this.com:51211/UA/SampleServer"
    Set ReadArguments1.NodeDescriptor.BrowsePath = BrowsePathParser.Parse("[ObjectsFolder]/Data/Dynamic/Scalar/FloatValue")

    Dim ReadArguments2 As New UAReadArguments
    ReadArguments2.EndpointDescriptor.UrlString = "http://opcua.demo-this.com:51211/UA/SampleServer"
    Set ReadArguments2.NodeDescriptor.BrowsePath = BrowsePathParser.Parse("[ObjectsFolder]/Data/Dynamic/Scalar/SByteValue")

    Dim ReadArguments3 As New UAReadArguments
    ReadArguments3.EndpointDescriptor.UrlString = "http://opcua.demo-this.com:51211/UA/SampleServer"
    Set ReadArguments3.NodeDescriptor.BrowsePath = BrowsePathParser.Parse("[ObjectsFolder]/Data/Static/Array/UInt16Value")

    Dim ReadArguments4 As New UAReadArguments
    ReadArguments4.EndpointDescriptor.UrlString = "http://opcua.demo-this.com:51211/UA/SampleServer"
    Set ReadArguments4.NodeDescriptor.BrowsePath = BrowsePathParser.Parse("[ObjectsFolder]/Data/Static/UserScalar/Int32Value")

    Dim arguments(3) As Variant
    Set arguments(0) = ReadArguments1
    Set arguments(1) = ReadArguments2
    Set arguments(2) = ReadArguments3
    Set arguments(3) = ReadArguments4

    ' Instantiate the client object
    Dim Client As New EasyUAClient

    ' Obtain values. By default, the Value attributes of the nodes will be read.
    Dim results() As Variant
    results = Client.ReadMultiple(arguments)

    ' Display results
    Dim i: For i = LBound(results) To UBound(results)
        Dim Result As UAAttributeDataResult: Set Result = results(i)
        OutputText = OutputText & "results(" & i & ").AttributeData: " & Result.AttributeData & vbCrLf
    Next
End Sub

If the examples don't help, please post your code and the error you are getting.

BTW, have you considered whether the Excel Option would not be an easier solution? (of course VBA is more powerful - it depends on the usage case).

Best regards

The following user(s) said Thank You: dani.hartman@metronik.si

Please Log in or Create an account to join the conversation.

dani.hartman@metronik.si
Topic Author
Offline
Senior Member

02 Feb 2018 11:03 #5933 by dani.hartman@metronik.si

Read multiple values in VBA was created by dani.hartman@metronik.si

Hello.

Could you please send me an example how to read multiple values from OPC UA server in VBA code. I couldn't find this kind of example among the other Excel examples and the description in help is also not detailed enough for VBA code…
I'm strugling with this issue for some time now and I always get some kind of VBA error. Otherwise reading and writing one value and also subscribing to multiple values works fine.

Thank you in advance and best regards

Please Log in or Create an account to join the conversation.

Moderators: support, vaclav.zaloudek

Time to create page: 0.148 seconds