Excel VBA Missing Reference - PI Osisoft

Question

I have an VBA code where I use many objects from PISDK, which I have to add as reference to my project.

I must explicitly declare the variables otherwise the code won't work. I don't know why. Excel throws an error ("types doesn't match") if I declare, for example, pt as object instead of PIPoint.

Here is part of my code:

Dim srv As Server
Dim pt As PIPoint
Dim pv As PIValue
Dim dt As New PITimeFormat

The problem is: when user doesn't have this reference installed, Excel gives me an compilation error, so it's impossible to catch and handle this error. Since this code runs on a user-defined function, as soon as the user opens the workbook, he gets stuck with compiling errors.

I must be able to catch this error.

I can't find documentations to fully implement late binding on this code. I don't know if it's really possible to do it. I know it could solve my problem.

Also, I know I could check if the reference is installed, thru:

thisworkbook.vbproject.references

But if the user doesn't allow access to the vbaProject object under Excel options, I am not able to do this.

Any idea?

Answer 1

You can use something like that:

Sub NotUsed()
    Dim pt As PIPoint
End Sub

Function ReferenceCheck() As Boolean
    On Error GoTo NoRef
    pt = Acrobat.AV_DOC_VIEW ' PIPoint

    ReferenceCheck = True
    Exit Function

NoRef:
    ReferenceCheck = False
End Function

Sub Test()
    If ReferenceCheck Then NotUsed
End Sub

The function refer to a proprieties of the object. If the reference it's ok return true otherwise false.
In the phase of init you can check like that.
The sub NotUsed, don't create Error because not called...
In my sample I use the ADOBE Object ...

Answer 2

I managed to solve my problem declaring everything as object and then using createobject afterwards. The main problem doing this was using functions, like this one: I have the function "arcValue". It takes three arguments: arcValue(TimeStamp as PITimeFormat, Mode as RetrievelTypeConstants, Optional asynchStatus as PIAyncnStatus) The way I use it doing early binding is:

dim pt as PIPoint
dim pv as PIValue
set pv = pt.data.arcValue("01/09/2014 17:00:00", rtInterpolated)

This works. But when I do:

Dim myPISDK As Object
Dim srv As Object
Dim pt As Object
Dim pd as Object
Dim pv as Object
Set myPISDK = CreateObject("PISDK.PISDK")
Set pv = CreateObject("PISDK.PIValue")
Set srv = myPISDK.Servers.defaultserver
Set pd = pt.DATA
Set pt = srv.PIPoints("piTAG")
Set pv = pd.ArcValue("01/09/2014 17:00:00", rtInterpolated)

It doesn't work. But why? There were two problems: First: When I use late binding (createobject) I don't have access to "rtInterpolated" constant, so I have to use its equivalent number.

Set pv = pd.ArcValue("01/09/2014 17:00:00", 3)

But this still doesn't work. So I had to do this to make it work:

Set pv = pd.ArcValue("01/09/2014 17:00:00", 3, Nothing)

And then everything started working. I don't know why, but VBA makes me write something do all parameters, even if they are optional.

This way, I am able to detect erros in runtime, so I used this code:

If myPISDK Is Nothing Then
    piVerified = "Erro PI"
    Exit Function
End If

Also, I had to remove all references (they are not used anymore, anyway) because it was causing malfunction on other parts of my code not related to this one when the references were missing.