Replace Function in Excel 97

[Monk]

The following is a part of a macro that works fine in later versions of
excel. However some users are still on Excel 97 and the replace function in
the code is not recognised. Can someone suggest code that may work under
both 97 and later versions or amended code that will wotk specifically for
97. I having being toying with the substitute command with little success.

Dim Rng As Range, ix As Long
Set Rng = Intersect(Range("D765536"), ActiveSheet.UsedRange)
For ix = Rng.Count To 1 Step -1
If Trim(Replace(Rng.Item(ix).Text, Chr(160), Chr(32))) = "" Then
Rng.Item(ix).EntireRow.Delete
End If
Next

Thanks

 

[Bernie Deitrick]

Monk,

Use Application.Worksheetfunction.Substitute instead of Replace. Will work
for all versions.

Encourage the 97 users to upgrade - 97 was bad...

HTH,
Bernie
MS Excel MVP

 

[Rick Rothstein \(MVP - VB\)]

You can add any one of the functions listed here..

http://www.xbeat.net/vbspeed/c_Replace.htm

to your code window and call that in place of the Replace reference you now
have.

Rick

 

[Jim Cone]

Bernie,
Re "97 was bad..."

So the other side gets a hearing <g>...
It runs code faster and calculates quicker than any later version.
The help file is far superior to later versions (XL2000 is just a joke).
It has classic menus.
You don't have to update it anymore.
Code compatibility problems are minimal with other versions.
The price is right.
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Bernie Deitrick" <deitbe @ consumer dot org>
wrote in message
Monk,
Use Application.Worksheetfunction.Substitute instead of Replace. Will work
for all versions.
Encourage the 97 users to upgrade - 97 was bad...
HTH,
Bernie
MS Excel MVP

 

[Mike Middleton]

Bernie Deitrick -

Regarding: "Encourage the 97 users to upgrade - 97 was bad..."

I agree that we should encourage 97 (and 2007) users to upgrade to 2003, but
I don't think that Excel 97 SR2 was so bad.

I still develop VBA add-ins in Excel 97 SR2 (and test in all later versions
of Excel), but I'm almost ready to move to Excel 2000 as my development
environment.

- Mike Middleton
http://www.DecisionToolworks.com
Decision Analysis Add-ins for Excel

 

[Monk]

Thanks Bernie. Some are resistent to change but I am working on them!!

One final query, the only other error message I am receiving in my code is a
Runtime error '13': Type Mismatch error with respect to the following line
"Set VBComps = ActiveWorkbook.VBProject.VBComponents" in the code below. Are
you able to advise a solution to this? Cheers

Sub DeleteAllVBA()



Dim oSht As Object

Application.DisplayAlerts = False 'suppress delete warning

For Each oSht In Sheets

If Not oSht.Visible Then oSht.Delete

Next

Application.DisplayAlerts = True



Dim VBComp As VBIDE.VBComponent

Dim VBComps As VBIDE.VBComponents



Set VBComps = ActiveWorkbook.VBProject.VBComponents



For Each VBComp In VBComps

Select Case VBComp.Type

Case vbext_ct_StdModule, vbext_ct_MSForm, vbext_ct_ClassModule

VBComps.Remove VBComp

Case Else

With VBComp.CodeModule

.DeleteLines 1, .CountOfLines


End With

End Select

Next VBComp

End Sub

 

[Peter T]

Some interesting replace Replace functions!

I tested some of them but didn't get anything like the speed improvements as
shown in the comparison table. Not much difference between them and the
built-in Replace function in VBA6. If anything some of those near the top of
the table seemed faster. This may be due to the difference of compiled VB6
vs VBA.

However they were all about 10x faster than Application.Substitute, which is
the normal method of Replace in XL97. If speed is consideration it would be
well worth using one of the simpler alternatives that do not require any of
the additional "doping" where indicated, eg Replace01, 02, 03

For use in Excel 97 change
Optional Compare As VbCompareMethod = vbBinaryCompare
to
Optional Compare As Long = vbBinaryCompare

I'd also rename the argument "Compare" to say "lngCompare"

If attempting to use the fastest (as indicated) class function in xl97,
Replace09, remove "Friend".

I'd definitely echo Jim's repost to Bernie's view of XL97 <g>

Regards,
Peter T