Frustrating intermittent VBA error dialog telling me that "User-define Type Not Defined"

[OaklandJim]

Team Mr. excel

I am encountering a frustrating issue. I have a workbook with two seemingly basic routines. I keep getting an error message that does not tell me where the issue is. And it is intermittent. The message is "User-define Type not Defined". The message header is "Microsoft Basic for Applications".

I have scoured the code and cannot determine what or where the error is. I did not intend to use a user-defined type.

I really appreciate your assistance. What should I look for and where in code?

Jim

' ----------------------------------------------------------------
' Procedure Name: SetNewPriceDataLinks
' Purpose: Set link-to-price for new stocks and funds entered by user.
' Procedure Kind: Sub
' Procedure Access: Public
' Author: Jim
' Date: 8/21/2024
' Comments: Iterate all cells in the Symbols range. First, determine if
'           there are any new entries. If so then sub adds the link-to-price
'           for new holding(s) entered to retrieve the price for an issue.
'           That link-to-price uses Excel's built-in stock price retrieval
'           functionality.
'
' ----------------------------------------------------------------

Sub SetNewPriceDataLinks()

'   ----------------------
'       Error Handling
'   ----------------------
    Dim sSubName As String
    Dim sStepID As String
    Dim sErrMsg As String
    
    sSubName = "SetNewPriceDataLinks"
    sStepID = ""
    sErrMsg = ""
    On Error GoTo ErrHandler
'   ----------------------
    
    sStepID = "Declarations"
    
    Dim rCell As Range
    
    Dim rSymbols As Range
    
    Dim sLink As String
        
    Set rSymbols = [Prices].Range("SymbolsList")

    With [Prices]
    
        sStepID = "Checking for new holding(s)"

'       Iterate through each symbol cell to dtermine if there is a new holding(s).
'       Note that rCell.value = symbol and rCell.Offset(0, 2).Value = price.
        For Each rCell In rSymbols
        
'           Is a new entry if len symbol >0 and len price = 0. If so then
'           1. a new entry was found and 2. exit for.
            If Len(rCell.Value) <> 0 And Len(rCell.Offset(0, 2).Value) = 0 _
             Then
                
                Exit For
            
'           No new holding(s) exist if encountering this scenario: len symbol = 0 AND
'           len price = 0. If so then 1. tell user "no new holdings (entered)" and 2. exit sub.
            ElseIf Len(rCell.Value) = 0 _
               And Len(rCell.Offset(0, 2).Value) = 0 _
             Then

                MsgBox "No new holding(s) symbol(s) were found", vbOKOnly + vbInformation, "Adding new holdings."
                Exit Sub
            
            End If

        Next rCell
        
        sStepID = "Iterating through symbols' cells"
           
'       Iterate all symbols cells to process any new holding(s) entered by user.
        For Each rCell In rSymbols
        
'           Process cells in the Symbols range. If encountering an empty cell
'           then there are no more symbols to iterate through.
            sStepID = "Checking symbol cell is empty. If so then done."
        
            If rCell.Value = "" Then Exit Sub
            
            sStepID = "Checking price cell is a string"

'           Establish the link-to-price for the current symbol if 1. Typename
'           for the price is either String or Empty.
            If TypeName(rCell.Offset(0, 2).Value) = "String" _
               Or Len(rCell.Offset(0, 2).Value) = 0 _
             Then
                
                sStepID = "Putting symbol into link cell"
                
'               Make the symbol upper case.
                rCell.Value = UCase(rCell.Value)
                                
'               Put the symbol into the link cell.
                rCell.Offset(0, 1).Value = rCell.Value
                
                sStepID = "Setting link-to-price for " & rCell.Value
                
                sErrMsg = "Sometimes setting the link-to-price for" & Chr(10) _
                        & "a symbol does not work so TRY AGAIN!"

'               Try to put the link--to-price for the SYmbol into its corresponding link cell.
                rCell.Offset(0, 1).ConvertToLinkedDataType ServiceID:=268435456, LanguageCulture:="en-US"
            
            End If

        Next rCell
    
    End With
    
Exit Sub

ErrHandler:

Call ErrorMessage(Err.Number, Err.Description, sSubName, sStepID, sErrMsg)

End Sub

'
' ----------------------------------------------------------------
' Procedure Name: ErrorMessage
' Purpose: Use to render the error messages from error handler.
' Procedure Kind: Function
' Procedure Access: Public
' Parameter piErrNum (Long): The Excel error number from the error handler.
' Parameter psErrDescr (): The Excel error description from the error handler.
' Parameter psSubName (): Name of calling sub or function.
' Parameter psStepID (): Name of the step within caller where the error occurred.
' Parameter psErrMsg (): Message to user about the error.
' Author: Jim
' Date: 7/24/2020
' ----------------------------------------------------------------
Function ErrorMessage( _
    piErrNum As Long, _
    psErrDescr As String, _
    Optional psSubName As String = "", _
    Optional psStepID As String = "", _
    Optional psErrMsg As String = "")

'   User interrupted operation
'    If piErrNum = 18 Then Exit Function

    On Error GoTo 0
    
    Dim sMsg As String

    Dim sTitle As String

    sTitle = "Error Message"

    sMsg = "Error #" & piErrNum & " occurred"

    If psSubName <> "" _
     Then sMsg = sMsg & " in procedure " & psSubName

    sMsg = sMsg & "."
    
    sMsg = sMsg & Chr(10) & Chr(10) & "Error Type: " & psErrDescr

    If Right(psErrDescr, 1) <> "." And Right(psErrDescr, 1) <> "!" _
     Then sMsg = sMsg & "."
    
    If psStepID <> "" _
     Then
        sMsg = sMsg & Chr(10) & Chr(10) & "Step ID: " & psStepID

        If Right(psStepID, 1) <> "." _
         Then sMsg = sMsg & "."
    End If

    If psErrMsg <> "" _
     Then
        sMsg = sMsg & Chr(10) & Chr(10) & psErrMsg

        If Right(psErrMsg, 1) <> "." And Right(psErrMsg, 1) <> "!" _
         Then sMsg = sMsg & "."
    End If
    
    MsgBox sMsg, vbOKOnly + vbCritical, sTitle

    Err.Clear

    Application.StatusBar = False

End Function
'

 

[Micron]

It's often the result of executing a line of code that uses a method or property for which the needed code library (Tools>References) is not selected or is missing. Check your references for missing ones first. If none found, try setting the option to Break on Unhandled Errors, or Break on All Errors and continue to use. Or have user(s) pay attention to what they were doing just prior to the error being raised.