Private Function within class module/object, can't pass value of range as string

You would use Set in conjunction with objects. The return value is a string therefore uses Let instead. And Let is optional.

Why do you think you should, or want to, be using Set?

I don't get that error with your files (after removing the Set). I do note though that you never check to see if your Find operations succeeded before using the found cell, which is bad practice.

Also note that code like this can fail:

Set sVeh_searchRng = tableSheet.Range(Cells(impFilePrefix_Cell.Row, 1), Cells(lastRowOfTableSheet, 1))

It needs to be:

Set sVeh_searchRng = tableSheet.Range(tableSheet.Cells(impFilePrefix_Cell.Row, 1), tableSheet.Cells(lastRowOfTableSheet, 1))

Originally Posted by rorya

I don't get that error with your files (after removing the Set). I do note though that you never check to see if your Find operations succeeded before using the found cell, which is bad practice.

Removing set get generates error 91 on my end.

    extractOrginalSourceVehNum = sourceVehCell.Offset(, 1).Value   'Line 390

I use the the debugger, and soruceVehCell is pointing to the correct cell that holds the value, and vba is refusing to pass the value to the string variable. Sheet3 shows what Sheet2 is supposed to look like.

I the following file (attached to this post), I'm able to assign to do extractOrginalSourceVehNum = sourceVehCell.Offset(, 1).Value, without that error message.

I even got rid of "As String", in the initial spreadsheet I uploaded, and the same problem still occurs.

As stated, this function and it's result is meant to be only called within the class module, not outside of it.

I went ahead and implemented property let and property get

Private Property Set e_extractOrginalSourceVehNum(tableSheet As Range)
    Dim impFP_searchRng As Range: Set impFP_searchRng = tableSheet.Range("C1:C" & lastRowOfTableSheet)
      
    'It's possible for 2 different car models to have same SourceVeh ID
    'Search for 1st impFilePrefix row, then search for 1st occurence of sourceVeh and extract original vehNum
    Dim impFilePrefix_Cell As Range: Set impFilePrefix_Cell = impFP_searchRng.Find(What:=impFilePrefixStr, lookat:=xlWhole, After:=impFP_searchRng(impFP_searchRng.Count))
    
    'Allows us to start searching at the first instance where impfilePrefix was obtained.
    Dim sVeh_searchRng As Range: Set sVeh_searchRng = tableSheet.Range(tableSheet.Cells(impFilePrefix_Cell.Row, 1), tableSheet.Cells(lastRowOfTableSheet, 1))   '1 is columnA
    
    Dim sourceVehCell As Range: Set sourceVehCell = sVeh_searchRng.Find(What:=sourceVeh, lookat:=xlWhole)
    
    Set sourceVehCell_vehNum = sourceVehCell.Offset(, 1)
End Property

Private Property Get e_extractOrginalSourceVehNum() As Range
    Let e_extractOrginalSourceVehNum = sourceVehCell_vehNum
End Property

And I'm getting the same error message, but in a different function

Private Function updateSourceVeh()
    'If vehNum matches orgiVehNum, don't run
    If sourceVehCell_vehNum.Value <> vehNum Then
        If Len(sourceVehCell_vehNum.Value) <> Len(vehNum) Then
            'VehNum was derived from sourVeh, right... right?
            sourceVehCell_vehNum.Value = enlargeOrigVehNum(sourceVeh, sourceVehCell_vehNum.Value, vehNum)
        End If
        
        'Replace origVehNum in sourceVeh with vehNum, by extracting values left of, and the appending it.
        Dim tempSourceVeh As String: tempSourceVeh = truncateSourceVeh(sourceVeh, sourceVehCell_vehNum.Value) + vehNum
        
        sourceVeh = tempSourceVeh + Right(sourceVeh, Len(sourceVeh) - Len(tempSourceVeh))
    End If
End Function

I think I found the cause.

In the ClassIntialize Function, I needed to reposition a Property Let/Get function, i.e. exportFileName(expFilePrefixStr) = vehNum

to below the sourceVehCell_vehNum = extractOrginalSourceVehNum initialization, since the former was calling a function, dependent on the result from the initialization of sourceVehCell_vehNum.

The debugger was displaying a sourceVeh number that was non-existent in Column A.