End(3) for end(xlup)

[John Davis]

Hello All:

I read somewhere on the board where there were numbers which can be used to replace XLUP, XLDOWN, XLTOLEFT ect. I use the End(3). However, I'd like to know where to find the other numbers. When I search the board for same, I keep getting no matches found. Can someone enlighten me on where I can find this information?

 

[MrKowz]

I don't know how End(3) is working for you. The constants, according to Excel's help file are:

xlDown: -4121
xlToLeft: -4159
xlToRight: -4161
xlUp: -4162

I would recommend using the xl*direction* method rather than typing in the numeric constant. It will maintain readability of your code, and make it easier to debug and follow should you (or someone else) have the need to in the future)

 

[John Davis]

I don't know how End(3) is working for you. The constants, according to Excel's help file are:

xlDown: -4121
xlToLeft: -4159
xlToRight: -4161
xlUp: -4162

I would recommend using the xl*direction* method rather than typing in the numeric constant. It will maintain readability of your code, and make it easier to debug and follow should you (or someone else) have the need to in the future)

Thanks for your reply. However, I wanted to know where I saw the (3) replacing (xlUp). I now know that (2) can replace (xlDown). Lacking the ability too find this information is only making more curious. It must be somewhere.

 

[RoryA]

2 is not the same as xlDown, it's xlRight. 4 would be xlDown. You're still much better off using the enumeration though unless you are trying to make your code hard to read.

 

[Norie]

John

You can find all the Excel VBA constants in the Object Browser, which you can access using F2 in the VBE.

They are all listed grouped by the collections they belong to, eg XLDirection is the group for xlDown, xlUp etc.

You can also find them by searching for the objects/methods etc you want to use them with.

I agree though, stick with the 'name'.

 

[Weaver]

I would've said the same, except for recently I've been writing code in Access to create and modify Excel spreadsheets and xlup, xldown etc don't mean anything in the access environment unless you define them yourself (what I did) or reference the Excel executable (didn't see the point for the sake of half a dozen variables, but I may be wrong...)

 

[Scott Huish]

You can also find them with something quick like this:

Sub test()
MsgBox xlDown
End Sub

 

[RoryA]

Makes the code more legible, and you can then reuse those anywhere in the code.

 

[John Davis]

John

You can find all the Excel VBA constants in the Object Browser, which you can access using F2 in the VBE.

They are all listed grouped by the collections they belong to, eg XLDirection is the group for xlDown, xlUp etc.

You can also find them by searching for the objects/methods etc you want to use them with.

I agree though, stick with the 'name'.

I'm just trying to learn things I don't know. Thanks for the information Norie. And thanks to all who replied.

 

[Norie]

Weaver

It's pretty easy to declare the constants in another application.

You could just dedicate a whole module to it and you could add constants as needed.

I suppose you could even copy/import that module between applications.

Oh, and if you use early binding the constants will work.