AnsweredAssumed Answered

Use PIAdvCalcExpVal() in Excel VBA

Question asked by henderson on Feb 2, 2015
Latest reply on Feb 6, 2015 by gregor

I have the following function, which I use to get PI values in Excel but using VBA and not the usual DataLink functions.

But that only works for a TAG. I need to do this;


=PIAdvCalcExpVal("('myTAG1'*'myTAG2')";"30/01/2015 00:00:00";"01/01/2015 00:00:00";"average";"time-weighted";"compressed";"24h";0;1;0)


Is there a way I can change my function to work like this?

My real use is more complex than what I've just explained, but I simplified it so you could understand.


Here is the function I've been using to return the average value of a TAG


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

    If Not sHabilitaModoDesenvolvedor Then On Error Resume Next
    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("sbs00as25")
    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