simple sort does not appear to end

User avatar
stuck
Panoramic Lounger
Posts: 8127
Joined: 25 Jan 2010, 09:09
Location: retirement

simple sort does not appear to end

Post by stuck »

I using Excel 365 version 2001 click-to-run) and I'm trying to do a really simple sort:

Code: Select all

    'select the range
    Range("OFFSET($A$3,0,0,COUNT($A:$A),13)").Select
    
    'sort
    Selection.Sort Key1:=Range("A3"), Order1:=xlAscending, Header:=xlYes
        
    'reset selection
    Range("A1").Select

If I run it, it works except the focus does not end up in A1, the entire selection remains selected. If I use F8 to step through it, I can't reach the last line, i.e. when I press F8 to execute the sort, the next line does not yellow highlight. Instead the cursor in the VBE is left sitting at the beginning of the sort line.

I assumed that meant it was crashing for some reason but if I put an 'on error goto crash' at the start of the code and a 'crash:' line at the end the code does not go to 'crash:'

Meanwhile back on the worksheet, the sort happens and I can manually select cell A1, or indeed do anything else, anywhere in the workbook.

This must be a :stupidme: incident, please can someone show me where I'm going wrong?

Thanks,

Ken

User avatar
stuck
Panoramic Lounger
Posts: 8127
Joined: 25 Jan 2010, 09:09
Location: retirement

Re: simple sort does not appear to end

Post by stuck »

OK, so I've posted I have something that does what I want:

Code: Select all

    Range("A1").Select

    Range("OFFSET($A$3,0,0,COUNT($A:$A),13)").Sort _
        Key1:=Range("A3"), Order1:=xlAscending, Header:=xlYes

I'd still value any other ideas.

Ken

User avatar
p45cal
2StarLounger
Posts: 142
Joined: 11 Jun 2012, 20:37

Re: simple sort does not appear to end

Post by p45cal »

Code: Select all

[OFFSET($A$3,0,0,COUNT($A:$A),13)].Sort Key1:=Range("A3"), Order1:=xlAscending, Header:=xlYes
Whatever was selected before the above is executed will remain so afterwards.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: simple sort does not appear to end

Post by HansV »

In most situations, it is not necessary to select a range to manipulate it. You can replace lines such as

Code: Select all

Range(...).Select
Selection.DoSomething
with

Code: Select all

Range(...).DoSomething
This has the advantage of being more efficient and not changing whatever was selected (with a few exceptions).

That being said, your code runs as expected in Excel 2019, version 2001: A1 is selected after running it.
Best wishes,
Hans

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: simple sort does not appear to end

Post by Doc.AElstein »

Forgive me for showing my usual stupidity in missing the obvious…_
_.... but what are you expecting to get from this, Ken
Range("OFFSET($A$3,0,0,COUNT($A:$A),13)")

What I mean is , I thought that if you are using the single argument Range(" ") referencing, then inside that " " you need some sort of string address reference like
A1:A3
=B2:C45
=Sheet1!A1:A100

_.. etc…
Or
the name of a named range

It’s a new one on me that I can put a formula inside the " " ( Is it a new feature in Excel 365 ? )

_... or is maybe OFFSET($A$3,0,0,COUNT($A:$A),13) a named range in your worksheet? – My Excel won’t let me use a name like that…
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: simple sort does not appear to end

Post by HansV »

That was a surprise to me too - it works in the non-Microsoft 365 version of Excel 2019.
Best wishes,
Hans

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Range Referrencing and/ with Excel Offset

Post by Doc.AElstein »

I have never had anything to do with the Excel spreadsheet Offset
The documentation for the Excel spreadsheet Offset does say that it returns a range reference, but I never realised that the thing inside the " " in Range(" ") would be evaluated similar to like the Evaluate(" ")

These 4 all do the same ( in a few of my computers, systems, Excels, that i just tried...) , which is to select the range A3:B4
Evaluate("OFFSET($A$3,0,0,2,2)").Select
Range("OFFSET($A$3,0,0,2,2)").Select
Evaluate("=OFFSET($A$3,0,0,2,2)").Select
Range("=OFFSET($A$3,0,0,2,2)").Select


( So this thing is working for me in Excels 2003 – 2013 )

I have seen some similarities in the way both Evaluate(" ") and Range(" ") react to a string address reference in the " ", but this "Offset( )" or "=Offset( )" in the Range( ) is a new one on me.

I can’t quite figure out why VBA would decide to do an evaluate in the Range(" ") when it sees Offset
Or… Having a quick look at this… these sort of things also work
Range("=Indirect(""A1"")").Select
Range("=INDEX(A1:B2,1,1)").Select
Range("=CHOOSE(1,A1,B1)").Select
Range("=IF(1=1,A1)").Select

Couple this with the fact that Evaluate(" “) does not quite work in the simple way of doing exactly what is in it as if it was in a cell, ( examples like Evaluate("A1,C3") returns us a two area range object, just like Range("A1,C3") does, or we can fiddle with it the dependence trees of a procedure run to give us a UDF that can change values in cells other than the cell in which the UDF is used ) … then this opens up some interesting ideas.
Or…
Its possible that Range(" ") and Evaluate(" “) are almost the same, and that like Range(" ") does something like this

Code: Select all

Sub Testit() '
 MethodOfRange("A1:B2").Select
 MethodOfRange("A1:Bsafh").Select
End Sub
Function MethodOfRange(ByVal strEval As String) As Range
Dim vEval As Variant
 On Error GoTo MethodOfRangeFail
 Let vEval = Evaluate(strEval).Address
 On Error GoTo 0
Dim RngEval As Range
 Set RngEval = Evaluate(strEval)
 Set MethodOfRange = RngEval
Exit Function
MethodOfRangeFail:
 MsgBox prompt:="Range Method Function Property Failed to be one,              " & vbCr & vbLf & "(and by the way, you have raised my exception state)"
End
End Function
That possibly helps partially explain why Evaluate wont work on a closed workbook reference ( although I have figured out in the meantime almost how to do that now also ) …_
_.. I will need to look into this a bit further. I will post back here in a year or five, when I think I have figured it all out




Interesting.. I thought range referencing was part of that minuscule portion of the vast area of VBA that I was passably clued up on. Clearly I missed something…
Ref https://www.myonlinetraininghub.com/exc ... ment-84892
_.____________________________________

@Ken
_1 As I now figured out what I had missed – I have tried various versions of what you are doing in my various Computers and Excels, and all is working correctly as we expect. Can you give us a sample workbook that demos your problem. Maybe some strange data is causing the problem
_2 Where did you come across the idea to use the Excel spreadsheet Offset inside the VBA Range(" ") ? ( This and related stuff I am starting thinking about could be very interesting ) .
_.___________________
Edit P.S.
HansV wrote:
18 Feb 2021, 18:55
…in the non-Microsoft 365 version of Excel 2019.
That’s something else I missed … I didn’t know there was a non Microsoft version of Excel :smile: :laugh:
Last edited by Doc.AElstein on 01 Mar 2021, 19:39, edited 1 time in total.
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also

LisaGreen
5StarLounger
Posts: 964
Joined: 08 Nov 2012, 17:54

Re: simple sort does not appear to end

Post by LisaGreen »

Hi,

I believe that it's possible to use Range(NamedRange). And I'm sure it's possible to put formulas into named ranges.

Could this range("Formula") thing be a variation on that? It would be interesting to see if it goes back some excel versions and if so how far.

Lisa

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: simple sort does not appear to end

Post by Doc.AElstein »

Hi Lisa
There is some almost mystic link between named ranges and things macro and Evaluate. I feel it in my bones, but I have not quite got it all clear in my mind yet.
All the stuff we seem to have discovered here certainly is working in all my Excels from 2003 – 2013

( One way to kick off old Excel 4 Macros is to put the command ( formula ) in the RefersTo bit of a named range, or something along those lines, I believe).

I will be thinking about all this for a while, and probably post back here again in a year or 5
:)

Alan
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also

LisaGreen
5StarLounger
Posts: 964
Joined: 08 Nov 2012, 17:54

Re: simple sort does not appear to end

Post by LisaGreen »

Wow!!! Do you have a thinking cap Alan?

I find it very interesting that this was in there so long.. What other goodies remain to be discovered I wonder! I'm sure there's stuff there that the developers included because it made their job easier then just left the code there and never said anything!

Lisa

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Range("yyy") like is Evaluate("yyy")

Post by Doc.AElstein »

I have a very nice Xmas cap, which has been helping me to think this winter, in between snow shovelling
https://www.eileenslounge.com/viewtopic ... 65#p280665
https://i.imgur.com/HoQEVoN.jpg
But as the beautiful snow is all melting, I may have to postpone my in door thinking on this one until next year.
( I did buy a large wod of old Excel 4 and Excel 4 Macro books very cheaply on ebay this Xmas, which will be part of my bedtime reading this year… I think there is some interesting potential there – You can use them to do a pseudo Evaluate(“ “) on a closed workbook… another little discovery waiting to be exposed… )
_._______________________________________________________________________________
I think there won’t be many great uses of this little “discovery”, since anything you can do with Range("yyy").xxxx you can probably do with Evaluate("yyy").xxxx
Range("yyy").xx will tell you if what you evaluated didn’t come out to be a range object, ( by erroring). So far that’s all I see as the difference.
But this extra bit of knowledge about Range("yyy") might help understand some other stuff.
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also