VBA for Beginners

As I’ve been whittling away at my Bloglines backlog, I read some interesting comments at Philip Greenspun’s blog. Phil asks,

Best Computer Language for a 13-year-old Beginner?

His first thought was Visual Basic. There wasn’t a lot of support for VB in the comments. There are some seemingly cool beginner programs out there like Scratch, Alice, and Hackety-hack, all of which I’ve noted for when I’m ready to handcuff my son to a computer and force him to learn how to program. And there was, of course, the obligatory suggestions of Perl, Python, PHP and MySQL, HTML and CSS, C++, and C#. C++ for a beginner?

I began to think about how VBA would fit into the mix. I’m not sure I could, in good conscience, teach a 13-year-old VBA. I predict VBA will be gone in the not-too-distant future and it wouldn’t do the kid justice to teach him a dying language. However, I think there are some properties of VBA that are useful to a beginning programmer.

The most obvious of those helpful properties, at least in Excel, is the macro recorder. Most VBA programmers who can remember their formative years will agree that the macro recorder is very helpful when it comes to learning the syntax and the object model.

VBA also offers a kind-of self-contained environment in which to work. It’s certainly possible to destroy your operating system from VBA, but a beginner will likely be changing properties and calling methods of objects like Workbook, Worksheet, and Range. If things get out of hand, close the workbook and start fresh.

The immediate feedback of VBA is nice too. You can run just-written code and see the effects right away in the UI. You don’t have to compile to an exe or anything like that. Speaking of immediate, there’s also the Immediate Window for trying out code.

There’s a lot of positive aspects of VBA when it comes to beginners. If a 13-year-old showed a genuine interest in VBA, I wouldn’t turn him away. But I don’t think I’d suggest it to someone interested in “programming”. Your thoughts?

In other news, I downloaded Instant Rails the other days and went through the cookbook tutorial. Very cool. I bought a book and decided that my cold-Nebraska-winter project is to learn Ruby.

Posted in Uncategorized

34 thoughts on “VBA for Beginners

  1. I predict VBA will be gone in the not-too-distant future
    Nooooooooo!!!!!!!!
    It hasn’t been around that long to begin with! 12 years maybe? 1995?ish
    Replaced that with what, “XML” isn’t real language!?
    Microsoft is greedy NOT dumb!

  2. I say jump right in and teach kids C#, maybe hold off on the object oriented concepts. All these languages are relatively the same. As the he/she gets more comfortable with the basics introduce inheritance, polymorphism, encapsulation, generics…all that good stuff. The language it self is easy to learn, it’s just that the .Net library is so big. Same thing with VBA I guess, a C++ developer will learn the language within 30 minutes, literally, but it will take time to get familiar with the Excel object model.

  3. VBA isn’t a bad start. I started in VBA and went to VB.Net. The logic is what I’d want to teach the kid first. What language you use doesn’t matter as much learning to think in 0 and 1.

  4. I’d give VBA / Excel a chance as an introduction because you are really hands on with data. Instead of writing to hashtables, arrays, variables, whatever invisible abstract stuff, read and write worksheets and cells, see it happen, change manually and run again. Also, there is no compiling and there’s a ready-to-go runtime environment in almost every computer.

  5. When I was 4 years old my father sat me in front of a computer for 28 hours a day and made me code in assembler, you tell that to the kids of today and they dont belive you. :-)

    VBA is good, the macro recored is a big bonus, but it might lead them into Excel OM isues, when they would be better of just getting stuck in to coding things.

    But I’d still say go for one of those kiddy things, thats what there for after all!

  6. I think you’ll enjoy Ruby, Dick – I certainly do. If ou get teh Win32OLE stuff working, you can even start automating Excel, which I do to some effect when I upload data to MySQL (or whichever database takes my fancy at the time) for Rails to chomp on.

    And I have a theory that the dawn of the DLR in .Net 3.whatever, with its support for Python, F#, Ruby (to various levels at present) and whatnot gives MS the chance to kill off VBA without upsetting the owners of those squillions of lines of Office macros: implement VBA in the DLR and bingo! We can write macros in any scripting language supported. So I could spend all day in Excel and never stop writing Ruby.

    Well it’s a thought.

    My 8- and 6-year olds love playing with Scratch, btw, although I don’t see it as a serious learning tool until subroutines (as DIY blocks, most likely) are implemented. I don’t know if it’s planned for the next release.

    Other than that, I rather think I may try the little darlings on Ruby in a few years. They’ll get the language OK, I think, and there are enough GUI things happening (shoes and gosu are both promising) that there should be some graphical immediacy. Until then, we can always just hit F5 and look in the output window in Scite, I suppose.

  7. If it’s about learning how to code in general terms then give the young men:
    A used copy of the book The Waite Group’s C++ Primer Plus
    A used copy of C++

    If this is too expensive or not available then go for Mono (http://www.mono-project.com/Main_Page)on the Linux platform as it’s the most inexpensive alternative.

    Personally I would not buy $$$ license to a 13 years old beginner ;)

    Kind regards,
    Dennis

  8. I often cite Excel as a good way to get into ‘programming’ (I can’t call myself a programmer, just a scripter).

    Excel macro recorder leads you gently into directly writing macros/code. Excel VBA help is a great and you get immediate results.

    I also recommend HTML, instant results, not strict on white space and capitalisation. HTML leads into vbscript, parallels with VBA. It also edges towards ASP and the principles of passing parameters from one place to another etc(as does Excel VBA admittedly).

    Then there’s standalone vbscripts and I still use batch files (under WinXP). The real fun is when you start to join things up.

    The motivation for me is a mix of self-satisfaction/problem solving/showing off and creating little utilities to shave hours off routine tasks.

    I had a fair grounding in Unix scripting before anything else, but that was all I had available to me. Maybe 28 hours a day cuffed to a green screen is the way to go?

    p.s. As discussed in a previous thread, I tend to use Notepad2 for batch files/web pages and vbscripts because of the syntax highlighting, but mainly because of the Control+l hotkey to launch what I’m seeing on screen.

  9. I’d highly recommend Scratch as a painless, effective and fun introduction to programming. Within three days my 10 year had grasped the fundamentals of programming and is now, a year later, starting to become interested in ‘real’ languages (started when he “hacked” Scratch to expose the underlying Smalltalk (Squeek)development panels)).

    Tom

  10. I’ve been working on learning VBA for a couple of years now by bits and pieces and the thought of it going away just when I’m getting a handle on it is not terribly encouraging. What language would you recommend as the potential replacement for it? .Net perhaps?

    I’m 46 and looking to get a handle on something to get me out of what I’m doing and into the programming field as it appears to be a growing field.

  11. Some interesting comments there. The imminent death of VBA seems to be much predicted, but I haven’t seen much evidence for it. I thing Microsoft would be out of their minds to dump VBA. Lack of VBA compatibility is the only thing that stops me switching to Open Office or Gnumeric; I don’t suppose I’m the only one.

    As for the best way to get a 13 year old into programming, I think it depends what sort of programming they are interested in. If they have got a path set towards a full time career in application development then sure, get them started in C++ or whatever, but if you just want them to have sufficient knowledge to get the most out of computers in whatever career they might follow then I’d strongly recommend starting with Excel, and when they are familiar with that, introduce VBA, starting with User Defined Functions. For anyone involved in science or engineering spreadsheets are an invaluable tool, and Excel as the industry standard is clearly the one to learn. From my limited experience it seems to me that Excel is looked down on by many university people as being some sort of toy product suitable only for bean counters. I think that’s quite wrong. I spend most of my working day doing engineering calculations, and most of that I spend in Excel. I’m sure I’m not alone.

    I believe it has uses in financial applications as well ;)

  12. My 14-year-old son is taking a programming elective course, and that course teaches Java. They step right into the OO, er, paradigm. Java is yet another descendant of ALGOL, so fairly similar to the other languages suggested.

    Taking my son as indicative, making teenagers use spreadsheets is a HUGE MISTAKE. Unlike us, they have few if any ideas what they’d want to do with a spreadsheet. Creating scatter plots from science class data may be the only noncontrived use they could make of Excel. As for checkbook registers, to quote my son: ‘Why not use Quicken?’ which came as part of the bundled software on his PC. Telling kids they could reinvent wheels crudely in spreadsheets isn’t going to fan the spark of interest into a blaze of enthusiasm. Kids are smart enough to realize (as some of us may not be) that computers can do a lot of interesting things. Calculating arithmetic results, OTOH, is an extraordinarily boring thing computers do well. Putting this differently, how much of your RECREATIONAL computer usage involves using spreadsheets?

    As for immediate gratification, nothing beats turtle graphics. My son and I have dabbled with Logo, but it’s just too different from procedural programming to make it practical as an first step that would lead to using procedural languages. An ancient copy of TurboPascal would work – TP included turtle graphics. But why bother with anything more than a browser?

    http://www.psinvention.com/Turtle.htm

    For something more standard, a strong argument could be made for starting with Python and programming turtle graphics using Tk.

  13. Doug stated: “I’d strongly recommend starting with Excel, and when they are familiar with that, introduce VBA, starting with User Defined Functions.”

    This is not a bad start. People forget that writing formulas in Excel is really programming. Worksheet formulas lead naturally into UDFs, and using the macro to carry out repetitive tasks leads to VBA programming. These both get into procedural programming, and VBA later is not a terrible introduction to objects, methods, properties, classes, UIs, and other concepts.

    Harald makes a nice observation that Excel has this nice grid which a programmer can use as a data reository, much less abstract than all of those data constructions that reside in RAM and not on screen.

  14. VBA may not have many generations of Office left to run, but there’s a tonne of user developed routines written in VBA. Microsoft will have to support legacy same as COBOL, FORTRAN are still there. So, with one more generation and legacy support, will this be 2017 or later. VBA consumes ready made objects well, it isn’t that great in creating new objects. But how many business users need to or want to create object factories i.e. they are more interested in solving the job at hand rather than pick up a career at a third party software house where one is more into object creation and reuse.

  15. Great topic choice Dick!
    Ross – you had it easy…

    We have a session of the future of VBA at the Excel dev conf in Cambridge, we’ll be discussing VSTO, the DLR etc. (V interesting that Mike sees scope here too). I don’t think VBA is going anywhere (good or bad) for a while, but I’ve missed any news of recent significant enhancements, or planned developments.

    I’ve also got half an eye out for languages my kids at some point in the future.
    I wouldn’t go for VB/VBA, mainly because basic is the only language like basic. If you learn any of the curly brace languages, you can pick up the others pretty easy. I might start with spreadsheets though, for all the good reasons given.
    As well as scratch etc I was wondering about javascript? the Web is second nature to them.
    I had a play with this on-line Ruby demo – neat. http://tryruby.hobix.com/
    Cheers
    Simon

  16. Ananda,

    I don’t think anyone creates objects in Excel often, you have everything you need ready made in the object model, it’s scripting after all.

    I’m in the financial services field, it would be nuts to take out VBA from Excel. Excel/VBA is used to prototype, get people by until a more robust app is built, and create small procedures to help with daily tasks. VBA is perfect for this, .Net is overkill. If MFST were to take out VBA and force business users to buy an IDE in order to program spreadsheets, then users will go to open office or other alternatives.

  17. ” how much of your RECREATIONAL computer usage involves using spreadsheets?”

    Almost all of it :)

    That’s just me of course, but I’d suggest that fzz under-rates the usefulness of a spreadsheet for science and maths schoolwork, and on the purely recreational side have a look at the games at the XL-Logic site. Much more than scatter plots and crummy copies of Quicken there.

  18. I am with Fzz on all this. VBA is interesting to Old Accountants and that’s about it. When I learnt to code I put together a graphics tool in C++ (not in the slightest bit visual) and I built the routines that drew the lines for the buttons that you click. The fun was in the linked lists and the scale & rotation matrix, not the calculation of the balance sheet. Give the kids a real challenge not Granddad’s Excel VBA.

    {sitting on the porch}
    “In my day we used a restrictive and buggy microsoft cashflow product that was designed for copy and paste macros”.

  19. It doesnt matter what programming language you begin on, it’s more important to learn the fundamentals of computer programming.

    Variables (Dim)
    Iterations (For Next, Do While)
    Branching (If Then Else EndIf, Select Case)
    Referencing Memory (Arrays, Range Lookups)
    Passing Variables to and from Functions

    The concepts learned in VB can be applied to most modern languages.

    That said, learning a programming language can be so much easier if example code snippets are widely available.
    Java, C, VB.NET, C#, VBA are all good choices.

  20. I would say, given that my kid would be proficient in English by that age, I would definitely go for Ruby. If you have even had a glimpse at Ruby code, you’ll understand the language factor.

    It’s beautifully simple and many of Ruby’s concepts are more intuitive than in other languages: mixins over multiple inheritance, iterators, sentence-like sugar-coated syntax. With the introduction of Rails Ruby now also has flash-bang gratification, and you can pull off cool stuff quickly. If the kid would still be into programming after a stint of Ruby, I would teach him C, just so that he wouldn’t have to pick it up later and realize that he didn’t actually know anything about programming before it.

    Of course in 20 years, when my hypothetical future kid will be in the programming age, everybody has seen the light and will be writing in Haskell anyway :)

  21. MacroMan,

    Sure, C# and ASP.net are snappy, and because of the buzzing around especially the former, there is a definite market for .Net developers. This however does not mean that it is a good idea to put your eggs in one basket and subscribe to one super-IDE driven paradigm. Still remember Delphi?

    There are a lot of problems the .Net Framework needs to deal with before I can honestly recommend it to a beginning developer. In web development ASP.net is a good alternative with nice RAD features, but the dependency to IIS and the higher hardware and software costs are a deal-breaker, whereas with PHP, Ruby, Python, Lisp and others freely available languages all you need is a linux box and fast internet connection, and you’ve got yourself a small-scale, production-quality server.

    To take the comparison towards the abstract, although there is a legion of .Net developers on message forums and newsgroups to give you help when you’re stuck, the support and spirit of open source community are a great motivator when learning the trade. You can’t underestimate that.

    For client-side and desktop development, at the moment .Net is not mature enough. The penetration of the CLR is nowhere near the numbers where I could consider switching to C# or VB.net full-time. There simply aren’t enough users out there to consitute a reliable target market, and the barrier to try out new software becomes high when you have to install a 22 MB CLR first, cutting out a lot of valuable exposure.

    I agree that .Net is fun to write, and the productivity gains can be amazing. For a large portion of Excel developer community VSTO is the first touch with “real” programming languages, and that is unquestionably a good thing. But when it comes to deciding one’s first language, or whether to port your company’s application to .Net, I wouldn’t be so hasty jumping the bandwagon just yet.

  22. Excel is useful, but that doesn’t mean it’s an ideal first programming paradigm.

    Excel formulas don’t do anything but return values to the cells that contain them. A fine example of a functional programming language EXCEPT that the only way to define functions in Excel’s language of formulas is by using XLM macro sheets, and that’s definitely NOT what well-meaning parents should be teaching their children. Yes, there’s also VBA, but there’s no relationship between VBA and cell formulas except for the happenstantial one that Microsoft combined them in a single product. So teaching Excel formulas and VBA is teaching TWO things at once, and that’s more often than not a recipe for failure.

    Putting it another way, anyone seriously suggesting teaching kids Excel as their first foray into programming has never tried to teach kids programming. Pitiful lack of experience in what SIMPLY DOES NOT WORK.

  23. I started in Pascal and C. Then went on to VB, C++, Java and the like. The nice thing about starting Pascal and C are that they readily point out if you are cut out to be a programmer. Based on the drop out rate it is fair to say that not everyone is. I was probably around 14 when I cut my teeth in Pascal (on an Apple IIe).

    Would I use VBA to teach programming. I don’t tend to think that the language used matters too much. The principals are the same no matter which language you are using. VBA with XL is nice from the stand point of immediate feedback. Additionally you can dip your toe into the whole idea of objects. The recorder is a plus and a minus in that it help you understand the object model but it teaches you nothing about actual programming. Recorded macros are purely sequential with no selection or looping structures.

    So in a nut shell either programming makes sense to you or it doesn’t. If it makes sense to you then the language choosen has little effect (IMO). What is important is to build good coding discipline in at the early stages. That will cut down on the frustration level more than the choice of language. The only thing I would say is that if you intend to do programming on more than just a casual basis take C at some point in your life. Understanding C means having some sort of clue what is going on inside the magic box on your desk. That kind of knowledge makes you better at all languages.

  24. Personally, I think many of you are wrong.
    The language to program in is not at all important, it’s the concepts behind it. Learn children to thing analytically. Learn them to think in terms of objects. Learn them to make abstraction of (almost) everything. Learn them to first find out what the real needs of the ‘customer’ are (for sure they won’t tell themselves). Learn them to think in terms of reusability. Etc. etc. etc.
    The language to use is something you choose based on what you want to do (flash is probably a better choice for animations then vba and cobol won’t do any good for building a database driven website), what platforms you’re programming for (vba won’t really work on linux), availability (if your employer gives you C# well that’s probably what you gonna have to use), what the client wants you to use (well, you know, dear programmer, we have been using fortran for the last 500 years, and we got used to it, so please…), etc. etc.
    Let me also mention that many of the “programming languages” previous commentors mentionned are not really programming languages, but scripting languages or not even that (eg html).
    But if you want me to name an educational language: KPL or LOGO.
    And if you want to prepare them to earn big money: teach them something “old” like Fortran or Cobol. Since there are much less programmers knowing these languages than there are needed, these guys get payed very(!) good.

  25. I work at the planning Ministry in my country. I’m not in the computer Départment, but I use all the time Excel because of what I have to do. And believe me, there are so many repetive things to that I wish every day that I know how to use VBA. Do you guys think that 1)I can learn VBA by myself? 2)In case I can survive that, VBA won’t be dying in the next 2 or 3 years? Thanks. I’ll be waiting for the answer

  26. I disagree with most of the comments here.
    When I tried to learn programming a long time ago, I tried to learn it too much from an academic perspective, with object oriented, structured design methodology, …, …

    I think it’s the wrong way to do it. That kind of stuff gets picked up along the way. I think, especially for a young person, and maybe even more so for people with better things to do, the important thing is to do something that provides a constructive or enjoyment ‘feedback’ as quickly as possible.

    For example, I began learning VBA by working out how to insert functionality by copying and pasting code I was nicking from other peoples macros. That way I was immediately doing cool stuff, which caused be to look at the code more, and start playing with it, etc…

    I think the most important thing is to cut with the textbook learning and be interested enough to set yourself tasks, such as, write a macro to allow me to pick up the names of all sheets and select which to print.

    Other than that, Java is a good one. Very intelligently designed, and safe.
    And with a good decompiler, you can nick a lot of great code :)

  27. Richard: You can learn VBA yourself. I did and so have many other people. You will learn it as fast as you want. I spent a lot of time answering newsgroup posts and learned it very quickly. If you don’t dedicate that much time, you won’t learn it as quickly.

    VBA won’t die anytime soon, in my opinion. The Excel 4 Macro Language is still supported over a decade later, so VBA will be supported for a long time. MS likely will not be making it better, but you will have not wasted your time learning VBA.

  28. I am trying to select a range of cells from workbook1 and copy them to workbook2. I get a Runtime error #9 indicating a subscript is out of range. I get this same error message whether I try to copy a range=1 or range = many. Line with ** in front of it is where it bombs out.

    Any ideas ?
    Thank you,
    Diane

    Sub OpenExcelFile()

    ‘save a copy of the template
    Dim crTemplate As String, newCR As String, chLog As String, z As String, newFullCR As String
    Dim fs, FName As String
    chLog = “c:Documents and SettingsweedendDesktopDataCR ProcessCR Approval ProcessServicing Program Change Log.xls”
    newCR = “CR” + “3” + “.xls”
    newFullCR = “c:Documents and SettingsweedendDesktopDataCR ProcessCR Approval Process” & newCR
    crTemplate = “c:Documents and SettingsweedendDesktopDataCR ProcessCR Approval ProcessCRFormTemplate.xls”

    ‘    Filename = “Test” & TextBox1.Value
     
        If Dir(“c:Documents and SettingsweedendDesktopDataCR ProcessCR Approval ProcessCR3.xls”) = “” Then
           
            FileCopy crTemplate, newFullCR

            ‘open the new CR file
           Workbooks.Open newFullCR
            **Workbooks(“Servicing Program Change Log.xls”).Worksheets(“Sheet1”).Range(“S11:AD11”).Select
            Selection.Copy
            Windows(“CR3.xls”).Activate
            Workbooks(“CR3.xls”).Worksheets(“Sheet1”).Range(“B13”).Select
            ActiveSheet.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
            False, Transpose:=False
            ActiveWorkbook.Save
            Windows(“Servicing Program Change Log.xls”).Activate
            ActiveWorkbook.Close True
            ThisWorkbook.Close True
             
        Else
            MsgBox “A file with this name already exists.  Check the filename and try again”, vbInformation
            ActiveWorkbook.Close False
           
        End If
     
    End Sub

  29. Diane –

    You can’t select a range in a worksheet which isn’t the active worksheet. The workbook you opened just before this statement is now active.

    The good news is that you don’t need to select the range. Change this:

    Workbooks(“Servicing Program Change Log.xls”).Worksheets(“Sheet1?).Range(“S11:AD11?).Select
    Selection.Copy

    to this:

    Workbooks(“Servicing Program Change Log.xls”).Worksheets(“Sheet1?).Range(“S11:AD11?).Copy


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published. Required fields are marked *