AnsweredAssumed Answered

Excel VBA - Average value using Summaries2

Question asked by henderson on Dec 15, 2014
Latest reply on Dec 19, 2014 by henderson

Hello everyone.


For some reasons I am not using the default datalink functions in Excel and have implemented my own PIAdvCalcExpVal and PIArcVal.

For PIArcVal I am having no problems.

But I am in trouble trying to make PIAdvCalcExpVal work.


What I am trying to do is implement a VBA function that will do the same as thi:

=PIAdvCalcExpVal("('myTAG')";"13/12/2014 18:00:00";"14/12/2014 18:00:00";"average";"time-weighted";"interpolated";"10m";0;1;0;"myServer")


Here is the code I have:


Function piVerifiedAverage(tag As String, datai As Variant, dataf As Variant, sample As String, Optional fator As Double = 1, Optional decplaces As Integer = 0, Optional dotruncate As Boolean = False, Optional doround As Boolean = False) As Variant

    Dim myPISDK As Object
    Dim srv As Object
    Dim pt As Object
    Dim pd As Object
    Dim ptd2 As Object
    Dim dtI As Object
    Dim dtF As Object
    Dim pv As Object
    Dim nval As Object
    Dim retorno As Variant
    If tag = "" Or datai = "" Or dataf = "" Then
        piVerifiedAverage = 0
        Exit Function
    End If

    Set myPISDK = CreateObject("PISDK.PISDK")
    Set pv = CreateObject("PISDK.PIValue")
    Set dtI = CreateObject("PITimeServer.PITimeFormat")
    Set dtF = CreateObject("PITimeServer.PITimeFormat")
    'Set ptd2 = CreateObject("PISDK.IPIData2")
    If myPISDK Is Nothing Then
        piVerifiedAverage = "Erro PI"
        Exit Function
    End If
    Set srv = myPISDK.Servers.Item("myServer")
    Set pt = srv.PIPoints(tag)
    Set pd = pt.DATA
    dtI.InputString = CStr(datai)
    dtF.InputString = CStr(dataf)
    Set ptd2 = pt.DATA
    Set nval = ptd2.Summaries2(dtI, dtF, CStr(sample), 5, 0, Nothing)
    Set pv = nval("Average").Value

    If pv.Item(1).IsGood Then
        retorno = CDbl(pv.Item(1))
        retorno = retorno * fator
        If doround Then
            retorno = Math.Round(retorno, decplaces)
        End If
        If dotruncate Then
            retorno = truncate(retorno, decplaces)
        End If
        retorno = "Erro PI"
    End If

    piVerifiedAverage = retorno
End Function


Sometimes it works great and I get the same result for both functions.

But for somedays I am getting zero when I use piVerifiedAverage and a non-zero value when I use PIAdvCalcExpVal.

Anyone know what I may be doing wrong?


Just a quick example

this gives me a non-zero value

=PIAdvCalcExpVal("('myTAG')";"02/12/2014 18:00:00";"03/12/14 18:00:00";"average";"time-weighted";"interpolated";"10m";0;1;0;"myServer")

and this gives me a non-zero value

=PIVerifiedAverage("myTAG";"02/12/2014 18:00:00";"03/12/14 18:00:00";"10m")


this gives me a non-zero value

=PIAdvCalcExpVal("('myTAG')";"13/12/2014 18:00:00";"14/12/14 18:00:00";"average";"time-weighted";"interpolated";"10m";0;1;0;"myServer")

but this gives me zero

=PIVerifiedAverage("myTAG";"13/12/2014 18:00:00";"14/12/14 18:00:00";"10m")


I noticed that for this day (between 13 and 14), this tag was zero for a period of the day (between 18 and 20).

Could this be the cause? How can I make it work right?