I would like to execute a PI Datalink formula (for example, PICalcDat()) from within an Excel vb function. Is this possible? Is this allowed?? After RECORDING a macro and going thru the strokes of manually typing in the PICalcDat() formula (i.e. filling in the user input box), there is NOTHING in the macro after entering formula and stopping the macro record. ... Using the "Worksheet.Function.Macro()" vb command does NOTHING and "Application.Run PICalcDat()" does NOTHING. Maybe this is supposed to be BLOCKED (??) ... Any help is greatly appreciated. Thanks!

OSIsoft vCampus is a subscription-based, online offering that consists of providing everything people need to develop applications on the PI System.
We invite you to take a "tour" of the OSIsoft Virtual Campus - also feel free to consult the FAQ  or contact OSIsoft vCampus for more details.

Shortly after the original post, I was able to resolve & fix the problem. ... The problem was that the default PICalcDat() prompt sets you up for a COLUMN vector layout (1 column, multiple rows). This default formula prompt sets the 8th argument (of total of 9 arguments) = 0. This results in an array that the VB code can't "see" because it is in the column vector layout; it only sees the 1st element; it needs to be in a row vector layout. You can either use the TRANSPOSE array VB function, or better yet, change the 8th argument value to a '2' or '3' (see example below). This sets up the resulting array as a ROW vector layout (1 row, multiple columns). When I did this, then all the array elements were visible to the VB code.

The following is what my code looks like:
PICalc = Application.Run("PICalcDat", Pnt, ST, ET, IT, Mode, 1, 3, PIServ)
'VERY IMPORTANT: '3' sets as Row Vector Array. This avoids TRANSPOSE issue!
'Might use Date-Time Stamp in some cases. So, using '3' versus '2'.

The following indented stuff (>) is from the PI HELP page:
> The outcode affects the output as follows:
> 1. Calculated values along a column (actually use a 0 in 8th argument for this)
> 2. Timestamps and values along 2 columns (use a 1 in 8th argument for this)
> 3. Calculated values along a row (actually use a 2 in 8th argument for this)
> 4. Timestamps and values along 2 rows (actually use a 3 in 8th argument for this)
Mess around with a simple PICalcDat() array in an excel sheet, changing this 8th # from 0 - 3, and you will see what I mean. You should either use a 2 or a 3 if you do PICalcDat() formulas in VB code. As I said above, I used a 3 since I thought someday I might want to make use of the DateTime stamp data. But, this adds extra load, and if dealing w/ lots of data, the macros can be very slow. In hindsight, I should probably have used a '2' as my macros are excessively slow.

Finally, the next line is required somewhere in your code. You may already have this; just let you know if you per chance don't already have it.
Application.RegisterXLL ("C:\Program Files\PIPC\Excel\pipc32.xll") 'Setup PI forumulas

Good luck, Hope this helps!

I'm a chemical engineer at Tate & Lyle (our plant is located in Lafayette, IN ... Sagamore Plant). If I click on the HELP of any ProcessBook display, our version is 2.31, build date is Aug 19, 2002. So, we have old PI software. Maybe your latest versions address the issue I have, so keep our version # in mind as you read my issue, and how I address it:
* As engineers, we commonly want AVERAGE data for a given signal (say TEMP on the Inlet Air to a Dryer), but you need to FILTER OUT all the TEMP data when the dryer is DOWN. You only want the AVERAGE when the dryer is UP! With the PI functions currently at our disposal, there are 3 "generational" ways of doing this. There is NO 1-shot function (where you'd input a FILTER expression, and the function would only look at time periods when the FILTER condition = T). Maybe later versions now have this (so someday we might then ... would be nice).
1) Generation-1: The hard manual way. In column A, show time stamps in multiple rows from START to END time, in equal time intervals. Then, in side tables, show DRYER TEMP (col B), and FILTER STATE as T/F (col C) (or how else you might want to show the filter state). Then, in col D show TEMP if FILTER = T, and AVERAGE at top. Slow and bulky, and makes for HUGE files.
2) Generation-2: Do all of this in 1 CELL using an elaborate ARRAY function. Something like the following:
{= AVERAGE(IF(ISNUMBER(PICalcDat(F1_ost,_1ST,_1ET,_1IT,_Avg,1, 0,_PI)), IF(PICalcDat
(F1_ost,_1ST,_1ET,_1IT,_Min,1, 0,_PI)>F1_ostMinV, IF(ISNUMBER(PICalcDat
($C16,_1ST,_1ET,_1IT,_Avg,1, 0,_PI)), PICalcDat($C16,_1ST,_1ET,_1IT,_Avg,1, 0,_PI)))))}

Here F1_ost is the FILTER STATE (as 1/0 in this example) and $C16 is the data to avg. The other vars are str-forward. The 1st IF makes sure the F1_ost(n) is a valid number so to clear out BAD DATA. If true, then the 2nd IF results in an ACCEPTED data value IF F1_ost(n) > F1_ostMinV (a filter state required min value). With this, the function builds an array of FILTER VALID data, and the AVERAGE function only then works on this data.
This 1-CELL approach works very well, except for two things, and that's when the VB approach must kick in:
2.1) Note that the FILTER ITEM has to be a PI point (not a filter expression). Sometimes the FILTER REQUIREMENT must use more than 1 PI POINT (like PUMP=ON, PRESSURE>5000 AND VALVE1=OPEN). To do this, you have to NEST several IF functions in the above 1-CELL function. Excel allows up to a MAX of 7 NESTED IF functions. And, yes, I hit that on 1 occasion. That's one issue.
2.2) A more common issue, and this takes the whole thing to another level of complexity, is when you want to NOT average data of a SINGLE PI POINT, but average data of an advanced CALCULATION of MANY PI POINTS (that no one has had time to program a PLC point for so to get on PI). For example, say I need to know the average BTU TRANSFER RATE for a given heat exchanger. In that case, I need to do calcuation INSIDE of EACH TIME INTERVAL using multiple PI points, and then average all of the results for those time periods that met certain FILTER REQUIREMENTS. ... Starts to get pretty harry! These are the sorts of things that future computers & programming will someday make REAL EASY. This is the sort of data that we want at our finger tips FAST & EASY! It helps us know when things are NOT performing right, and makes it easy to find what these things are quickly! ... Well, the above 1-cell approach just CAN'T do this level of array math. A typical BTU calc = FLOW x (TEMP1 - TEMP2) x K. ... The array math: FLOW(1-n) x (TEMP1(1-n) - TEMP2(1-n)) is JUNK. Excel won't even let you try it.
So, this is what drove me to doing all of this in VB.

3) Generation-3: The VB breaks the arrays up, does the math on the individual elements, re-packs a new array but only with calc results during time periods when ALL of the FILTER requirements were met (my macro allows up to 3 FILTER PI points) and then sends this final array back to the worksheet where the AVERAGE function is done on it. You could do a STDEV or COUNT or MAX or MIN function on this array if you wanted.

4) Concerning teh PISDK route? ... We have a PLC programming guy that takes care of PI stuff. He has access to these sorts of programs. I don't have a clue about them. Installing these programs amongst the engineers and resources would probably be frowned upon. So, I honestly doubt this would be a viable option given the typical corporate environment on higher level PC (non-network) programs like this.
... That's the long answer to your question. Hope this all makes sense. Please let me know if later versions have built-ins to Gen-2 and/or Gen-3 automatically. ... Much Thanks, Joe Sauer.

RJK Administrator, Ok, got some answers for you: Found out from our PI plant guy that the Process Book version has nothing to do w/ what I am struggling with. What is really important is the version of the Excel PI Add-in as well as our PI Server version.
So, here you go: If I click on the About (in Excel) under the PI menu of commands, it says PI-Datalink V2.1 26-Apr-02. And, I am told that our PI Server version is 3.4.370.76 (and that soon we will be doing a PI server hardware upgrade and going to 3.4.380 (if I heard him right??).
Yes, I am VERY interested in finding out what enhancements we could achieve if we upgraded our Excel PI-Addin. We would have to get systems to do that on our Citrix servers, but, in the meantime, we might be able to do some test work on a PC around here if we could acquire this software for a single user. If we liked what we saw, then it would give us more firepower when talking to systems.
Could you send me a proposal for a single user test of the latest Excel PI-Addin that would work with our version PI server? Please send to joe.sauer@tateandlyle.com as well as zachary.fisher@tateandlyle.com
Much Thanks!

Haven't seen quote via email yet; coming????