In the early 1990s when Visual Basic (VB) first infiltrated Excel to become Visual Basic for Applications (VBA), it helped push Microsoft's then-fledgling spreadsheet so far ahead of others that most people today are not aware many competitors even existed. Lotus Development - since consumed by IBM - turned down a chance to …
Someone remind me just what Tory B Liar had in mind when he was a sock puppet for Microsoft? How many years ago is it now?
VBA, ladies toilets and penetration
"Think of VBA as like the ladies toilets in a firm where 99% of the staff is male.
A version of Office without VBA would therefore find it extremely hard to penetrate..."
There's got to be loads of jokes to be made about the holy trinity but they all escape me. Help, please...
A way to get rid of it...
Just make it calculate things with a randomized error added. Of course, it could already have this, but will we ever know? That could be the reason we have this credit mess. Conspiracy theorists can probably comment further.
VBA to blame
for the collapse of sub prime loan industry well I don't know sounds about right to me but then I use Linux.
The B stands for BASIC
If there was one word which would entice derision , apart from COBOL its BASIC, at least among IT types
Openoffice seems to favour a form of java script, how f-ing predictable
The only difference between VBA and java is history
VBA might have GOTO somewhere in its guts but you never see it and never need it because it has more structured loop constructs than say Pascal or C. Likewise it never uses the old BASIC line numbers
Making a true object might be possible but is hardly a prerequisite for a scripting language
Java/ java -like script contains lots of gottchas in the form of curly brackets forgotten, an = where you meant = = instead of crystal clear END IF and a compiler smart enough to know an assignment from a comparison
Yeah, all the PhD quants working for banks etc. are using Excel and VBA rather than Maple, MatLab or Mathematica or writing custom code. After all, these people would never have gained experience in numerical programming in grad school since they all could handle gauge theory in 12 dimensional spacetime in their heads.
So the week after the Reg completely screwed up by reporting VBA would be dropped from the next Windows version of Office we get this gem of reportage on VBA. Perhaps you all should consider just ignoring VBA and hope it goes away.
@B stands for Basic.
Did I forget something?
Oh yeah, I like Perl.
This article is sooo true.
I worked in the financial systems biz when there was a big push to put Unix (Solaris mostly) on the desktop. Most of the traders flatly refused, because their Reuters Terminal/Excel/DDE functionality could not be replicated in the Unix world, however much (alleged) cleverness was deployed.
It's typical of the wrong sort of IT geek (or indeed salesman) that they ignore users when they tell you that a function is *essential* and take it away anyway because they consider they know best.
@@B stands for BASIC
"Oh yeah, I like Perl"
Ah, I had forgotten Perl, blessed son of AWK!
Yes for syntax not so sugar, more 100's and 1000's you can't beat Perl
Its what high level languages have shrived for years to achieve, crystal clear even to a beginner, designed more for humans than to make itself machine readable and much much quicker than doing the same by hand, so I am told
Generally a fair article - Except:
Computer and Science should not be used together in a sentence unless the sentence is "Computer Science is not a real science - Physics and Chemistry, and if you are not too particular, Biology are Science. These subjects use the Scientific Method.
Some of us, my lad, used FORTRAN to do sums when Accountants used mechanical calculators, and IT graduates were terrified to let nasty users near their systems - They might get their card readers dirty.
Anyway wasn't the most important thing for an IT graduate the ability to play with new programming languages? Ther is no fun in doing anything productive.
Other than that, VBA is useful for for business to quickly cobble something together. The 'application' should then be sent to a professional to tidy up.
The last version of Excel on the Mac pops up the message about viruses if you use it to open a file (say a JPEG) - There is no easy way of turning that off...
As an aside, Microsoft Access uses its own special variety of VBA - MS has effectively depreciated it already...
Oh! Never, ever, let an accountant or marketer near a spread-sheet - If you do, they will use their special spreadsheet skills to fly the company into the ground.
VBA is the biggest pile of festering sputum, worse even than a deft kick in the ghoulies, that I have ever had the misfortune to have been forced to use. I hope it dies a swift yet painful death and is confined evermore to the computing equivalent of room 101, the great recycle bin the sky.
That said VSTA/VSTO isn't much better.
Oh by the way, I'm one of those Phd- owning Matlab-touting software engineer wannabe software engineers.
Anyone claiming CS to be a 'science' (including yourself) took one look at the name and let ignorance do the rest. It was never a science except in name; just mathematics with some engineering thrown in.
I don't want to go all wikipedia on your ass ...
... but do you have a citation for this?
"VBA, for example, runs the world financial markets. The credit crunch happened mostly in the minds of Excel spreadsheets doing horribly complex calculations in a language designed to change formatting, or to capture and validate user input."
... or is it just some sort of question-begging "common knowledge"?
Apparently CitiCorp have a daily turnover of (cue Dr Evil) one trillion dollars. It's somewhat hard to believe that this amount is managed in a big Excel spreadsheet.
I can believe that Excel may be used for ad-hoc forecasting, but if that's "running the financial markets" they my diary runs my business.
Since it was El Reg that broke the "news" of VBA being dropped I'm taking this all with a large pinch of NaCl.
VBA Not used in banks today
I don't know about the rest of the world but, thanks to End User Computing initiatives (i.e. auditors) there is little VBA used in banking today. This wasn't the case two years ago mind.
The real problem seems to be funds, stock trading etc. I know of one mid-sized fund admin outfit that would die tomorrow if one guy left (or got hit by a bus) because he's written all their systems in Excel/VBA and no-one else has a clue.
Oh the naiveity
Eddie, you would be suprised mate.
Dominic Connor's article is suprisingly accurate based on my experiences of working in financial services in London and New York.
@ Chris Branch
Yes, nearly right Chris. I am the AC with the jaundiced view of Computing Science. I took a real science course in the late 60s, and computer science courses in the 70s.
The mind is starting to go now, but I seem to remember that universities were keen to call their old mathematics courses that dealt with computing "Computing Science" because anything to do with science was a shortcut to funding - Mathematics was then thought of as men wearing tweed jackets with leather patches on the elbows, or women with bad haircuts, and a blackboard. They only needed chalk and a duster...
A cynic may say that computer courses started to have "Engineering" in their names when funding for engineering was easier to tap into.
While I am still feeling bilious, where the hell did "Chartered Engineer" come from for IT types. Engineering involves machine oil and blokes with hammers making steam engines.
"Yes for syntax not so sugar, more 100's and 1000's you can't beat Perl
Its what high level languages have shrived for years to achieve, crystal clear even to a beginner"
It can be described as many things, most of them positive, but "crystal clear even to a beginner" ain't one of 'em.
Death of EUC?
VBA isn't just used in trader spreadsheets, but all over product control, finance and go knows where else. The simple fact is that holping to erradicate EUC with strategic solutions doesn't work since the development cycle is too slow and the process too expensive. VBA is available on the desktop and can be used is a structured manner with a little programming discipline to produce readable maintainable systems.
Of course you can create horrors in VBA- I spend a lot of time rewriting them, but I've also seen lots of impossible stuff coded with "propper" tools by developers that just want the system unreadable for job security.
I've been developing tactical/rad systems for years and the joke is that every system you work on "has only 6 month to live as there's a strategic solution*about to be deployed". Nearly always the system is still running years after as either:
1. the strategic solution never actually worked and gets canned
2. the strategic solution was years behind schedule and your dept gets descoped
3. the strategic solution is deployed but in the 2 years since it was spec'd the buisess has moved on and you need to adapt the tactical system to cope
The solution isn't to try and kill off EUC/RAD development, but to use it as a path to developing strategic solutions and get developeres working with the users (same desk) to improve the vab code standards.
I'm currently developing an Excel/VBA suite to price the Global Funds Management business of the only huge US financial institution to escape the Sub-Prime writedowns (so far!). The only problem we have is to remember not to use 'Integer' anywhere - 300 billion dollars don't fit in 'em!
Davos Dilemmas ...... in the Matrix of their Own Moral Hazards.
"I'm currently developing an Excel/VBA suite to price the Global Funds Management business of the only huge US financial institution to escape the Sub-Prime writedowns (so far!). "
:-) In such a playing/killing field as is the Perceptions Management of Money, that was/always is a very prudent caveat, Brutus ....... for Much Bigger Picture Players than have been Encountered before are touting their Wares in XXXXPertEase and finding the Markets ever more Vulnerable to the Zero Day Virtual Trader and ITs Astute Subterranean Posts.
A whole new Class of Masters of the Universe.....Wizardry way beyond the lowly 33rd Degree of Bohemian Groves and their Ilk.
Develop anything to escape Sub-Prime performance and it will guarantee you an open cheque for Life and be a bargain. Good Luck, although as you would surely know, Luck plays no part in IT....... for it is an Ancient Wisdom in the Most Modern of Settings.
You actually work for the mafia ?? Respect, man !!
I'd lke to see you do a Physics degree!
How very snippy of you to attempt to ridicule Physics Graduates simply because you beleive they don't do structured programming. As an Astrophysiscs graduate, I've been in the IT industry for 25 years and I bet I've been involved in more structured programming projects than you've ever contemplated!
And you know what? Structured programming is a piece of piss after dealing with General Relativity, the complex mathematics of Stellar structures, oh yes and Quantum Mechanics, so take your facile opinion and stick it where the stellar electromagnetic emissions come from!
Physics degrees, VBA, what??
As a full-time computer programmer with a Physics degree, whose few undergrad programming courses in the Seventies were indeed FORTRAN, I take exception to the idea that we use VBA because we can't/won't work with more powerful languages. I have worked with other languages, but in the last 20 years I've made my living working with ERP systems. They ran on VAX BASIC (very nicely too) and COBOL. By far and away, COBOL is the language I'd use for finance -- not VBA. If I wanted a language that was far too complex to completely debug, I'd use C++ .... Oh, Microsoft wrote Windows in C++? Microsoft hires geniuses??
Recently I wrote a few VBA scripts because A. it was FAST B. it's modular & structured when I want it/need it to be. C. it's right there in Excel and D. it's still on the bottom line BASIC so I can use the ancient syntax "Open *filename* for input as #n" and it. just. works. No ambiguity, no stack problems, no indirections, works on strings just fine, it's almost as straight-forward as COBOL (sorry, didn't mean to make you flinch), and it got. the. job. done.
I learned from my Physics degree, and my engineering job experience, that it isn't they hype, it's what actually works that counts.
VBA is perfect for who it's aimed at, need to do some stupid function look on the web and vola. do the same on any other product.... no, hence it's popularity. If i was microsoft I'd add a few more security options and keep expanding the beast, making it even simpler for numpties to do what they want and to stay out of my hair.
It seems that SpitefulGOD is omnipotent etc. and has also re-written the rules on grammar, punctuation & spelling.
Those numpties may well come back to metaphorically bite you with their unmanageable spaghetti code.
A simple example of that... At one point in the dim & distant past (before I ascended to VBA Heaven) I was working for a two-bit, ropey IT consultancy in North London. One of the businesses we were supporting had managed to write some code to go through a list of data they had and to delete every fourth item, or something similar. We were that ropey, we feared users gaining _any_ sort of power we didn't understand or any kind of territory that we didn't control.
In those days, Excel had a limit of 65k characters in a module. Would you believe that this guy had gone through, recording his keystrokes over and over (and over) again until he'd hit this limit, at which point the code failed to compile? One would hope that the business would have the vision to employ knowledgeable IT staff who would analyse their needs and write ~5 lines of code to achieve the same result.
Yes, VBA can be as ropey as that particular IT outfit (and maybe a few other outfits as well), but it is a very useful and powerful tool in the right hands.
Naturally, with great power comes great responsibility, etc.
If you think there is no science in Computer Science you've never tutored first year CS.
Hypothesis: This code will fulfil this objective.
Experiment: Run the code. Try various inputs, see if they work.
Conclusion: This code does not work. Let's try a new hypothesis with version 2.0!!
Computer Science is only not science in the hands of those who actually *treat* it as mathematics.
Actually, given the existence of compiler bugs, sometimes even the mathematicians are reduced to this sort of methodology...
"itinerant VBA hackers"
That's me. I'm a VBA coder for a Bank - primarily MS Access and sometimes Excel when I need to. And your "facts" are substantially wrong... driven by some condescending sense of inflated elitism.
Firstly, financial modeling is not done in VBA - it's typically done on (typically off site) dedicated servers running C++ or Java programming over Oracle or SQL Server databases. These are complicated beasts run by boffins with PhDs in maths and programming. My head hurts even thinking about them.
Secondly, financial modeling was not responsible for the credit crunch. Unrealistic ratings from Moodys/S&P/Fitch etc (who were just hungry for fees for providing ratings to new vehicles) were at the core of what ought to be a scandal.
Thirdly, VBA is capable of executing very good code. IT's a case of GIGO - and a developer who knows what they are doing can use VBA to fantastic effect. Hence it's widespread adoption in the Financial services industry. While there is undoubtedly a cludge of badly written VBA legacy disasters, blaming the programming language is hardly helpful.
Finally, within the financial services industry VBA makes a far more significant contribution then a ladies toilet for one female employee (the metaphor police should cart you away for that one). It is the putty of the IT department. It is a RAD environment that allows in-house developers respond to the ridiculously short lead times given for requested tools. It is flexible and powerful enough to adequately integrate with the functions of almost any banking system for ad hoc bolt ons, and often fills major gaps in the IT packages.
It is the true unsung workhorse behind in-house dev work in financial IT depts. So get a clue before your next self-important blog rant.
"don't know about the rest of the world but, thanks to End User Computing initiatives (i.e. auditors) there is little VBA used in banking today. This wasn't the case two years ago mind."
Maybe not in the bank you worked in but the one I worked in until 6 months ago relied on VBA and quite a few "meddling modified macros" to keep its financial models workin a treat. When you see things like :
On Error Goto Muppet
You know you are onto a winner. And now with Excel server...
@ Anon 2
"VBA might have GOTO somewhere in its guts but you never see it and never need it because it has more structured loop constructs than say Pascal or C."
Except the error handling:
On Error Goto xxx
I like VBA though, much easier than some stupid macro language.
- Review Is it an iPad? Is it a MacBook Air? No, it's a Surface Pro 3
- Microsoft refuses to nip 'Windows 9' unzip lip slip
- US Copyright Office rules that monkeys CAN'T claim copyright over their selfies
- Tesla: YES – We'll build a network of free Superchargers in Oz
- True fact: 1 in 4 Brits are now TERRORISTS