Rtd Functions Excel For Mac

Microsoft Office Excel provides a worksheet function, RealTimeData (RTD). This function enables you to call a Component Object Model (COM) Automation server to retrieve data in real time. When the server has been programmed to continually update results, unlike other functions, RTD formulas will change when Microsoft Excel is in automatic calculation mode. RTD is designed to return data continuously, but for the current value, try this: Dim xlApp As New Excel.Application Sub test Range('D1') = xlApp.WorksheetFunction.RTD('tickerplantrtdserver', Null, '4#2#1#6768#FUTSTK#N1#0#XX#Bid') End Sub xlApp is slow, so you want to initialize once only. I became an Excel MVP in 2001, and Ron followed soon after, in 2002. Ron has been a strong advocate for users of Excel on the Mac. Ron covers many Excel and VBA topics on Excel Automation, and he covers Mac-specific Excel issues at Mac Excel Automation. RTD is designed to return data continuously, but for the current value, try this: Dim xlApp As New Excel.Application Sub test Range('D1') = xlApp.WorksheetFunction.RTD('tickerplantrtdserver', Null, '4#2#1#6768#FUTSTK#N1#0#XX#Bid') End Sub xlApp is slow, so you want to initialize once only.

  1. Python Excel Rtd
  2. Rtd Functions Excel For Mac Pdf
  3. Rtd Functions Excel For Mac Download

Almost two years ago, we showed you how to access Yoctopuce devices from Excel by creating your own Excel add-in using Excel-DNA. Today we are going to show something simpler and even better: read any Yoctopuce sensor from Excel, with instant live refresh, without even recompiling a single line of code...
The first enhancement compared to our previous blog post comes directly out of Yoctopuce programming API: since the beginning of the year, it is possible to use the YSensor abstract class to read any sensor, rather than having to use a specific sensor class. This is what will make it possible for you to test our code with any Yoctopuce sensor, without even recompiling our Excel Add-In.
The other improvement idea comes from one of our customer: using a RealTimeData Server (RTD Server in short) for Excel rather than using explicit VisualBasic function calls. A RTD server appears to the user like a simple Excel function, but it is a function that can spontaneously change its value and notify Excel from the change so that all dependent cells are automatically computed again. This is like a direct transposition in Excel of the value change callbacks in our API. And if course we will use a callback function to implement the RTD server, thereby providing a high refresh rate.

The demo


Want to give it a try right now ? First make sure you have a recent .NET Framework installed. Then open Excel, enable 'developer' menu and select 'Add-ins'. Click on 'browse...' button to find out which directory Excel is using for Add-Ins. Copy the content of this zip file into it this directory, and select the YoctoRTD.xll plug-in for activation. That's it.


Installing our Excel Add-In

Rtd Functions Excel For Mac


If you have a Yoctopuce sensor connected to your PC, you can now add in any cell the formula


to see the current value of the first sensor found. And the value changes automagically. Instead of 'any', you can add the unique identifier of any sensor function (for instance 'LIGHTMK2-24F0F.lightSensor') or the corresponding logical name, if you have configured one. By default, Excel only refresh dynamic values every 2 seconds, but our plug-in adds a menu to easily raise the refresh rate up to 100Hz. Have a look:


The implementation

Python Excel Rtd


If you are interested to learn how this works and to change this example, here are a few explanations about the code. As in our previous Add-in example based on Excel-DNA, we have started by creating a C# 'Class Library' project in VisualStudio Express 2012 (the full project is available for download in this zip file). Apart from the reference to ExcelDNA.Integration and the Yoctopuce API base file yocto_api.cs, we only had to add a single source file to implement the RTD server.

Rtd Functions Excel For Mac


The C# .NET project structure


Here are a few words about the most important methods found in YoctoRTDServer.cs:

ServerStart()

This is the method invoked by Excel when the user adds for the first time a reference to YSensorValue(). We have to use it to initialize Yoctopuce library, including a timer that will periodically give control to the Yoctopuce library to handle USB events.

string errmsg =';
YAPI.RegisterLogFunction(log);
if(YAPI.RegisterHub('usb', ref errmsg)YAPI.SUCCESS){
Console.Beep();
log('USB ready');
_notifyUpdates =true;
_timer =new Timer(delegate{
string dummy =';
YAPI.HandleEvents(ref dummy);
}, null, 0, 10);
}
else log(errmsg);


ServerTerminate()

This is the opposite method, invoked when the last reference to YSensorValue() is removed (or when the document is closed).

if(_timer !=null){
Console.Beep();
_timer.Change(Timeout.Infinite, Timeout.Infinite);
_timer =null;
_notifyUpdates =false;
YAPI.UnregisterHub('usb');
YAPI.FreeAPI();
log('USB closed');
}


ConnectData()

This method is invoked each time that a reference to YSensorValue() is added to the Excel sheet. This method must enable the value change callback for the selected sensor. Excel provides a numeric topicId for the request, that we will have to use each to we need to announce a new value to Excel. So we store this identifier directly in the YSensor object returned by the Yoctopuce API, using the userData attribute which is exactly intended for this kind of purpose:

Excel
sensor =YSensor.FindSensor(sensorId);
if(!sensor.isOnline()){
log('Sensor '+ sensorId +' is not online, check USB cable');
}
sensor.set_userData(newobject[]{this, (int)topicId });
sensor.registerValueCallback(sensorValueChangeCallBack);


The value change callback will only have to save each and every new value reported by the sensor into an internal dictionary containing all 'changes to be notified', and to notify Excel (if not yet done) that there are new values to be loaded:

srv._topics[topicId]=value;
if(srv._notifyUpdates){
srv._notifyUpdates =false;
srv._callback.UpdateNotify();
}


Rtd Functions Excel For Mac Pdf

RefreshData()

This is the method invoked by Excel to retrieve value changes when notified of new values. To save Excel from unnecessary cascaded computations, we only include in this list the sensors whose value has indeed changed, using our dictionary of 'changes to be notified'.

// Count changed values
topicCount =0;
foreach(varvaluein _topics.Values){
if(value!=') topicCount++;
}
// Create an array of changes to report
object[,] results =newobject[2, topicCount];
int idx =0;
double floatVal =0;
foreach(KeyValuePair<int, string> pair in _topics){
if(pair.Value!='){
results[0, idx]= pair.Key;
results[1, idx]=double.Parse(pair.Value);
idx++;
}
}
// Clear reported values from the dictionary
for(idx =0; idx < topicCount; idx++) _topics[idx]=';
// Enable further updates now that this one is processed
_notifyUpdates =true;
return results;

Rtd Functions Excel For Mac Download


That's about it, other parts of YoctoRTDServer.cs are mostly cosmetic stuff to create the custom menu. The real magic that makes all this so easy is provided by Excel-DNA, which is definitely a very good library!