Would Like To Learn VBA

[texastomass]

Hi,

Thought this would be the best place to ask. I would like to start learning VBA script, i have a very basic knowledge but want to know more. Where would be the best startingpoint to gain this understanding and once i have this what professional qualifications would you advise i look into ?

Thanks In Advance

 

[ajetrumpet]

I would learn it on your own. Especially if you are running a business. I do not believe in paying for classes on this sort of stuff, because you lose the knowledge you gain right away without practical application, especially for visual basic. And with this language too, you have the wonderful intellisense dropdown menu's in the programs that can use the language.

As with any language, you can virtually become an expert if you can learn the objects, their classes, and their respective properties. That's about all there is to any language out there. After that comes tying the stuff in with other programs and other languages. That's the fun part of it. That's when you get to start using gooooooooooooooooooooogle. good luck to ya.

 

[jl123]

As with any language, you can virtually become an expert if you can learn the objects, their classes, and their respective properties.

I'm in a similiar position to the original poster.

Where would be a good source to learn about objects/classes/properities?

 

[SydneyGeek]

Depends if you want to pay or not. I'd suggest a book; either John Walkenbach's Power Programming (pick the one for your version of Excel) or Billl Jelen's VBA and Macros for Microsoft Excel, available from the MrExcel book store.

Record some macros that do simple things:
Select an entire region (Ctrl+*)
Navigate to the end of a column or row
Select to the end of a column or row
Select a cell containing a formula, press F2 and Tab. This records the formula
Add a worksheet and rename it

All of these will record code that you can inspect and play with. The recorder doesn't give the best syntax but it is a great way to learn the object model.

Also, in the code environment (Alt+F11), press F2 to go to the object browser. You can search for detail on any of the objects in Excel.

But the best way to learn is to pick a project that matters to you. Try a few things, post here for help, get that bit sorted, try the next bit. Hands-on, solving the problems by yourself (with help) is always the best option.

Denis

 

[MyExcel]

SydneyGeek it is a great advice
i can read code and understand most steps in the code
but My Problem
when i need to create code
i can't get the start point to write my code
how i can improve my self

 

[SydneyGeek]

It's going to depend on how you learn. If you like to go digging, pick a project and get started. if you like to have a book to refer to, check out the ones I mentioned earlier.

Regardless, pick the minds of people who know. That's where this Board comes in. Record stuff, try to change it, if you get stuck post and learn from the solutions.
If you use the search facility, you will often find plenty of approaches to the same problem.

Denis

 

[ajetrumpet]

when i need to create code
i can't get the start point to write my code
how i can improve my self

you can't. You just learn. Use this board like the geek said. we are here to help you. I don't post a lot of questions because i think i know it all, but i certainly do not. anyway, you have to start somewhere. Just remember that boards like this are stomping grounds for geniuses, but in order to get responses and respect from them, show initiative that you tried to get done what you needed first. That will get you more responses than you can imagine. People love to see others try, even if they fail over and over again.

here is WONDERFUL example of what NOT to do. This shows that you do not want to work to get it done: http://www.mrexcel.com/forum/showthread.php?t=375990

 

[MyExcel]

thanx geek
and thanx adam
I appreciate all what u say
But i feel shy to ask and ask all time
i know the geniuses of excel here will help me
but it is hard to ask him about the code or any difficult word in code
For example in this code

With Range("D1:D" & ActiveSheet.UsedRange.Rows.Count)

i don''t know what is the meaning of UsedRange and how Rows.count work
i try to read in VBA help a bout UsedRange, but when u found anyone who can explain it for you it will be better
i hope u can get My idea adam

 

[SydneyGeek]

One way to think of it: In VBA, most of the things you do are based around manipulating some sort of Object.
Objects are the nouns of VBA -- Workbooks, Worksheets, Ranges, Charts, etc.
To manipulate an Object you use a Method, which is an action (a verb). These include Add, Delete, Cut, Copy, Paste, Save, and so on.
Or you can change a Property, which decribes something about the Object and can be thought of as an adjective. Properties include Visible, Font, Border, Value, Formula, and so on.

When you look at the code you start big, and work down through smaller objects until you get to the one you want. Then you use a Method or change a Property. Each step is separated by a dot. So, if you had a Car object you might have a procedure like

If Car.Engine.Oil = Dirty Then 
  Car.Engine.Oil.Change
End If

In this case you check a Property. If it meets a condition, a Method does something to the object.

VBA helps you with typing the code too. If you type in

If Range("A1").

you will get a dropdown with all of the relevant Methods and Properties of the Range object. It's called Intellisense, and it can be pretty useful.

This is just a small part of it, but hopefully will give you some pointers.

Denis

 

[ajetrumpet]

with range("d1:d" & activesheet.usedrange.rows.count)

i don''t know what is the meaning of usedrange and how rows.count work
i try to read in vba help a bout usedrange, but when u found anyone who can explain it for you it will be better
i hope u can get my idea adam

used range is a property of the sheet. In this case, what it refers to is the entire sheet area that contains cells with something in them. E.g. - from the upper left most cell that is in use to the bottom right most cell that is in use (or not, dependent on what the last row is). Example - say you have something in cells b3, f10, and a17. Your used range would be:

a3:f17

it's funny you asked about that, because i don't see that used very often, if ever. But anyway, it is a property of the sheet object. "rows" is a property of the "range" object, which in this case, refers to the "used range" (as a property). "count" is a property of the rows property. So you see, it builds and builds and builds. It's almost like building a pyramid from the bottom up. Every layer of brick you lay is built on something else, until there is nothing left but a point at the top.

It's also like what the geek said. Nouns - adjectives, etc...


****the dog that is pleasent is said to be obedient. Dog = noun, pleasent = adjective of DOG (describes the dog), obedient (describes what pleasent is supposed to mean)= adjective.


****sheet.rows.count. Sheet = noun, rows = adjective of sheet, count = adjective of rows (how many rows are there on the sheet).


The reason you have to build is because the people that came up with the vba language tell you that you cannot do this:

sheet.rowcount