syntax question

  • Thread starter Thread starter A Mad Doberman
  • Start date Start date
A

A Mad Doberman

Here is a snippet of code from a common sort routine.

Header:=xlGuess

2 questions:

1.) What does xl mean and do in VBA code. I have seen and used this in
many situations, although it has been copy/paste and I really don't
understand what it does. Another common place I use this is:
ActiveCell.SpecialCells(xlLastCell).Select

2.) What does Guess do, as in Header:=xlGuess. The answer seems
obvious enough, but I want to understand what the code is actually
doing and basing behavior on.

Thanks in advance all!
 
Excel has it's own set of constants. Lots of them are prefixed with the "xl"
characters.

In this case, headers:= can be xlyes, xlno, or xlguess

You can find out what numbers these constants mean by:
Open the VBE
Hit ctrl-g to see the immediate window
type this and hit enter:
?xlyes
and you'll see
1

Then
?xlno
and you'll see
2

Then try
?xlguess
and you'll see
0

It makes the code lots easier to understand later if you have the defined
constants that make sense in an English sort of way:

...., header:=0, ...

May not mean much in a week (or hour), but ..., header:=xlguess, ... is easy to
understand.

You can do the same thing with other constants (like xllastcell and
xlcelltypelastcell), too. There are lots of these constants and it would be
impossible (for me!) to remember the numeric equivalent.

As for what guessing really does...
if you look at data|sort (xl2003 menus), you'll see a couple of options at the
bottom. If you don't specify what you want, excel will try to guess if you have
headers in your data or not.

Sometimes formatting will help it make a decision. But lots of times, xl will
guess wrong. If I know my data, I always supply what I want (xlyes or xlno).
It's just safer than trusting excel.
 
The xl is simply a prefix identifying that particular constant to Excel
application. You will find some constants with a prefix of vb for visual
basic and some with mso for Microwsoft Office. They identify the particular
application environment that they were developed for.
 
Here is a snippet of code from a common sort routine.

Header:=xlGuess

2 questions:

1.) What does xl mean and do in VBA code. I have seen and used this in
many situations, although it has been copy/paste and I really don't
understand what it does. Another common place I use this is:
ActiveCell.SpecialCells(xlLastCell).Select

2.) What does Guess do, as in Header:=xlGuess. The answer seems
obvious enough, but I want to understand what the code is actually
doing and basing behavior on.

Thanks in advance all!
Click to expand...

Anything that starts with xl is a predefined constant in Excel. It is
not something in itself, it is just a convention that the MS
programmers adopted. In the first case, it is telling the SORT method
to guess whether the data block has a header or not (as opposed to
xlYes (it has) or xlNo (it doesn't)). You could just as well use
Header:=0 in it's place (the value defined for the constant), though
the use of the named constant is more descriptive.

Tom Lavedas
===========
http://members.cox.net/tglbatch/wsh/
 
1.) This is Excels way of Naming a Variable to equal a Value. For example,

LastRow = Sheets("Sheet1").Cells(Rows.count, "B").End(xlUp).Row

LastRow is a name that represents the Row Value of the last row with data in
it in Col.B

In your case, xlGuess = 0, that is how Excel sees xlGuess.

2.) the guessing of the header, I'm not so sure about. But I do know that
if you have two rows at the top of a worksheet that are merged and you have
Header:=xlGuess then the 2 Rows are considered the header else a single row
is your header.

Hope this helps!
 
If you read the help file for "Sort Method" it pretty much explains what the
different constants are for.
 
Anything that starts with xl is a predefined constant in Excel.  It is
not something in itself, it is just a convention that the MS
programmers adopted.  In the first case, it is telling the SORT method
to guess whether the data block has a header or not (as opposed to
xlYes (it has) or xlNo (it doesn't)).  You could just as well use
Header:=0 in it's place (the value defined for the constant), though
the use of the named constant is more descriptive.

Tom Lavedas
===========http://members.cox.net/tglbatch/wsh/- Hide quoted text -

- Show quoted text -
Click to expand...

thanks, everyone. That's very helpfull.
 
I should have specified the VBA help files. You will have to open the VB
editor and then click on that help file to search for VBA related items.
 
Back
Top