Return Types in Excel VBA

[Jesse0311]

I've defined a user define type (ItemType) in a public module (Utils):


Public Type ItemType
sTag As String
lTime As Long
dOpen As Double
dHigh As Double
dLow As Double
dClose As Double
lVolume As Long
dWAP As Double
lCount As Long
End Type


I then created a class (cRingBuffer) in which I implemented a function (GetItem) with a return type "ItemType":

Public Function GetItem(iOffSett As Integer) As ItemType
Dim buffIndex As Integer

On Error GoTo ErrorHandler

buffIndex = CalcIndex(Index - iOffSett)
If iOffSett >= 0 Then
GetItem.sTag = ItemBuffer(buffIndex).sTag
GetItem.lTime = ItemBuffer(buffIndex).lTime
GetItem.dOpen = ItemBuffer(buffIndex).dOpen
GetItem.dHigh = ItemBuffer(buffIndex).dHigh
GetItem.dLow = ItemBuffer(buffIndex).dLow
GetItem.dClose = ItemBuffer(buffIndex).dClose
GetItem.lVolume = ItemBuffer(buffIndex).lVolume
GetItem.dWAP = ItemBuffer(buffIndex).dWAP
GetItem.lCount = ItemBuffer(buffIndex).lCount
Else
GetItem.sTag = "Does Not Exist"
End If

Exit Function

ErrorHandler:
MsgBox "3 - " & Err.Description

End Function


I then created a 2nd class module (cAPI) in which I implemented a Public Sub "SetHandle" to pass in a reference (from main module) to an instance of cRingBuffer. In the Sub, I call GetItem and attempt to display one of the fields of the user defined type.



' ===============
' set up link to ring buffer
' ===============
Public myBuffer As Object

Public Sub SetHandle(objRB As Object)

On Error GoTo ErrorHandler

Set myBuffer = objRB

If Not myBuffer Is Nothing Then
MsgBox myBuffer.GetItem(0).sTag <----- 1st field of ItemType
End If

Exit Sub


ErrorHandler:

MsgBox "1 - " & Err.Description & " #: " & Err.LastDllError

End Sub

So, the issue is that I get error -2147417848 when execution gets to the MsgBox line. By changing the return type to integer on GetItem the error disappears. According to the VBA documentation, I should be able to return a user defined type. Any Ideas?

[Aflatoon]

It is because you are late binding by using Objects. If you change the declaration of myBuffer to Public myBuffer As cRingBuffer, then the code should work. If you really need to use Object for some reason, then you will have to convert the UDT into a class.