How to make UDF search using InStr run faster?

I've got a very simple UDA to search for text in each cell of a 1 dimensional array. See attached. It runs pretty fast until its combined with a few other Excel functions and given a limited number of row to work on. Any idea how to make it run faster?

As a first comment: you are using the full column B ... it takes time, just limit the range to what is needed: B7:B21

A few comments:

As a rule, any VBA operation is slower than an equivalent Excel formula only operation due to the overhead needed for VBA and Excel to talk to each other. It appears to me that your current operation is equivalent to =IFERROR(INDEX(data!A,MATCH("*"&A4&"*",data!C,0)),"not found") where data!C contains a formula to convert any single entry numbers to text =IF(ISTEXT(A2),B2,TEXT(B2,"General")). This approach uses a slow exact match linear lookup, but is still much faster than your UDF.

As noted, you are using full column references, and your UDF has no code for testing "used range". So the UDF goes through all 1 million rows in column B -- even though almost 1 million of those rows are blank. Limiting the range passed to the UDF should dramatically help performance. If you want to be able to pass full column references, you will want some way for the procedure to detect "last used row" so that it will stop when it gets to that point.

You have two calls to your UDF inside of each formula -- effectively duplicating that effort. I would expect some improvement from putting the udf-instrsearch() function into a helper cell, so your final formula does not need to repeat the lookup twice per formula.

Finally, you are using OFFSET() instead of INDEX(). OFFSET() is volatile, so every copy of the formula will recalculate with each calculate event -- even when nothing has changed.

Unless there is some other reason that I am not seeing to use a VBA UDF, I would be inclined to use the INDEX(...MATCH()) function with an exact, wildcard match. If there are other reasons you are required to use VBA for this, I would spend some time with the code to improve the lookup and how it handles full column references. Maybe even call the MATCH() function from within VBA (https://docs.microsoft.com/en-us/off...n-visual-basic ).

You might need VBA for what you are asking -- it isn't clear to me exactly what you want to do next. However, what you describe sounds more like a filter, so I would start by exploring the autofilter (and maybe advanced filter) tools to see if I could achieve my goal using these built in tools before turning to VBA.

Autofilter: https://www.wikihow.com/Use-AutoFilter-in-MS-Excel
Advanced filter: https://www.contextures.com/xladvfilter01.html

At this point, the problem also starts to feel like something that power pivot/power query/get and transform might also handle better than VBA. I have no experience with these tools, so I cannot help with specifics, but you might find it worthwhile to become familiar with what these tools can do (if you frequently need to query/filter your data).