The Lounge is rated PG. If you're about to post something you wouldn't want your
kid sister to read then don't post it. No flame wars, no abusive conduct, no programming
questions and please don't post ads.
Now before I start this rant, I mean no insult to any VBA programmers out there reading this. I could not have greater admiration for you for succeeding where I have failed in actually accomplishing anything in VBA for Excel without lines and lines of hacks and botched, messy code. I want to get the general consensus on this, as it has been bugging me no end.
My first gripe is that the VBA programming environment that comes with Office (2007) is to my eyes just utter tat. It is currently removing and inserting random whitespace here there and everywhere in what seems to be an overtly malicious attempt to mess with my head. The 'intellisense' and 'syntax highlighting' leave much to be desired, but I could whine about that all day, so I'll stop there.
In my opinion however, the absolute worst thing about the VBA editor that shipped with Excel 2007 is...
The environment alerts you to coding errors with - get this - a MODAL messagebox that totally interrupts what you're trying to accomplish by screaming in your face until you click the stupid 'OK' button.
Secondly it seems to be to be impossible, or at least highly impractical, to accomplish anything approaching an elegant solution to a problem using VBA in Excel. So far this project, I have been repeatedly flummoxed by the arcane, esoteric knowledge of all the peculiarities of VBA that seems to be required to do anything at all in the language with any degree of efficiency.
While I am far from being a VB guru, VB.NET is my main programming language alongside Java and also my main source of income. I was astonished to find that my half a decade of VB.NET experience did not help one jot with VBA.
Is this just me? Please tell me, because I'm not sure if the problem is on my end (very possible) or with whichever dark ritual was used to create this 'programming language' in the first place.
A programming language is to a programmer what a fine hat is to one who is fond of fancy garden parties. Just don't try wearing any .NET language on your head. Some of them are sharp.
Horrible. Whenever possible, use .Net and/or Java to program for excel.
If it is possible to do your automation solution with OOo, I'd say give it a try. It's scriptable with both Python and Java, not just a VBA-like language (which it does allow, in order to be able to import MSO files containing macros).
But more informatively, Excel spreadsheet. Yes, I too gag at the relationship. However, think about the genesis of it. What's the easiest way to keep track of typed-in data -> the spreadsheet. The origin of which is a peice of paper, a pencil or pen and a cheap calculator.
I was thankful for Lotus 1-2-3. And post-wysiwyg scripting.
I positively could not wait to go "Visual" with the move to Excel in 2000.
All hindsight permutations, without VBA and THE SUBSET I might not have succeeded in excercising VB or VBNET or C++ or ... well not c#, to good effect.
It's a language I'd recommend as a stepping stone to others. Especially to children interested in keeping track of accounts.
I don't think Microsoft want you to use VBA any more - you can now use .Net to do exactly the same stuff[^]. You still need to know the ghastly innards of Office COM automation but it should be that little less frustrating.
Also remember, if you want to distribute your spreadsheet, that VBA is now an optional installation component (turned off by default from what I remember), where VSTO is always installed (from what I remember ).
It is putrid though, if you want worse try writing extensive Visual Studio plugins (project systems, debuggers, etc.).
He who asks a question is a fool for five minutes. He who does not ask a question remains a fool forever. [Chinese Proverb]
Nope, you can't do Access programming in .Net (other than Add-Ins). I just read your link and it seems to confirm that for Excel, Word, etc. You can create Add-Ins, but that's only one aspect of what you can create using VBA for those apps.
VBA (a.k.a. Active Content) is disabled by default in Office apps when the document, spreadsheet, etc. is from an unknown source. A security message bar appears at the top of the document warning you about this; clicking the Enable Content button on it allows any included VBA code to run.
VBA, like the VB6 it was derived from, is actually quite powerful. You can call Windows APIs directly from it to perform tasks that aren't already handled by the multitude (literally hundreds provided by default by Microsoft alone - not to mention 3rd party) of ActiveX objects and DLLs that are available. It allows the creation of class modules to so it can be as OO as anything.
Before you all dismiss me as completely and utterly mad, imagine the scenario ... 1. In the dark and distant past a user discovers Excel macros and starts to "automate" certain daily functions 2. It soon becomes all too much for them to handle so the macros get passed to an "IT contractor" to be brought up to production strength (an oxymoron if ever there was). Note that at this point the IT department still know nothing about this 3. In the meantime these spreadsheets somehow manage to become business critical "applications" 4. Eventually someone in the user department has an epiphany - or it might have been an audit finding - and said piece of s**t is passed over to the IT department to support. 5. By now the business-critical excel "application" consists of more than 120 spreadsheets mashed together like a web built by a hundred drunken spiders 6. Enter yours truly ... the re-engineering of various parts of this mess (when allowed to by the red-tape afficiandos) has kept me in gainful employment for some considerable time!!
p.s. around here "VBA" is known as "Visual Basic for Amateurs"
You forgot to mention that most of the macros are "written" using the "Record" button, not by actually hacking out code.
My wife is in finance (and a formal auditor) and I cringe every time she shows me a spreadsheet that they're using to run the business. There's always a magical button that flashes through screens, changing pivot tables and spitting out charts, enough to be seizure inducing.