VBA Trim - has no effect in workbook- looking for ideas

[KR]

I'm writing this from a different PC (the target workbook is only accessible
from certain PCs) so I will gladly post the code as a followup, but I
figured I'd start with a request for some basic information/ideas. I've done
a fair bit of Excel VBA coding, and this is such a simple thing to not work
properly....

One end user pastes a text file into a worksheet in our shared workbook.
They just changed the format of that report, so now the contents of some
columns contain lagging spaces (lots of them). So for example, a cell that
used to contain "34A71" might now contain "34A71 "

The VBA code starts by loading cells from that worksheet into an array (so
the array values now include those extra spaces).

Then in one part of my code, I pull the values from one "row" at a time from
the array, and compare them against another array. Once I finished pulling
out my hair trying to figure out why the code wasn't working (thats how I
learned about the extra spaces) I added a trim function to the code that
pulls the values. I put a code breakpoint at my trim statement (right before
it actually), and walked through the lines... and lo and behold, the trim
doesn't seem to be doing anything- my variable value still contains
boatloads of spaces. I also tried Application.Trim and
Application.WorksheetFunction.Trim.

Basically, my code is

MyNewValue = Trim(MyArray(A,B,C))

where MyArray is a 3-dimensional array, and the A,B,C represent the location
in that array that has the value I'm pulling.
When I mouseover over MyNewValue after processing this line, it still shows
a string with many spaces regardless of which trim function I use.

I checked Tools/References to see if anything was listed as missing, but it
all looks ok.

Is there any other reason why Trim wouldn't work in the code? I'm completely
stumped at this point.

Thanks,
Keith

 

[Charlie]

The Trim function only trims single elements of an array, not all elements of
an array at once. Try something like this:

Dim i As Long
For i = LBound(MyArray) To UBound(MyArray)
MyArray(i) = Trim(MyArray(i))
Next i

 

[ben]

the trim function as far as i know works for spaces with certain asc codes,
those may not actually be spaces, try taking the last part of the string as a
Chr code and see what the return is
msgbox chr(right(MyArray(A,B,C),1))
msgbox chr(" ")
see if the match

 

[Tom Ogilvy]

try

v = replace(v,chr(160),"")

The character 160 is a non-breaking space often found on web pages. Trim
does not "trim" it.

 

[ben]

sorry
msgbox asc(right(MyArray(A,B,C),1))
msgbox asc(" ")

 

[KR]

Thanks to all who replied- this turns out to be exactly the problem. All the
"spaces" are chr(160). I'll update my code accordingly.

Many, many thanks,
Keith

 

[Charlie]

Sorry, obviously I didn't read far enough in your post. Ben and Tom are
probably on the right track.

 

[Charlie]

That's a good one to know.

try

v = replace(v,chr(160),"")

The character 160 is a non-breaking space often found on web pages. Trim
does not "trim" it.

 

[Ben]

Excellent! This just made up for 4 hours of frustrating work.