Odd Application-Defined Error on TRIM() function in Excel?? 2

[kjv1611]

I got around this one - it happened ONE time in a process that is run at least monthly, and has now been in place for at least 4 or 5 years, possibly longer.

I don't run the process, but I built it (with much help from tek-tipsters as well), and tweaked off an on initially. But I've not had to touch it since then.

The odd thing is the problem didn't arise with anything but a reference to the TRIM command. My solution? I commented out the Trim line, stepped past that line, uncommented the line, and let 'er rip. Never had another moments issue.

So, anybody got any clues why it would mess up on just one cell?

The cell value was a street address. I forget the address, but it didn't look that unusual. Something like:
123 Main Other Blvd and that was it - no leading or trailing spaces, no extra characters that I noticed (but was a quick glance).

The code was just looping through the cells, and it did this (same as every other cell, every other run):

ws.Cells(x,2).Formula = Trim(ws.Cells(x,2))

So hopefully I won't have to mess with it again, but thought I'd post here in case anyone can say... "oh yeah, that's the old Trim... bla bla bla error.

Thanks for any thoughts, links, suggestions, whatever,

Stephen

 

[MajP]

Not sure if this is your issue. But if trim did work and now it stopped working, this is somewhat common. You can test this in the immediate window by typing Trim("somestring") and hit return.

You will see a lot of threads where string functions or other common functions stop working "trim, left, right, len, ucase, date, now, etc.....". Often the user "says len function stopped working", but in fact all of these functions are not working they just happen to be using "len". In fact a whole bunch more functions have stopped working.

These functions are all in the VBA library. If you look in the vb editor under references, the very first reference is Visual Basic for Applications. If you actually scroll down the references you will see multiple times VBA is listed. In my case it is listed 6 times. This represents 6 seperate instances of the vba library on my machine.

When a new office suite, visual studio, is updated or loaded sometimes you are no longer pointing to an existing reference. Not exactly sure what happens, but if First step is to check to see if you even have a reference. Sometimes you just have to scroll down to VBA and readd it. Othere times it will not let you because it says it is locked. I normally in that case build a new file and import all the code and other objects.
Here is some discussion

http://www.xtremevbtalk.com/archive/index.php/t-228112.html

You can query tek-tips and finds lots of threads where "x function stopped working

 

[SkipVought]

hi,

Sometimes the compiler will ASSUME the .value property of the RANGE OBJECT and resolve, and sometimes NOT.

ws.Cells(x,2).Formula = Trim(ws.Cells(x,2)[b][highlight].Value[/highlight][/b])

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[kjv1611]

Thank you both for your inputs. Those are both things to try and remember going forward.

On the specification of what to look at, I believe I've been doing that more times than not... in recent history. So if at some point I can find the time, perhaps I'll go back and look at that in some old code instances as well, including this one in particular.

Regarding references, that reminds me of the idea of looking to see if a reference is set, and if not, set it programatically. I forget the hows, but I remember at least a discussion on it.

Sorry took so long to respond, forgot I had posted the question.