0

My code is pretty simple, I am looking for a particular word in a column from a different worksheet. I made a "find" function to return True or false depending on whether or not it finds this word. The find function has two parameters, the particular column in the worksheet and the word it is looking for. See code below:

Public Function FoundExp(expID As String, ByVal lsCol As listColumn) As Boolean

Dim exp As String
Dim listCol As ListColumn
exp = expID
Set listCol = lsCol


FoundExp = Not lsCol.Range.find(exp) Is Nothing 'changed per BigBen <--error thrown here

End Function

When I call this function from another sub I get the error saying the object hasn't been set. Also, when I checked the datatype it comes out as a string.

This is the calling line:

Any idea on what I am doing wrong?

Edit: It might have something to do with the way I am instantiating the original listColumn that I am passing. I am calling the function from a separate sub, in this case in a different module, but the final position of the sub will on the same module as the find function.

Calling sub looks like:

Sub Foo()

Dim tbl2 as ListObject    
Dim lsCol as ListColumn
Set tbl2 = ws.ListObjects.("TableName")
Set lsCol = tbl2.ListColumns(2)

Debug.Print lsCol             'this reads column header text
Debug.Print VarType(lsCol)    'this reads as 8

FoundExp "foo", lsCol

End Sub
Astro
  • 13
  • 1
  • 5
  • Objects always passed byref. – user14797724 Jan 15 '21 at 01:35
  • 2
    @user14797724 - not true, a common misconception. – BigBen Jan 15 '21 at 01:43
  • `listCol.Range.find(exp) = True` assumes that the `Find` is successful, which is not necessarily the case. You need to test if the `Find` succeeded. – BigBen Jan 15 '21 at 01:47
  • You cannot copy an object. Any attempt to merely raises the reference count and returns the same object. – user14797724 Jan 15 '21 at 01:49
  • @user14797724 - common misconception. The pointer to the object is passed `ByVal`. But the question is already closed as a duplicate. – BigBen Jan 15 '21 at 01:51
  • OP: see the linked question for how to test if the `Find` succeeded, using `If Not ... Is Nothing Then`. – BigBen Jan 15 '21 at 01:52
  • @BigBen Sorry, but the question was not about testing the Find function, although I may not be using it correctly. My problem starts before I even get there. When I set the object, the subsequent line of code states the error that the object was not set. – Astro Jan 15 '21 at 01:55
  • But the problem is that you *have* to test if the `Find` succeeded. You do that using `If Not ... Is Nothing`. – BigBen Jan 15 '21 at 01:56
  • The body of your entire function can be one line: `FoundExp = Not lsCol.Range.Find(exp) Is Nothing` but you should really specify more parameters of `Range.Find`: `LookIn, LookAt`, etc. – BigBen Jan 15 '21 at 02:00
  • I get object required as an error at the line you suggested. I am still passing the original listobject as a ListObject per the function's parameter. Should I change that to listobject? – Astro Jan 15 '21 at 02:03
  • You forgot to `Set tbl2`. – BigBen Jan 15 '21 at 02:05
  • I didn't explicitly write it here, but I did have it set in my code. That was my fault. I still have the error at the line you mentioned. Does it have to do with the way I am setting listColumn? – Astro Jan 15 '21 at 02:13
  • 1
    @BigBen I finally understand what you mean about having to test if find succeeded using the If Not Is Nothing logic. – Astro Jan 15 '21 at 02:48
  • The property is passed byval but the object byref. – user14797724 Jan 15 '21 at 03:50
  • ByVal passes a copy of the pointer (with objects) whereas ByRef passes the pointer. One of my favourite explanations is given [here](https://stackoverflow.com/questions/41812969/do-i-need-to-pass-a-worksheet-as-byref-or-byval/41813615?). The object is NOT passed around. – QHarr Jan 15 '21 at 06:47

0 Answers0