VBA raw nerve

I can be a bit defensive over the state of my office. Mrs Smurf moans that its like a bomb site, which it is, but rather than fix it up I get all defensive, and suggest she could stay out.

So to Microsoft and their unprecedented publication of rebuttals to the rumour on VBAs demise. Touchy? defensive?

I’m sure most people know that .net is the future of programming for Microsoft, well actually the present too I guess. I even have a feeling that they have said long term Office automation will move to be .net based (anyone confirm? VSTA maybe? reference? MSDN?). What they have never done is define ‘long term’. Or even hinted at VBA’s End of Line.

They might be right in their claims that .net will be a genuine improvement over VBA. But they need to move their education efforts up a few gears if they are going to convince the ever increasing VBA user/developer community.

They have Visual Studio evangelists, but I don’t see any investment in VSTO evangelists targeting the VBA dev community. They need to recruit folks from the VBA community, the VS folks they have got have no experience of our work. I guess they have a new blog.

Personally I think VBA is much better than .net for people like us, because it was designed from the ground up for people like us. VSTO/.net is just a port of a web developers toolset graunched into Office. Our priorities are not its priorities. Neither are perfect of course, but VBA is the best fit in my opinion for what I do.

Its clear that MS are in a spot. I think they should invest in VBA (modern editor mainly – no breaking code changes) in Office 14, it IS a key feature after all. But I’m sure they won’t.

“Personally I think VBA is much better than .net for people like us, because it was designed from the ground up for people like us.”

What does “people like us” mean?

I don’t really get why you and “people like you” :) love VBA so much? Except that’s something you already know, what is better with VBA than .Net? Is there anything at all that is better in the VBA IDE than in Visual Studio? I’ve been working with both .net and VBA for a really long time and the only point where VBA is better than .net is that it’s so simple to get started and for really simple project it has some benefits. But for bigger projects (60-200 hours) VBA is just painful.

The best enhancement in InfoPath 2007 compared to other officeprograms in that it uses VSTA instead of VBA. A proper IDE, a good programming language and still the simplicity of VBA where the code is stored inside the document. I wish Word, Excel and PowerPoint worked the same way.

I think it’s kind of strange that office dev hang on to vba so much. When .net was launched and asp.net took over from asp classic (vb script) no one complained. For me, the transition from VBA to .Net is exacly the same. It’s a step in the right direction and it was actually one of the first things I looked for when I started office development 6 years ago.

I know you work a lot in C++ and Delphi, so I don’t include Mr Smurf in this, but I’m starting to think vba devs are a bit lazy and afraid to learn anything new. I don’t know if most office devs are end users that learned some vba and don’t have a programmer background. I don’t understand. What is so great about VBA and why hang on to it so much?

// The wierd office developer that actually like both .net, vsto and the ribbon. Please don’t kill me.

I really don’t care what the language is. I could as easily use Perl or Python as VBA, or I could get used to C[++]-like languages again. To me it’s the precise syntax requirements for working with Excel that matter. If y’all say C# is a PITA for the Excel OM, I believe you.

As for more general issues, the majority of VBA code I’ve written has been udfs and event handlers. In short, EXECUTION SPEED MATTERS MOST. From what I’ve read, .Net is slower than VBA. If so, it’d be unacceptable for me. OTOH, if .Net accepts 3D references (SheetA:SheetZ!X99:Y1000), which VBA doesn’t but XLLs do, that’d be a point in its favor (though not a decisive one).

As for the IDE, I prefer simplicity. I’ll admit it – I’m lost using VS. It’s easier for me to use vim and make. I wouldn’t view VS replacing VBE as a step forward, but I don’t write much UI code other than adding things to the Tools menu in the standard menu bar (which is another reason I don’t care for the ribbon) and the odd dialog (or odd dialogs depending on how you want to interpret that).

Johan
I’ll do a post in response to your questions
Why did .net take over so fast from .asp? because it server based – no deployment hassle.
At a language level, I agree with you, any modern (.net or other) language is nicer than the VB language. If you take a holistic view though the VBA system beats .net in so many ways.
Re lazy: I’m sure a few people from mainstream dev and office dev might fit in that description. One thing though is Office devs tend to have a ‘main’ profession that is not sw dev. They may be investing their personal development in that, rather than learning new languages that only offer them minor benefits.

A lot of the sample VSTO for Excel coding samples I’ve seen would not be used in the real world. The sample projects has a grid on top of Excel’s cells. This is so useless. Why use a gridview control when Excel it’s self is a grid?! The reason why business users want to see reports on Excel is so they can do their own calculations and sorts on the spreadsheet.

Hardcore devs use Excel like a design surface such as Windows Forms. They don’t get it because they don’t know how to use Excel as an application.

MacroMan: I couldn’t agree with you more about the totally worthless samples microsoft provided. I can’t see any point what so ever to put winforms controls on a spreadsheet or a word document.

But there are some really good features to, such as data binding to named ranges and list objects, storing data in data island inside the file and simple things like Change-events on named ranges. As for the GUI I think task panes are great for things like filtering data before you generate a worksheet from a database.

I find it kind of cool to mimic cool features in VBA that doesn’t exist, like simple things to create a good looking progress bar with labels, or a dropdown listbox with images, date-selector for cells and other cool GUI features that users are used to from “real” windows apps. But in most projects there isn’t budget for spending x extra hours for a gui feature that everyone just takes for granted. With .net, you have all these features in you toolbox so you can spend time on solving real business problems.

In a way I look at VBA dev the same way of web dev. There’s alot of pride in creating windows- och apple-like user interfaces without the rich toolset. Back in 1998 you where the coolest dev in the company when you created menus and toolbar with just dhtml. Or a fancy tree view controls with a bulleted list and a bunch of images to render the tree. 10 years later we still need bloated JavaScript frameworks and dhtml+css widgets to create a basic user interface that looks like a real app. VBA is kind of the same, except that there aren’t as much cool framework to have a vba form to look really vista-lika.

It’s like we’re stuck in Y2K and most VBA devs like it!

I wonder if there we’re some usegroups or fidonet with discussions of how VBA was bad and XLM4 and WordBasic was so good and Microsoft didn’t understand what users and devs wanted…

Harlan: “As for more general issues, the majority of VBA code I’ve written has been udfs and event handlers.”

Our projects differs ALOT! For a simple UDF or a BeforePrint-event handler VBA is great. But my projects are much bigger than that. I think Microsoft also have the bigger projects in there version. BI is the keyword now I think.

Simon:
“One thing though is Office devs tend to have a ‘main’ profession that is not sw dev”

But not for “real” spreadsheet developers. That’s what bugs me. the discussion is always about “code” because those driving the debate are “coders” – they love the stuff. Unfortunately Excel’s and the business’s processes it assists should be first and foremost, with the “code” simply part of an overall business solution. I know this isn’t politically correct to say but that’s how I look at it.

@Johan, ‘simple’ udfs? Ever written fourrier transforms using numerical integration over complex domains? FFTs aren’t sufficient when you’re also simulating parameter risk. Ever written Kalman filters to deal with arbitrary dimension matrices? Ever even written a B-spline routine? Would you even know what an eigenvector is?

‘Simple’ event handlers? Ever written any Excel application in which half the menu changes when different groups of worksheets are visible? Try doing that with the ribbon.

Your response is an exquisite example of exactly what’s wrong with ‘your kind’ – you have absolutely no clue what other people really do, but you’re more than ready to generalize your limited perspective and lack of experience as a guide for others.

@Dennis, I believe many mean a developer has ‘real experience’ when they once earned the bulk if not entirety of their income from working in the industries to which they now sell their development services. For example, once having approved or denied loans and now developing credit models for banks. Another way to put it, they learned business logic, often expressed in spreadsheet formulas, before they learned how to write code.

Clearly different people mean different things by the phrase ‘spreadsheet developer’. Myself, I infer from it someone with business experience completely separate from selling software development services. Seems you mean someone who includes spreadsheets as just one of the many tools used in the business software development process.

“Seems you mean someone who includes spreadsheets as just one of the many tools used in the business software development process.”

In the 90’s we could make a living by providing genuine and standalone Excel (I also shipped Lotus solutions in the early 90’s). Today the situation is different as we face challenges where we need to know and use other technologies as well (in order to make a living on it). At least in my part of the world.

Well, if we put it in terms of experience from any area that involves practical use of spreadsheets then I can, to some degree, accept ‘real spreadsheet developers’.

I really enjoy to explore .NET & VSTO & Excel but that does not imply, neither implicit or explicit, that I argue about that everyone else should/must ‘jump into’ it.

I’ve said this before, in our department, a business department, we are not allowed to have Visual Studio since we are not part of IT. How am I going to use VSTO?? Microsoft has to take this into account. Many Excel devs are a part of business departments. Many Excel devs are business people first and Excel devs second.

“we are not allowed to have Visual Studio since we are not part of IT”

In my experience, the IT departments in most companies handle the networks and servers and the infrastructure, but do not provide any programming support. Maybe my experience is narrow, but it seems to me forcing departments to rely on IT to provide such business-related support is shortsighted.

Shortsighted? Businesses? Next you’ll claim governments can be venal and bureaucrats can be, well, bureaucratic!

Where I’ve worked, IT is responsible for keeping the end-user PCs running, which includes making sure the software works, and that means prohibiting anything they haven’t approved. While they don’t do any application development themselves, they’re the only department permitted to contract with outside developers.

At one place I worked, for a while upon Windows bootup, the XLSTART directory was cleaned out. I couldn’t figure out where my macros were going, since I’d carefully added them to personal.xls the day before.

When I figured it out, I went to the boss and compained. He had no idea what I was rattling on about, but to his credit he called the head of the site’s IT department and got it fixed.

I didn’t remember that about Laroux, though I do remember the macro sheets it would insert. I guess cleaning the xlstart directory would have undone the damage done by one of the IT techs. This guy would circulate around the place with a stack of floppies supposedly containing various diagnostics, and he seemed to be motivated by trying to insert each floppy into every computer he visited. When he was fired for spreading viruses, I was vindicated in not giving him my boot password.

I started using a boot password a couple years earlier after I met Michael. He considered himself a helpful computer expert, and he would go around “optimizing” everyone’s desktops, usually sneaking onto unattended computers during lunchtime. After I heard from several coworkers about how Michael had screwed up their computers, I started locking down my PC. I couldn’t believe that nobody ever complained, though, they just placidly fixed up their PCs every week or two after Michael had struck. IMO, Michael was worse than Michelangelo.

I remember a particularly frustrating day spent getting Laroux off my work pc in the 90’s (last known VBA virus??).
The code was so badly written it was a real pita to unravel, I eventually worked out the combination of opening closing and deleting that stopped it spreading.
I still remember the dick that sent me it too.
We were specifically not allowed boot passwords so others could use our pcs, and mess with our settings.
Your Michael sounds very helpful (not), if there was someone floating round like that I think I would use a boot password too no matter what the rules said.

Harlan: “Ever written any Excel application in which half the menu changes when different groups of worksheets are visible? Try doing that with the ribbon.”

Sounds like a perfect fit for contextual tabs…. I would think that it’s alot easier to create a XML file for contextual tabs than to write alot of VBA code to create and delete or show/hide CommandBarXXX.

Since I haven’t worked with the ribbon, I don’t know. In VBA it can be done using table-driven code that’s only called when the user runs particular menu commands. No processing overhead.

From what little I’ve read about contextual tabs, they can be displayed when particular objects are selected, but that sounds to me like there’s some background processing drag checking what object is currently active. And maybe messy interaction between XML and VBA since a ribbon command in one context (one set of worksheets visible) would lead to a change in context (making a different set of worksheets visible).

Then there’s what the user sees. In the old menu, the top-level menu appears unchanged. Only the commands within it would change. Would top level ribbon tabs remain unchanged and only the commands/icons/whatever within them change as contexts change, or would one tab named XYZ disappear to be replaced by a different tab named XYZ as context (the particular visible worksheets) changes?

Johan: “I would think that it’s alot easier to create a XML file for contextual tabs….”

One would think that, but one would be wrong. The ribbon is pretty easy to program static tabs for, and you can do callbacks to make the tabs appear dynamic. But you cannot find out with VBA which tab is active, and you cannot tell the ribbon to make a particular tab active. So you cannot program your own contextual tabs.

Maybe it’s because I’m used to it, but it seems to me that dynamic UI changes are easier with Office 2003 command bars.

Harlan: The ribbon stays basically the same as workbooks are activated and deactivated. Customizations live in the context of whichever workbook they are attached to. Switching from one to another workbook changes which customizations are visible, so it’s the same XYZ tab, but with the potential for different controls on the tab. An add-in’s customizations are available for any workbook.

Jon: it’s different workSHEETs within the same workbook. For example, set A: 3 account summary worksheets, sets B-E: 5 product line detail worksheets in each set, set F: 6 overhead and markup worksheets all in a single workbook. There’s a top-level menu labeled Section under with are 6 commands, one each for the 6 different sections, and each section corresponds to one of these sets of worksheets. Running any of these menu entries causes that set of worksheets to become visible while all the others are hidden.

There’s another top-level menu labeled Actions (we’re a Lotus Notes shop, so everyone’s familiar with special purpose commands appearing under the Actions menu) with commands that change depending on which section is in use, i.e., which set of worksheets is visible. The VBA code that’s run when the user selects an entry in the Sections menu changes the commands that appear in the Actions menu. I suppose this code would alter what appears in the Add-Ins ribbon tab if left unchanged, but could contextual tabs be used to produce a dynamic Actions tab as the user changes sections?

Harlan: I know there are these callbacks that let you manipulate the ribbon on the fly, but I haven’t used them. Presumably you could have some kind of Actions tab or group (it’s hidden in Excel 2003, as I recall) which would update as you describe. I’m sure it would be ugly in the Add-Ins tab.

You can’t control whether the Actions tab is on top, and I haven’t yet figured out how to programmatically execute a ribbon control the way you can .Execute a commandbar control.