Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Trim - has no effect in workbook- looking for ideas
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 -- The enclosed questions or comments are entirely mine and don't represent the thoughts, views, or policy of my employer. Any errors or omissions are my own. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Trim - has no effect in workbook- looking for ideas
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 "KR" wrote: 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 -- The enclosed questions or comments are entirely mine and don't represent the thoughts, views, or policy of my employer. Any errors or omissions are my own. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Trim - has no effect in workbook- looking for ideas
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 -- When you lose your mind, you free your life. Ever Notice how we use '' for comments in our posts even if they aren''t expected to go into the code? "KR" wrote: 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 -- The enclosed questions or comments are entirely mine and don't represent the thoughts, views, or policy of my employer. Any errors or omissions are my own. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Trim - has no effect in workbook- looking for ideas
try
v = replace(v,chr(160),"") The character 160 is a non-breaking space often found on web pages. Trim does not "trim" it. -- Regards, Tom Ogilvy "KR" wrote in message ... 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 -- The enclosed questions or comments are entirely mine and don't represent the thoughts, views, or policy of my employer. Any errors or omissions are my own. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Trim - has no effect in workbook- looking for ideas
sorry
msgbox asc(right(MyArray(A,B,C),1)) msgbox asc(" ") -- When you lose your mind, you free your life. Ever Notice how we use '' for comments in our posts even if they aren''t expected to go into the code? "ben" wrote: 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 -- When you lose your mind, you free your life. Ever Notice how we use '' for comments in our posts even if they aren''t expected to go into the code? "KR" wrote: 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 -- The enclosed questions or comments are entirely mine and don't represent the thoughts, views, or policy of my employer. Any errors or omissions are my own. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Trim - has no effect in workbook- looking for ideas
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 "Tom Ogilvy" wrote in message ... try v = replace(v,chr(160),"") The character 160 is a non-breaking space often found on web pages. Trim does not "trim" it. -- Regards, Tom Ogilvy "KR" wrote in message ... 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 -- The enclosed questions or comments are entirely mine and don't represent the thoughts, views, or policy of my employer. Any errors or omissions are my own. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Trim - has no effect in workbook- looking for ideas
Sorry, obviously I didn't read far enough in your post. Ben and Tom are
probably on the right track. "Charlie" wrote: 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 "KR" wrote: 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 -- The enclosed questions or comments are entirely mine and don't represent the thoughts, views, or policy of my employer. Any errors or omissions are my own. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Trim - has no effect in workbook- looking for ideas
That's a good one to know.
"Tom Ogilvy" wrote: try v = replace(v,chr(160),"") The character 160 is a non-breaking space often found on web pages. Trim does not "trim" it. -- Regards, Tom Ogilvy "KR" wrote in message ... 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 -- The enclosed questions or comments are entirely mine and don't represent the thoughts, views, or policy of my employer. Any errors or omissions are my own. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Trim - has no effect in workbook- looking for ideas
Excellent! This just made up for 4 hours of frustrating work.
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excell keeps closing when I try to save a workbook. Any ideas! | Excel Discussion (Misc queries) | |||
change settings in a single workbook that doesn't effect another w | Setting up and Configuration of Excel | |||
What is the reverse effect? | Excel Discussion (Misc queries) | |||
VBA Trim and Application.worksheetfunction.Trim | Excel Programming | |||
Trim like worksheet Trim | Excel Programming |