Function to create an instance of a class in VBA

Question

I am trying to use a function within VBA to create a new instance of a class I've defined.

The data looks like this:

Address 1        | Address 2
123 Sample Ln NW | 1        
123 Sample Ln NW | 2

The class has been defined in a class module named "Apt" like this:

Option Explicit

Public Add1 As String
Public Add2 As String

I want to use a function to return an instance of this class (because I have to do it from inside several different subroutines).

Sub TestIssue2()

    Dim i As Integer
    i = 2

    currApt = makeApt(i)

    MsgBox (currApt.Add1 & ", " & currApt.Add2)

End Sub


Function makeApt(i As Integer) As Object

    Dim x As New Apt
    x.Add1 = Worksheets("Sheet1").Range("A" & i).Value
    x.Add2 = Worksheets("Sheet1").Range("B" & i).Value

    makeApt = x

End Function

I get:

"Run-time error '91': Object variable or With block variable not set".

What I've tried:

I have found it does work if I define instance inside the subroutine, so this does work:

Sub TestIssue1()

    Dim i As Integer
     i = 2

    Dim currApt As New Apt
    currApt.Add1 = Worksheets("Sheet1").Range("A" & i).Value
    currApt.Add2 = Worksheets("Sheet1").Range("B" & i).Value

    MsgBox (currApt.Add1 & ", " & currApt.Add2)

End Sub

Searching multiple variations on "use function VBA to create new class instance" led me to sites like this tutorial or this tutorial, but both are talking about storing functions inside classes, not using a function to create one.
The closest question I could locate on this site is this question, where some of the answers seem to indicate what I want is possible. However, that question asks about a different problem, and doesn't seem to provide an answer to this issue.

score 3 · Accepted Answer · answered Jan 05 '20 at 15:15

You have a couple of small mistakes. Firstly in TestIssue2() you must define currApt, and then use the Set keyword when assigning. So this becomes:

Sub TestIssue2()

    Dim i As Integer
    i = 2
    Dim currApt As Apt

    Set currApt = makeApt(i)

    MsgBox (currApt.Add1 & ", " & currApt.Add2)

End Sub

Secondly you should make your makeApt Function return an Apt (rather than an Object), and also use the Set keyword. Like this:

Function makeApt(i As Integer) As Apt

    Dim x As New Apt
    x.Add1 = Worksheets("Sheet1").Range("A" & i).Value
    x.Add2 = Worksheets("Sheet1").Range("B" & i).Value

    Set makeApt = x

End Function

It should now work fine.

Function to create an instance of a class in VBA

1 Answers1