The Lounge - CodeProject

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.

modified 2-Jul-17 15:32pm.

The vba environment is vb6. The visual studio IDE for fb6 was exactly the same way.

Horrible. Whenever possible, use .Net and/or Java to program for excel.

If it moves, compile it

loctrice wrote:
program for excel.

Still horrible.

Thankfully I've not done any excel at all in my new job. I've done a couple of csv files, but it's just not something we do much.

If it moves, compile it

I normally use vbscript - probably more archaic than VB6 but the great thing is you can copy stuff from odbc databases to create word, excel and powerpoint documents. If you get fed up of vbscript, you can use javascript to achieve the same result.

loctrice wrote:
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).

If I did it these days I most likely would use Libre. When I was doing the stuff before, it was required to use MS office. Not for reasons that made sense, but still required.

If it moves, compile it

It's not just you. Beer | [beer]

SixOfTheClock wrote:
a MODAL messagebox

Yes, that is the VERY worst thing about VBA and it's been like that for decades. My only solace when I had to do VB.net was that VS doesn't do that.

As to the rest, yes, finding out how to do anything is very difficult.

I'ts been a long while since I did VBA maintenance programming but if I recall you can turn the MODAL message box off. Goto Tools/Options from the editor.

this "language" as you call it is a spin off of vb6, the development environment is VB6, and the dark ritual was probably the same that got adam and eve out of the paradise in the first place...

I'm brazilian and english (well, human languages in general) aren't my best skill, so, sorry by my english. (if you want we can speak in C# or VB.Net =p)

You're saying that VB6 was an Apple product? Confused | :confused:

well, it runs (runned at least) on macs...

I'm brazilian and english (well, human languages in general) aren't my best skill, so, sorry by my english. (if you want we can speak in C# or VB.Net =p)

You can turn off the message box via Tools->Options->Auto Syntax Check

You, my friend, are amazing. Smile | :)

Thanks!

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.

you sir, you hacked life.

I'm brazilian and english (well, human languages in general) aren't my best skill, so, sorry by my english. (if you want we can speak in C# or VB.Net =p)

Just look at what you had before VBA. It was so much worse.

VBA:

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.

Message Closed

"I've seen more information on a frickin' sticky note!" - Dave Kreskowiak

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.

Jake Ginnivan also has a pretty nice library[^] that should help you with your COM headaches.

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 Big Grin | :-D

).

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]

Jonathan C Dickinson (C# Software Engineer)

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.

Da Bomb

Ah VBA! I love it, love it, love it!

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!!

Big Grin | :-D

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.

CHill60 wrote:
Ah VBA! I love it, love it, love it!

LOL! Yeah, sometimes the technology that some people hate can be a gold mine.

CB

I have profited by the scenario you describe many times but regardless of how well you are being paid, maintaining those business critical Excel "applications" is often like playing Twister with a bunch of long unwashed dudes who reek of garlic and stale sweat. You have to run home for a long shower with the stereo blasting in an attempt to purge the memory of the experience from your body and soul.

Welcome to the Lounge