The RTD / BDP functions provide real-time data from bloomberg directly to an excel sheet. However if you're subscribing to fields which Bloomberg hasn't designated to be 'real-time enabled' you have to manually refresh bloomberg data for it to have any effect.

Let's look at an example

FUT_CTD_PX (Cheapest to deliver price) is a static FIELD for a real-time VALUE. IE, if you check in the BB terminal this value will be constantly changing, but excel sees it as a static field.

To get the CTD price for a future we can use the following formula:

=BDP("SOME FUTURE'S TICKER", "FUT_CTD_PX")

This field will never update, it will stay static with it's initial value. If the spreadsheet has been open for 5 days, it's the price from 5 days ago which will be displayed.

Microsoft's RTD server model is event driven, in that the data-source notifies the RTD server when a new value is available and that value is picked up by a spreadsheet when excel calls it's RTD.RefreshData method (which it does every 2 seconds by default). In our example, Bloomberg's data-source never notifies the RTD server of a change in value for any of it's STATIC fields, and they therefore remain the same indefinitely.

This also explains why calling RTD.RefreshData from VBA code has no effect, as the RTD server has no new value to pass forwards.

Solutions

The only REAL solution is for Bloomberg to provide such key fields as real-time. Static fields should be reserved for values which do not change on a regular basis, such as instrument name, maturity date, ISIN, and so forth.

As a manual solution, Bloomberg does provide a Excel menu containing data-refresh commands. Clicking 'refresh entire workbook' will eventually update all static values (it can take up to 10 seconds), but only for the =BDP formula. If you're using =RTD you'll have to re-open your workbook or delete and re-type the formulas.

"Can I do this Programatically?" I hear you cry.... Why yes you can!

It's not a graceful solution by any means but you can use one of the following to activate the respective bloomberg command:

Application.Run ("bloombergui.xla!RefreshData") [Default]

Application.Run ("bloombergui.xla!RefreshCurrentSelection")

Application.Run ("bloombergui.xla!RefreshEntireWorksheet")

Application.Run ("bloombergui.xla!RefreshEntireWorkbook")

Application.Run ("bloombergui.xla!RefreshAllWorkbooks")

be sure to wrap any use of these in good error checking to avoid Excel getting mad at the user.

Summary

It's important to be aware that some bloomberg fields are not real-time. If you're using one of these fields for a key calculation you'll have to make sure that you manually refresh regularly, or that you have integrated a crude automation into your code.

Regardless of how much time Microsoft invests into developing Visual Studio Tools for Office there’s still going to be a market for VBA, but that doesn’t mean we have to compromise our methodologies or best practices.

Dependency injection in VBA is not only possible, but it’s not as much work to implement as you might think. Sure VB6’s system for Interface inheritance leaves a lot to be desired, and it’s a whole lot less flexible than modern languages, but you can still add a massive amount of flexibility and control to your code through a little thought and planning.

There are a few key things we need for a basic IoC implementation.

1. Interfaces for key dependencies that we want to ‘inject’ (for example a data-repository)
2. A centralized way to change what concrete object is mapped to that interface by default
3. A way to inject that concrete object upon creation of a dependant object (as VBA does not allow you to pass parameters on construction)

Let’s address how to do each of these things in turn:

Interfaces

Interfaces in VBA are created by making a class and populating it with stub methods and properties which don’t do anything, that’s it. You don’t have to declare it as an interface, but it would probably make sense to prefix the name with an ‘I’.
For example, here’s an interface for IDataSource:

Option Explicit
Public Function GetOrders(customerID As String) As collection
End Function
Public Function GetAllOrders() As collection

End Function

Easy!

And here’s a basic example implementation of that interface:

Option Explicit
Implements IDatasource
Public Function IDatasource_GetOrders(customerID As String) As collection
	Dim returnCollection As New collection

	returnCollection.Add ("Item1")
	'Collection logic here
	Set IDatasource_GetOrders = returnCollection
End Function

Public Function IDatasource_GetAllOrders() As collection
	Dim returnCollection As New collection

	returnCollection.Add ("Item1")
	returnCollection.Add ("Item2")
	'Collection logic here
	Set IDatasource_GetAllOrders = returnCollection

End Function

Object Mapping

Because we don’t have use of Unity, Spring.net, or any other IoC framework, the simplest thing to do is to completely dedicate a module to the job of being an ‘object provider’

Because VBA doesn’t need to be compiled, it’s almost as easy as using a configuration file as you can change it on the fly without having to recompile and redeploy.

Additionally, if you want to externalize the assignment of concrete objects to a configuration file, you can just extend this basic object.

Here is an example of a simple object provider:

Option Explicit
'set your bindings here
Private mDataSource As New SqlDataSource
Public Property Get DataSource() As IDatasource
Set DataSource = mDataSource
End Property

All you have to do is chage the type of the private members to switch the default concrete class.

Dependency Injection

VBA doesn’t allow us to have non-default class constructors, which means we cannot construct objects like below:

Dim dataService as new OrderService(SqlRepository)

Instead we simple have to be a little more creative with our objects, I tend to add a method called Initialize which takes a IDataRepository class. If you haven’t initialized the class, no methods that rely on this private member will work, which acts as a failsafe:

Dim repository as IOrderRepository
Dim dataService as new ObjectService
Set repository = ObjectProvider.OrderRepository
dataService.Initialize repository

Summary

Coding VBA can be frustrating and confusing when one is used to more elegant languages and frameworks, but that doesn’t mean it’s impossible to import modern programming strategies and methodologies.

Using inversion of control in any framework promotes code reuse, separates concerns, and makes for more manageable maintenance going forwards. Working with an outdated framework does not mean one has to write code like it’s 1995.
 

Range.Offset[a, b].value , when using VBA is a great way to add values to adjacent cells without having to actually select every cell you write to (which is very slow as it requires the UI). When I started using VSTO I could only find the Offset method attached to the strongly typed Named_Range object.

Luckily, it does exist for a regular range, except the method is called get_Offset (obviously).

So there you have it. Sounds simple, but this has been frustrating me for a couple of weeks now.

rangeObject.get_Offset[1, 2];
 

When I starting working with VBA 4 years ago arrays were my best friends, they’re easy to use, easy to understand, and getting data to / from an Excel sheet is a piece of cake as cell (0, 1) can map nicely to array position 0, 1. However I’ve developed the opinion that arrays should be avoided in favour of other forms of data lists / collections.

What’s Wrong With Arrays

It’s not that arrays are particularly wrong, it’s more that in comparison to the alternatives they’re limited and frustrating to use and support;

·         Size declaration – You have to declare how big you’re array is going to be, even if you don’t know yet. This is a key limitation regardless of the fact you can ‘re-declare’ to increase the size.

·         No in-built sorting / filters / search functions. An array is an array, it doesn’t have any methods associated with it. It doesn’t even have it’s only object type.

·         It can only hold strings, numeric values, characters etc.

·         Moving through an array is arduous with large amounts of data and there’s no standard ‘structure’ an array must take (I can declare a 0 based array, a co-worker may declare a 1 based array, and there’s no standard slot for each piece of information etc)

So What are the Alternatives?

Instead of using arrays to store string and numerical values I’ve recently been using ADODB.recordsets (recordsets).

Recordsets are slightly more object oriented than arrays, as in essence they are collections of record objects, although they can be manipulated without even using or referring to the record object.

·         No size declaration, you can type myRecordset.AddNew to create a new record and populate it with values.

·         You define fields to specify what data is where, instead of having to remember which position in the array the address is you can just move to a record and use myRecordset.Fields(“myField”).value to retrieve it.

·         You also don’t have to iterate through a recordset to output the contents to a worksheet:

‘ Move to the beginning of the recordset

myRecordset.MoveFirst

‘output the contents to a worksheet starting at cell A1

someWorksheet.Range(“A1”).value = myRecordset.GetRows()

·         They have inbuilt search functions

·         They’re easier to iterate over if you need to, or you can do bulk updates

·         The list goes on....

There are of course limitations to recordsets, they’re frustrating to work out at times, and can be counter intuitive, but I’ve found the benefits of having a more structured set of data are fantastic.

Add Microsoft ActiveX Data Objects Library 2.8 to your VBA references and have a play.

If you’ve declared a customer object and given that customer properties such as FirstName, LastName and CustomerID there’s no confusion on how to find that information.

If you combine that benefit with the use of the Collection object you make your code so much easier to understand, use and support.

Collections and object lists figure more in strongly typed object oriented languages such as C# than in VBA, but we can still take advantage of VBA’s generic collection object.

Dim c as New Collection

The more you object orient your code in VBA the more you’ll use collections, and the easier your life will become, you won’t have to pass around huge arrays and remember between procedures which attribute is where, as you’ll have strongly typed objects to work from and the re-use and quality of your code will vastly improve.

In the near future I’ll post a getting started guide to object orientation in VBA so that these benefits can reveal themselves more clearly.

Overall object orientation is the best path to take for storing data, however, even the use of recordsets can drastically improve the speed, reliability, supportability and expandability of your code.

Creating C# DLL's for use with COM is not too hard, however event exposure can be a little messy especially when trying to subscribe to events in vb6 or vba.

Here's an example of an everyday COM exposed class:

ComVisible(true)
ClassInterface(ClassInterfaceType.AutoDual)
public class CComInterop

We've integrated automatic interface generation so we don't have to make our own. However, this interface type does not expose events.

The solution is to create a separate interface to expose the events but no other properties / methods of the class. Using some jiggery pokery it is also possible to integrate the contents of this interface into the class itself so that you don't have to deal with multiple objects on the COM side.

METHOD:

this is our event, and the event's delegate:

public event SomeEventHandler SomeEvent;

public delegate void SomeEventHandler(string teststring);

To expose this event we create the following interface:

ComVisible(true)
InterfaceTypeAttribute(ComInterfaceType.InterfaceIsIDispatch)
public interface IComInteropEvents
{
void SomeEvent(string teststring);
}

The important part is that we specify the interface type as IDispatch.

But we don't want to have to declare a separate object for events, so we add a 'ComSourceInterfacesAttribute' referencing this interface, like so:

ComVisible(true)
ComSourceInterfacesAttribute("Tam.Common.LzLibrary.IComInteropEvents")
ClassInterface(ClassInterfaceType.AutoDual)
public class CComInterop

Now we can use the object's events in vb6 or vba without having to declare separate objects!

Public WithEvents InteropObject As Some_Library.CComInterop

and you can still use the methods and properties of the object normally with all intellisense intact.

Full sample Class and interface shown below:

ComVisible(true)
InterfaceTypeAttribute(ComInterfaceType.InterfaceIsIDispatch)
public interface IComInteropEvents
{
void SomeEvent(string teststring);
}

public delegate void SomeEventHandler(string teststring);

ComVisible(true)
ComSourceInterfacesAttribute("Tam.Common.LzLibrary.IComInteropEvents")
ClassInterface(ClassInterfaceType.AutoDual)
public class CComInterop
{
private int m_nSomeProperty = 0;
public event SomeEventHandler SomeEvent;

public void SomeMethod(int nValue)
{
m_nSomeProperty = nValue;
// Raises the event
SomeEvent("Hello");
}

public int SomeProperty
{
get { return m_nSomeProperty; }
set { m_nSomeProperty = value; }
}
}

Enjoy!