VBA: Best way to learn VBA?

  • I've got kind of a general question. I've been reviewing this message board for the last few weeks and also trying to read a little about using VBA from various books. However, it's SLOWLY sinking in, way too slowly. I do have programming background and have done many similar things before with other languages. However, I'm having trouble seeing exact parallels in order to write the necessary VBA code that "works" in an efficient, effective manner.


    I need some recommendations that would provide some fundamental knowledge that would help me better interpret code in many of these messages as well as allow me to "start easier" in writing my own routines. I can usually interpret most of the code in most of the messages on the Excel/VBA board, but I can't seem to get to a level yet where I can begin from scratch to write the necessary code to do something sometimes that even seems simple (at least in my mind). Some of the initial phases/activities I seem to have trouble with are:


    1. How to assign values to a variable you create? (What should be the syntax of the command? Should I use "Set"? When to use "="? How to combine text and the value of a function or other variable? Etc.)


    2. How to know which property/attribute to use in your commands to do what you want to do? (Is there a generalized command structure/naming convention that will help me make this decision or is it simply becoming familiar with as many different ones as possible and knowing how they're used? For example, how do I rename a worksheet using a combination of text and the current date function? How can I make a pivot table refer to a spreadsheet in a generalized way rather than specifically by the spreadsheet name, e.g., base the pivot table on the 3rd spreadsheet in the workbook?)


    3. Are there some basic generalized principles and/or naming conventions that could provide a foundation for getting all this to make sense and guide someone in developing their own code? It just seems like each code solution I see has some basic structure (e.g., define dims, assign values, do loops, error handling), however, to determine what specifically goes in each of those sections to address what you want to do seems to vary all over the book and often results in something that is not clearly evident much less something that you could create yourself without a thorough knowledge of every specific property used in the code.


    I would really appreciate some experienced guidance and tips on this one. I don't mind working at this, but I need a better way than what I've been doing.


    Thanks.

  • I'm probably not the best one to address your question adequately, I learned by reading Walkenback's Power Programming in Excel, which worked reasonably well because I had need to use it on speciric projects at the time and those sections sank in. It also includes a CD with lots of example code that was really useful and a lengthly discussion of VBA code structure.


    But before I push Walkenback too much, make sure you've looked at the following thread with more info.


    http://www.ozgrid.com/forum/viewthread.php?tid=7517


    Another thing to remember is to make use of the macro recorder. You can't record loops and other more elaborate things, but you can record a lot of what you might want to do and you can spend your time digesting code that did things you care about and that have context to you. I always find that more efficient than general reading.

  • Record and Read for straight forward stuff.


    You'll definately need a reference book of some type for the serious stuff like defining variables, loops, if's, File System Objects etc.


    I like Walkenbachs as above and also some of the books from Mr Excel (sorry Dave), also Hans Herber put out a great CD with stacks of code on it and fully searchable.


    Other than that stick with this message board and be patient. Don't forget how many programmers MS has developing Excel. It's gonna take a little while to catch up and cotton on but keep at it.


    AJW

  • Hi Starman,


    No-one's yet mentioned it but don't underestimate the usefulness of the VBA help files as well. Everyone loves to slag off the help files, however in terms of the language Structure you refer to in your post, the help files are probably the best source of information.


    They certainly make a good fist of letting you know which properties and methods go with which objects etc...


    Experiment, experiment, experiment too! Nothing beats practice.... Oh, and learn the fundamentals about code debugging...it will make it easier for you to see exactly where you have gone wrong....after a time you get to know what the error messages actually mean in English :wink2:


    After all that... the link provided by dave


    http://www.ozgrid.com/Services/VBAExampleList.htm


    This is a great tool. I bought it earlier this year and you can read my review here...


    http://www.ozgrid.com/forum/viewthread.php?tid=4467


    Hope this helps,


    WIll

  • Don't be discouraged if it "seems" to be taking a long time - if you use VBA frequently and do a diverse range of tasks you will learn heaps within few months and then they'll be no stopping you.


    "Never, Never, Never give up." Winston Churchill
    Applies to life AND computers

    There are three types of people in this world.
    Those who can count and those who can't.

  • If we are quoting Churchill, then this one is probably more suitable for XL/VBA users :wink2:



    Quote


    Every day you may make progress. Every step may be fruitful. Yet there will stretch out before you an ever-lengthening, ever-ascending, ever-improving path. You know you will never get to the end of the journey. But this, so far from discouraging, only adds to the joy and glory of the climb.


    or maybe this (my current favourite)


    Quote


    Success is the ability to go from one failure to another with no loss of enthusiasm.


    How apt!


    Will

  • Reiterating points already made but...


    Record macro's and remember exactly what you did. Read the resulting macro and place what you did against the code.


    Personally, I'm a fan of the help files, but would stress that "surfing them" is not the best method. Find a problem then try to solve it. The "see also" function is one I always check, even when I think I've found a solution.


    Books wise, I've an old MS VBA book that came with MS Office. Structure wise it's pants but detailing functions, keywords etc it's a goldmine. No idea where you'd find a copy though.


    The main problem I've found is that VBA is a procedural language that's been pushed towards an object orientated language. As such it is not easy to pick up with just either paradigm in mind. Start procedural but realise that the more complex stuff dives into the obj based realms.


    Lastly there's no better way than just writing code to solve a problem, banging your head against your keyboard when it doesn't work, trying again until the wee hours and asking for help (i.e. here) when you run out of caffeine.


    Best of luck.


    John

  • Hi Starman,


    I think the others have already covered the 'general' guidance for starting out. I'll just add that, like John, when I first started out in VBA I found that I learned best my reading problems on the forums and trying to solve them (I didn't actually post any replies initially, just compared my approach to those suggested in the replies :) ).


    As regards some of the specific queries:


    1. You use "=" for most variable assignments, and "Set" for object variable assignments. EG

    Code
    Sub decs()
        Dim i As Integer, strTest As String, wsTest As Worksheet
        i = 10
        strTest = "Starman"
        Set wsTest = Worksheets("Sheet1")
    End Sub

    2. If you want to find the properties and methods of an object then the Help files are probably the best source.


    3. Naming conventions. Have a look here : http://www.xoc.net/standards/rvbanc.asp


    You'll end up adopting your own approach but this should get you started.


    HTH

  • Another couple of things I find useful are


    Make sure you have the Require Variable Definition checked,
    it can be found on the VBE options dialog. ALT+F11 then Tools > Options


    It automatics inserts Option Explicit at the top of a code module.
    It does mean that you will have to dimension all the variables you use BUT it stops you getting caught out by typing errors in variable names.


    Another source of information about objects/methods/properties and constants is the Object browser.
    When in the VBE press F2


    Cheers
    Andy

  • A few questions re: your responses:


    Will, you mentioned "learn the fundamentals about code debugging." What's the best way to do this? Can you refer me to any specific help/message topics on this?


    Richie, you indicated: "If you want to find the properties and methods of an object then the Help files are probably the best source." What's are some recomended ways to use the Help files while you're working on code or just studying VBA? I'm not sure if my VBA Help is working correctly. If I highlight a property/object in some code and then press F1, shouldn't I be able to get help info about that item? Right now, I just get a blank Help window. (For example, if I highlight "Sheets" and press F1. If I highlight "Dim" and press F1, I go get a Help screen, however, it appears "incomplete". The top indicates the words DIM Statement but then has a bunch of "small boxes going across the window. Typically, I've seen links in this area to examples, "see also", etc. These don't seem to be appearing in my Help windows for VBA. Other links do appear, however. This seems to have occured since I've loaded an addin called Macrosystems Addin Manager. I tried removing the Addin, but doesn't seem to affect things.)




    Andy, you suggested: "Another source of information about objects/methods/properties and constants is the Object browser." What are some recommended ways to interact/work with this? Is there a way to get Help and/or examples of how some of the objects/properties? I'm not sure if my VBA Help is working correctly. When I go to the Object Browser and highlight an object or property and then press F1, an empty Help window comes up. Is this normal?

  • As probably one of the newest to VBA of the group responding, I may have the least to offer. But I found Walkenbach's book great. Here is a quote about structured programming that hit me like a ton of bricks.


    Excel 2002 Power Programming with VBA by John Walkenbach



    Software: OpenOffice 3.0/NeoOffice 3.0 on Mac OS X 10.5.6
    Humanware: Older than dirt


    Old, slow, and confused - but at least I'm inconsistent!


    Rich
    (retired Excel 2003 user, 3.28.2008)

  • It does sound as though your help system is not functioning properly.


    Pressing F1 within the object browser did locate the relevant help.


    As you will see the object browser only supplys the function/methods names. Not a description of what they are or how to use them.
    What it can do is show you all the related information about a object. You can also traverse through the objects heirarchy.


    Cheers
    Andy

  • Im writing a very complex lesson on the VBE of RoyUk and my web site excel-it.com but talking that aside the help files if installed are useful until very advanced programmer but im sure everyone here still looks up bits in help, in version 2000 and above its not bad at all have a very good long wade through it and try to use the VBA that’s given and play edit the codes.


    Remember OzGrid is a massive site and full of VBA just read as many posts as you can and follow the feed through and try the codes, this is a jump in learning everyone i know has developed a jump this way!!!


    Jack

  • Starman,


    I think you should take note of both Richie & Shades' comments, as it's easy to miss the obvious good advice sometimes.


    Shades mentions Structured Programming. Richie has provided an excellent link to VBA Naming and Coding Conventions written by Greg Riddick.


    Now whilst I will be the first to admit that this board is a superb resource for finding code solutions to both specific and general problems/tasks, there is a danger that by not "learning the basics" you will leave yourself with problems later on.


    It's a bit like learning any new skill really.... for example learning to drive :biggrin:


    In the UK at least, you have to pass a test in order to be able to legally drive unsupervised on the roads... it's a fact that some people who just take some lessons from a qualified instructor in a structured way have an easier time passing the test than those who may have been driving for years (say on private roads or farmland). When the latter come to take the test they fail, because their driving style is full of bad habits & incorrect procedures. They might be able to drive, but their style is not necessarily amenable to other road users ( :wink1: ).


    So, my point is that you can got at this VBA language problem in a number of ways...


    Either admit to yourself that it is going to take time to learn this new language & take a structured approach to it, maybe by purchasing a good book (& Walkenbach's has had more than a few mentions here). On this forum you have plenty of "test questions" to cut your teeth on (see Richie's comments again)... see how your approach improves by comparing yourself against how the problems got solved...


    Or jump straight in using examples from the boards to help you...


    The drawback as I see it from thwe latter approach is that you may end up tailoring pieces of code to suit your purpose without really understanding what the code does. At a later stage this will make it almost impossible for you to debug without outside help.


    Hope this helps...


    Will


    BTW: re your question on Debugging.


    Once you have got your Help files working properly, take a look at the following helpfile How To subjects:


    Start, Stop & Continue Code execution. (3 topics)
    Set & Clear a Breakpoint
    Start, Stop & Trace Code Execution
    Use the immediate window
    Use the object browser..


    Also there's a good explanation of the Debug Toolbar


    Hope this helps...

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!