Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 09-19-2013, 04:58 AM
mikec mikec is offline VBA sort table code Windows XP VBA sort table code Office 2010 32bit
Advanced Beginner
VBA sort table code
 
Join Date: Nov 2012
Posts: 30
mikec is on a distinguished road
Default VBA sort table code


I have attached 2 versions of VBA code intended to sort a table. I can't get the "Key" parameter to run without providing a specific cell address. I have attached a Word document holding the coding and some further explanation
Attached Files
File Type: doc Problems with sort code.doc (24.5 KB, 13 views)
Reply With Quote
  #2  
Old 09-19-2013, 05:16 AM
BobBridges's Avatar
BobBridges BobBridges is offline VBA sort table code Windows 7 64bit VBA sort table code Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 695
BobBridges has a spectacular aura aboutBobBridges has a spectacular aura about
Default

Mikec, can you attach a copy of the worksheet with both versions of the program (in separate modules if necessary)? I don't use ListObjects, but I suspect what's going on here, based on the error you report, is that Range.ListObject.Sort.SortFields.Add Key:= requires a Range object, and Range.ListObject.ListColumns(1) is some other type. But I want to step into the program to inspect the objects to be sure.

Or you can, of course. But I'm interested in learning it myself, anyway.
Reply With Quote
  #3  
Old 09-19-2013, 06:24 AM
mikec mikec is offline VBA sort table code Windows XP VBA sort table code Office 2010 32bit
Advanced Beginner
VBA sort table code
 
Join Date: Nov 2012
Posts: 30
mikec is on a distinguished road
Default VBA sort

I have attached POY REV 14b.xlsm. The code under discussion is in the Diaries form. It is fired by the Save command on the first page of the form. A call to Private Sub SortCompetition from the save command code prompts the sort of the table. This sub is in General section of the form code. Immediately below are subs I have renamed as SortCompetition1 and Sort Competitions. The former has the Key definition that does not run. The latter is the original code. As general background I am going through this project with the intention of removing direct cell references and range selection wherever I can. Why? Well it's fun!
Thank you for your interest - this one is driving me crazy
Attached Files
File Type: xlsm POY REV 14b.xlsm (311.7 KB, 15 views)
Reply With Quote
  #4  
Old 09-19-2013, 07:25 AM
BobBridges's Avatar
BobBridges BobBridges is offline VBA sort table code Windows 7 64bit VBA sort table code Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 695
BobBridges has a spectacular aura aboutBobBridges has a spectacular aura about
Default

This is 'way more sophisticated than anything I've tried to do in Excel before. In Access, sure, but I've never used real forms in Excel, never more than the occasional button. I had to do some exploring to see how this thing hangs together. I'm impressed.

But I think I see what the problem is. You can, too: Put a break on the statement that's hanging up:
Code:
.Sort.SortFields.Add Key:=.ListColumns(1), _
         SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers
...and then run the program. When it pauses, Add Watch for the parent object, Range(pcEditTable); drill down through .ListObject to .ListColumns, and you'll see that each item in the collection is of type ListColumn.

But Sortfields.Add Key:= wants not a ListColumn object but a Range; that's why the other form of the command works. I don't know anything about ListObjects, but I opened up one of the ListColumn objects and I see that Range is one of its properties. I infer that ListColumn is has many non-range properties so it can't be a range itself, but offers you the range as one of the properties.

I changed your statement, therefore, to
Code:
.Sort.SortFields.Add Key:=.ListColumns(1).Range, _
         SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers
...and VBA accepted it. What I mean is that it executed the statement without complaining about an error. You will know better than I whether that actually fixes the problem.
Reply With Quote
  #5  
Old 09-19-2013, 08:58 AM
mikec mikec is offline VBA sort table code Windows XP VBA sort table code Office 2010 32bit
Advanced Beginner
VBA sort table code
 
Join Date: Nov 2012
Posts: 30
mikec is on a distinguished road
Default

Yes it works. However, there is always a but. I've tried the watch feature but can't make much sense out of the mass of info it presents. How did you investigate the listobject info?
Like you I am more used to database stuff - I use Foxpro but started my interest in this area on dBase 3 in the good old DOS days. While I have managed some significant achievements with Fox I can't really say that I have fully come to terms with OOP and completely different sintax of VBA leaves me gasping at times. I used Excel for this because the users in my golf club are more comfortable with spreadsheets. I have been pleasantly surprised by what VBA can achieve.
Any help you can give me with reading up on the system (other than MS incomprehenible Help system) would be appreciated
Reply With Quote
  #6  
Old 09-19-2013, 02:34 PM
BobBridges's Avatar
BobBridges BobBridges is offline VBA sort table code Windows 7 64bit VBA sort table code Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 695
BobBridges has a spectacular aura aboutBobBridges has a spectacular aura about
Default

I'm not sure how you got to be so advanced without being familiar with OO concepts in general, and with the VBA editor's Watch window in particular. But then, I'm sort of self-taught (in the OO world anyway) and it's left some curious holes in my education; I still have a lot to figure out about recordsets, for example. So I guess I shouldn't be surprised if you're in the same boat about object-watching.

Let's look at a simpler object, one that has only 20 properties. I just created a scratch program:
Code:
Sub Main()
  Set so = ThisWorkbook.Worksheets(1)
  Set co = so.Range("A1")
  Set fo = co.Font
  Stop
  End Sub
You can create that program in any workbook, even an empty one; it doesn't depend on any actual content. If you'll run it until it stops, and then set a watch on the object I named "fo" (for "font object"), you'll see fo show up in the Watch window with a boxed "+" next to it.

In my Watch window I see four columns:

Expression: In this case this is just the name of the object. But you can "watch" something more complicated, such as an arithmetic expression or a complex object with properties, maybe even methods (I've never tried that).

Value: No value is listed for fo; fo is an object and has no value of its own. Or you can say that fo has many different values, if you care to look at it another way: That is, it has lots of properties and most of them have values. But none of them are listed here.

Some objects have a "default" property, and the Watch window would display its value here. A cell object, for example, has many properties (Parent, Borders, Font, Column and Row etc); but its default property is Value, so Watch would display that here. But apparently fo has no default property.

Type: This tells you what kind of variable it is. I'm looking at a number of items in my Watch window just now, and they display things like this:

Long: A variable of the Long data type
Variant/Boolean: This is a variable that can be any data type (that's the "Variant" part); just now it's Boolean.
Range: I guess you know what a Range is.

For fo it says "Variant/Object/Font", meaning fo is a variable that could have other types of data but just now it's an object pointer, and the object it points to is of type Font.

Context: This tells me where the variable resides and has its meaning. In this simple program, all the items in my Watch window are labeled "Scratch.Main" ("Scratch" is the name of the module put this program in). If I were watching some variables in my main program, and in a form, and in a subroutine somewhere, the Context would be different for each of them.

Now let's look at fo's properties. Click on the '+' sign and it'll expand to show you each of them. Most of them are scalar variables (ie having just one value apiece), but two of them are further objects. "Application" is a pointer back up to the main Excel app itself, with all its properties; if you were to drill down there you could find the Workbooks object, and then the workbook this code is running in, and then the worksheet, and then cell A1 of that worksheet, and then that cell's font, and you'd have come full circle and found yourself where you started (only several layers deeper). And Parent is a pointer to the parent of fo, which should be the cell object, co....yep, in the Type column for Parent it says "Range", so it's talking about the cell this font object applies to.

The rest of the values have to do with the definition of the font itself: typeface, size, bold, strikethrough, italic and so on.

Let's look at a more complex object; set up a watch for co, the cell object that is fo's parent. Here you'll see lots and lots of properties—I think I count 85—many of which mean little or nothing to me. Oh, I recognize some: Parent, for example, points to the parent worksheet. Worksheet points to the very same thing. IndentLevel has to do with the cell's alignment. ColumnWidth is obvious, so is Value, so will others be to you too. But what in the world is Precedents? Or PrefixCharacter? Or CountLarge? If I ever need to know, no doubt I'll find out eventually. Oh, and notice Font; that's the property you already looked at in fo, and if you expand Font you'll see it's exactly the same. It has to be the same; it's the same object, just pointed two in two different ways.

---

Now, when I looked at your ListColumn thingy, I didn't know what I would find. But I could already tell (from the Type in the Watch window) that a ListColumn isn't a range; and yet it seemed from the syntax of the command, and from your expectations, and from the term "ListColumn" itself, that it's about a column in Excel. If so, I thought there might be a range in it, as one of its properties. I didn't know what it might be named, or even how far down I might have to look in the object "tree"; I was prepared to give up after a short look and go back to the documentation. But it happened to be named simply "Range" and to be in the first level, so it was easy to spot. That's pretty much all I did: I started with a suspicion (that the error message meant that the ListColumn object is not a Range object) and stumbled across the answer more or less right away.

---

If you're used to doing this in Access, then I'm pretty sure that what leaves you gasping isn't the difference in VBA itself—they're the same language—but the differences between the Access and Excel objects. Since they're two different applications, I guess it makes sense that they should be different; Access doesn't think of terms of cells, columns and formulae, but of rows, SQL queries and recordsets. And wait 'til you first try automating something in Word; it's totally different. And Outlook is even worse; I really don't get Outlook, though I've made a start. But take heart; VBA hasn't changed, it's just the objects with their properties and methods.

---

I kept hearing how OOP is different, and when I first started working on a macro in VBA (back just before Y2K, that was) I couldn't figure out what all the fuss was about. True, I had repeated visits from an error message "...does not support this property or method", and that one drove me crazy. It was only years later that I began to understand why a "method" is not a function, and why, therefore, I should not expect it to be generally available. If that's one of the things bothering you, I'd be happy to spare you the pain I went through, by explaining it better than it was explained to me. If I can, that is.
Reply With Quote
  #7  
Old 09-20-2013, 12:55 AM
mikec mikec is offline VBA sort table code Windows XP VBA sort table code Office 2010 32bit
Advanced Beginner
VBA sort table code
 
Join Date: Nov 2012
Posts: 30
mikec is on a distinguished road
Default

It's going to take some time for me to work through your contribution and I look forward to the experience. I'm grateful for your offer to provide some further tuition and I will probably take you up on that once I have paused to consider what it is about this particular form of OOP that I find so difficult to get my head round. I do know that I find the move from VFP to MS Office is not straight forward. VFP also had Properties and Methods but in VFP methods were realy more akin to functions which were written by the programmer. I'll be back later once I have taken stock. Many thanks
Reply With Quote
  #8  
Old 09-20-2013, 09:59 AM
BobBridges's Avatar
BobBridges BobBridges is offline VBA sort table code Windows 7 64bit VBA sort table code Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 695
BobBridges has a spectacular aura aboutBobBridges has a spectacular aura about
Default

Actually, that's exactly how I'd describe a method: a function written by a programmer (or programming team), but for that object and for no other. That's where my assumptions used to lead me astray. For example: I had learned of a "method" (which I thought of as a VBA function) that I could use to determine whether a certain item exists in a Dictionary object:
Code:
Set od = CreateObject("Scripting.Dictionary")
' ...populate the Dictionary, then
If Not od.Exists("History") Then Abend "Missing History key."
Very good; that's useful. So later on I try to use this Exists "function" in other places in my program:
Code:
If Not ThisWorkbook.Worksheets.Exists("MySheet") Then...
...and I get an error: This object does not support this property or method.

For years that drove me crazy. Why could I use the Exists function one time and not another?! It wasn't until later—not until I finally wrote my own object, in fact—that I saw, very simply, that Exists was not a function of the VBA language, nor even a function of the Excel application, but a function of the Dictionary object. The folks who created the Dictionary object wrote an Exists method into it; but the folks who wrote the Worksheets collection in Excel did not—and a method exists only in the object it's written for.

Likewise, when you're adding an item to most Collections, the first argument for the Add method is the item you're adding and the second item is the optional key. But in a Dictionary's Add method, the first argument is the required key and the second is the contents. Different objects, different rules. Different subroutines, in fact, despite the fact that they have the same name.

It's my understanding that this is common to object-oriented programming, not specific to one language or environment or another. If this VFP thingy (is that Visual FoxPro?) had a great deal of consistency in the methods between various objects, it's probably because the architects decided to maintain an overall design philosophy rather than let each item be constructed from scratch.

I don't usually let it bother me, now. When I decided I wanted an Exists method for Collections, I went ahead and wrote it as a function and use it all the time, now.
Reply With Quote
  #9  
Old 10-01-2013, 04:37 PM
macropod's Avatar
macropod macropod is offline VBA sort table code Windows 7 32bit VBA sort table code Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,927
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Quote:
Originally Posted by mikec View Post
I have attached POY REV 14b.xlsm. The code under discussion is in the Diaries form. It is fired by the Save command on the first page of the form. A call to Private Sub SortCompetition from the save command code prompts the sort of the table. This sub is in General section of the form code. Immediately below are subs I have renamed as SortCompetition1 and Sort Competitions. The former has the Key definition that does not run. The latter is the original code. As general background I am going through this project with the intention of removing direct cell references and range selection wherever I can. Why? Well it's fun!
Thank you for your interest - this one is driving me crazy
Hi Mike,

The answer is quite simple:
.ListColumns(1) returns the string 'Date', not its cell range.
What you need is something like:
.ListColumns(1).Range.Cells(1, 1)
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
Reply

Tags
range, sort, vba

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Outlook Combobox Sort Code lms Outlook 1 09-18-2013 09:56 AM
VBA sort table code How to Sort Table Cells from Left to Right tamaracklodge Word Tables 4 03-04-2013 04:00 AM
Sort table using macro saslotteroy Word VBA 2 09-15-2011 02:41 PM
VBA sort table code How to sort table having three columns? Bahir Barak Word 2 01-20-2011 01:52 PM
Can I sort a Pivot table by subtotals? oshkosh Excel 1 12-07-2010 06:20 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 09:38 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2023, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2023 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft