Error returning object instance from function in VBA

Question

I'm trying to instantiate an object and return it from a function. The class I'm working with is one that I've created. However when I try to set an Object to what was returned from the function I get an error. What am I doing wrong?

Function CreateBlah(NAME As String, Count As Integer, val As String) As Blah
    Dim b As Blah
    Set b = New Blah
    bkmrk.Initialize NAME, Count, val
    MsgBox (bkmrk.NAME)
    CreateBlah = bkmrk
End Function

Then in the other function...

Dim bmrk As Blah
Set bmrk = CreateBlah("Test", 1, Trim(AString))

I also tried...

Dim bmrk As Object
Set bmrk = CreateBlah("Test", 1, Trim(AString))

I'm new to VBA, can anyone tell me what I'm doing wrong?

This question is pretty poorly worded since you've left out your real data types. It's not clear what kind of "objects" you're dealing with, though because of the .Initialize, it looks like a standalone class module. Including that information would like enhance your answers. That said, it's pretty obvious, as @AdamRalph points out below, that the last line of your function should be "Set CreateBlah = bkmrk". That may not be what's causing your problem, but it's a flaw in the code you posted (which may or may not be close to your original). — David-W-Fenton, Feb 06 '10 at 01:45

score 19 · Answer 1 · answered Feb 09 '10 at 03:21

19

You need to use set every time you assign an object. This means when setting return value and when assigning the return value to a variable. Late Bound Example:

Public Sub Example()
    Dim objWrd As Object
    Set objWrd = GetWord
    objWrd.Visible = True
    objWrd.Quit
End Sub

Public Function GetWord() As Object
    Set GetWord = CreateObject("Word.Application")
End Function

Early Bound Example:

Public Sub Example()
    ''//Requires reference to Microsoft Office Word
    ''//(Tools>References)
    Dim objWrd As Word.Application
    Set objWrd = GetWord
    objWrd.Visible = True
    objWrd.Quit
End Sub

Public Function GetWord() As Word.Application
    Set GetWord = New Word.Application
End Function

answered Feb 09 '10 at 03:21

Oorang

6,630
1
35
52

In the Sub Example() you should have the statement: set objWrd = nothing after the statement objWrd.Quit to prevent a memory leak. – Jay Haase May 17 '12 at 02:23
2

@JayHaase, I am not sure about VB, but it is not necessary to set that to "nothing" in other PLs. This is a local variable(or better to call it pointer or reference) and will be automatically destroyed when the subroutine finishes. – meir May 25 '12 at 11:46
@Meir, from my understanding, setting the reference to nothing will speed up the garbage collection process which can be important for expensive resources like recordsets. Please refer to this question: http://stackoverflow.com/questions/517006/is-there-a-need-to-set-objects-to-nothing-inside-vba-functions – Jay Haase May 25 '12 at 21:03

score 7 · Answer 2 · answered Feb 05 '10 at 20:38

I assume that

Dim b As Blah
Set b = New Blah

should actually read

Dim bkmrk As Blah
Set bkmrk = New Blah

if so, you're missing a set keyword.

Set CreateBlah = bkmrk

Once you've fixed this then both versions of your consuming code should work, although the former is better since you are strongly typing the variable.

Error returning object instance from function in VBA

2 Answers2

Linked