Confused with commands/methods

[RIchavalues]

Hi Experts,

Am sure this will be very easy for You all to explain but am having big trouble in understanding commands/methods.
For Example consider the below code:

Public previousCell As Range

Set previousCell = ActiveCell


ActiveCell.Offset(0, -1).Select


Range(Selection, Selection.Offset(0, 3)).Select


Range(prevCell.Offset(0, 0), prevCell.Offset(-1, 0)).Select

I tried to write VBA that's dynamic (ie., by offset method) and hence somehow applied trial and error method to reach my goal and by far I know its not best practice.

So now I would like to know more on functionalities that "Cells" and "Range" have and what exactly last line of my code does. I will be glad if You can provide any reference where I will get examples and also explanation that each command does in VBA. (I tried to learn every command from msdn and am more confused than I understand). Please let me know if there are any books that explains in a way that a person having 0% knowledge on programming/vba can understand and write code independently.

PS: Am trying to apply auto indent to code and searched google for the same. Got to know about MZtools but can't figure out how to auto indent code. Am looking for something like select all the code and apply auto indent so that formatting of the code looks nice. ("Ctrl+A" To select all code AND "Ctrl+I" to auto indent). Like something we have in Java editor.

Regards,
Richa

 

[Jeffrey Mahoney]

Auto indent happens, well automatically. IF you want to indent code after the fact, then highlight all the rows you want and press TAB.

I'm assuming these are random lines of code, not code you are using? Breaking down the last line:

Range() is a defined of cells
PrevCell is probably a range you defined previously like: Dim PrevCell as Range
.Offset(0,0) doesn't do anything
Prevcell.offset(-1,0) is a cell one row above of prevcell and zero columns from prevcell
.Select well select the range. Meaning it actually highlights the range

Most times in code, you don't have to "Select" a cell or a range, you just need to do something with it, like copy or paste. Selecting ranges is slow.

Set previousCell = ActiveCell is making the range or cell equal to another

ActiveCell.Offset(0, -1).Select is selecting the cell one column to the left of Activecell

Range(Selection, Selection.Offset(0, 3)).Select expands the highlighted range by three columns to the right

Jeff

 

[RIchavalues]

Thanks for the detail explanation Jeff. How is it possible to know each and every method in so detail? I want to learn more VBA starting from very very basic from the root. So is there any materials available so that I can buy some good ebooks and learn on own. Probably one that doesn't confuse me with concepts. Thanks for Your reply.

 

[Norie]

VBA kind of automatically indents code as long as you use TAB instead of space.

If you want a tool to 'properly' indent code look at Smart Indenter.

That is a great tool, but you can't forget some of the inbuilt tools/options available in Excel/VBA itself.

One of the most important of those is to use Option Explicit throughout your code, and if you check the Requre Variable Declaration option Tools>Options... then every module will have Option Explicit.

 

[iconiuss]

I learned most of the basics of VBA from a site called Udemy. There are quite a few beginner tutorials you can get for about $10.
You can surely find a lot of free tutorials online, but I found watching through the videos really put a lot of it in perspective for me.
Another thing to do is give yourself a project to work through.
Also looking through other peoples post on this or other forums is good. Try solve the issues they have. Even if you don't get an answer before someone else, just working through the problems adds new tools for yourself.

One of the biggest things to get your head around with VBA, is that is all about Objects and Collections.
ThisWorkbook - is an object
ThisWorkbook.Worksheets - is a Collection of sheets within that workbook
ThisWorkbook.Worksheets("Sheet1") - is a Sheet object within that collection