As I’m sure most of you know, the future of VBA and VB.NET for Office Developers is still undecided. The Visual Studio Tools for Office has introduced Office to those that have adopted .NET, but has done nothing to introduce .NET to the traditional Office/VBA developer. VSTO has also unearthed some fundamental issues that effectively prevent us from controlling Excel from .NET reliably, which have yet to be fixed. I’m sure you’re also aware of the amazing synergy between VBA and Visual Basic 6 (aka ‘Classic VB’); many VB6 code snippets can be dropped into a VBA project and used without any changes, and it’s extremely easy to move VBA code into VB6 DLLs for better performance, improved security and better handling of class modules. Looking back, we can readily see that VBA has tended to follow in VB’s footsteps and it’s not difficult to predict that the same will continue into the future.
And that gives us all a very big problem.
When Microsoft introduced .NET, development of Classic VB stopped and the product entered the support phase of its lifecycle. Development of VBA stopped at the same time; Office 2000, XP and 2003 all have exactly the same VBA IDE. Right now, we’re all developing Office-based applications using a language and development environment that hasn’t changed in five years, and will probably never be updated. What is likely to happen is that VBA (and the VBA IDE) will continue to exist in its current state and Microsoft will introduce a new way for us to use .NET to program Office – perhaps using the Visual Studio.NET IDE, perhaps creating a brand new IDE just for Office. What is critical to us all is how that is done, such that we will be able to adopt .NET and use it alongside and integrated with the millions of lines of working VBA code we already have. If VBA follows the VB6 experience, we have a bleak future.
It’s almost unanimously agreed that Microsoft got it wrong when introducing VB.NET, by not providing an easy route for all the existing VB6 developers to start to include VB.NET into their applications. Microsoft effectively gave them an “all or nothing” choice – keep working in VB6 or rewrite your application in VB.NET. (Sure, they made an attempt at a code converter and provided the ability to interop between VB6 and VB.NET dlls, but both those options could only be used by a small fraction of VB6 developers).
What you might not know is that mainstream support for Visual Basic 6 ends on March 31. To mark this event, lots of MVPs past and present have put together a petition urging Microsoft to reconsider their past mistake and reintroduce ‘Classic VB’ as a mainstream language incorporated into Visual Studio, alongside VB.NET. They point out that (unmanaged) C++ coexists happily with (managed) C# and that by supporting both (unmanaged) Classic VB and (managed) VB.NET, Microsoft would finally be preserving all the investment their customers have made in their VB6 applications, provide an up to date language and IDE for maintaining those assets going forward and (perhaps most importantly) provide a platform for the gradual and managed migration of that VB6 code to VB.NET, where (and if) that makes sense.
If Classic VB is included in the Visual Studio IDE, it would be a very small step to also support VBA and thereby provide a clear path for us all to tread, allowing us to decide when, if and how to include .NET code in our Office-based applications. Microsoft already knows that an extremely high level of VBA/VB.NET interoperability is a ‘must have’ if they want their VBA customers to adopt .NET; the MVPs’ petition suggests a way in which that can be accomplished.
The Coca-Cola company corrected their big mistake by reintroducing ‘Classic Coke’. Please sign the petition to ask Microsoft to do the same, and give ‘Classic VB’, VBA and all our existing code a future.
Thanks
Stephen Bullen
Done and done.
This is intresting. Might one reason be that MS want to try and “force” users to switch to .NET?
Also if the path of XLM is anthing to go by MS might inculed backwards support for VBA in excel.
Stephen,
First of all, many thanks for the heads up on this issue, which is highly appreciated.
Second, it’s surprising how MSFT deal with it. In the past MSFT have always tried to secure backwards-compatibility but when it comes to .NET the strategy have apparently been changed.
From a technical point of view there are obviously some major obstacles that may make it impossible to let VB/VBA co-exist with .NET. But as long as MSFT does not inform we can only speculate…
The most important aspect is that many large worldwide companies have made huge investments in the VB/VBA-platform as well as many organisations within the public sectors.
Perhaps these large customers can “persuade” MSFT to re-consider their present stand.
Kind regards,
Dennis
Thanks for the heads-up on this! I immediately forwarded your web site to my colleagues and signed the petition.
This is, to put it mildly, an unalloyed catastrophe. While where I work we won’t be affected for the next year or so, within a couple of years we will be then facing, bar solutions from MSFT, massive reworking costs that simply wouldn’t be necessary if MSFT had thought this through.
We’re not a programming shop, but use Excel as a programming tool to get our jobs done: taking away VBA and replacing it with .NET is sort of like taking away a construction worker’s hammer and replacing it with a pneumatically driven nuclear-powered piledriver. That all we want to do is write relatively small snippets of code and a few loops to handle daily problems means that for us VBA is a nicely weighted and balanced hammer: from what I’ve seen (correct me if I’m wrong!), .NET is vast overkill for the relatively small, yet fiercely complex tasks we need it for. And we gotta learn how to do everything all over again.
The production processes of where I work are pumped through VBA pipes that can’t simply be replaced.
Or if they are replaced then not with anything from MSFT. If you’re gonna make me have to rebuild everything, then any argument *not* to use OOffice and a scripting language like Python or JavaScript to reproduce what we *need* to do – as opposed to what MSFT will force us to do – will be moot.
Can it be that MSFT is increasingly behaving like IBM of the bad days? Not good, not good at all…
John
As an intermediate VBA / ASP dabbler as small part of my real job, last year I investigated VB.NET / ASP.NET to see what they were about and to hopefully get a head start on a transition people even then talked of as inevitable.
After several months of frustration I eventually gave up in dispair. To get even the simplest task done in VB.NET takes weeks of learning; the help disks on VB.NET 2003 are on 3 cds and take half an hour to install. Forget trying to find the answer to a simple problem in there, or the communities for that matter, which I presume are so sparse and concerned with serious programming issues that no-one has time for those of us with the stupid questions.
For an English speaker, learning VBA is like learning French; VB.NET is like learning Cantonese.
Thanks for the comments. Just to clarify, I’m certain that VBA will be around and supported for many years to come – just like we can still write XLM macros in Excel 2003 if we want to. Without a positive outcome from this petition, however, I very much doubt if we’ll see any changes to it – we’ll still be using the same IDE that won’t have changed at all since Excel 2000. Hopefully, anything added to Excel will be exposed through its object model, so VBA will still be able to control it, but that’s something I’m much less certain of.
So yes, there are two issues:
(a) VB.NET is overkill for many (if not most) Office automation tasks
(b) For those tasks where it makes sense to use VB.NET, it’s extremely difficult to include it within an Office project.
Adding VB6 and VBA to the Visual Studio IDE and allowing them to interoperate with VB.NET at a low level would be an ideal resolution to both those issues.
“Adding VB6 and VBA to the Visual Studio IDE and allowing them to interoperate with VB.NET at a low level would be an ideal resolution to both those issues.”
…and boost the sale volume of VS.NET for MSFT.
Kind regards,
Dennis
A bit off subject but there seems to be varying flavors of .NET ranging from $200 – $2000. In view of all that has been discussed over this issue, I have been looking into the ‘upgrading’. But not sure which is which. =) I currently am using VB6.0 & VBA in Office 2000 – what would be the minimal ‘upgrade’ of VB6.0?
doco
Hi Doco
That depends on what you want to do :-). If you want to just see what it’s all about, you’re probably best with just the cheapest VB.NET you can find. If you’re hoping of hooking it up to Excel, don’t bother. Microsoft has only provided official interop assemblies (that handle the Excel.NET communication) for Excel 2002 and 2003 – so you’d need to upgrade Office too. Personally, I wouldn’t bother until (at least) Excel 12 and VS 2005 are released – sometime next year, I guess. If you’ve bought Pro Excel Dev, have a read of chapter 22 before spending any money (which highlights a few of the bigger issues that have yet to be resolved). If you haven’t bought it, find it in a bookstore and read it there.
Regards
Stephen Bullen
Graham said “For an English speaker, learning VBA is like learning French; VB.NET is like learning Cantonese.”
Boy, I know exactly what you mean. I’ve been, on and off, trying to learn VB.NET since it came out first. But I just don’t have the time and effort to make it work.
To play Devil’s Advocate here, if VB.NET is the future, and if MSFT allows VB / VBA to co-exist in .NET, do you believe that will help people to transition? I know if I was given the choice I’d quite happily carry on programming in VB in the new IDE and ignore the VB.NET side. I’m not a dedicated programmer, I use quite a lot of VBA in my work, making models for others to use, but it’s not my day job, and unless I get some serious help I don’t see me ever giving up VBA voluntarily. And I see think there is a large population out there using VBA similarly to me.
Mind you I said much the same when they dragged me screaming and kicking away from Lotus 123 :-)
ìFor an VBA speaker, learning VB.NET is like learning Klingon.î
TJM asks, if MSFT allows VB / VBA to co-exist in .NET, do you believe that will help people to transition?
I don’t think it will help people to transition, because ClassicVB and VB.Net are incompatible. I think it would delay the transition (especially if everyone is as resistant to this kind of change as I am).
I also think the petition has a snowball’s chance in hell of changing Microsoft’s attitude. But then, the Red Sox won the Series this year, so anything’s possible.
“Would it help people transition?”
Let’s assume for a moment that Microsoft ignore the petition and a future version of Office comes with two IDEs – the same one for VBA and a new one for .NET. Which IDE will you spend most of your time inside? Will you *ever* open the .NET IDE? What are the chances of you transitioning then?
On the other hand, let’s assume there is a single IDE that hosts both VBA and VB.NET, and allows them to easily interoperate. You’ll already be in an IDE that supports .NET, so if you had a slow day, you might possibly think “Show me this procedure in VB.NET” (using something like to existing VB snippet converter). If it’s a complicated procedure, you’re likely to be scared off, but if it’s a more simple procedure, your reaction is just as likely to be one of “Hey, it’s not actually that different”. Do that a few times and you’ll find yourself recognising more and more, and consequently be able to move more and more of your code to .NET (if that’s something you want to do).
Without a single IDE, traditional VBA devs will not move to VB.NET in any significant numbers. With a single IDE, they might.
ok, good points Stephen.
What i’ll add is 2 things:
If i can cut apples with a 7? knife, and all i eat is apples, I’m not going to buy an 12? knife. But if my kife is “busted” (lol!), and they only sell 12? knifes, well!
my other point is, say in 3 years, XML if the defecto standard, even for MS, and .NET is really good at XML, and VBA is not very good, then, I’ll learn .NET – i.e. if it adds!!!!! somthing that I NEED, well, then yes, I’ll invest the time.
? – no?
Can i ask the “older”/wiser, chaps around here a question?
what was the change from XLM to VBA like, and how “fundermental” is it different from VBA to .Net?
I think that the problem with migrating VBA to VB.Net is not .Net itself. The real trouble is the Excel Object Model, which is not very “.Net friendly” (and the PIA’s, which do not really help much).
For MSFT to develop a VBA/VB6 reverse-compatible version is actually kind of silly. They already have. (As much as I think is reasonable/possible anyway.) If one utilizes VB.Net with ‘Option Strict Off’, which I think is the default setting, and relies heavily on the Microsoft.VisualBasic Namespace then one can utilize code that looks all-but-identical to legacy VBA/VB6. All the usual commands can be used including such familiar commands. as LBound(), UBound(), IsDate(), etc…
As the programmer progresses (and it shouldn’t take but a matter of days or a few weeks), they will likely begin to leave some of the old “legacy crutches” behind and start dabbling in true VB.Net code, say using Array.Length() instead of UBound(), etc.
I think the key to a smooth transition requires two things that MSFT can and should provide. (But it does NOT include a sort of “specail VBA/VB6? language. This would only hold VBA’ers back.) Here is what I think that MSFT needs to do:
(1) The PIA’s need to be improved considerably. Currently they do not do much at all other than facilitate access by C# developers. It’s good that they did this, but since .Net is really an ‘Option Strict’ world, MSFT really should have gone further. For example, the return type of Range.Cells() be ‘As Excel.Range’ instead of ‘As Object’. This occurs with a wide number of Excel properties & methods. This is problematic in VBA/VB6 where IntelliSense is lost. It’s even more of a hassle in .Net, where under ‘Option Strict On’ an explicit cast is generally required, resulting in unnecessarily cumbersome code. Instead, the PIA’s, acting as a wrapper, could easily have contained this cast internally within the PIA. This would create a really nice help to the VB.Net developer, avoiding excessive use of CTyp() and DirectCast(). As it stands now, using the PIA’s is “recommended”, but the truth is, they really don’t do a whole heck of a lot unless one is working in C#.
In short: MSFT needs to provide a better set of PIA’s, which I don’t believe is hard to do at all. I have not gotten any sense that this is happening (a pity given the high “bang for the buck”), but my guess is that this will absolutely have to be addressed whenever MS Office itself is truly on a .Net platform.
(2) A solid .Net “Macro Recorder” would ease the fears of all VBA’ers. Using .Net is hard, it is. And a “Macro Recorder” is not a cure-all, but as well all know from using it in VBA, it usually is a strong step in the right direction. No matter how well you know the Excel Object Model, the Macro Recorder is still the VBA programmer’s best friend, in my opinion.
So if the VBA programmer is migrating to .Net and is intimidated… Well, I think that nothing would help bring one up to speed faster in .Net than to have a Macro Recorder that would generate the correct VB.Net code.
The code, by the way, would be so similar to existing VBA code that I think the VBA’er would be very pleasantly surprised. If using ‘Option Strict Off’ then the code would look essentially identical. If using ‘Option Strict On’ I would hope that the Macro Recorder would be aware of that fact and add the appropriate DirectCast() or CType() keywords as appropriate. (At least if the PIAís are not fixed/improved by then.)
Overall, I really would relax. .Net is something to look forward to, not to fear. The really good news is that .Net is of no use now for MS Office development, it really just makes things harder at the moment. So we have a long, long time to get used to it. When .Net arrives for MS Office, I think that VBA programmers will come up to speed rapidly, especially if MSFT provides a .Net Macro Recorder as an included tool, and hopefully an improved object model and/or PIA wrapper.
Mike Rosenblum
(Mike_R on xtremevbtalk.com forum)
Hi Ross,
The change from XLM to VBA was similar to the change from VBA to .NET, in that the only way to do it was to redesign and rewrite the application.
There were, however, lots of differences as well, some of them being:
– Very, very few people liked coding in XLM or found it easy; moving to VBA made things *much* easier to develop, test and understand.
– There were very few large-scale applications written in XLM, so rewriting them in VBA was not a big hurdle to get over.
– The change of XLM to VBA coincided with the massive advancement of Excel 5 over Excel 4, so most people wanted to redesign their apps anyway, to make use of all the new things Excel 5 provided.
– Significantly, VBA was a *very* different language to XLM, so there was no confusion about writing code that ‘should work’ but doesn’t (cf. VB.NET is just different enough to VBA to be extremely confusing and annoying).
– The move from XLM to VBA could be done incrementally, in that VBA could call individual (existing) XLM procedures. In .NET that is possible, but officially unsupported.
So the move from XLM to VBA was very similar to the move from ASP to ASP.NET – it is demonstrably better, easier and quicker to develop using ASP.NET rather than ASP, and the ASP community has largely welcomed the change.
VBA to VB.NET is not a natural transition. For this reason (among others) I recommend going the extra mile and considering C#, which so far hasn’t been mentioned in this thread. I did and I haven’t been back to VB.NET since.
P.S. Sorry, I can’t resist this one:
GL: “VB.NET is like learning Cantonese”
TJM: ” Boy, I know exactly what you mean. Iíve been, on and off, trying to learn VB.NET since it came out first”
I’m pretty sure Cantonese came out first. ;-)
Jamie.
SB “Letís assume for a moment that Microsoft ignore the petition and a future version of Office comes with two IDEs – the same one for VBA and a new one for .NET. Which IDE will you spend most of your time inside? Will you *ever* open the .NET IDE?”
I can see a parallel for MS Access users when MS started to push ADO and demote DAO e.g. they removed the default reference to DAO in the VBE, made the nice new Jet 4.0 features available only via ADO, etc. It seems that the MS Access community did not bite: ‘power’ users still favour DAO and I find myself having to explain to MS Access MVPs Jet 4.0 features that were introduced five years ago.
Jamie.
Ok Jamie,
Now you got me curious. I’ve tried learning C#, but it just seems so, mhm… unnatural I guess. I have written some things in VB.Net, and altough it’s a big step from VBA, at least the code seems to be in “english”.
Now… what’s so much better about C# over VB.Net that could convince me to learn Cantonese ? :-)
As you get better at VB.Net, you’ll find C# worth learning, at least well enough so that you can read it. The reason is that there a lot of articles, blogs and tutorials out there written in C# and it’s nice to be able to follow what they are saying.
However, for MS Office Development, using C# really is harder. C# does not recognize optional parameters and cannot use properties that take a parameter (only methods can take parameters in C#). The Primary Interop Assemblies (PIA’s) accommodate for this to some degree, but C# code is still quite cumbersome when working with MS Office.
VB.Net is generally more verbose than C#, but for interacting with MS Office it is the opposite and using C# results in clumsy code. Also, if one wishes to use Late Binding, this is supported natively in VB.Net with the Reflection code being generated invisibly behind the scenes. With C#, one would have to be a Master or Reflection, and this really is a very advanced topic.
Overall, I would absolutely learn VB.Net first. Reading and learning a bit of C# on the side is of some value (I’m glad that I know it a little) but a VBAer’s primary development environment should be VB.Net.
If one day your skill set improves in C# — and my then perhaps MSFT will have improved the Excel Object Model and/or PIA’s putting C# on to a more even footing with VB.Net — then, sure, one could switch over.
Currently, I would just stick with VBA/VB6. Moving up to VB.Net at this stage only creates hassles for the VBA developer. This should improve with the next version of VS Tools (which looks very promising) and I would imagine that MS Office 12 would have to change things for the better as well.
But for now, VB.Net really only makes MS Office development somewhat more difficult to use and C# only makes things harder still.
Mike
Hi Mike
“Currently, I would just stick with VBA/VB6. Moving up to VB.Net at this stage only creates hassles for the VBA developer. This should improve with the next version of VS Tools (which looks very promising) and I would imagine that MS Office 12 would have to change things for the better as well.
But for now, VB.Net really only makes MS Office development somewhat more difficult to use and C# only makes things harder still.”
My thoughts exactly (though I’m not as positive about VSTO 2005)!
Regards
Stephen Bullen
Hi Stephen,
Well from what I’ve seen of VSTO 2005, it seems to be a step in the right direction, a natural evolution. (But I’ve not personally kicked it around, I don’t even know if a Beta version that’s avail.?) From what I’ve seen, it allows Worksheets to act as a Designer, so you can drag-and-drop controls on to it and the code-behind is .Net. One can think of this as “VBA.Net”, in a way, and I would think it represents the coming evolution of what will ultimately be built into MS Office Applications.
But I guess that this is what the petition is arguing against!
But I really think that the problems that need to be fixed is not the IDE, nor the general .Net mentality. The VBA’er really can use VB.Net in an all-but-identical fashion to VBA if they want. They can still use Modules and never even use Class Modules if they wish. Virtually all code could look the same as in VBA. One can copy-paste almost any code from VBA and it will just run. Before long, the programmer won’t want to be using this VBA-styled coding anymore, but they absolutely could…
The problem, as I said, is with the PIA’s. Return-types need to be strong-typed as an appropriate return type, not ‘As Object’. There are also some other glitches, such as the fact that the Excel.Application events are not exposed to .Net unless the programmer manipulates the compiled CIL code directly by hand! (MSFT is well aware of this one… no worries.) Also, some For..Each enumerators seem to fail in .Net, I’m not sure why. For example, the Workbook.Names() collection is one and — more troubling — Range.Cells() is another. Instead, the programmer has to loop ‘For i = 1 to Range.Cells.Count()’ instead of using For..Each.
All this is fixable by MSFT without much trouble at all, so I honestly think that all of these issues should be fixed for us in the next version(s) which should be when the VBA community is ready to begin using it!
Until then, yeah, I really agree with you that VBA programmers should simply be patient for now and not try to jump in too early. MS Office is just not ready for .Net yet…
Mike
Hi Mike
Just for the record, the petition isn’t actually arguing *against* anything. It is asking for VB6 and VBA to be reintroduced to the marketplace, within the Visual Studio IDE and alongside VB.NET, to provide a future platform in which existing VBA code can be maintained and ultimately to make it easier for the non-programmers amongst us to adopt .NET (if they want to).
The problem with VSTO is that it’s built on top of the same Excel application interface that we use in VBA – there isn’t any code in Excel that says “Hey, I’d better tell the VS IDE to update it’s display”. The best that can be done is to have a quick-firing timer and code to work out if anything’s changed. To me, that’s a string-and-sticky-tape solution, waiting to become unstuck.
And while I agree that much of the object-manipulating code in VBA (i.e. macro recorder spit) can be (almost) cut and pasted to VB.NET, few of my more complex routines can!
Hey Stephen
>> “The problem with VSTO is that itís built on top of the same Excel application interface that we use in VBA – there isnít any code in Excel that says ìHey, Iíd better tell the VS IDE to update itís displayî. The best that can be done is to have a quick-firing timer and code to work out if anythingís changed…”
This sounds really bad… but I would not expect such an issue to exist in the final version? I’m not even quite sure I follow this… Are you saying that you need a Timer to keep firing Application.ScreenUpdating = True, or are you saying that the Worksheet_Change() event is not firing and so you must constantly check cell values using a Timer?
>> “And while I agree that much of the object-manipulating code in VBA (i.e. macro recorder spit) can be (almost) cut and pasted to VB.NET, few of my more complex routines can!”
Agreed… Even if 98% of the code is the same, this implies that one in 50 lines will fail. So a 10 line routine would have a 20% chance of failure. Harder/longer routines would fail at a higher rate. It is a problem.
But getting out that “last 2%” will be tough I think. It’s a worthy goal, but not all that easy from the “.Net” side of things. MSFT was highly cognizant of reverse-compatibility issues and I think have done a spectacular job of allowing legacy VBA coding style to still be viable within a vastly different environment. Of the “2%” that is failing, I would personally guess that the majority of the issues is due to failures in the PIA for Excel that MSFT is providing at this stage.
>> “Just for the record, the petition isnít actually arguing *against* anything. It is asking for VB6 and VBA to be reintroduced to the marketplace, within the Visual Studio IDE and alongside VB.NET, to provide a future platform in which existing VBA code can be maintained and ultimately to make it easier for the non-programmers amongst us to adopt .NET (if they want to).”
Yeah this really is an issue and I don’t know what the right solution is. I think there are actually two issues to this:
(1) Will VBA Projects from MS Office versions 11 and below even RUN in MS Office 12+?
(2) If VBA will run on MS Office 12+, will the VBA code be editable from within Office 12+?
That is, MSFT could provide a mechanism to allow VBA code to run as COM (.Net can run all COM routines 100% fine), but not necessarily allow VBA editing of the same within MS Office 12. To actually EDIT such VBA code, the VBA programmer would have to use a PC with MS Office 11 or lower.
But do we even know the answer to #1? I would certainly hope that legacy VBA code would at least still RUN, right?!
If VBA will at least run, then it would not seem hard to have a 2nd IDE as the Petition proposes, for VBA is essentially COM running in a Design-Time environment. Although, once we are talking about COM wrapped within .Net, I’m less sure that the ‘VBA Design Mode’ could be accommodated. I just don’t know enough about the issue. I suspect that it would either be exceptionally easy for MSFT to include the IDE (perhaps the run-time elements may comprise 95% of what is needed anyway?) or exceptionally difficult, perhaps given some sort of COM-.Net complexity that we just cannot have a clue about as outsiders…
It would be nice if an alternate, additional VBA IDE were provided. I would hope that MSFT would do so, if it is reasonable for them. But at a MINIMUM, what we really need is for the VBA to just RUN within the new versions. If we have to find Office 11 and lower to do our actual VBA editing, this, of course, would be less convenient, but it wouldn’t be “the end of the world” really.
So here’s the question: Is VBA even going to run?
Hi Mike
A simple example of the timer issue is that Excel doesn’t have a ‘New worksheet’ or ‘Worksheet Copied’ event. So if you create or copy a worksheet while inside the VS2005 IDE, the only way the IDE can respond to that (to create new code-behind classes) is to have a quick-firing timer that compares which sheets existed at each point. In Beta 1, that is a (very) noticable delay!
I don’t think we need to be worried about whether VBA will run, or can be edited in Office 12. That can be achieved by simply doing nothing (other than testing), and removing it has an extremely big financial downside (of people simply not upgrading).
Regards
Stephen Bullen
Hi Stephen,
Ah, ok, I see the updating issue now… But a timer should be able to run on a 1/10th of a second interval (or less) without any trouble. My guess for the delay then is either (1) the creation of the “code behind” actually takes some time, or (2) there is some sort of error handling code within their routine that does all this.
The single biggest weakness of .Net is that error handling is shockingly slow. So any sort of code that says “Try this, but On Error do that” will slow dramatically. Anything that flows into the Catch section (even if handled cleanly) is subject to a 1/2 second to a full second delay. It’s stunning (and disheartening).
It is possible that they have some sort of error handling going on and they should try to use pre-test conditions instead of relying on a Try-Catch block.
>> “I don’t think we need to be worried about whether VBA will run, or can be edited in Office 12.”
I’m not sure I understand… Let’s assume that VBA, which is essentially COM run as P-Code cannot function in Excel whenever Excel is truly on a .Net platform. (Let’s call this Excel 12+, even if it’s not “truly” there until 13 or so…) Then this is an enormous issue, no?
(1) Existing XLA’s would then have to be converted to be COM DLL’s using VB6. (.Net can run legacy COM DLL’s 100% cleanly.) For some that’s not a big problem, but what percentage of VBA programmers even have VB6?
(2) And most VBA are really Workbook-solutions, code to help a given Worksheet or Workbook have some extra functionality. If the VBA won’t run, then MSFT will have a hard time convincing anyone to upgrade. Of course, Dell will start shipping PC’s with the newest version built in anyway, and then we’ll start to develop a serious rift…
Obviously, MSFT is aware of this… and I would imagine if it is at all possible that VBA embedded in Workbooks would still have to run. I really hope so anyway!
But following up …
Given the more reasonable assumption that legacy VBA will at least RUN within MS Office 12+, then I really do think that we are fine.
The Excel PIA’s need to be improved, the bugs need to be knocked out of VSTO 2005, but VB.Net itself has reverse-compatibility in mind to an enormous degree. It can run native COM DLL’s through the Interop flawlessly. Even OCX’s are generally 100% fine.
As for coding style, if one doesn’t like to use myVar.ToString(“C2?) then one can still call Format(myVar, “$#,###.00?). It’s all up to you. I can’t think off-hand of any legacy command that has been left out. (There must be one or two I can’t think of, but if it could be done, it was done.)
So the language and interoperability really are being addressed in my opinion. That the PIA’s and VSTO Beta have bugs is annoying, but it is not surprising. These are still ìearly daysî and these aren’t full release versions, after all.
I have faith in MSFT that they will get it right. They usually do…
Hi Mike
I guess we’re going to have to agree to disagree on how easy it will be to transition from VBA to .NET, or whether the majority of existing VBA developers are going to want to or be able to.
FWIW, I don’t think we’ll see Excel being “truly on a .NET platform” any time soon, which is why I believe in the longevity of VBA in Excel.
Pesonally, I don’t think Microsoft got it right for VB6->VB.NET and don’t see any evidence of a them using a different strategy for VBA->.NET (hence my support for this petition).
Regards
Stephen Bullen
Hi Stephen,
Ok, fair enough, I certainly hope that MSFT makes this transition as smooth as possible for the VBA programming community, which is both loyal and vast.
If MS Office 12 is still COM-based, as you suggest, then .Net and VS Tools remain the appendage. This gives MSFT more time to “get it right” and also gives us more time to get used to it.
In as much as MSFT complies with the petition, I would hope that they would spend their time “getting out the last 2%”. That is, I believe that the correct goal is to get .Net completely legacy-compatible with VBA. I believe they are close, but I hope that they not give up on getting it to 100%.
Mike
Juan Pablo,
In case you are still curious, Mike R is spot on. I do find VB too verbose, although I equally take his point about all those Type.Missing’s. I guess I must use the Excel object model less (I’ve never liked using named arguments) than ‘regular’ code because C# still appeals to me.
Jamie.
” and itís extremely easy to move VBA code into VB6 DLLs for better performance, improved security and better handling of class modules.”
This is a very interesting topic! What about giving an example or two on how to do it?
I do have VB6, and have used it to help interact with databases. However, I have a long way to go before I will be reasonably competent.
Thanks!
Hi Michael
It’s a little too big a topic for a blog post, but there’s an entire chapter about it and worked example in our book, Professional Excel Development. You might like to read that chapter in your local bookstore, or maybe buy the book ;-)!
FWIW, we use a simple example of a ‘Paste Special’ commandbar, written initially as a standard Excel addin in VBA, then as a VB6 COM Addin, then as a VB.NET VSTO solution. By doing so, we (hope to) show that the main differences between the three are all to do with how the code is loaded and started, rather than the code itself.
Regards
Stephen Bullen
Coincidentally, I was making heavy use of chapters 20 & 21 yesterday. I’m writing a VB program to do some installation tasks, all of which at one time or another I’d done in Excel, so I had the Excel code. I was developing a quote for a client who liked VBA but wanted something more secure, so I proposed an ActiveX DLL. And I built a VB preloader to do some housekeeping before launching an Access DB.
Last weekend I was at a two day MSDN event called Code Camp. It was heavy into .Net, and chapters 22 & 23 on VSTO and XML/Web Services gave me some insight into the sessions, and gave me some counters to all the rose-colored pronouncements.
Over the past month, I’ve been relying heavily on chapter 4 and 6 on Worksheet Design and Dictator Apps. You’d think that after 13 years of using Excel I’d know a bit about worksheet design, and I do. But there are some techniques I haven’t even known about.
There are other chapters I haven’t used much, but I know I will. It’s the single most useful Excel book I have. It vertically integrates nicely with all of the breadth of John Walkenbach’s books, and goes one rung higher.
Buy the book.
Thanks, Jon. Any chance of adding that to the book’s page on Amazon.com?
Stephen,
I did get your book, and I am slowly trying to read it. I am currently re-reading the chapter on worksheet design – lots of good stuff there!
A question for Stephen Bullen. (I just ordered the book by the way and can’t wait for it to arrive1). Assuming that one is a VBA developer, has accepted that the writing is on the wall for that language and has (reluctantly) decided to learn VB.Net will I still be able to manipulate Office applications to the same degree and with same ease as with VBA. I develop utilities for Excel, Access and Word. Thanks
Lyle Green
Hi Lyle
Yes and no . It very much depends on your situation and what you do with which apps. If most of what you do is ‘joining the dots’ of the Office object model, there is very little difference between the VB.NET and VBA code (you just need to be more explicit in the way you refer to variables, e.g. using Excel.xlPaperSizes.xlA4 instead of just xlA4). There are lots of little gotchas (such as not being able to For…Each through the cells of a Range object), but yes, you can manipulate Office to almost the same degree and with almost the same ease as with VBA. At the moment, the ‘hooks’ that allow you to attach VB.NET code to documents only works for Excel and Word, not Access. None of the ‘On…’ (such as OnKey, OnAction etc) are available, nor are the special procedure names in Word (such as Sub FileNew). Excel automation also suffers from the major problem of stopping working if you change regional settings, and trying to share your code with others (particularly those not connected to the same network) is quite a bit harder than VBA.
The preceding is, of course, talking about Office 2003 Professional and the Visual Studio Tools for Office for the code-behind bit, and really only applies for new developments. While it is possible to extend VBA-based applications with VB.NET code by creating a hybrid solution, that scenario is officially unsupported by Microsoft.
So far, Microsoft’s focus has been on making Office available to those that have adopted .NET, rather than making .NET integrate well with Office and existing VBA-based applications. I’m hoping than Office 12 will see some enhancements that make .NET approachable by the non-programmers that make up most of the typical Office developer base – including having a single IDE that brings .NET and VBA together.
Thank-you very much for your answer Stephen. I’m both pleased and somewhat dismayed by your answer. Not being able to code in Access is a downer, but if I read some of your earlier comments I may not have to worry about that until at least Office 13 or possibly 14 is released and VBA is killed off for good! I’ll hopefully be retired by then. Best wishes, I’m looking forward to reading your book.
I really like the way code is stored in an XLS file.
I can just copy the single XLS around and know it’s going to work, no installation hassles.
Ideally, .net source code would be stored the same – within the XLS file.
Does anyone know (or have an educated guess) of how this will work with later versions of Excel?
Lyle, you can still use .NET with Access, by creating COM Classes in .NET that are instantiated and used from your VBA code. My comment was that Access doesn’t have an automatic way of associating .NET code with your database that is loaded and run when the database is opened (ala VSTO).
Rob, whenever this issue was raised in respect to VSTO, the reply has always been that having code contained within a document/workbook is seen as a security risk. So I doubt if we’ll be able to embed .NET code within our workbooks. As for VBA, Microsoft’s unwillingness to touch anything to do with VBA might actually work for us in this case, as it would involve some work to split the VBA project out of the documents.
Regards
Stephen Bullen
Stephen,
This all seems to be a big negative.
On one side, MS have suggested that we not use VBA any more because it has no future (what was wrong with it anyway?)
On the other, they have this replacement with the promise of a “edit and continue” in v2 – oh, and BTW… it only works when you upgrade all of your clients to the latest version of Office, half of the Excel object model is gone and you can’t embed code in your documents anymore.
I’m already developing solutions outside of Excel in VS.NET C#. It’s a great tool but I’m *really* confused as to where Excel VB is going.
I think when Excel’s Macro Recorder moves to .net, that’s when I will too.
Microsoft Excel VBA and a great new book
I have exchanged a number of emails over the past couple of years with Stephen Bullen. He and I met up…
Joel on Software
ISBN: 1590593898
http://www.joelonsoftware.com
Here Joel ( by the way the person who specified the VBA for Excel ) describes in one of the chapters why VBA is dead for good.
Microsoft just need to push new things to make money. If they did not break this, no one would be upgrading since the tools today are just fine…
Investements of customers in existing VBA code – hmmm, I don¥t think that this interests Microsoft a bit.
The change goes as far as WinAPI will be replaced by WinFX => EVERYTHING has changed.
I have finished the book over the weekend and it was very much worth reading.
It’s true that Microsoft needs to publish new software to make money, however there is still the ability to publish new versions of “Classic VB” and make money. For instance, having classic VB compile to 64-bit is a worthwhile incentive. I’ll be the first to admit that the .NET CRL opens a lot of power but there are cases where Classic VB provides exactly what you need in a simple fashion. If I were Microsoft, I’d continue the classic VB line alongside VB.NET and use classic VB as a segway to the power of .NET. Personally, I have yet to run into any task I haven’t been able to accomplish with Classic VB and I’m assuming I’m not alone. I will only start to run into tasks I can’t accomplish because Microsoft has quit supporting components I may need in the future as they develop other product lines and integrate them (sharepoint, etc).
How can I assign a command Button to run a macro or a VBA code
Omar –
This is one in a series of VBA How-To articles on my blog:
How To: Assign a Macro to a Toolbar or Menu