CODEWITHSUNDEEP
vbaexcel
Nicholas Muir
Nicholas Muir

Reputation: 3114

VBA Evaluate function not working. Error 2015

I am trying to connect OsiSoft Pi Datalink with Excel VBA and put the output into a variable for further VBA procesing.

When running the command in the excel cell it works fine but when I try to run it in VBA I get “Error 2015”

enter image description here

This is the code that is working fine in the cell:

= PITimeDat("PP2941FIC01_PV_S", "7/07/2018  11:01:58 AM", "", "interpolated")

Converting this to VBA: 

Sub getPiData()

    Dim tagName As String
    tagName = "PP2941FIC01_PV_S"

    Dim timeStamp As String
    timeStamp = "7/07/2018  11:01:58 AM"

    Dim rootPath As String
    rootPath = ""

    Dim retrieveMode As String
    retrieveMode = "interpolated"

    Dim result As String
    result = CStr(Application.Evaluate("=PITimeDat(tagName, timeStamp, rootPath, retrieveMode)"))

    Debug.Print (result)

End Sub

I have checked the VBA references as below and they also seem correct:

enter image description here

There is no information in the user guides or available that I could find on the internet for corretcly connecting VBA to Pi Datalink.

I found this on stackoverflow but it didn't explain the issue:

Pulling data from PI DataLink using VBA

I am possibly missing somthing really basic but it seems like I am executing the exact same code in VBA as in the cell. The cell is returning a value and VBA is just returning an error.

Thanks in advance for your help

Upvotes: 2

Views: 10047

Answers (1)

Ambie
Ambie

Reputation: 4977

The point that cyboashu makes is a good one. It might also be the case, though, that you are receiving this error because of your syntax.

When using Evaluate, you must pass in the string exactly as you want it to be evaluated. Your code is passing the name of the variables instead of their values. If I could simplify an example. The following code would return an Error (2029 in this case, which is a #NAME error):

Dim dat As Variant
Dim txt As String

txt = "25 December 1966"

dat = Application.Evaluate("=DateValue(txt)")

In this example you would need to create the string so that the value of txt were evaluated, like so:

Dim dat As Variant
Dim txt As String

txt = "25 December 1966"

dat = Application.Evaluate("=DateValue(""" & txt & """)")

I suspect this is the cause because if inverted commas are missing from a required string, then Evaluate will return a 2015 Error. This would do it for example:

Dim dat As Variant
Dim txt As String

txt = "25 December 1966"

dat = Application.Evaluate("=DateValue(" & txt & ")")

I don't have the library to test your exact code, but try rewriting your evaluation string to incorporate the values of your variables instead and remember to pass in the inverted commas too.

Upvotes: 3

Related Questions