Compensate for a space or multiple spaces

crmpicco · May 3rd, 2005, 04:22 AM

The code i have below works fine if the cell is empty (has had nothing entered into it and has not been editted):

Code:

If Range(sRange).Text <> "" Then
   Range(sRange).Interior.ColorIndex = iColour
End If

But if there is a 'SPACE' (i.e. someone has pressed the spacebar inside the cell) or multiple spaces inside the cell then it colours it as it sees it as NOT EMPTY. How can i get around this? What code can i add into my if statement that i have posted above?

Thanks.

Picco

www.crmpicco.co.uk
www.crmpicco.co.uk.tt
www.milklemonadechocolate.uk.tt
www.griswolds.uk.tt
www.piccosmini.co.uk.tt
www.morton.uk.tt

gcianfanelli · May 3rd, 2005, 04:58 AM

Use the Trim function to remove any excess white space to the left and right of the string.

I also use the Len function, rather than compare with a literal string (your zero-length string, that is), although either way try to use the built-in constant vbNullString rather than ""

so, all of the following should work:

Code:

If Len(Trim$(Range(sRange).Text)) <> 0 Then
   Range(sRange).Interior.ColorIndex = iColour
End If

If Trim$(Range(sRange).Text) <> "" Then
   Range(sRange).Interior.ColorIndex = iColour
End If

If Trim$(Range(sRange).Text) <> vbNullString Then
   Range(sRange).Interior.ColorIndex = iColour
End If

crmpicco · May 3rd, 2005, 05:15 AM

Thanks that one worked a treat:

Code:

If Trim$(Range(sRange).Text) <> "" Then
   Range(sRange).Interior.ColorIndex = iColour
End If

www.crmpicco.co.uk
www.crmpicco.co.uk.tt
www.milklemonadechocolate.uk.tt
www.griswolds.uk.tt
www.piccosmini.co.uk.tt
www.morton.uk.tt

crmpicco · May 3rd, 2005, 05:22 AM

why is the $ needed?

www.crmpicco.co.uk
www.crmpicco.co.uk.tt
www.milklemonadechocolate.uk.tt
www.griswolds.uk.tt
www.piccosmini.co.uk.tt
www.morton.uk.tt

gcianfanelli · May 3rd, 2005, 05:39 AM

It's not needed: "Trim" on its own will do just fine.

just remembered that Trim$ is technically more optimal than Trim: Trim on its own will accept a variant as a parameter, whereas Trim$ only accepts a string

in fact, to make your code run more optimally you probably need to code something like

Code:

If LenB(Trim$(Range(sRange).Text)) <> 0 Then
   Range(sRange).Interior.ColorIndex = iColour
End If

That's because LenB is more optimal than Len.

of course i doubt this'll make much of an impact on your application, unless you're doing several thousand iterations, but who knows..

BrianWren · May 3rd, 2005, 01:49 PM

Many of the string functions have a version with the $ and one without. The one without receives a Variant (and therefore 1) can handle Nulls, and 2) takes time to convert the string you send it to Variant(String)).

I believe there is also a LenB$().

What are all those URLs?