How to use a class function outside the class

[jcarlosd]

I'm trying to use a class for defining a set of data and also to get values from two different ways, a database and a spreadsheet. Let me explain with an example.

I created a class called clsData with the following code:

Public conn As ADODB.Connection
Private p_isin As String
Private p_tick As String

Public Property Let isin(myvalue As String)
p_isin = myvalue
End Property
Public Property Get isin() As String
isin = p_isin
End Property
Public Property Let tick(myvalue As String)
p_tick = myvalue
End Property
Public Property Get tick() As String
tick = p_tick
End Property


I inserted a public function inside that class, in order to retrieve the data in the two mentioned ways:

Public Function GetData(myvalue As String, DB As Boolean) As clsData
Dim rs As ADODB.Recordset
Dim sql As String
Dim c As Range
Dim myData As clsData
Set myData = New clsData

If DB Then
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseServer
sql = "SELECT * FROM dbData WHERE isin='" & myvalue & "'"
rs.Open sql, conn

If Not rs.EOF Then
myData.isin = rs("isin")
myData.tick = rs("tick")
End If
rs.Close
Else
With ActiveSheet
Set c = .Range("A:A").Find(myvalue, LookAt:=xlWhole)
If Not c Is Nothing Then
myData.isin = .Cells(c.Row, 1)
myData.tick = .Cells(c.Row, 2)
End If
End With
End If
GetData = myData
End Function

All that code goes into clsData. Now I add a standard mode with this code:

Public Function GetClassData() As clsData
Set GetClassData = New clsData
End Function

Sub myTest()
Dim AllData As clsData
Set AllData = New clsData
AllData = GetClassData.GetData("FI0009000681", False)
End Sub

The first function is in order to access the class and the function inside
The routine myTest is trying to retrieve the data I need

I get an error in last line of the function: "GetData = myData". Error message window says "Object variable or block With not established" (actually it is a translation of my current Excel version)

Anybody knows what is wrong with this code?
I can send the whole code to anybody who can help

JC

 

[Hermanito]

You would need to use
Set GetData = myData

When assigning 'values' to class instances, you need to use Set (Set is the exact same thing as Let, but for objects). VBA understands implicit Let, but not implicit Set.

For example:
Public Property Let isin(myvalue As String)
p_isin = myvalue
End Property
There you assign a simple type, so you don't need the Let, but what it actually says to the VB-compiler is this:
Public Property Let isin(myvalue As String)
Let p_isin = myvalue
End Property

In Microsoft's infinite wisdom, it was decided that the user should be the one deciding on when and where to use implicit Let or explicit Set...
In modern VB.NET, neither Let nor Set are needed. The compiler can figure it out himself...

I think there are some more strange issues in your code, but I don't have the time to address them now... maybe later on

 

[jcarlosd]

Thanks "Hermanito"!

You were right, and I forgot how to assign values to classes.

In my code I have to add "Set" at the end of the Sub inside the class, but I have also to add a "Set" in the calling subroutine, in this way:

Sub myTest()
Dim AllData As clsData
Set AllData = GetClassData.GetData("FI0009000681", False)
End Sub

Thanks for the clarification!

JC