Singletons in VBA?

[Guest]

Here's a simple method...

Public Function fetchFields(securities() As String, fields() As String) As
Variant
Set blp = New BlpData
fetchFields = blp.BLPSubscribe(securities, fields)
End Function

The only problem is that the first line is expensive, it opens a port to the
underlying bloomberg engine, and takes about 1-2 seconds. This method gets
called repeatedly, so it adds up.

What I would like to do is set the object once. So I tried this...

Private blp As BlpData
Public Function fetchFields(securities() As String, fields() As String) As
Variant
If IsNull(blp) Or IsEmpty(blp) Or IsMissing(blp) Then
MsgBox "is null"
Set blp = New BlpData
End If

fetchFields = blp.BLPSubscribe(securities, fields)
End Function

But this always gives an error saying that it isn't set. What is the trick
here?

Maury

 

[Matthias Klaey]

Here's a simple method...

Public Function fetchFields(securities() As String, fields() As String) As
Variant
Set blp = New BlpData
fetchFields = blp.BLPSubscribe(securities, fields)
End Function

The only problem is that the first line is expensive, it opens a port to the
underlying bloomberg engine, and takes about 1-2 seconds. This method gets
called repeatedly, so it adds up.

What I would like to do is set the object once. So I tried this...

Private blp As BlpData
Public Function fetchFields(securities() As String, fields() As String) As
Variant
If IsNull(blp) Or IsEmpty(blp) Or IsMissing(blp) Then
MsgBox "is null"
Set blp = New BlpData
End If

fetchFields = blp.BLPSubscribe(securities, fields)
End Function

But this always gives an error saying that it isn't set. What is the trick
here?

Maury

You might try

Private blp As New BlpData

and leave out the If test entierly, or

Private blp As BlpData
....
If blp Is Nothing Then
Set blp = New BlpData
End If

HTH
Matthias Kläy

 

[Guest]

Worked like a champ, thanks! Strange syntax, this VBA...