Microsoft Excel - Is it me? - Asked By chaz d'chaz on 14-Jun-12 12:04 PM

with xlWS
.
.
.
r = Range(Cells(1, 1), Cells(1, .UsedRange.Columns.Count - 1)).address
.
.
.
end with

works -- for weeks -- until it throws an "Application-defined or object-defined error" error.  Thereafer, the following seems to work (please not the "dot-Range/dot-Cells" syntax):

with xlWS
.
.
.
r = .Range(.Cells(1, 1), .Cells(1, .UsedRange.Columns.Count - 1)).address
.
.
.
end with

?Por que?  Because it's probably only a matter of time before this syntax starts throwing the error, and I have to do it the other way again.  (Note that when the macro editor creates the code, it's "Range('blah...')" NOT "dot-Range('blah...')"

Note, this actually takes place in a sort routine. I omitted that to keep the example simple.

[edit] I wouldn't say I solved the problem, but it does seem to be related to having an existing instance of Excel open when the code is run.  When that's not the case, all is well.  When it is, all is not. I suspect this has something to do with the way I instantiate it (from Access) -- something I admit I've never been completely clear on. Hence I turn to the www for clues. Here's how it appears, minus some bells and whistles:

Dim xlApp As Object, xlWB As Object, xlWS As Object, IStartedXL As Boolean

    

    '''if no excel app is running, this throws an error. just move on and open it

    On Error Resume Next

    Set xlApp = GetObject(, "excel.application")

   

On Error GoTo err_

   

    If xlApp Is Nothing Then

   

    Set xlApp = CreateObject("excel.application")

    IStartedXL = True

   

    End If

   

   

    Set xlWB = xlApp.Workbooks.Open(theFile_, , False, , True)

    Set xlWS = xlWB.Worksheets(1)

   

    xlApp.DisplayAlerts = False

   

With xlWS

   

    .Cells(.UsedRange.Rows.Count, .UsedRange.Columns.Count).Select

 

    .sort.SortFields.Clear

    .sort.SortFields.Add Key:=Range(.Cells(1, 1), .Cells(1, .UsedRange.Columns.Count - 1)), _

    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

   

    With .sort

    .SetRange Range(xlWS.Cells(1, 1), xlWS.Cells(xlWS.UsedRange.Rows.Count, xlWS.UsedRange.Columns.Count))

    .Header = xlYes

    .MatchCase = False

    .Orientation = xlLeftToRight

    .SortMethod = xlPinYin

    .Apply

    End With

   

   

    End With

 

End With

 


wally eye replied to chaz d'chaz on 14-Jun-12 06:52 PM
Without the "." in there, Range will point to the active worksheet.  This isn't the best behavior if you aren't launching it from a worksheet event, sometimes even then it can be suspect.  It might be that your worksheet is saved with a different active worksheet, on occasion.  Using the "." consistently within your With statement will get around this.
Pichart Y. replied to chaz d'chaz on 14-Jun-12 09:08 PM
Hi Chaz,

Attach your sample file here will be much more easy to understand your work, please.

pichart Y.

chaz d'chaz replied to wally eye on 15-Jun-12 08:14 AM
I sense that there is some confusion about what the active worksheet is, when there's an existing instance of excel -- and so I suppose I should .activate the sheet I'm hoping to sort.  I admit I might need to tighten that sort of thing up.  But I guess I figured it was implicit in With... but on reflection I see that's not so.

Other fires to fight at the moment...will get back to this one to tighten it up.
chaz d'chaz replied to wally eye on 15-Jun-12 08:14 AM
meant to "flag as helpful".
chaz d'chaz replied to wally eye on 19-Jun-12 02:57 PM
You know what?  I inadvertently selected a cell before beginning the sort routine.  So when I tried to disambiguate the references, it threw errors trying to sort that cell.  Some weird errors, too.  Before disambiguating (using Range instead of .Range)  I got "the remote machine is not available," something I haven't seen before.

Seems to be squared-up for now.
wally eye replied to chaz d'chaz on 19-Jun-12 05:15 PM
You can use Range, Cell, ActiveSheet and the like, sometimes it is even necessary (particularly when writing generic functions).  Typically in a dedicated function like this, you would want to specify what the range belongs to.  Sometimes I even back it up a level and specify the workbook (ThisWorkbook, ActiveWorkbook, or set wbkCurr = application.workbooks.open("c:\...").  It is annoying to work on Sheet1 in the wrong workbook...

Yes, the sort won't want to sort on a column that isn't in the range being sorted, funny that way.