Excel VBA Search Results Array Crashing

Hope someone is versed enough in VBA to help me figure out the problem here. I understand enough to implement this code (copy paste) but do not understand its workings. I need to find out how to avoid it causing Excel to crash (to the point where Excel tries to run Recovery of the files).

I have a sheet where row:A is a list of words, few hundred lines down - each with a phrase (prescription drug names). Cell B1 is an empty cell (where user will type in a single word). B2:B50 is an array with the following function/formula: {=TRANSPOSE(ArrayOfPartialMatch(B1,A1:A500))}

It works great for the most part, but for certain words it crashes (commonly for 3 letter words) among others. And its always, the same words - its not random... for instance I will type in "hydrochloride" and it will crash Excel EVERY time...

My sub is as follows (which is where the function above, for the array comes in)....

Option Explicit


Function ArrayOfPartialMatch(ByVal SearchFor As String, ByVal SearchRange As Range, Optional Index As Long) As Variant
Dim outRRay() As String, pointer As Long
Dim oneCell As Range

Application.Volatile

Set SearchRange = Application.Intersect(SearchRange, SearchRange.Parent.UsedRange)
If SearchRange Is Nothing Then
ReDim outRRay(1 To 1)
Else
ReDim outRRay(1 To SearchRange.Cells.Count)
For Each oneCell In SearchRange
If InStr(LCase(CStr(oneCell.Value)), LCase(SearchFor)) > 0 Then
Rem use preceeding line for case insensitive.

Rem use following line for case sensitive
'If InStr(CStr(oneCell.Value), SearchFor) > 0 Then

pointer = pointer + 1
outRRay(pointer) = CStr(oneCell.Value)
End If
Next oneCell
End If

ReDim Preserve outRRay(1 To Application.Max(Application.Caller.Cells.Count, Index, pointer))
If 0 < Index Then
ArrayOfPartialMatch = outRRay(Index)
Else
ArrayOfPartialMatch = outRRay
End If
End Function

What's it look like when it crashes? Does it just shut down quietly, or do you get an error message first?


Can you post your source data (the content of column A)?


Have a look at our tutorial about "debug". Set a breakpoint at the top of the function and trace it through using the single-step commands. It will likely still crash, but you will see which line is the last to be run before it does so.

Hello Josh,

The advice Cas has given you is excellent and you should typically follow that kind of debugging methodology to problem-shoot your own code.

In this instance though I'm going to try to give you a nudge because I think that this bug is going to be very hard for you spot, especially since the code you provided is well-written and because the crash only occurs under certain conditions. And my intuition tells me that the problem here is not actually with your UDF code per se. Let me elaborate:

Situation that causes a crash:
If your UDF identifies a cell in the range A1:A500 which :
(1) contains the search string and;
(2) the total cell text is longer than 255 characters .

This would explain why you have found consistent search words which are causing the crash.
Try it out and see if my theory holds true.

If it does then have a think about why that may be and post any ideas you have.
To give a hint, there are certain known limitations with the transpose worksheet function.

Also think about how you might try to cater for this by modifying your code. If you're not sure then we'll give you a hand.

I hope that helps,
Colin

I will give some of these a try. The data in the spreadsheet (up-to-date) is at workplace, but an older version here at home does not seem to duplicate the problem. Not sure if it might be a memory problem - since I think our work computers have only 512MB and home I have 2GB running. Could also be something I added to the data.

The crash itself is a hard crash, no error messages - just crashes the whole program - including any other workbooks that are currently open. I previously thought it was an issue with the cell containing more than 255 char of text - but tried to fix it using a left(cell,250) to try and resovle that problem. Since the problem was still occuring - I assumed that wasnt the problem. Maybe I have added data to it since, that has created new cells that are more than 250 char...

Will post back once I do more research.

Not sure if it might be a memory problem - since I think our work computers have only 512MB and home I have 2GB running. Could also be something I added to the data.
I don't think that's likely. a 500-element array would have to contain huge items to start taking up any serious amount of memory. Also, I experimented a little with pushing Excel to the limits of available RAM, and it's very well behaved - it checks first and gives you an "operation can't be completed" error rather than trying and failing to carry it out. :)

The crash itself is a hard crash, no error messages - just crashes the whole program - including any other workbooks that are currently open. I previously thought it was an issue with the cell containing more than 255 char of text - but tried to fix it using a left(cell,250) to try and resovle that problem. Since the problem was still occuring - I assumed that wasnt the problem. Maybe I have added data to it since, that has created new cells that are more than 250 char....

Hello Josh,

Which version of Excel are you using here?

Okay, first up I think we should establish if the cause of the problem is indeed the transpose worksheet function like I suggested.

In a copied test sheet, delete your transpose array formula in B2:B50 so that no formulas containing the transpose function are remaining. Then, type in this multiple result array formula in cells B2:AY2

=ArrayOfPartialMatch($B$1,$A$1:$A$500) 'array formula: CSE entered.


This will give the same search results; it will just present them along a row instead of down a column. And now test it with different search strings in cell B1 - including "hydrochloride" which crashed Excel everytime when the transpose worksheet function was present. Does your Excel crash? If not then we can take it as a pretty good indicator that the problem lies with transpose.

In addition to the 255 character limit per array element when working with the transpose worksheet function, here are other limitations/workarounds you should be aware of:
http://support.microsoft.com/kb/177991
http://translate.google.co.uk/translate?hl=en&sl=de&u=http://www.xlam.ch/xlimits/arrays.htm&sa=X&oi=translate&resnum=1&ct=result&prev=/search&#37;3Fq%3DExcel%2BVBA%2BTRANSPOSE%2B255%2Barray%2Belement%2Bcharact er%2Blimit%26start%3D40%26hl%3Den%26sa%3DN

Check to see if any of them apply to you.

Colin

Thanks for the help - as I mentioned I was working with an older version of the file here and after returning to work it turns out that additional cells of text had been added to the current version (at work). These new lines had more than 255 characters - so it turns out it was as simple as that =P