Dick has a post over at DDOE about encouraging people to have a go at VBA.
I completely agree, and in the spirit of “Recruit a new VBA programmer” week, I would like to hear how you got started in VBA.
- What books did you use?
- What courses did you attend?
- Did you have an on-site expert to help?
- Did you use MS help?(could you find it?)
- When, and which version did you learn on?
- Maybe you havent learnt, in which case do you have a plan?
- If you have no plans to learn VBA, why not?
- oh, and why did you learn VBA?
Me:
I learnt VBA in Excel 5.0 when it was first introduced (1995?). I had already done some XLM, VBA was a BIG change.
I used some deadly dull teach yersen in 21 days type book initially. Then I spent about 10 years with the help constantly open. Then t’intawebs happened and I now go to Google first help second. Can’t remember when we got broadband but even MS help is better than a dial up to Google.
I learnt VBA to escape the monotony of management accounting, after 30 odd period ends I was truly sick of the same accruals and prepayments the day before, and the same sales, gross profit reports after.
At one place I worked my colleagues were concerned that my desk was full of technical books ‘you’re meant to be the expert’ they said. Accepting that you don’t already know everything is perhaps the first step to becoming an expert.
I have never been on a VBA course, although I have now taught plenty. Never had an on-site expert I could bounce ideas off, although more recently I have often been in a team of multiple devs which is great.
My most recommended VBA book would be Excel VBA Programming for Dummies
Mainly because it dives right in there with real world useful stuff without boring you to tears with anal levels of background/foundations.
I guess this is the latest version for all you ribbon lovers Excel 2007 VBA Programming for Dummies
If after that you think VBA is something you want to persue further, then is the time to dredge through all that tedium about datatypes etc. We’ll cover that ‘next steps’ in another post later.
What about you?
Cheers
Simon
Smurf on Spreadsheets 
Thursday, 19th November, 2009 at 11:12 am |
I’ve just picked it up as I went along in the last three years. A problem leads to investigation of hitherto unused features which leads to increased knowledge. I’m fairly confident that there isn’t much more to VBA that I need to know to get the job here done, but am aware that there are a lot of things I’ve never seen the need for, and probably never will.
I’m now branching out more and more to .NET and database stuff, often just using VBA as a presentation layer as Excel exists on all corporate desktops and the company desktops & IIS server are pretty well locked down which currently prevents me using VB.NET or ASP.NET for forms etc. as often as I’d perhaps like to. That’s the way I see myseld moving in future though.
Thursday, 19th November, 2009 at 11:37 am |
I more-or-less already knew VBA when it shipped, having been working in VB since version 1 (1991). Converting from Excel 4* macros was entertaining.
The fact that we’re still using (effectively) VB6 a decade after it appeared in Excel just shows the paucity of Microsoft’s forward thinking. (They should hire the ExcelDNA guy, though.) More and more, as the free/cheap/webby competition develops, the real differentiating factor in Excel/Office is the programmability. And they dropped that ball so long ago I’m starting to think they’ve given up looking for it.
*I don’t know why we call them after v4, there wasn’t any real change from XL3, was there? Or is my memory playing tricks on me)
Thursday, 19th November, 2009 at 12:23 pm |
I’d learnt and taught Lotus 123 including macros since 1985 but my big breakthrough into VBA functions (as opposed to macros) was around 1996 with John Walkenbach’s Power Programming With Excel 95 book – in that among the 1000 or more pages on macros, there was a single VBA function that caught my attention
Given that there was nothing else around on functions, I had no choice but to learn on my own especially on how to handle arrays within functions
My main focus is on developing material to teach in the areas of portfolio theory and now numerical methods for pricing derivatives – currently at a rather extreme in that I use no Excel functions in my VBA code, partly for accuracy and partly to ensure portability into other languages such as VB.NET and C++
Of the many subsequent books that I’ve bought, the most valuable have been the Wrox Excel VBA one that Stephen Bullen co-authored and his current one, Professional Excel Development
Still avoid macros as much as possible and keep my own style of VBA that is as simple as possible (short variable names, types of doubles, longs and variants for vectors and matrices) whilst still ensuring high accuracy within the constraints of Excel’s 15 decimal places
Thursday, 19th November, 2009 at 12:35 pm |
Self taught. At first using books, the inbuilt help and then the internet.
I originally started out using Basic back in MS Dos and Windows 3.1 days. Then left it for several years to concentrate on becoming a Structural Engineer. For the last few years (15?) having realised that spreadsheets and databases could help do the mundane repetitive things I started learning VBA and have not stopped. I suppose using Access 97 (Alison Balters book) got me started.
Is it me or are is the inbuilt help in the latest versions of Excel/Word etc not as good as the previous versions.
Thursday, 19th November, 2009 at 3:43 pm |
I had already had course work in FORTRAN and Pascal, learned an ancient BASIC dialect on my own from manuals in college, and hand learned C from books on my own afterwards. Never bothered with any VB[A]-specific texts, but I’ve made A LOT of use of the macro recorder.
Thursday, 19th November, 2009 at 3:44 pm |
I learned VB5 in university and went on to pick up VBA from that and, surprisingly, MS help. Of course, various VBA forums were also extremely useful to me. I learned VBA because my first job involved automating a budget template that pulled from Essbase and the only way you could easily do this was through the DLL’s provided combined with VBA and Excel.
Thursday, 19th November, 2009 at 5:45 pm |
I come from a programming background, having started in Algol and Assembler.
As you do, I worked my way up the corporate ladder, and although I remained in technical fields, I got further away from programming. Spreadsheets were a god-send for me, and like Mike, I started on Lotus 123 too and loved its macros capability. In the 1990s, my company was undergoing major IT shifts, and although officially were a total Unix shop, I had loads of autonomy and we started developing using VB more and more. To begin with, VB really was basic, But then we began with Excel and XLM, and whilst these were very much client tools, they were very powerful for the time.
The big change came for me around 1996, when we built a data warehouse, and the business wanted to use Excel to view the output (Not much has changed there then), and so we started to write routines in VBA to pull in data, format it, and so on, all basic stuff, but hugely appreciated and it just snowballed, We were building portals and dashboards, although we didn’t call them that then, all using VB, VBS, VBA, and so on, always ending up in Excel, in workbooks that became more and more dynamic.
The only books I ever recall buying were John Walkenbach’s Power Programming With Excel 95, Excel 2000 VBA Programmers Reference by Bullen, Bovey and Green, and VB & VBA In A Nutshell by Paul Lomax, although I have acquired dozens more (oh, I did buy Hardcore Visual Basic by Bruce McKinney in a second hand store and which is now available online, http://vb.mvps.org/hardcore/. There were half a dozen there, I wished I had bought them all to give out to people).
Since those heady early days, I have also gotten into Excel core functionality much more, and like Mike I try and do it without VBA if I can, although that rarely lasts long.
For me, Excel and VBA has been the best thing I have ever found in IT, not because it is the best language, not because it is the best IDE (it is far from either of these), but because its usage is amazing. I love SQL Server, I love VB, I love JavaScript, hell I even like HTML and ASP, but I am constantly staggered by what people do with Excel and VBA (in cell charts at DDoE as a brilliant example). Unfortunately, my better half thinks it is the worst thing I have ever found.
Thursday, 19th November, 2009 at 7:23 pm |
‘acquired’ – coat with big pockets??
Thursday, 19th November, 2009 at 9:44 pm |
As a dabbler who uses it to help in his admin job rather than a developer
* What books did you use? None
* What courses did you attend? A VB6 introductory course in 96 which i found of little use
* Did you have an on-site expert to help? Does Google and Excel-L count?
* Did you use MS help?(could you find it?) Rarely and usually only after the aforementioned experts
* When, and which version did you learn on? I can’t remember what we used before 97 and it must have been pre 97 given the VB6 course in 96
* Maybe you havent learnt, in which case do you have a plan? Yep haven’t learnt yet (still learning anyway) Plan is to keep monitoring this and other blogs, contribute and observe on Excel-l and keep experimenting myself
* If you have no plans to learn VBA, why not? Until M$ come up with something accessible to us dabblers and not just those with the full and expensive toolset I couldn’t understand anybody with no plans to
* oh, and why did you learn VBA?
To write procedures that save seconds on first use, over the manual alternative, but then save hours next time round
Thursday, 19th November, 2009 at 11:08 pm |
I started using spreadsheets with VisiCalc and then progressed to Lotus 123 and learnt some macro programming within Lotus then onto Quatro Po and finally Excel. The job I was doing at that time required to be speeded up hence my first real need to start teaching myself VBA.
From that point there has been no looking back and having been made redundant in 2001 and out of work for a year I had the perfect opportunity to increase my knowledge and find all the amazing sites available on the net all of which have been such a great help over the last few years and still continue to be. I rentered the job market automating manual systems for small local companies and am now woring for a financial software house using not only the Excel and VBA skills I have picked up along the way but also the database and SQL skills I learnt as a necessary supplement to the automating work I had been doing. I agree that Excel VBA for Dummies is a great starting place as are the various Mr Excel books as well as John Walkenbach’s Power Programming With VBA series and Bullen and Bovey’s VBA Programmers Reference series. I still fell a tinge of pleasure when something I have been developing works as desired even after all these years. It’s never to late to start and you never stop learning.
Friday, 20th November, 2009 at 1:24 am |
What books did you use?
There weren’t any when I started in Excel 5 – used the actual printed docs – when the had such things
What courses did you attend?
None – gave a few myself but found it ereally hard to teach VBA because there are so many levels you gotta know in order to do anything but an “Hello-World” dialog box :-)
Did you have an on-site expert to help?
That was me I’m afraid
Did you use MS help?(could you find it?)
All the time
When, and which version did you learn on?
5.0
Maybe you havent learnt, in which case do you have a plan?
Too late…
If you have no plans to learn VBA, why not?
If I didn’t I’d be a fool …
oh, and why did you learn VBA?
Because I believed that Spreadsheets were going to be my thing (and they still are) and I just accepted that VBA was the future, regardless of how much I loved XLM macros. Frankly I’ve always thought that VBA was over-kill in Excel and I said at the time and still think so. IMHO it has frankly probably been a net drain on spreadsheet customization because it really requires a “professional” developer to do anything rather than a Power-User like old 1-2-3 and XM macros. And there simply isn’t enough legitimacy in the Excel development game to give it enough energy to grow – quite the opposite I’m afraid…
Dick
Friday, 20th November, 2009 at 1:51 am |
I had enough programming experience (Fortran, assembler, proprietary sequence languages, etc.) so that the syntax of VBA was not a challenge. Dummies 97 and VBA Power Programming (thanks j-walk) really enhanced my knowledge of Excel itself (what can be done) and the object model (how to do it).
Friday, 20th November, 2009 at 2:04 am |
Interesting thread.
I started learning Excel in 1985 when it was called 123. I’d just moved to Australia after 3 computer free years in Saudi Arabia and was working for a civil engineering consultant company. They had a copy of 123 in the accounts department, and no-one was using it because they didn’t know what it was for, so I borrowed it and worked through the on-line tutorials (half-guiltily, because it was an accounting program, and I was an engineer).
Spreadsheets have been the main-stay of my design work ever since. I did very little in Excel until about 2004, when my main engineering analysis program introduced an API that would talk to VBA but not Lotus Script. Discovered that the Lotus Script macros and functions translated into VBA surprisingly easily (for the most part), and I now do almost everything in Excel, apart from a few legacy 123 worksheets.
I still miss the old “keyboard macro” style macros from 123, whilst recognising that they are well and truly “ex-parrots”.
As in other areas, in engineering spreadsheet skills remain very much under appreciated, other than by those who actuallu use them. Seems to be an increasing movement to replace spreadheets with “proper maths and engineering” applications, even when the spreadsheet would do exactly the same job without the learning curve.
Anyway, looks like my FEA program has finished cooking, so I’ll go and feed the results to Excel :)
Friday, 20th November, 2009 at 3:14 am |
Doug: “I started learning Excel in 1985 when it was called 123.”
LOL
I started programming Excel in the good old XLM days. I’d been doing repetitive SOLVER analyses, row by row, ad nauseum, and realized the computer could be working for me, instead of the reverse.
Spent weeks with the paper manual in my lap until I’d automated the sucker.
Didn’t bother learning VBA in Excel 5, since it still did XLM. But when Excel 97 came out, I decided to get into it. I took a 3-day Excel VBA class. After five minutes I realized the instructor would be too busy reminding folks which were the left and right mouse buttons, and my style isn’t to sit there selecting the same cell as the instructor does.
So while the instructor taught, I played. When the class did exercises, I called the instructor over to answer questions.
I got a QUE book on Excel 95 (even though I was using 97) which had a couple chapters on VBA in the back. I never had any other VBA books until the authors were my colleagues and they gave me complimentary copies. I got a lot of mileage out of the Excel-L email list. Back then it had lots of knowledgeable members and lots of good, technical exchanges. Then I discovered the Microsoft public newsgroups. I tried to answer every question in the programming group. I didn’t answer too many posts, but I worked out the problems. It was good practice.
Along the way I discovered Chip Pearson’s and John Walkenbach’s web sites, and more which were less extensive. Google too. And at work, I started coding tools for my whole group to use. I learned to program for other people, which is two levels more difficult than programming for yourself.
Friday, 20th November, 2009 at 5:22 am |
What books did you use?
-The Help books that came with Excel 95 – Were brilliant
What courses did you attend?
– None
Did you have an on-site expert to help?
– Google/Excel-L/Blogs/etc…
Did you use MS help?(could you find it?)
Only up to Excel 97….since then the help has gone down with every version
When, and which version did you learn on?
Excel 95/97
oh, and why did you learn VBA?
In my first job everything was wanted yesterday…so I had to find a way to go home on time….
Sunday, 22nd November, 2009 at 12:08 am |
I first learned Basic (and COBOL!) on an HP3000 “mini-computer.” Then GWBasic in DOS on an IBM clone – I googled GWBasic and found a pic of the IDE. Thanks for sparking that bit of nostalgia.
I think I created a couple of macros in 123 and then recorded a few in Excel 5. I remember a few years later trying to find a recorded macro, going into the IDE instead of the macro worksheet and being confused. In about 2000 I got serious about learning VBA for 2 reasons: 1) a consulting job 2) a poor grasp of the native abilities of Excel, like data validation. It was a classic beginner’s project – every row had it’s own checkbox, among other unwieldy features).
I used Help, Deja News (then Google Groups) and the Walkenbach and Bullen, et. al. books.
Sunday, 22nd November, 2009 at 7:25 pm |
I wasn’t actually thinking of going back this far, but as you lot are…
I started in Lotus 123, possibly 1.2? I remember the upgrade to 2.2? I certainly remember the upgrade to the one where you had \ for normal commands and : for formatting. 3.4 or 4.3? anyway all on dos in the early nineties. I did quite a bit of Macro stuff using the printed help books that came with computer programmes at that time. Then I bluffed my way into an Excel 4 on Win3.11 job, never used Lotus since.
Monday, 23rd November, 2009 at 7:55 pm |
It’s /, not \, for 123 menu commands. : was for Always/WYSIWYG formatting commands. Always had been a 3rd party add-on, but Lotus Devel Corp bought it in 1988 and bundled Always with 123 version 2.2 in 1989. LDC then incorporated the Always functionality as WYSIWYG in 123 version 3.1 in 1990.
I used Lotus Symphony back in 1984 (since it was cheaper than buying both 123 and WordStar), then branched out to VP Planner (one of the spreadsheet competitors Lotus killed off by lawsuit) then to 123. I’ve used or evaluated a lot of different spreadsheets, but I never tried SuperCalc or Javelin Plus (which wasn’t really a spreadsheet, but from everything I recall reading it would have been a much better business application development platform than spreadsheets, it’s just that it required some design-before-implement discipline which ruined its chances compared to the instant gratification of spreadsheets). I also tried Lotus Improv and a similar product named Advance, but calculation hypercubes and formula by dimension just aren’t general enough/are too restrictive.
FWIW, the spreadsheet wars ended in 1997 with MSO 97 and Lotus SmartSuite 97. VBE is SO MUCH BETTER than the LotusScript IDE that that alone would have been enough to kill off Lotus. The 123 object model was a virus writer’s playground. There was no way to turn off macros, only a way to disable processing of macros automatically run at startup (the equivalents of Excel’s Auto_Open macros and Workbook_Open event handlers). No way to disable, say, the 123 OM equivalent of SheetCalculate event handlers. Unlike Excel, 123’s calculation mode was[/is?] determined by the calculation mode at time of last file-save of the most recently opened file. Therefore, all it would take in 123 would be saving a workbook with the formula @RAND or @NOW in one cell under automatic recalculation and anything at all in the OnCalculate event handler, and there’d be no way to prevent 123 from recalculating on opening that file, so no way to bypass that OnCalculate event handler. In short, it was a very good thing Lotus died off when it did since it’s last chance to win back some respect/market share was as crappy as it was.