VBA Function breaks on End Function: "Object doesn't support this property or method"

Question

I'm trying to return my custom class XportTemplateRow which looks like this:

Option Explicit

Public Token As ShapeToken
Public Index As Integer
Public Value As Variant

From my function that validates input from a spreadsheet. However, my function is unable to execute successfully, raising the error "Object doesn't support this property or method." when it gets to the End Function line.

The watch window (attached below) shows that the values are being assigned as expected. The row data looks similar to this, where ellipses are many rows with data that isn't used in this function. :

index
 |
 |  token
 |    |
 |    |          value
 V    V            V
[1, NONE, ..., 0             , ...]
[2, HEAD, ..., "someItemName", ...]
[3, NONE, ..., 1234          , ...]
 ...

I'm not sure what's going wrong. If I remove Set from the second-to-last line I get the error "Object variable or with variable not set." I don't know how to continue debugging this.

The function causing problems looks like this:

Public Function getXportTemplateRow(rowNumber As Long, template As XportTemplate, _
                                    MasterFileSpecSheet As Worksheet) _
                                    As XportTemplateRow
    ' -----------------------------------------------------------
    ' Return an XportTemplateRow by parsing the XportTemplate at given row.
    ' -----------------------------------------------------------
    Dim outputRow As XportTemplateRow
    Set outputRow = New XportTemplateRow
    Dim tokenDict
    Set tokenDict = getTokenDict()
    ' Build tokenDict from settings file

    outputRow.Index = template.IndexRng.Cells(rowNumber, 1)
    outputRow.Token = tokenDict(template.TokenRng.Cells(rowNumber, 1))

    ' May need to validate here
    outputRow.Value = getXportValue(template.ConstRng.Cells(rowNumber, 1), _
                                    template.LookupRng.Cells(rowNumber, 1), _
                                    MasterFileSpecSheet)

    Set getXportTemplateRow = outputRow
End Function ' <--- Debugger breaks on this line

I want this function to return the XportTemplateRow class containing the 3 values: (Index, Token, Value)

Edit 1:

I call the function like this:


Dim template As XportTemplate
Set template = New XportTemplate
' ...
' Code to populate template
' ...

Dim i As Long
For i = LBound(skuWorksheet) To Ubound(skuWorksheet)
    Dim xportRow As XportTemplateRow
    Set xportRow = New XportTemplateRow
    xportRow = getXportTemplateRow(i + 1, template, skuWorksheet) ' (Range index from 1)
    ' ...
    ' do things with xportRow
    ' ...
Next i

Edit 2: Minimum Reproducible Example

Public Sub TestXport()
    Dim specSheet As Worksheet
    Set specSheet = Worksheets("PRODUCTS")

    Dim xportTemplateSheet As Worksheet
    Set xportTemplateSheet = Worksheets("PRODUCTS_Template")

    Dim template As XportTemplate
    Set template = New XportTemplate

    Set template.IndexRng = xportTemplateSheet.Range("A9:A16")
    Set template.ConstRng = xportTemplateSheet.Range("D9:D16")
    Set template.TokenRng = xportTemplateSheet.Range("C9:C16")
    Set template.LookupRng = xportTemplateSheet.Range("E9:E16")

    Dim xportRow As XportTemplateRow
    Set xportRow = New XportTemplateRow
    xportRow = getXportTemplateRow(0 + 1, template, specSheet) ' <--- Breaks within
    Debug.Print xportRow.Index
    Debug.Print xportRow.Token
    Debug.Print xportRow.Value
End Sub

Solution

    Dim xportRow As XportTemplateRow
    Set xportRow = New XportTemplateRow
    xportRow = getXportTemplateRow(i + 1, template, skuWorksheet) ' (Range index from 1)

becomes

    Dim xportRow As XportTemplateRow
    Set xportRow = getXportTemplateRow(i + 1, template, skuWorksheet) ' (Range index from 1)

The debugger probably isn't being accurate about the line which has the problem. `End Function` itself is clearly not the problem. Without a [mcve], it is hard to say what the problem is. — John Coleman, Nov 14 '19 at 01:22
"The watch window (attached below) shows that the values are being assigned as expected" -- does that mean that you expect `Token` to be `NONE` and `Value` to be `0`? — John Coleman, Nov 14 '19 at 01:44
@JohnColeman Yes, that's the output I expect; the row essentially looks like this [1, NONE, 0]. I was pretty confused to see ```End Function``` being flagged as the problem. I'll put together a minimal reproducible example to hopefully clear things up. — Jehy, Nov 14 '19 at 03:08
`I call the function like this:` - you are supposed to call it [with `Set`](https://stackoverflow.com/a/17877644/11683) too. And you should remove the `Set xportRow = New XportTemplateRow` which is before you call the function (but keep it inside the function). — GSerg, Nov 14 '19 at 18:02
@GSerg ```Set``` was the problem! It's been the bane of my VBA experience every time. Thanks :). Could you clarify what you mean by "but keep it inside the function?" — Jehy, Nov 14 '19 at 18:11
Inside `getXportTemplateRow` you have `Set outputRow = New XportTemplateRow`. That you need to keep. — GSerg, Nov 14 '19 at 18:12

VBA Function breaks on End Function: "Object doesn't support this property or method"

0 Answers0