0

I have a similar problem as the discussed as in this case.

Actually mine is even more simple.

My VBA code checks the last used row in a column which then defines a range for sorting.

Dim LastRow As Long
Dim KeyR As Range

LastRow = ActiveSheet.Range("E" & Rows.Count).End(xlUp).Row
Set KeyR = ActiveWorkbook.Worksheets("Summary_Cost").Range("E10:AS" & LastRow)


KeyR.Select
ActiveWorkbook.Worksheets("Summary_Cost").Sort.SortFields.Clear

ActiveWorkbook.Worksheets("Summary_Cost").Sort.SortFields.Add _
Key:=Range("E10:AS" & LastRow), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal


With ActiveWorkbook.Worksheets("Summary_Cost").Sort
    .SetRange = Range("E10:AS" & LastRow)
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
   .Apply
End With

For some reason, my Excel (Office 365) gives me an error message #438 that I don't understand. And always in the .SetRange expression. It seems that my Excel does no allow any formula here - while I find many opposite examples like the discussed here.

Any hint is highly appreciated. Thank you! LE

Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235
LEP77
  • 1

1 Answers1

1
With ActiveWorkbook.Worksheets("Summary_Cost").Sort
    .SetRange = Range("E10:AS" & LastRow)

The Sort object being held by the With block here, belongs to the ActiveWorkbook.Worksheets("Summary_Cost") worksheet object, and expects to be given a Range on that same sheet.

    .SetRange = Range("E10:AS" & LastRow)

See this answer for more details about how scoping and identifier resolution works in VBA, but long story short (and assuming the code is written in a standard procedural module), the Range member call here is being made against an implicit global object aptly named [_Global], that dynamically (at run-time) gets whatever the current ActiveSheet is, and invokes its Range member.

If that very same exact identical code is written in a worksheet module, then the Range member call now resolves to Me.Range, so Sheet1.Range if it's in the Sheet1 module, or Sheet2.Range if it's in the Sheet2 module.

While implicit code appears "beginner-friendly" on the surface (see, so little code, so simple!), it quickly gets confusing and bug-prone: there's too much implicit code going on for the reader/maintainer to correctly infer from the code alone what's going on.

The solution is to always be explicit about which Worksheet objects you're working with - and we can do that by declaring (and using) a Worksheet local variable:

Dim summarySheet As Worksheet
Set summarySheet = ActiveWorkbook.Worksheets("Summary_Cost")

With summarySheet.Sort
    .SetRange = summarySheet.Range("E10:AS" & lastRow)

And now the code no longer implicitly refers to whatever the active sheet is.

Rubberduck has an inspection that can warn you about this. (<~ my website and pet open-source project :)

...now, that's not going to compile as-is, because SetRange is a method, not a property - what you have at the right-hand side of the assignment operator needs to become an argument to a member call, like this:

With summarySheet.Sort
    .SetRange summarySheet.Range("E10:AS" & lastRow)

Error 438 occurs when a late-bound member call is made against an object, and the object doesn't have that member; because of the = operator, VBA was looking for a Property Let member named SetRange (as per the syntax used to invoke the member), and couldn't find it. Without the = operator, it's looking for a Sub procedure by that name.

The problem moves from run-time to compile-time (that's better!) when you work off a Worksheet declared local variable, because ActiveWorkbook.Worksheets("Summary_Cost") returns an Object, not a Worksheet - so the resolution of any chained member calls are deferred to run-time (and that's implicit late-binding).

With a local Worksheet variable, the compiler is able to pick up that the = operator makes the code try to do something it can't do.

Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235