Issue with VBA library (1 Viewer)

[Zakraket]

I need to communicate with a PLC using OPCUA. So I found two libraries for Excel on the Siemens site.

OPC_UA_ClientLibrary.dll

OPC_UA_ExcelCLient.dll

They come with working Excel VBA examples.

The first one works, but has limitations; there is no method to read a "struct", that's a PLC node that holds multiple values. I can read/write values to a PLC tho, and also subscribe to valuesupdates (this creates an event when the PLC updates a node)

The second one seems to be an addon on the second one, and the Excel VBA example file also has possibilities to read a "struct" (and also OPC UA methods). When I create an object I can
Set g_OpcUA = New Opc_Ua_ExcelClient.OpcUaClient
or
Set g_OpcUA = New OpcUaClient

But when I register that DLL I can't use the "readvalues" method on the OPCUAobject.

Excel example:

Private Sub CommandButtonRead_Click()
    'NodeId of Item
    Dim strNodeId(0) As String
    strNodeId(0) = Range("NODEID_READ").Value
    Dim strValues() As String
    'read value
    On Error Resume Next
    strValues = Sheets(3).m_OpcUaClient.ReadValues(strNodeId)
    'show value
    Range("VAL_READ").Value = strValues(0)
End Sub

My Access version

Private Sub cmdRead_Click()
    Dim strNodeId(0) As String
    strNodeId(0) = Me.NodeStart
    Dim strValues() As String
    'read value
    On Error Resume Next
    strValues = g_OpcUA.ReadValues(strNodeId)
    debug.print strValues(0)
End Sub

Btw, first you need to connect the g_OpcUA object to the OPCUA server, this works without issues. But as soon as I add the code for reading a value, I'm getting this error on the .ReadValues method when compiling:

Function marked as restricted or uses a Automation type not supported in Visual Basic​

Maybe it's a bit too simple a thought, but I was assuming that a library that works for MS Excel is compatible with MS Access? Does anybody know a way to fix this? What does this exactly mean: the function is restricted for use in the library (but it's available in Excel) or the variable types are not compatible with MS Access VBA?

I could use the other library, but than I can't read structs. Everything else works though.

 

[The_Doc_Man]

The key to understanding this is simple... VBA works by having something activate it with some kind of context. Using Excel with VBA or using Access with VBA (and add to those the "old" Outlook, Word, and Powerpoint cases) they ALL use the same VBA library. It isn't VBA that changes for each one, but the context linkage that allows VBA to find the objects it can touch.

These OPC (Open Platform Communications) libraries must perform some kind of customized operation to activate what VBA actually does for you in the context of PLCs (Programmable Logic Controllers). In other words, THEIR context is whatever is the Component Object Model schema presents for OPC/PLCs. The question is whether there has been some modification to the "standard" VBA library in order to support this usage. However, it is indicative that SOMETHING special is going on because the "Function marked as restricted" error tells me that there must be a data type not compatible with "standard" VBA. So how did it get there if NOT via some type of modification. The fact that you have special VBA libraries other than the standard VBA "generic" library tells me that they are not "pure" VBA libraries. SOMETHING is odd about them.

The sad news is that unless someone on this forum has some more intimate knowledge of the modifications done for VBA in the OPC/PLC context, we are going to be a limited source of help for this problem. Not zero, because we still can handle syntax questions and general knowledge questions. But custom mods to a VBA library can only be answered by the folks who modified it in the first place. See if the vendor has a help line.

You MIGHT get some insights if you can use the object browser to see what is in the specific structures that you use here. Also, here is a thought for you, a simple experiment. You mentioned two libraries. Do you use (have Tools>>References links to) both at the same time? If so, go into the VBA page >> Tools >> References pop-up form and see if you can change the order in which those two are listed. If you select one and click the UP or DOWN buttons on the pop-up, the highlighted item moves up or down in the list. This means the order in which the libraries are searched ALSO changes, and that might help you. But I will CLEARLY state that is a wild-eyed GUESS.

 

[Zakraket]

Hi, thanks for you reply. I'm not familiar with the background of what libraries do like you are, so this is interesting.

Siemens has a forum, but it doesn't work properly (for me at least, I see other users posting threads there, but I can't seem to post on the forum (no button that I can find...) and I'm getting a lot of pageerrors navigating the forum. I'll try and see if I can get some help with that from Siemens

At the moment I only have the OPC_UA_Excelclient.dll registered. Before that I had the "normal" OPCUAClientlibrary registered, but I came into the limitations regarding the structs and found the OPC_UA_Excelclient.dll last week. I had both registered a for a few days, but thought that maybe they would conflict, so I unregistered the first.
Maybe I need to restart the machine, but I could try to register both an follow your tip (I am familiar with the fact that the order of libraries can make a difference)

ObjectBrowser regarding ReadValues:
Function ReadValues(nodeIdStrings() As String) As String()
    Member of Opc_Ua_ExcelClient.OpcUaClient

I tried setting all variables in my cmdRead_Click() as "variant" but that doesn't make a difference. Strange that the most basic function of OPC UA (read a nodevalue) has this issue.

Btw, both libraries have the same objectname (OpcUaClient) and I think all source files are also in the package (but I'm not able to make something of that).
If I look at the filesizes the Opc.Ua.Client.dll and Opc.Ua.Code.dll are larger in the package with teh Opc.Ua.ExcelClient, so I'm assuming newer

edit: different browser, Siemens forum seems to work. Probably cache issues, I'll try my luck there

 

[sonic8]

However, it is indicative that SOMETHING special is going on because the "Function marked as restricted" error tells me that there must be a data type not compatible with "standard" VBA. So how did it get there if NOT via some type of modification.

The library was certainly not written in VBA. Not all data types valid in COM are supported by VBA. - It's a weird idea though to create a COM library explicitly meant for Excel and then using a data type not supported by VBA.

Code: ObjectBrowser regarding ReadValues:
Function ReadValues(nodeIdStrings() As String) As String()
Member of Opc_Ua_ExcelClient.OpcUaClient

I guess, the actual signature of the method is: Function ReadValues(ByVal nodeIdStrings() As String) As String()
VBA cannot pass the string array ByVal, but only ByRef. I also guess, that the Object Browser also cannot cope with the fact that the argument is supposed to be passed ByVal and thus does omit the keyword when displaying the method's signature.

I don't have a suggestion for a solution, but I found two threads on VBForums, which deal with the same situation:

• OPC-UA client for VB6?
• Failed to implement a VBA library in vb6, any alternative?

Maybe there are some clues for a solution suitable to you in those threads.

 

[Zakraket]

@sonic8 The second thread is exactly the same issue, and it seems someone fixed the library by updating the library to accept the proper type since the thread has been marked resolved.

update; I think the solution is in the first thread, I'll look into this, tnx

 

[Zakraket]

@sonic8 I was able to fix my issue by using an updated .dll from the first topic, again thanks for pointing them out

 

[amorosik]

@sonic8 I was able to fix my issue by using an updated .dll from the first topic, again thanks for pointing them out

I am very interested in the topic of communication between Vba and an opc server
Where can I find the correct library?