macro runs from keyboard, but not from macro button-why?

[BobW]

I created a macro to sort a table by column 2, which is filled with numbers.
I used the usual method of creating a macro - Word's macro "wizard" .

The macro runs OK when executed via the keyboard shortcut I assigned.

But if I create a macrobutton at the top of the column to run the same
macro, it refuses to execute; when I double click on the macrobutton field, I
get "Visual Basic Run Time error 9125 - String Passed Can't Be Parsed".

How can a macro string execute perfectly from keyboard, but not from a
macrobutton?

Here's the 'problem' string that the wizard generated - does if offer any
clues?

Selection.Sort ExcludeHeader:=True, FieldNumber:="Column 2", SortFieldType _
:=wdSortFieldNumeric, SortOrder:=wdSortOrderAscending,
FieldNumber2:="", _
SortFieldType2:=wdSortFieldAlphanumeric,
SortOrder2:=wdSortOrderAscending _
, FieldNumber3:="", SortFieldType3:=wdSortFieldAlphanumeric,
SortOrder3:= _
wdSortOrderAscending, Separator:=wdSortSeparateByCommas,
SortColumn:= _
False, CaseSensitive:=False, LanguageID:=wdEnglishUS,
SubFieldNumber:= _
"Paragraphs", SubFieldNumber2:="Paragraphs", SubFieldNumber3:= _
"Paragraphs"

 

[Jezebel]

I assume your 'usual way' of creating a macro is to record it: this is
actually a terrible way to create non-trivial macros, for precisely the
reason you've discovered the hard way. The problem is that your macro
assumes that you have a table selected when you call the macro; your macro
then sorts the selection. But when you use your macro button, you don't have
a table selected.

You'll need to explain what you're actually trying to do if you want good
suggestions about how to do it.

 

[BobW]

Thanks, Jezebel; It's a price list table laid out as follows:

15 unit price 1000 unit price
Vendor A $6,000.00 $123,000.00
Vendor B $13,000.00 $98,500.00
Vendor C $9,500 $140,000.00

I'd like to be able to sort on price by double clicking on the column B or
Column C headers.

I think you've detected the cause of the problem...I now see that the
keyboard macro fails, too, unless I manually select the table, first. But if
the answer is to add 'select entire table' to the column 2 sort macro and the
column 3 sort macro, Word won't let me select the table when I am recording a
macro...the cursor turns into a little cassette tape icon and won't let me
select anything in the document.

Curses, foiled again....

 

[BobW]

OK, I engaged my IQ this time and figured out how to include "select table"
in the two macros. This works because when I click on the macrobutton, the
cursor is already positioned in the table (on the macro button at the top of
the column) so the macro knows what table it needs to select. Now the
macrobuttons work fine.

Thanks, Jezebel, for the seeds of the solution!

 

[Jezebel]

Much better is not to use the Selection at all in macros. The recorder
always does; but it makes for bad code. With only very arcane exceptions,
there is nothing you can do with the Selection that you can't do better with
direct references to ranges and objects.

 

[BobW]

I guess you must be talking 'Visual Basic'. I haven't seen any references to
'ranges and objects' in Word Help.

 

[Suzanne S. Barnhill]

VBA (Visual Basic for Applications) is the language used for macros in Word.

 

[Jezebel]

Display your macro code, go to View > Object Browser, or try VBA Help.

A Range is one kind of object -- it refers to any contiguous part of the
document. The Selection is a special kind of Range: it's the Range
highlighted by the user at the time.