0

With the use of call statement, I am calling a sub RETURNSEARCHMATCHES that includes UDF FINDCOLLETTEROFNAMEDRANGE(string).

The code where I call the function is below:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    On Error Resume Next
    Application.EnableEvents = False
    If Selection.Count = 1 Then
        If Not Intersect(Target, Range("SearchField")) Is Nothing Then
            Call OSS_macros.RETURNSEARCHMATCHES
        End If
    End If
    Application.EnableEvents = True
End Sub

I was debugging the code inside RETURNSEARCHMATCHES and I found out that the UDF function FINDCOLLETTEROFNAMEDRANGE(string) is not called by the sub (the code is below):

Public Function FINDCOLLETTEROFNAMEDRANGE(range_name As String) As String
    Dim cell_range As Range
    
    Set cell_range = Range(range_name)
    
    If cell_range.Address(0, 0) <> "" Then
        FINDCOLLETTEROFNAMEDRANGE = Left(cell_range.Address(0, 0), 1)
    Else
        FINDCOLLETTEROFNAMEDRANGE = "NONE"
    End If
End Function

Sub RETURNSEARCHMATCHES()
    Dim cw As Worksheet
    Dim is_matchLeft_name As String
    Dim is_matchLeft_col As String
    Dim last_row As String    
 
    Set cw = Sheets("4c.Travel Costs (Search)")
    
    last_row = CStr(cw.Cells(cw.Rows.Count, 2).End(xlUp).Row)
    
    Debug.Print "OK"

    is_matchLeft_name = "Is_Match_from_left" 
    is_matchLeft_col = FINDCOLLETTEROFNAMEDRANGE(is_matchLeft_name)
    
    Debug.Print is_matchLeft_col
End Sub

Do you know why it is like this?

Am I supposed to pass this UDF function somewhere in the call statement?

braX
  • 11,506
  • 5
  • 20
  • 33
John Snow
  • 107
  • 1
  • 10
  • 1
    First, if you remove `On Error Resume Next` do you get any errors? – braX Dec 15 '21 at 10:06
  • Yes, I was getting run-time error 91 without it. I think that the problem is not in `Worksheet_SelectionChange` Sub – John Snow Dec 15 '21 at 10:10
  • 2
    When it errors, and you click debug, which line is highlighted? – braX Dec 15 '21 at 10:16
  • The problem is that id does not produce an error, it just does not call `FINDCOLLETTEROFNAMEDRANGE` function as `Debug.Print is_matchLeft_col` produces nothing – John Snow Dec 15 '21 at 10:28
  • Then your `Intersect` must have returned `Nothing`. – braX Dec 15 '21 at 10:29
  • @JohnSnow what is this: "FINDCOLLETTEROFNAMEDRANGE" function, can you show? – bankeris Dec 15 '21 at 10:30
  • 1
    Make sure you leave out `On Error Resume Next` - do not put it back in. You need to fix that problem, not ignore it. – braX Dec 15 '21 at 10:30
  • @braX I tried `MsgBox` after `Intersect` and it gave the right output so I do not think it's the problem – John Snow Dec 15 '21 at 10:34
  • Then put a breakpoint on that `If` statement and use `F8` to step through it. That's how you find what needs to be fixed. – braX Dec 15 '21 at 10:35
  • @braX You were right. The call ignored the error message because of 'On Error Resume Next` line. Thanks a lot! – John Snow Dec 15 '21 at 10:36
  • 1
    OK so you have it fixed then? – braX Dec 15 '21 at 10:38
  • Not yet but I am thinking to resolve the problem with line: `Set cell_range = Range(range_name)` inside `FINDCOLLETTEROFNAMEDRANGE` function. – John Snow Dec 15 '21 at 10:39
  • OK, I found out the problem was with Range object. My problem was that I called a sub from wsheet, then I used a global `Range` with named value and Excel was misconfused about the Range. The problem is also explained here: https://stackoverflow.com/questions/61873319/excel-vba-error-1004-on-range-objects-using-range-names-scoped-to-the-workbook-l All in all, thanks for help. – John Snow Dec 15 '21 at 11:25

0 Answers0