Solved: Trim Function...

[Flipback]

I saw that there was a code submitted by Anne Troy that Changed the case of selected cells.
[vba]
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 [/vba]
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]

Hi Flipback,
Welcome to VBAX
try
[VBA] Rng.Value = UCase(Trim(Rng.Value)) [/VBA]

[mdmackillop]

BTW, when you post code, select it and click the VBA button to format it as shown.

[Flipback]

That worked great. Now what would I do if I just wanted the trim function?

[Bob Phillips]

[vba]

Rng.Value = Trim(Rng.Value)
[/vba]

[Flipback]

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]

You can't call your sub trim, otherwise that overrides the real Trim function, and it tries to call itself.

[Flipback]

Thanks for the help. Everything worked out great. =)

[mdmackillop]

Hi Flipback,
If this is Solved, you can mark it so using the Tread Tools dropdown
Regards
MD

[malik641]

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]

Why? If you take that attitude, you should also apply it to VBA.Left, VBA.Len, etc.

[malik641]

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]

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]

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