• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to apply CleanTrim UserDefine Function(UDF) through Evaluate on usedrange in one run without loop?

C

Chirag R Raval

Member
Dear All Respected Experts & Seekers,

I found 1 CleanTrim function created by Respected by Mr Rick Rothsteian on below link

Trim all Cells in a Worksheet - VBA

Hi All, Here is a VBA routine to Trim as well as Clean all used range in a worksheet. '// Developed by Kris @ ExcelFox.com Sub TRIMALL(ByRef Sht As Worksheet) Dim aAddr As String Dim MaxLen As Long Dim v As Variant
www.excelfox.com

Code: 
Function CleanTrim(ByVal S As String, Optional ConvertNonBreakingSpace As Boolean = True) As String
  Dim X As Long, CodesToClean As Variant
  CodesToClean = Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, _
                       21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 127, 129, 141, 143, 144, 157)
  If ConvertNonBreakingSpace Then S = Replace(S, Chr(160), " ")
  For X = LBound(CodesToClean) To UBound(CodesToClean)
    If InStr(S, Chr(CodesToClean(X))) Then S = Replace(S, Chr(CodesToClean(X)), "")
  Next
  CleanTrim = WorksheetFunction.Trim(S)
End Function

Above function work 1 cell in one time , but based on Evaluate method as per very knowledgeable reference from Chandoo.org

Eliminating Run-time error '13': while trying to eliminate ASCII 160

I have the following working line of code which TRIMs and CLEANs the data. .Value = .Parent.Evaluate(Replace("IF(#>"""",TRIM(CLEAN(#)),"""")", "#", .Address)) I'm trying to merge the following: SUBSTITUTE(#,CHAR(160),"") into the above line to also "weedout" ASCII 160, but the following...
chandoo.org

How can this (Sir Rick Rothstein's ) function apply on whole used range in one run without loop by help of Evaluate?

of course source link above from Excel fox have some successfully modified version available , but its run on single cell in one time with the help of loops.
but there are possible to run that in one run without loop through Evaluate but how?

Chandoo.org source ref for Evaluate
.Value = .Parent.Evaluate(Replace("IF(#>"""",TRIM(CLEAN(SUBSTITUTE(#,CHAR(160),""""))),"""")", "#", .Address))

how to modify below in our current requirement?
Function CleanTrim(ByVal S As String, Optional ConvertNonBreakingSpace As Boolean = True) As String

How to apply Evaluate approach ?
.Value = .Parent.Evaluate("CleanTrim("& activesheet.usedrange &"),True"))")
but not work.

Can some one can help regarding this? thank you in advance , help always be appreciated.

Regards,

Chirag Raval
 
Last edited:
Hi, I never tried with an UDF but your original function expects as first parameter a string​
so try with a string within your code instead of an object without any property …​
​
But I doubt it may work as it is : should replace the string parameter by a cell address and if only Evaluate works with an UDF.​
​
 
It seems it can't work that way …​
For a range via VBA, instead of that kind of gas factory function it's better to use Excel inner features :​
Code: 
Sub TrimClean(Rg As Range)
        Application.ScreenUpdating = False
    For Each V In Array(127, 129, 141, 143, 144, 157)
        Rg.Replace Chr$(V), "", xlPart
    Next
        Rg.Replace Chr$(160), " ", xlPart
        Rg.Value = Application.Trim(Application.Clean(Rg.Value))
        Application.ScreenUpdating = True
End Sub
Do you like it ? So thanks to click on bottom right Like !​
​
 
Dear Sir @Marc L ,

Sorry for late reply,

Just Amazing, Directly cover all replaceable Chars in loop, & in another run on same range apply Clean Trim
Just Great & work like a charm.

As per this thread's requirement can it be convert as form of Evaluate ?
Can we convert your valuable code as like below
Code: 
Dim myrng as srtring
myrng=activesheet.usedrange.address

usedrange=Evaluate(" & myrng & ", Application.trim(application.clean(" & myrng &", if#=mid{127, 129, 141, 143, 144, 157},replace("& myrng &", "#",""," & myrng & ")")

hope there are some way found..

Regards,

Chirag Raval
 
Evaluate works with a text as a formula, for example​
​
.Value = .Parent.Evaluate("IF({1},TRIM(CLEAN(SUBSTITUTE(" & .Address & ",CHAR(160),"" ""))))")​
​
 
Dear Sir @Marc L ,

Thank you , it seem from , my mentioned reference of eliminate char 160 thread ...but how to apply this approach to our current thread?

Thank you very much.

Hope some way found there

Regards,

Chirag Raval
 
Let start from the beginning on Excel side, not on VBA aside :​
test a formula directly within a cell in order to get the clean & trim result of a source cell.​
Once you get a functional formula then - and only - it's time to think about Evaluate as a global VBA process.​
But maybe this process will run slower than my post #4 sample …​
​
 
Yep, in most cases Evaluate will be much slower than other methods.
Usually looping through multiple arrays (loaded from single column each) is fastest, then Formula/WorksheetFunction method, then comes Evaluate.

But in this case, since there is built in Range.Replace, it will likely be the fastest method.

Evaluate comes in handy, when speed isn't critical and when you want to process error values returned as well.
 
Dear Sirs @Chihiro & @Marc L ,

Thanks for spread light on the matter.
May be Loop on array seems better in this situation & then come netive functions but, Can we inherit extra charge to replace along with chr 160 in formula? Like replace array in formula {127,129,141,143,144,157,160} in one run?

actually I ( and may be other seekers) want to make this process as one liner as want to know & achieve how can we get similar result without loop.

Mysterious thing in evaluate , "Place Holder's " role like if #<>0 as it's say like every # as for each cell or that celll's value on which Evaluate method work at the same time but individually on every cell of course .at the same time . Like stormy could throwing lightening spark on every cell at same time & every spark is unique for that individual cell. This happens in same time on every cell, not one by one
In particular direction like loop do. That's seems must be achievable art or technique.

it will be great if we can get same result with Evaluate just want to know Evaluate can stand besides loop.

Hope there are still the place for Evaluate.
Though it's seems slower but stay with its core power of do the job on same time on every cell without loop.

in this thread , as Evaluate formula or function , also want more attention & deep understanding that require to know which function return what to process further on that return result.

Hope your some valuable attention regarding this thread's requirements.

Regatds,

Chirag Raval..
 
Last edited:
The power of Evaluate method is its capacity to execute a formula array - not precised in its VBA help - but sometimes​
it may not understand such expectation so adding in the formula IF({1}, for example forces it running a formula array.​
​
If you need more characters to replace / delete, mod the original formula by adding SUBSTITUTE function for example.​
You can test for a single source cell directly within a cell formula ...​
​
 
Dear Sir @Marc L ,

Thank you for given your invaluable understanding on the matter.

Some where in this forum, you tips to Mr @YasserKhalil about same thing that " You must need on someday, to put {1} in Evaluate to force as formula array"

Now we are on core point on formula way
For Substitute multiple Char instead of just Chat(160) that's exciting to understand if we can do on more multiple iinstances nstead of work 1 instance at one time in one formula.

How can we understand core difference between Replace & Substitute?

If substitute work here then how to substitute multiple instances? How to force formula to accept Arrays of multiple instances need to replace in one string ?

Some time feel Evaluate is work like say physically force towards result by pressing Keyboard's Ctrl+Shift+Ent or As Ctrl+Enter
If you want result in one cell or multiple cells at one time That's feel great

We already done the job through loop way but there are another way to do the job that we need to understand

So how can I try substitute in formula to replace multi char in one string or on one cell?

Regards,

Chirag Raval
 
Last edited:
As we are on the VBA section, the better is to open a thread in the formula forum « Ask an Excel question »​
(just for a one cell formula) where some formula master - as I'm just a VBA ronin - may find a way …​
​
 
Dear Sir @Marc L ,

First i found this on search the web...
www.mrexcel.com

Can I Substitute multiple characters in one substitution?

I am using this: =LOWER( SUBSTITUTE( A1, " ", "-")) to replace spaces in a sentence with dashes and make it all lowercase. Occasionally there is a dash in the original cell so I end up with three dashes --- Currently One - Short Sentence becomes one---short-sentence but I would like it to...
www.mrexcel.com www.mrexcel.com

59866

But, Shoking, Here is final answer? on same thread?
59865


But... on here
dhexcel1.wordpress.com

#Excel: Remove Multiple Characters From a String Using The TEXTJOIN Function and Without Using the SUBSTITUTE or REPLACE Function

There are a number of examples of the removal of characters from a string which utilize nested SUBSTITUTE or REPLACE functions. However, they are hard-coded in that the formulas are built wi…
dhexcel1.wordpress.com dhexcel1.wordpress.com

IN "A1" x,da.”xyz”.c?e!

The following array formula removes those symbols

=TEXTJOIN(“”,TRUE,IF(ISERROR(MATCH(MID(A1,ROW(INDIRECT(“1:”&LEN(A1))),1),{“,”;”.”;”?”;”!”;””””},0)),MID(A1,ROW(INDIRECT(“1:”&LEN(A1))),1),””))

and affords the desired string shown below.
xdaxyzce

i try it but formula stuck on (“1:” can not resolve.

Another Find Here

i will try


Regards,
Chirag Raval
 
As I've no time to blow my mind with that kind of formula headache, ask to the formula masters …
Also I never had to delete / replace characters else than #0 to #31 & #160, so the original formula is enough on my side.
And in the case of more characters on huge range, I prefer to use an easy code to maintain like mine within post #4
'cause maybe a one line statement is a beauty but, if you don't understand it well, how can you maintain it ?!
​
 
Dear Sir @Marc L,

I already apply your invaluable VBA in rmy daily routine that do job like a Magic.
Many thanks for your efforts towards this thread.

Thanks Sir @Chihiro for your support.

Okay so I need to more search on formula way regarding Evaluate it & hope it will be found in this great forum.

May be I will be back soon..till than
Many many thanks to you all Experts.

Regards,
Chirag Raval
 
Dear All,

Very interesting site found on Evaluate here

usefulgyaan.wordpress.com

VBA Trick of the Week :: Avoid Loop for Range Calculations – Evaluate

At times we need to change cell values in a range based on a calculation. The most common way of doing it is using loop. e.g. We have numeric data in range A1:B10 of Sheet1 and we need to multiply…
usefulgyaan.wordpress.com usefulgyaan.wordpress.com

And Here

VBA .Index Returned Date Format Different for Range or Array - Eileen's Lounge

www.eileenslounge.com

And Here
usefulgyaan.wordpress.com

VBA Trick of the Week :: Slicing an Array Without Loop – Application.Index

Sometimes we need to slice an array i.e. fetch a row/column from a multidimensional array. There is no inbuilt function in VBA to do the same and the most common way to do so is using a loop. Howev…
usefulgyaan.wordpress.com usefulgyaan.wordpress.com
And Here

Delete One Row From A 2D Variant Array - Page 2

Working in VBA with a 2D array in memory that was formed from a worksheet range is much faster than trying to work with the range directly on the worksheet itself. The way the array is formed is quite simple... just assign the range to a Variant variable. So, if you wanted to create an array in...
www.excelfox.com

Trouble writing huge array into worksheet range [SOLVED]

I admit I'm still learning the mechanics of building large-set arrays in memory, then writing the resulting data out to the worksheet all at once at the end. I've written a macro that builds a one-dimensional array in memory Dim MyArr as Variant, NR as Long Redim MyArr (1 to 1) NR=1...
www.excelforum.com

VBA .Match works differently for second argument Array of El - Eileen's Lounge

www.eileenslounge.com

VBA .Match works differently for second argument Array of El - Eileen's Lounge

www.eileenslounge.com

Hope some one can have the solution in mind.

Regards,

Chirag Raval
 
Last edited:
Nothing new within your links, you already had all the material within the samples from this forum !​
​
And again, restart from the beginning with the TEBV main rule : Think Excel Before VBA ‼​
So the trick is first to just create a valid formula which does the job for one result cell from one source cell, nothing else …​
​
 
You must log in or register to reply here.
Back
Top