Instr Function - Runtime error '7' Out of Memory

  • Thread starter Thread starter RichardJ
  • Start date Start date
R

RichardJ

I am running into a problem with the InStr function on an Excel 95 xls
on some machines, usually the new builds with W2K and XP. When I use
the compare option TEXT , I get the runtime error '7' Out of Memory.
However, when I change the compare option BINARY, then it works.

Sub Test

' Compare option Text
ActiveSheet.Cells(4, 4).Value = InStr(1, "ABC", "B", 1) ' Error

' Compare option Binary
ActiveSheet.Cells(4, 4).Value = InStr(1, "ABC", "B", 0) ' Works

End Sub

Does this have anything to do with doublebyte character sets (DCBS) or
is it the OS builds (dlls) ? I have listed below the HELP file
content for InStr for reference.

Please help.

Thanks.


***********************************************************
INSTR Function (From Help)
***********************************************************

Returns the position of the first occurrence of one string within
another.

Syntax

InStr([start,]string1,string2[,compare])

Elements

The InStr function syntax has these parts:

Part Description

start Numeric expression that sets the starting position for each
search. If omitted, search begins at the first character position.
If start contains no valid data, an error occurs. Start is required
if compare is specified.
string1 String expression being searched. If string1 contains no
valid data, Null is returned.
string2 String expression sought. If string2 contains no valid data,
Null is returned.

compare Number specifying the type of string comparison. Specify 1 to
perform a textual case-insensitive comparison. Specify 0 (default) to
perform a binary comparison. If compare is Null
, an error occurs. Start is required if compare is specified. If
compare is omitted, the setting of Option Compare is used to determine
the type of comparison.

Return Values

Value Description

0 string1 is zero-length.
start string2 is zero-length.
0 string2 not found.
Position at which match is found string2 is found within string1.
0 start > string2.

Remarks

Note When Option Compare Text is specified, comparisons are textual
and case-insensitive. When Option Compare Binary is specified,
comparisons are strictly binary.

Note Another function (InStrB) is provided for use with the
double-byte character sets (DBCS) used in some Asian locales. Instead
of returning the character position of the first occurence of one
string within another, InStrB returns the byte position. In areas
where DBCS is not used, InStrB behaves the same as InStr.
 
Hi RichardJ,

It will be appreciated you post the error information in the newsgroup which may help us know more on this error.

Please feel free to let me know if you have any questions.

Thank you for using Microsoft NewsGroup!

Wei-Dong Xu
Microsoft Product Support Services
Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.
 
Hi RichardJ,

It will be appreciated you tell me which OS you are using with Excel 95. I will try to test this for you. Furthermore, I'd suggest you may upgrade your
excel to a newer version. For office 2000 and later, Microsoft provides automatic update detection service from office.microsoft.com which will
always keep your office system stable.

Please feel free to let me know if you have any questions.

Thank you for using Microsoft NewsGroup!

Wei-Dong Xu
Microsoft Product Support Services
Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.
 
Hi Wei-Dong Xu,

Thanks for you quick response. The OS's we have problems
are are W2K and XP. Some machines have the problem but
others do not. I have temporarily change the inStr
function to use binary search to make it run. But I am a
bit concern about what else would break in the future.
So I am trying to understand the real cause of the
problem so we can make decissions as to how to move
forward.

Thanks in advance for your help.

Regards.

-----Original Message-----
Hi RichardJ,

It will be appreciated you tell me which OS you are
Click to expand...
using with Excel 95. I will try to test this for you.
Furthermore, I'd suggest you may upgrade your
excel to a newer version. For office 2000 and later,
Click to expand...
Microsoft provides automatic update detection service
from office.microsoft.com which will
 
Hi RichardJ,

Thank you for posting in MSDN managed newsgroup!

I have tested this issue for you in windows 2000 with service pack 4. This function performs very well and gives no any runtime error. For your
scenario, this issue may need to perform further debugging for you. However, each Microsoft product has its life cycle for the support. You can find
the Excel lifecycle information from the link of support.microsoft.com:
http://support.microsoft.com/default.aspx?id=fh;[ln];lifeprode

Based on this, Excel 95 has exceeded its lifecycle now, we will not support this product. So I'd suggest you can reinstall Excel 95 on these machines
which throw runtime error.

I also tested this for you in Excel 2000 and Excel 2003. There was no any runtime thrown ever. I'd also suggest you may upate to a newer version of
Office.

Thank you once more for using Micosoft Newsgroup!

Wei-Dong Xu
Microsoft Product Support Services
Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.
 
Back
Top