0

I'm using excel 2007 - pt-PT and I'm trying to trim all cells (compactar) in my worksheet using a vba script. The following script returns this error - "Object does not support this property or method" and I don't understand why.

Sub TrimAllCells()
    Dim cell As Range
    For Each cell In ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants)
    cell = WorksheetFunction.Compactar(cell)
    Next cell
End Sub

What am I doing wrong?

BigBen
  • 46,229
  • 7
  • 24
  • 40
Eunito
  • 416
  • 5
  • 22

1 Answers1

3

While Excel functions are localized, the corresponding methods in the object model library are not - having different members per localization would mean a different build version for every possible localization, and then support and debugging would be unfathomably chaotic. Not to mention, some locales use non-ANSI characters, which the VBE could have a hard time displaying correctly.

Application.WorksheetFunction is early-bound - that means the compiler knows what it's looking at, so when you type the dot in WorksheetFunction., you get a list of all members of that object; Compactar (or any other localized worksheet function name) isn't in that list - hence the "object does not support this property or method" compile error.

Rule of thumb, when you're coding against an early-bound object, the list of members you get when you type the . derefencing operator is the list of everything you can legally do with that object. There are cases where sometimes members are hidden (you can make them visible in the Object Browser; F2 and right-click anywhere, select "show hidden members"), and cases where late-bound members are tacked-on at run-time on an extensible COM interface, but generally speaking, if it's not in the list of members, it's not a legal member to call.

If you used the late-bound equivalent, your code would compile - and blow up at run-time with error 438 instead (the late-bound / run-time equivalent of the compile-time error you're getting):

cell = Application.Compactar(cell) '<~ error 438 at run-time

You're looking for the Trim worksheet function... but note that VBA has its own Trim function, so you don't even need to get WorksheetFunction involved for this.

Note that you are implicitly assigning to the default member of the Range class here. More explicit code would be like this:

cell.Value = Trim(cell.Value) ' fully-qualified: VBA.Strings.Trim

But, this assumes there is no worksheet error - if any cell contains e.g. #VALUE! or #REF!, then this would raise a type mismatch error. Always validate the type of cell values before treating them as a String, as a Date, or any Double or otherwise numeric value - the IsError function can be used to avoid working with a Variant/Error value and accidentally coercing it into a String:

If Not IsError(cell.Value) Then cell.Value = Trim(cell.Value)
Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235
  • I get type mismatch now – Eunito Oct 03 '19 at 16:08
  • @Eunito good, progress! Could it be that `cell.Value` contains a `Variant/Error` value? See edit. – Mathieu Guindon Oct 03 '19 at 16:08
  • 1
    Just a note - `WorksheetFunction.Trim` and `Trim` don't have exactly the same behavior. The `WorksheetFunction` will remove extra whitespace within a string, i.e. between words, while `Trim` only removes leading/trailing spaces on the string as a whole. Not sure which OP wants. – BigBen Oct 03 '19 at 16:59
  • @BigBen thanks, that's good to know - that makes `WorksheetFunction.Trim` wildly non-standard =) – Mathieu Guindon Oct 03 '19 at 17:09
  • Agreed. It is however quite useful parsing semi-unstructured text files with lots of whitespace inbetween words (don't ask, very crappy data). – BigBen Oct 03 '19 at 17:10
  • I'll give it a try but I also believe I need to do a Clean first... there are many \n in there... could that also cause the error? – Eunito Oct 03 '19 at 18:58
  • No, a line break shouldn't cause any errors. As far as string-handling is concerned, it's just another character. – Mathieu Guindon Oct 03 '19 at 19:07