I'm starting a new job - my first - as a financial controller. The job will mean working a lot with excel files, for example formatting a document so that it can be imported and understood by other financial programs like SAP, or creating charts with the data in a document. There might be many specific tasks where vb.net/vba can come useful so I would really to be good at it.
My question is, should I do vb.net through visual studio or vba via Excel? My understanding is that you can achieve basically the same things with both in terms of excel-files, but perhaps vba is quicker and easier to learn and use. Vb.net on the other hand has a much better IDE through visual studio and learning it will give me a knowledge which can also be more useful elsewhere. Is this correct? Instead of trying one of them only to find out after some time that I should have gone with the other, I hope to get it right from the start.
I use both VBA and VB.Net so I would recommend learning both.
Learning VBA is easier than learning VB.Net because there is so much less to learn. I find VBA tutorials easier to master mainly because most VB.Net tutorials seem to be more concerned with demonstrating the amazing functionality of VB.net than teaching you the basics of the language. Once you have VBA under your belt the learning curve to VB.Net is not too steep.
You access Excel workbooks from VB.Net using an interop which can read and write from any Excel version from 2003 onwards. It may be able to access earlier versions but I do not know any one who uses earlier versions so cannot test. The interop is slow. If your program is doing nothing but manipulate Excel worksheets, you are probably better using VBA.
VB.Net's forms have far more controls than VBA's. If you are trying to create an attractive, flexible, adjustable user interface then VB.Net is the one to choose.
VB.Net is compiled to an immediate language which is then compiled to machine code at runtime to take advantage of the capabilities of the machine it is running on. VBA compiles to an immediate language which is interpreted at runtime. For heavy processing, VB.Net programs can be thousands of time faster than VBA macros.
VB.Net creates executable programs (MyProgram.exe) which can run on any Windows machine making them easy to distribute if necessary. VBA macros run within Excel so the user need to have and open Excel to run them.
To summarise: start with VBA but then try VB.Net once you have mastered the syntax.
Incorrect. VBA is far faster has it runs in process. All calls are marshalled into network protocols to be sent cross process.
Plus you have to load COM with VBA. To use Excel you have to load COM. To use VB.NET with Excel you load COM and .NET - a far bigger resource load.
The code would be almost identical.