Wednesday, April 24, 2013

Difference between VB.NET and VBA

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



Adding references

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.

References PathsWhat 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.

X86 only

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.

Function overloading


This part is empty. L

Variant type


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.


The End

I invite you to read my others post or share your comments.
Here are other interesting and popular posts:


References:



The program I love to use, buy it: Visual Studio 2010 Professional (Old Version)


SQL Server Developer Edition 2012 and Microsoft Visual Studio Pro 2012 are very cheap on Amazon ! Free Delivery. Check here:
  

Monday, April 22, 2013

Folder Renamer

Folder Renamer

With source code in visual basic. Free to use.


hyphen
Last afternoon, I when to a little party for my friend’s wife in the restaurant: Pacini at Sainte-Julie in the Québec Province. I take a lot of picture with my camera from Canon. Using the ZoomBrowser Ex from Canon, I could easily import all the pictures I want in my computer. The program automatically creates folder and sob folder based on the year, the month, the day and a description. Cool! But the problem is the date separator. All the folders created with the Canon’s ZoomBrowser Ex uses underscore “_” and I want to use hyphen “-”. I checked everywhere in that software I haven’t found any option related to the separator. What happens if I have a lot of pictures in a lot of folder? I have to rename then one by one? No way!
Honestly, I love my little camera from Canon and I like to use the importer for years, but I never thought I could program a little program to rename my folder the way I would like them.  Today, I decided to program a little application to help me rename all the folders under my Pictures folders. That way, I won’t have to look for a folder because they are sorted differently because of the underscore versus hyphen.



For those who want to download my program, there is no problem. The program is free to use. Otherwise, I will take one moment to explain the code.

form1

To make a program that renames all my folders under my Pictures Folder, I used a few objects:

  1. FolderBrowserDialog
  2. TextBox
  3. Button
  4. Checkbox
  5. Form


I think that is all.

I also use my same find files and find directory from one of my previous post: search file - Improve performance
I slightly change it to handle sub-folders. I also use two TextBox to get the character to find and another to replace. Here is a piece of code written in Visual Basic:


For Each foundDirectory As String In My.Computer.FileSystem.GetDirectories(dirPath, FileIO.SearchOption.SearchTopLevelOnly, String_to_Search)

               Dim aArray() As String = foundDirectory.Split("\")
                Dim sFolder1 As String = aArray(aArray.Length - 1)
                Dim sFolder2 As String = sFolder1.Replace(TextBox2.Text, TextBox3.Text)
                My.Computer.FileSystem.RenameDirectory(foundDirectory,
sFolder2)

            Next


Yes I know, I declare the variable inside the FOR loop and I know is not a good practice. Sorry, I only want to show something quickly and I wanted to preserve one of the previous codes.
I use the “My.Computer.FileSystem.RenameDirectory” method to rename each directory that contains the little keyword. That keyword is in TextBox2. I replace it with the character inside TextBox3.

I also use the method “My.Computer.FileSystem.GetDirectories” to easily retrieve all folders that matches TextBox2.

renamed
Of course, I made this quickly; there is not enough robustness in the programming. We have to check if the path doesn’t exceed the system-defined maximum length. You could read this interesting article: Long Paths in .NET, Part 1 of 3 [Kim Hamilton]. Also have to make sure the inside TextBox1, it doesn’t contain any invalid path, empty path or anything with invalid char such as the semi- colon “:”. You also have to make sure TextBox1 is not a relative path that starts with “\\” or “\”.
Inside the code, I also put very basic permission verification. You have to put more stuff to make the program better. (I haven’t).



Reference:

Project with code sample : ReplaceCharInFolder.zip
Download the program from this page : Folder Renamer Download

http://www.pacini.ca/qc/



SQL Server Developer Edition 2012 and Microsoft Visual Studio Pro 2012 are very cheap on Amazon ! Free Delivery. Check here:
  




Download Free Folder Renamer for Windows

Download Folder Renamer for Windows


Thank you for your interest in Folder Renamer.

Description


100% Freeware from Check Technologies
Folder Renamer is a freeware for personal, educational, charity, government, and commercial use. This program is very if you wish to massively rename folders

System Requirements


Windows XP, Windows 7, Windows 8



Instructions for downloading Folder Renamer


Click the Download Now button above.
Please note: You may have a warning from your User Access Control and your anti-virus program.

Friday, April 19, 2013

How to extract icon from file

How to extract icon from file

Example of code to extract all icons from a file using shell32


Extract icons from filesHow could you extract all the icons from a DLL or a file using .NET? is simple if you use shell32.dll . Sometimes, the framework .NET can’t give you what you need, so you have to rely on older techniques.

Here is a program where you could load a file and extract all the icons in two ImageList. The first is for small icons and the seconds for larger ones. All those icons will be injected in a ListView. This program is free and you could easily view all the icons from any files. Pretty cool!
Here is the code written in VB.NET easily convertible in C#.

Thursday, April 18, 2013

Using the ADO and Recordset in VB.NET

Using the ADO and Recordset in VB.NET


Everything you need to know on Recordset with Visual Basic 2010


For VB6 or VBA programmers, this article might be a good summary on how to connect and retrieve data from a MS-Access File. You might notice that objects you used in the past such as RecordSet, ADODC, ADODB, Microsoft.Jet.OLEDB, CursorLocation, adUserServer, adUseClient… might not work as well in Visual Studio .NET (Visual Basic 2005, 2008, 2010, 2012…)

There are many reasons why calling ADODB might not work correctly. The simplest and the shortest way to say it is that ADODB relies on older technologies. It uses the old COM (Component Object Model) and old OLE DB and ODBC stuff created many years ago. COM library are still used but it might be less cost efficient these modern days. Also, all ADO and ODBC stuff where removed from every default Windows 7 installation.

Here is a sample made in VBA-VB6 in Excel 2010. It is a simple UserForm1 that read a MS-Access File. Make sure you added a reference to Microsoft ActiveX Data Object Library.

'ajouter en référence Microsoft ActiveX Data Object (ADO) n'importe quelle numéro de version




Private Sub CommandButton1_Click()
    TEST(TextBox1.Value)
End Sub

Private Sub CommandButton2_Click()
    If Len(TextBox1.Value) > 0 Then
        TEST(TextBox1.Value)
    Else
        MsgBox("you must put a path for the MS-Access File")
    End If

End Sub

Private Sub Frame1_Click()

End Sub

Private Sub UserForm_Click()

End Sub


'''
' Check-Kay Wong
'--
'Pour utiliser les vieilles technologies
'et  importer des base de données style MDB avec Excel.
'----
Public Sub TEST(Optional sPath As String)

    Dim index1 As Integer
    Dim aString As String
    Dim oConnection As ADODB.Connection
    Dim oCommand As ADODB.Command
    Dim oRecordset As ADODB.Recordset

    On Error GoTo ErrorHandler

    oConnection = New ADODB.Connection
    With oConnection
        .Provider = "Microsoft.Jet.OLEDB.4.0;"
        .ConnectionTimeout = 30
        .IsolationLevel = adXactIsolated
        .Mode = adModeReadWrite
        .Open("Data Source=" & sPath & " ;User Id=Admin; Password=")
    End With

    oRecordset = New ADODB.Recordset
    With oRecordset
        .CursorLocation = adUseClient
        .LockType = adLockBatchOptimistic
        .CursorType = adOpenStatic
        .CacheSize = 30
        .Source = "SELECT * From dictionary"
        .ActiveConnection = oConnection
        '    Set .ActiveCommand = oCommand
        .Open, , , , adCmdText
    End With


    oRecordset.MoveFirst()
    index1 = 0
    Do
        aString = ""

        For index1 = 0 To oRecordset.Fields.Count - 1 Step 1
            aString = aString & oRecordset.Fields(index1).Value & vbCrLf
        Next index1

        MsgBox(aString)

        oRecordset.MoveNext()
    Loop Until oRecordset.EOF = True

ErrorHandler:
    If Err.Number <> 0 Then
        MsgBox(Err.Description)
    End If


End Sub





Has you can’t see, nothing very special because this is a sample. It starts with some basic Error Handling, make a Connection, get a RecordSet from a String and navigate thought the RecordSet line by line. It is pretty basic.

Now here is the something similar in VB.NET hoping the transition from VB6 to VB.NET is done nice and sweet.


    Private Sub Load_SQL_MDB(ByVal sPath As String, sTable As String)
        Dim oOleDbConnection As System.Data.OleDb.OleDbConnection
        Dim oOleDbCommand As OleDb.OleDbCommand
        Dim oOleDbDataReader As OleDb.OleDbDataReader
        Dim sqlText As String
        Dim index1 As Integer

        Try
            index1 = 0
            'ConnectionString
            oOleDbConnection = New System.Data.OleDb.OleDbConnection()
            oOleDbConnection.ConnectionString = "Provider =Microsoft.ACE.OLEDB.12.0; Data Source =" & sPath & "; Persist Security Info =False;"
            oOleDbConnection.Open() 'ATTENTION, FONTIONNE QUE POUR X86 ; ATTENTION ONLY FOR x86


            If oOleDbConnection.State = ConnectionState.Open Then
                sqlText = "select * from " & sTable
                oOleDbCommand = New System.Data.OleDb.OleDbCommand(sqlText, oOleDbConnection)
                oOleDbDataReader = oOleDbCommand.ExecuteReader()
                'oOleDbDataReader.FieldCount
                Do While oOleDbDataReader.Read()
                    'oOleDbDataReader.FieldCount
                    Dim str(oOleDbDataReader.FieldCount - 1) As String
                    For index1 = 0 To oOleDbDataReader.FieldCount - 1 Step 1
                        str(index1) = oOleDbDataReader.GetValue(index1)
                    Next index1

                    Me.DataGridView1.Rows.Add(str)
                    'ComboBox1.Items.Insert(index1, oOleDbDataReader.GetValue(0))
                    index1 = index1 + 1
                Loop

            End If


            oOleDbDataReader = Nothing
            oOleDbCommand = Nothing
        Catch ex As Exception

        End Try
    End Sub


The VB6-VBA code looks pretty the same with the VB.NET code. Is not the most popular way to get information from a MDB file, but it will work. If you run the code, you will be able to load a MDN file and to display some stuff in the little table DataGridView1.
Now, why this way is not popular? Lets put it this way. The old fashion way, you need to build a SQL sentence to get all the data. If you make a syntax error, well, you are screwed. The new fashion way use methods and object to get your data. You could take a look the MSDN article 315974  for more information. Please note that the last update on that article was in November 2007.

Personally, I still half way between the old VB6 and VB.NET. I simply prefer to use old command string to open a DataRecord. Shame on me!



The Excel File and the VB.NET project are available to download or to test.

x86 in Visual StudioThe 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.
If you have this message, that means you VB.NET project is un “any CPU” or running on x64. You have to run your project in x86. Sorry, old technology.



Download the project  : OpenMDBSample.zip

References:



The program I love to use, buy it: Visual Studio 2010 Professional (Old Version)

Friday, April 12, 2013

How to shut down Windows 8 ?

How to shut down Windows 8 ?

You need to know how to turn off your Windows 8 but you don’t know how ?  Here are a few simple way to turn off your Windows 8.

The stupid way

Go to the search feature under the setting search. Type: Turn, Shut or off in the textbox. Yes, this is a strange idea from Microsoft.

How to shut down Windows 8 ?

Download Shut Down 8 for Windows 8

Download Shut Down 8 for Windows 8


Thank you for your interest in the Shut Down 8.

Description


100% Freeware from Check Technologies
Shut Down 8 is a freeware for personal, educational, charity, government, and commercial use. This program is very useful to turn off, restart, log off and sleep your computer.

Shut Down Windows 8

Right click your desktop to bring the Shut Down 8 menu:

Shut Down 8 for Windows 8

Double click the Shut Down 8 shortcut on your desktop

Shut Down 8 for Windows 8
Easily find Shut Down 8 from the search application from Windows 8.

Shut Down 8 for Windows 8

Select between Shut Down, Restart, Log Off and Sleep. Easy to use.

System Requirements


  • Windows 8
  • Windows 7
  • Windows Vista
  • Windows XP.
  • Both 32-bit and 64-bit versions of Windows are supported.


 Instructions for downloading Shut Down 8

Click the Download Now button above.

When you see the file download, select Run, then follow the steps presented in the installation. Alternatively, you may also select Save, and then run the setup.exe to install Shut Down 8.

Please note: You may have a warning from your User Access Control and your anti-virus program. The software needs to obtain an Authentication Certificate from a valid authority and is pretty expensive.

Friday, April 5, 2013

Example on AcceptTcpClient method in Visual Basic

Example on AcceptTcpClient method in Visual Basic


AcceptTcpClient VB.NET


When you create a TCPClient, is not enough to start your server, you also need to accept incoming connection. In a previous post, I explained in 3 steps how to make a client and a server but nothing more: Tutorial client server using Visual Basic.
You will have to use the AcceptTcpClient method to safely pick up any inbound connection (server side).
If you don’t use AcceptTCPClient or any equivalent, no communication will start. Is like trying to call for someone and never be able to speak with the person you wish to speak. The phone is ringing and no one is there.


Wednesday, April 3, 2013

Tutorial client server using Visual Basic

Tutorial client server using Visual Basic


Client/Server in VB.NET


are you ready to connect to your server? (y/n)
This is a tutorial for beginner or intermediate programmers. This will show you how communication between two computers works using a client server scheme. With Visual Basic as a programming language, you will be able to get the few concept of a classic TCP/IP data communication.

There are many samples on the internet. Some are written in Java or C++.  Some applies for mobiles phones and other uses older technologies such as Winsock. In all case, I wasn’t satisfied because they are either to complex or incomplete.

This tutorial will be only a beginning for those who want to start to do a client server application using Visual Studio: Visual Basic .NET (VB.NET), C# .NET . This tutorial will contain many samples and many references to help learn the main concept of a client server application for Windows. I will slowly illustrate very small code and tiny code sample in the first pages of this article. I will eventually end up with a more complete program if I have the time.