PDA

View Full Version : Solved: Trim Function...



Flipback
05-29-2007, 01:17 PM
I saw that there was a code submitted by Anne Troy that Changed the case of selected cells.

Option Explicit

Sub ConvertCase()
Dim Rng As Range
For Each Rng In Selection.Cells
If Rng.HasFormula = False Then
'Use this line for UpperCase text; change UCase to LCase for LowerCase text.
Rng.Value = UCase(Rng.Value)
End If
Next Rng
End Sub
I saw that you could change the text from upper to lower case by just changing " Rng.Value = UCase(Rng.Value) "

I would like to summon the trim function instead of lower case or upper case for the selected text.
Or even better combine the two Upper case and Trim.

Thanks in adavance.

mdmackillop
05-29-2007, 01:28 PM
Hi Flipback,
Welcome to VBAX
try
Rng.Value = UCase(Trim(Rng.Value))

mdmackillop
05-29-2007, 01:29 PM
BTW, when you post code, select it and click the VBA button to format it as shown.

Flipback
05-29-2007, 01:51 PM
That worked great. Now what would I do if I just wanted the trim function?

Bob Phillips
05-29-2007, 02:02 PM
Rng.Value = Trim(Rng.Value)

Flipback
05-29-2007, 02:22 PM
Compile error:
Wrong number of arguments or invalid property assignment
___________________________________________________________

Sub Trim()
Dim Rng As Range
For Each Rng In Selection.Cells
If Rng.HasFormula = False Then
Rng.Value = Trim(Rng.Value)
End If
Next Rng
End Sub

Bob Phillips
05-29-2007, 02:26 PM
You can't call your sub trim, otherwise that overrides the real Trim function, and it tries to call itself.

Flipback
05-29-2007, 02:51 PM
Thanks for the help. Everything worked out great. =)

mdmackillop
05-30-2007, 12:36 AM
Hi Flipback,
If this is Solved, you can mark it so using the Tread Tools dropdown
Regards
MD

malik641
05-30-2007, 07:53 AM
You should explicitly call which Trim function you want. Either VBA.Trim or Worksheetfunction.Trim:



Sub TEST()
MsgBox VBA.Trim(" Malik 641 ") ' Results "Malik 641"
MsgBox WorksheetFunction.Trim(" Malik 641 ") ' Results "Malik 641"
End Sub

Plane old Trim with no explicit call results in VBA.Trim. FYI :)

Bob Phillips
05-30-2007, 08:37 AM
Why? If you take that attitude, you should also apply it to VBA.Left, VBA.Len, etc.

malik641
05-30-2007, 08:51 AM
I've just been in that mode lately. Like Excel.Range and Public Sub, rather than relying on defaults. I don't know if they have any gains, I just like it better. It's clearer, even though it takes longer to code.

...Maybe I shouldn't have said "You should explicitly call...". It's a preference, really. I haven't found problems with defaults.

And the OP should know that Trim will default to VBA.Trim and that they will have to call for Worksheetfunction.Trim for different results.

Bob Phillips
05-30-2007, 10:37 AM
I tend toi agree with that in principle, for instance I always use Private and Public in procedure signatures, but using VBA seems like taking it too far even for me.

malik641
05-30-2007, 10:59 AM
I see your point. But like I said I'm just in a mode of doing this for now. I don't know if I'll stick with it forever. A big part of the reason I'm doing it is to get familiar with VBA.methods :thumb