Confused with commands/methods

RIchavalues

New Member
Joined
Feb 24, 2016
Messages
25
Office Version
  1. 2019
Platform
  1. Windows
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:
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
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,830
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Feb 24, 2016
Messages
25
Office Version
  1. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Feb 8, 2016
Messages
51
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,511
Messages
5,596,581
Members
414,079
Latest member
Frills

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top