Difference between VB.NET and VBA
This is a 7 pages summary comparison between Visual Basic for Application and Visual Basic .NET
What are the differences between the two languages? Are they really different? Is one is better than another? Which one is easier to learn? Could I program application in Excel with VB.NET ? If you are reading this, probably you are looking for an opinion. I’ll try to give you mine.
First of all, VBA is practically the same thing than VB6. The only main thing between VBA and VB6 is that your VBA editor can’t build a executable and any compiled file (DLL for example). On the other hand, VB6 could build stand-alone programs and compile files. That means the VB6 is a little bit more powerfull because the VB6 editor could give you more options. It could also manipulate multiples objects and applications.
Practical example, you would like a make a program the nicest looking and the most powerful program ever. If could achieve the same good result if you made it from VB6. The only problem is that you will simply spend more time to make your program. In other words, you could use the VBA editor from Excel or Word and create a video game. Only problem is that your video game will need Excel in background.
In the year 2000, Microsoft introduced Visual Basic .NET to replace the old VB6 (1998). One of the main reason why VB6 was replaced is because VB6 is not a realy object oriented language. VB6 was more like a advance C language. In that time, his main competitor was C++ (or Cpp) witch deliver a real object oriented programming.
What is object oriented language? Well to make this very simple, instead of working with basic variable such as String, Char, Integer, Long, … you could work with object. Objects could be anything you have in mind while you are programming. For example, house, a city, a street, a car, a country… each object contains multiples elementary variables, function and classes and make programming easier and faster.
I’ll give you 1 example: you have a MMORPG such as World of Tanks Online (who cares). Many players around the world are playing tank to kill each other (who cares). Let’s says you want o hit an opponent, in fact, you have to send information to the others players. In the old fashion way you will have to send a coordinate of the initial X-axes , a number for the initial Y-Axes, a number for the type of bullet, the vector of the velocity (direction and power), etc, etc. And you have to make a big TCP-IP protocol to acknowledge you communication. Don’t bother me, that game will have thousand of bugs. If you use a Object oriented language approaches, you will only need to send the object to the other computer making the transmission more easily. ALRIGHT, is just a “figure de style”.
For the programmer
Even if VBA and VB.NET are different, the difference is still not important for him. The basic syntax are between them are similar
Set an object
If you want to assign an object to another, you have to use the keyword SET in VBA.
Dim myObject As Object
Set myObject = aObject
In VB.NET, you won’t need to Keyword SET anymore.
Dim myObject As Object
myObject = aObject
In VBA, external references such as COM library could be added from the tools and References menu. Some DLL such as shell32, kernal32 and user32 could be easily be included in your code. In VB.NET, you could add the same COM references using the “Add References” from your VB.NET project. In VB.NET, you could add .NET libraries.
What is the most interesting thing is that you could give your VB.NET project multiples path to look for your references. So, in case a DLL is not available, the project will automatically look for it elsewhere.
COM object only
Interfacing with external COM object is time consuming and VBA could only work with COM object. You always have to look in Google for the function name and for the parameters because they are unmanaged libraries. Using .NET libraries will save you a lot of times you there integrate perfectly in any .NET project.
Also, VBA give you the chance to read COM libraries but you can’t create them. That means that your VBA code will not be encrypted inside a DLL file. Meaning is that your code is not protected.
I can’t confirm and we are in 2013. VBA and VB6 is only in 32 bit or less. That means that your program will only work for 32 bits processor. Even if you have a x64 processor, your application will most likely work in 32 bit more.
What will happen is that for any calculation, your computer will be limited to smaller number or smaller precision. With a 32 bit platform, you files are limited to a 2GB. Your memory could be limited to a theoretical value of 2^32 = 4GB. Not only saying that a x64 processor is faster than a 32 bits. Is even worse if your x64 need to downgrade variable in 32 bits format.
Please note that most programs that run VBA editor use a 32 bits format. Office 2010 introduces 32 bits and 64 bits versions.
Yet, I never saw a 64 bits version of Office 2010 in my whole life.
Properties all the same
One of them most interesting thing when you are passing from VBA to VB.NET is that all property names in Windows Forms or Userforms is practically the same.
In VBA, you offend have to switch from caption and text for many properties. Frame, UserForm, Label, CheckBox and CommandButton uses caption to display the description while TextBox ComboBox and ListBox uses Text. Radiobutton uses the property Value for his description.
In VB.NET, the property is the only property you need to use. This makes your work much simpler.
Array, table or collection
In VBA, maybe you array first item index is Zero. I said maybe because not every VBA work the same way for array or collection. In general circumstances, the first bound is zero and VB.NET work with index at zero. Be careful dealing with empty object. You may need some time to adapt your programming if you are using Array, collection (Microsoft.VisualBasic) and HashSet or List.
Inherits and inheritance
In VB.NET, you could make new class from existent class. For example, in a Windows Application Form, you could easily create a Form1 inherited from the System.Windows.Forms.Form control. That way you could re-use all the functions and properties from the basic form. You could re-write function from the parent Form for your child ones.
In VBA, you are very limited in inheritance. You are so limited that you almost always have to recreate every controls event if they all look the same. Without inheritance, you program takes times to customize.
Here is an example of inheritance. I made a new type of button from the basic button. That button glows when the mouse is hover or something like that. I put a lot of button inside a form. With the use of inheritance, I only need to set up a function a single time to make my button change color. Without and inheritance, I will have to put the same function for each button. The moment I forget one, my program will not have a button like the others.
Link: Inherits Button
This part is empty. L
In VBA, a variant is a kind of all-purpose type. It could a long, integer, short, string, bytes, structure, userform… is good if you need to debug to know the API you are working with. Unfortunately, using variant decrease performance in your code.
Operator Overloading (French : Surcharge d’opérateur et fonctions )
In VB.NET, you could overload binary operator and unary operators like in C++. That means that you could use and overloads le plus sign, the minus sign and many more to add or subtract other type. You could also use it of object.
Example: In VB.NET, you could do a time delay and you could also “upgrade” the time delay with any time format.
Error handling in VBA vs VB.NET
In VBA and VB6, you will use thing like:
+ On Error Resume Next
+ On Error Goto Somewhere
It was simple and maybe a lot of you loved it. It is very similar to assembly language for low level processors (Motorola, IBM, Texas Instruments, STMicroelectronics , Intel, AT&T, National Semiconductor, Atmel, …) . When an error occurs, you get something in the Err object. You could deal with the error and even reset it.
In VB.NET, you will only use the Try Catch and Finally statement. Is very different and like it or not, you won’t have the choice. The old On Error is simply not available in VB.NET
Editor more powerful (IDE)
The best VBA or VB6 editor is Visual Basic 6.0 enterprise edition. Unfortunately, that version is discontinued. It also includes a very basic SQL debugger and a very basic deployment tool. It also a fait good versioning for your projects files.
If you want to work with a good visual basic editor, I’ll say forget about the 2003 and 2005 version. They are not worth for it. Thing are getting good starting from 2008. All Visual Basic Express Edition or Visual Studio Express Edition gives you exactly the same programming. Beside the fact that non-express edition have a little bit more tools to help the programmer, the main difference is that express edition are made for home use. It means that you can’t distribute your programs made with any Express editions in exchange of money.
Framework on every Windows computer (compatibility guaranteed)
VB.NET comes with the .NET Framework and it gives addition security features not available in VBA.
I will be honest. Most Windows OS are not secure. Every user could read and write, delete files for create ones. Everyone in the world could access your computer in administrator mode and it was kind of stupid. That way, many guys pretended to be good in computing. This also reflects on programmers. Many programmers still work in a C approach (not OOP). They are mostly limited for example to 4 states for IO (in-out):
Read, write, Read Only, Hidden.
Link to read in Wikipedia: List of programming languages by type
Access Databse (no more Recordset)
In VB.NET, Data Access Object (DAO) is no longer supported. ActiveX® Data Objects (ADO) is still available but in backward compatibility. Here is a article I made to help people passing from VBA (or VB6) to VB.NET using ADO : Using the ADO and Recordset in VB.NET
In VB.NET, we now use ADO.NET. I invite you to read an old article from MSDN : Benefits of ADO.NET
Winsock replaced by Socket.net
I honestly have no ideas in my mind. VB6 use Winsock 6 for client-server communication. In VB.NET, all the stuff are in System.Socket .NET. I honestly have nothing to say between both of them except the fact Winsock 6 is replaced by Socket.NET.
VBA can’t compile, VB.NET do everything.
One of the main disadvantages is that your VBA code is 100% accessible to everyone. VBA files could be save inside an Office documents (Word , Excel…) or inside a regular text file with a BAS extension for Module, FRM for a Userform or CLS for a Module class. Your code is extremely vulnerable for modifications or alterations.
In VBA, when your code becomes big, you will notice that it will become very heavy to work with it. There is no way to work in team and it will be very difficult to isolate pieces of your VBA project with others.
In VB.NET, you could build binary files such as DLL. Your code will be encrypted inside your DLL’s and everyone or any applications will be able to use them. Your code is protected from any modifications.
DLL are also easy to copy from one computer to and other. If your VB.NET program is getting bigger, you could easily separate your VB.NET project into multiples functions, DLL’s, and EXE files. You could also make a setup program to help your distribute your application over your network. For instance, Inside Visual Studio express, there is a free and fair deployment tool perfect for local area network or medium wide network.
Code is not encrypted in dll or exe
Shell32, user32 and kernel32 are very popular DLL mostly written in C++ by Microsoft Windows. Those files evolved in the years but they still do the same functions. Those DLL’s are use by almost every application running inside Windows. It is also true for EXE such as shutdown.exe used but many programs if they need to reboot your computer after an upgrade.
Code Quality between VBA and VB.NET
You could achieve the same quality and reach the same reliability if we don’t consider the VBA limitations.
In practice, VBA is more accessible to la larger number of people, including programmer with small skill in programming. The large number of VBA forum and the huge number of small VBA application makes VBA a kind of cheap programming language. Also, Excel and general Office application make VBA very easy to program because their API is extremely learning forward. For example, Objects in Excel are virtually available anywhere. Options Strict is also off by default preventing the VBA code from crashing if the VBA programmer doesn’t declare type correctly. Most time, young VBA Programmer doesn’t care about conversion type or precision data lost.
In Visual Basic Dot NET, clean code is important. For example, parentheses will be added after calling a Sub or Function even if they are not taking any arguments. That way, they will be no mix between variables and methods. There are others example, but I can’t remember all of them.
I invite you to read my others post or share your comments.
Here are other interesting and popular posts: