1 Reply Latest reply on Jan 9, 2013 11:48 AM by Gregor

    Using PiCurrVal in VBA, Excel

    Nick D'Orazio

      An interesting thread came up on the OSIsoft Users Community that I'd like to copy here that may be of interest to beginning users of the PI SDK.


      A user posted this:


      Hi.  I am trying to use the PiCurrVal function from inside a vba macro.  If I was the write the formula (below, eq1) into a cell, it will correctly return the tag value.  However I am so far unable to call this from VBA.  I believe the formula is from the pipc32.xll addin, and I have enables this in tools>refrences.  I would expect the correct code to be as shown in (below, eq2), however this does not work.  I am new to this forum, can anybody shed some light?

      eq1. PICurrVal("gtt2515.fin", 0, "AUSLFDKN4")
      eq2. Pipc32.PICurrVal("gtt2515.fin", 0, "AUSLFDKN4")

      Here is the response from user bmercer:

      Learn the PISDK.   There are lots of examples in the help.   A short snippet below of a basic, basic, basic way to get the snapshot for the default PI server

      In references check  PISDK 1.3 Type Library


      Option Explicit

      Dim srv As Server
      Dim pt As PIPoint
      Dim curval

      Sub main()
      Set srv = PISDK.Servers.DefaultServer
      Set pt = srv.PIPoints("sinusoid")
      curval = pt.Data.Snapshot

      Set srv = Nothing
      Set pt = Nothing
      End Sub


        • Re: Using PiCurrVal in VBA, Excel

          Hello Nick,


          Thank you for copying this thread to vCampus.


          From the PI DataLink Manual (2010 and later 4.2.x page 83):


          Note that VBA does not recognize PI DataLink functions, but you can formulate a function as a text string, and then set the formula Array property of a range of cells to the text string value. The VBA code can then check the cell values for the PI function results. This yields the same results as manual entry of PI functions in the same range of cells.


          Here is an example I took from a TechSupport call:

          Cells(1, 1).Value = "sinusoid"
          Cells(1, 2).Value = "localhost"
          Cells(2, 2).Formula = "=PICurrVal($A$1, 0,$B$1)"