Over the weekend I've spent a significant amount of time working in Visual Studio 2005 with VB.net. The more I work with it, the more I like the IDE, and some of the cool things that I can do in it. This post, though, is going to focus on some of the differences between VBA and .Net that I'm playing with.
First thing to notice in importance is the existence of Option Strict. As VBA'ers, we're used to using Option Explicit (at least you should be), but Option Strict adds another dimension to your code. As I believe in following best practices, I am coding with both Option Explicit (OE) and Option Strict (OS) set to On.
It should also be noted that while I am quite proficient with VBA, I am certainly not an expert on VB.Net, and I am not trying to pass myself off as such. It's very likely that I will get some things wrong in this, and if I do, feel free to comment on the post. I'm doing this to learn, and blogging to share my experiences with those who have both gone before, and may choose to come after me on this road.
Okay, so here we go...
Early vs Late Binding
In VBA, we have the option of Early or Late binding. Generally, I develop using late binding, mainly because I work in an environment of mixed Office versions. There is nothing worse than having an Excel 2003 user open a file, have the references upgraded, and the 97 user can no longer get in. In VB.Net, with OS on, you must use Early binding.
In addition, OS demands very strict Type casting. I'm used to the following to create an instance of Word in VBA:
[vb]oWord = CreateObject("Word.Application") [/vb]
This does not work in VB.Net though, as "Option Strict On disallows implicit conversions from 'Object' to 'Microsoft.Office.Interop.Word.Application'."
Instead, we need to use the following to explicitly cast the Type:
[vb]'At the top of the module:
Imports Word = Microsoft.Office.Interop.Word
'In your procedure
oWord = CType(CreateObject("Word.Application"), Word.Application)[/vb]
Functions...
In VBA, we'd use our Right() function to return x number of characters from the right of a string. In VB.Net, we need to preface that with something more:
[vb]Strings.Right(text, chars)[/vb]
Type Casting
Probably the biggest issue that I've run into is casting types. VB.Net (OS on) will NOT convert data from one type to another. This is evident in the binding example above, as well as many other things. Fortunately Option Strict kicks up little error messages to tell you what's up, and after a bit you can generally make some sense of how to fix them. Usually it involves a converting something to a type that we're used to... CStr, CBool, etc... What really threw me, though, was how to type data as an Object. The secret is the Imports statement... For example, look at the MessageBox constants (more on this below).
With a msgbox, you can't just use the constants like vbYesno anymore. You need to use Microsoft.Office.Core.MsgBoxStyle.YesNo. Now that's a bit of a pain, but there is an easier way. Thanks to Mike Rosenblum, who explained this to me in great detail, you can do this:
At the top of your module, insert the following statement:
[vb]Imports Microsoft.Office.Core[/vb]
Now you can refer to that constant as:
[vb]MsgBoxStyle.YesNo[/vb]
MessageBox Constants
In addition to the issue with Type Casting above, the messagebox also changed a bit in that you don't add your options together in .Net as we did in VBA. Let's look at the differences:
[vb]'VBA version
Select Case MsgBox("Are you sure you want to do this?", _
vbYesNo + vbQuestion, "Are you sure?")
Case Is = vbYes
Confirm = True
Case Else
Confirm = False
End Select
'VB.Net Version
Select Case MsgBox("Are you sure you want to do this?", _
MsgBoxStyle.YesNo Or MsgBoxStyle.Question, "Are you sure?")
Case Is = vbYes
Confirm = True
Case Else
Confirm = False
End Select [/vb]
Notice that it is an Or statement, not a +. This seems weird, but I have tested it, and it does work. Seems counter intuitive, I'll grant, but what are you going to do.
Final Word
Well, that's my observations so far on language differences. There's way more, obviously, and I'm sure I'll share more as time goes along. 🙂
11 thoughts on “VBA vs VB.Net”
Hi Ken,
Casting is the thing I found/find the hardest thing to do. I am starting to get the hang of the language syntax and the frame work a bit more now - to the point where I can find out what I need to do without it taking 3 hours! Over xmas I knocked up a useful little app in about a day, which I was happy with.
The biggest issue now is deploying the thing!
I'm not sure about late binding so much, if your working with .Net unless your automating from a standard exe (or writing a class - hopefully when I get this technique nailed down I will make some posts about it!), you have to develop against a PAI/AI, so you effectively are early binding - although I think you can "get away" with going for the early version for some development endeavours – not best practice mind!.
And if you’re working with VSTO your coding against 2003 or 12 anyway...
There there’s the whole issue of App Domains!
Yep there sure is a lot to learn! Like you though I am finding it good fun 😉
Have you discovered system.Windows.Forms.MessageBox.Show(...) for a much more .NET implementation of the messagebox?
While I'm at it, you can do Right, Left and Mid in the .NET fashion by using the Substring Method of the string object.
Hi, cud you please tell me the advantages/Disadvantages of .net over vba? Why do I have to miragte to .net ? currently I can work in vba very quickly and make some money to keep me going.
Hi, cud you please tell me the advantages/Disadvantages of .net over vb6? Why do I have to miragte to .net ? currently I can work in vba very quickly and make some money to keep me going.
Hi Emma,
I'm going to see if I can get Dennis Walletin to weigh in on this as well, as he is a .NET guy, but I'll give you my first impressions on the .NET vs VBA side.
VBA is a solid development platform if all you want to do is build add-ins or files for use within the Office suite. (They can certainly manipulate non-Office components through VBA, but must run from within Office.) I see nothing wrong with this at all, and have certainly built some very good applications that work very well. It's easy to code in VBA, and the deployment is a snap.
Where VBA falls down in a big way is security. I'd never develop a commercial application in an Office format. Every level of protection in Excel can be quickly hacked, exposing all of your code for your competitor to steal, should they choose to do so. This is where either VB6 or .NET can really help you out.
VB6 and .NET can compile their code into applications that, while not impossible to hack, are certainly not as trivial to do so. They bring that level of security needed for professional software vendors. In addition, if your project requires a large amount of configuration on the PC, it can shift the burden from the end user to the developer. I can't say much more, as I haven't yet tried to deploy a .NET solution of my own, but from what I've seen, a properly built exe or msi file can easily bundle up all the setup for the users.
One thing that you may want to keep in mind, however, is that Microsoft is pushing towards .NET and VSTO for Office development. They have not upgraded or invested in VBA for many years. Now, they may reverse that position one day, or they may not. So are you left holding on to an old technology or embracing a new one?
Ultimately, I'd say it comes down to what you want to do. Regardless, my personal feeling is that you can't have too many programming languages in your toolbox.
Ken,
Great to see that You have started out with .NET.
# Option Strict
In some cases we are forced to turn it off. At present I'm working with the Domino Object class (Lotus Notes) which gives me some hard times where I finally ended up in turning it off.
Emma,
# VBA/VB6 vs VB.NET
In my opinion it's not a question about either VBA/VB6 or VB.NET.
What we can do with VB.NET can nearly almost be done with VBA/VB6 with the following exceptions (summarized):
The .NET Framework play a critical role in the new security paradigm that Windows Vista use, i e VB.NET fully support the security.
With VB.NET we can develop a richer UI and in my experience the overall developing time is also less then with VBA/VB6.
With VB.NET we can fully leverage the OOP-paradigm (Object Orientated Programming).
VB.NET offers a better integration with databases and provides us with ADO.NET as well as with LINQ (instead of string SQL expressions they are embedded in the language itself). VSTO offer a great feature in terms of Data Islands.
VSTO provides us with the option to detach and attach 'code behind the workbooks'.
The deployment model 'ClickOnce' which allow us to publish Windows-based applications to a Web server or network file share for simplified installation.
But .NET comes with the following 'costs':
.NET Framework must be available on the targeting computers. The PIAs (Primarily Interop Assemblies) must also be available. For VSTO solutions it also requires the VSTO loader to be available.
The PIAs are version specific, i e Office 2003 has its own PIAs which also Office 2007 has. If we target a mixed Office versions environment then we need either to develop versions specific solutions or use a third-party tool (Add-in Express .NET) to use version neutral IAs.
The deployment process is much more complicated then the 'one file model' and we need to be more knowledgeable when it comes to security according to MSFT.
I hope the above gives some inputs.
Kind regards,
Denns
One of the really neat things I've started playing with in VB.Net is the multi-threaded support found in system.threading library. With this, I'm able to take processes that, in VBA, are traditionally linear (i.e. a for next loop through an array) and divide it up a few times to be run concurrently by the processor on different threads (this has more then 1/2 the run-time of some of my more processor-intense macros).
You just have to remember that the same object cannot be accessed by different threads at the same time, or it can create some weird errors.
Cheers.
Wow. I just realized I resurrected a 3 year old thread.
LOL!
I'm okay with that, William. 🙂
Sounds interesting... I wish I had the time to continue playing in this area... I just seem to keep finding too many other parts of Excel to keep chasing!
Hi Ken,
I work pretty well with VBA and am interested in learning more! I was looking up information on VB.net when I came across your blog. It appears to be pretty old now, but I am wondering if anyone has any suggestions of where to start? I would like to learn but I am not certain of what literature to get and where to start the process? Any suggestions would be appreciated.
thank you.
Pingback: The Ken Puls (Excelguru) Blog » Blog Archive » A review of Add-in Express