# Moving on from Excel VBA to another programming language - what to choose?



## xancalagonx

First off I want to thank everyone on this forum for the quick, accurate and friendly replies to the threads that myself (and others of course) have started when we were stuck with VBA programming or otherwise ran into problems while using Excel.

For myself, I'm just a novice when it comes to programming in Excel VBA. With my limited ability to create some simple VBA code I reduce a lot of the workload for myself as well as my co-workers (many whom hardly even use autofilters, never knew pivot-tables existed and VBA coding is entirely unknown). Starting to use and learning VBA code has opened a world of possibilities for me, and I've started putting more effort into the userform parts of it as well to make it easier for co-workers in their daily jobs and easier for new people joining projects. Where I spent hours working with lists of information to crossrefence, compare or link together data, I now spend minutes (though I don't tell anyone, so I have hours I can spend reading up on VBA instead, shhh).

More to the point of the thread though. Having acknowledged the vast possibilities from even the simplest coding in VBA, I've taken an interest towards stepping into the world of programming outside of Excel VBA. Perhaps to make some smaller executables that can handle updating reports on a weekly basis and things like that. I'm sure the possibilities are endless when I start learning the basic, just as learning the basics in Excel VBA opened up a world of possibilities within Excel.

But as I have been reading up on this I find there are a multitude of languages out there. Java, Python, Visual Basic, Visual Basic .NET, C+, C++, C#, so on and so forth. There's so many, and a majority of them seem to have very different languages / syntax. It feels quite overwhelming and there is no way I can, at least not as the novice I am, begin to learn them all.

And that is why I am bringing this up here, where I know there are a lot of gifted and talented people who have worked with this for years (perhaps even decades).

Can you advice me which programming language I should focus on as a novice VBA programmer?

I would really appreciate some advice what to go for and, optionally, maybe a brief explanation what the real differences between all these programming languages is.

Again, thanks for all the Excel VBA help you have given, and thanks for helping me out


----------



## Kyle123

Ok I'll start the ball rolling 

One thing before I begin, most of the languages are Object Orientated, it you don't know what that is, you might be best up reading up on it since it's probably going to be the biggest learning curve coming from Excel VBA.

VBA is a stripped back version of Visual Basic (VB6) so it's the closest thing to VBA you're going to find so the smallest learning curve. That said, I couldn't recommend learning it, it is very outdated now, it isn't explicitly object orientated and there are far better alternatives - they have better support, better libraries to get things done, wider active communities for asking questions to 

Java is an extremely widely used cross platform operating language. That means that most of the code you write in it will generally work across Windows, Mac, Linux and Android operating systems. There's a huge amount of help and resource out there, it also gives you a bit of a heads up when it comes to web development since the syntax is similar to Javascript. The syntax in general is nothing like VBA, but then very few languages are, most of them at least look broadly similar to Java.

C# (subjectively speaking) is Java "done right" it is relatively easy to pick up and use with a good support base. The following comments apply to both VB.NET and C# although you write them differently are compiled on the same code. They are microsoft languages - so will generally only run on windows (though there are ways of getting it  to run on other platforms) utilising the .Net framework. They have a great IDE (Integrated Development Environment - Like the Excel VBE) so it will ease your transition (of the languages you mentioned Python doesn't have one). It's important to note that Visual Basic and Visual Basic.Net are entirely programming languages and about all they have in common is the name . C# and Visual Basic.Net are both Object Orientated Languages. C# syntax is very similar to Java.

Python is a cross platform language and will run happily on windows, mac, linux machines. It is generally not written in an IDE, though there are IDEs available, it's also the most different from VBA than any of the languages you have listed. It is a great language and if you had no programming experience, I'd recommend it, but with you knowing some VBA, I think it might actually make it harder.

All the above are known as interpreted languages, that means they're high level languages that are taken and converted into code that the computer understands  C/C++ are low level languages, so you write in a language the computer understands this means that technically it's more powerful but it's not particularly easy to pick up and generally you have to work harder to do things than you do in high level languages.

In short, I'd probably say start with VB.Net, or if you are feeling a bit adventurous, you won't regret starting with C#, these will let you create desktop languages and easily create websites if fancy it. Speaking of the web, there if it's websites you're looking to build there are even more choices


----------



## xancalagonx

Thanks for that very informative reply. It certainly makes it a bit easier for me to grasp what they are (at least broadly) about. Up until now the different names for all the different languages meant little to me.

When I was first searching for what was available out there, my first hits were on Visual Basic 6 as you mentioned. But after digging into it and reading various discussions about it I had the feeling it was outdated or replaced by another language (VB.Net I think). That's when I first started realizing I know nothing about all this (I mean, really *nothing*), and certainly not enough to make an informed decision on what to pick up on.

I haven't been thinking about websites at all. I'm guessing that's HTML and things like that (which, beyond being an abbreviation I commonly see from webpages and other places, means little to me).

When I have been programming in VBA, especially after I started using the UserForms more, I really wanted to make something that works without having to open an Excel file each time to run the code. Even though I can run it with the Workbooks Open under ThisWorkbook so anyone using the file never have to worry about clicking a Commandbutton or something like that to start it, it's not really what I want. I want the UserForm there for the user to put in his/her data and/or get the information they need, but without the excel file in the background.

I did figure out a way to hide the Excel file entirely, but then it's really hidden and it can get messed up getting it back. Plus the excel process is still running in the background.

That's why I figured learning a programming language that is separate from Excel itself, and then rather extract the data I need from an Excel file or directly from a database and present it via a separate program, would be a much neater and tidy way to handle it. We are constantly working with lists of information where I work (we build oilrig at onshore yards and ship them offshore) and each project can vary from 2000 objects (such as instrument tags, cables, electrical motors, piping valves et.c.) up to 35.000-40.000 objects, depending on the size and complexity of the oilrigs. Needless to say, there is a tremendous amount of information to handle in regards to progress reports, handling of documentation, status reports split up on discipline or system levels, weekly reports on what is being finalized as well as what should be finalized... 

Without good knowledge of Excel it's very hard to combine all this information which is derived from different databases as well as different queries within a database. Hence why learning VBA has been a lifesaver for me (and many co-workers  ).

Anyways, I'm rambling a bit. I'm going to look into VB.Net and C# and see if I can find some tutorials and examples of code so I can see what I feel comfortable with. I feel I can really make things alot easier for both myself and the people I work with if I can get my head around this and start finding some creative solutions. 

I have this goal of fully automating the main progress report we run every monday by making it run automatically at 9am, grab the data it needs from the database using SQL, input all the information into a matrix / graph and automagically email it to the relevant people... without me needing to click or open nothing. I'm positive it can be done, but I don't think I can get there unless I move on from VBA.


----------



## Kyle123

Databases are a doddle with one of the .Net languages, you can create a form, and bind it to a dataset without even writing a line of code if you were so inclined.

It's probably worth getting a book with a CD that has some projects on you can try, go for C# if you can, it's more popular so there's more help out there and it's a lot more similar to other languages (which means you'll pick them up easier). If you compare the same code written in C# with the Equivalent in VB.Net you'll see that once you get over the curly brackets, they actually have pretty much the same syntax


----------



## xancalagonx

This might be a silly question, but how does C# or VB.Net handle visual representations such as graphs? 

Can that be generated using C# or VB.Net or does it need to grab it from another program, e.g. Excel?

Since I've only used VBA inside Excel it has never really been a problem since Excel has built-in functions that handle graphs so you can present something with all the bells and whistles.


----------



## sous2817

There are various 3rd party controls for charting as well as a control developed by Microsoft: Chart Types (Chart Controls)

I've used the Microsoft one and it works well enough.


----------



## xancalagonx

Well, I downloaded Visual Studios Express for starters and have begun looking at C#. Seems quite different from VBA but at same time it has a familiarity to it. Also downloaded a C# video training app called "C# 2010 Fundamentals" from livelessons to my iPad. From a quick glance, am I wrong in using my VBA eyes and comparing Class to the different "Sheets", "Module" and "ThisWorkbook" sections where you enter code in VBA, and the Methods as the Subs ?

I know it's not the same, but we all compare to what we have learned from earlier.

I'm quite excited to get started on this and hopefully I'll have the wits to figure things out as I go along.

Also, does any of you know any good tutorials for C#? Obviously I've found quite a few from google searches as well as browsing for apps, but not all gold nuggets are found through broad searches.


----------



## Kyle123

> am I wrong in using my VBA eyes and comparing Class to the different "Sheets", "Module" and "ThisWorkbook" sections where you enter code in VBA, and the Methods as the Subs ?



Erm yes and no, the sheets and ThisWorkbook are Objects, which are groups of classes. Where Range, Cells etc are properties of the object/class and verbs like Activate are methods. You can have classes in VBA, you add them in the same way as you do a normal module. C sharp doesn't have modules in the same way as VBA since it is Object orientated, In C sharp all the functions you create would need to be in a class/object that you create and use the properties methods and functions of that class.

Any time you have used New when declaring something in VBA you've been using classes/objects. Like collections for example, you create them use them and then destroy them, everything in C sharp is a class/object. 

It might be worth reading up on classes/Object orientated programming if it helps.


----------



## xancalagonx

Yeah I really need to look into the classes and objects. I was reading quite a bit on Wikipedia to just get started. I should have been in the sack at 11pm (I need to be up at 6am in the mornings for work), but reading up on all of this kept me going until 1am  It can be quite addictive, especially for a knowledge hungry person like myself. When faced with a problem, or something I don't fully understand, I don't relent until I do understand it. The "Moon illusion" haunted me for a long time :P

Like you said in the second line of your first reply, I think the first thing I need to work on as far as C# is concerned is understanding the definitions and relationship between the classes, objects and how it's all linked together. At least I have a starting point and that's the first step.


----------



## sous2817

There are a ton of good books out there as well.  I like Apress as a publisher, but I'm sure everyone has their favorites.  Take a look at this Beginning C# Object-Oriented Programming


----------



## xancalagonx

First off I want to thank everyone on this forum for the quick, accurate and friendly replies to the threads that myself (and others of course) have started when we were stuck with VBA programming or otherwise ran into problems while using Excel.

For myself, I'm just a novice when it comes to programming in Excel VBA. With my limited ability to create some simple VBA code I reduce a lot of the workload for myself as well as my co-workers (many whom hardly even use autofilters, never knew pivot-tables existed and VBA coding is entirely unknown). Starting to use and learning VBA code has opened a world of possibilities for me, and I've started putting more effort into the userform parts of it as well to make it easier for co-workers in their daily jobs and easier for new people joining projects. Where I spent hours working with lists of information to crossrefence, compare or link together data, I now spend minutes (though I don't tell anyone, so I have hours I can spend reading up on VBA instead, shhh).

More to the point of the thread though. Having acknowledged the vast possibilities from even the simplest coding in VBA, I've taken an interest towards stepping into the world of programming outside of Excel VBA. Perhaps to make some smaller executables that can handle updating reports on a weekly basis and things like that. I'm sure the possibilities are endless when I start learning the basic, just as learning the basics in Excel VBA opened up a world of possibilities within Excel.

But as I have been reading up on this I find there are a multitude of languages out there. Java, Python, Visual Basic, Visual Basic .NET, C+, C++, C#, so on and so forth. There's so many, and a majority of them seem to have very different languages / syntax. It feels quite overwhelming and there is no way I can, at least not as the novice I am, begin to learn them all.

And that is why I am bringing this up here, where I know there are a lot of gifted and talented people who have worked with this for years (perhaps even decades).

Can you advice me which programming language I should focus on as a novice VBA programmer?

I would really appreciate some advice what to go for and, optionally, maybe a brief explanation what the real differences between all these programming languages is.

Again, thanks for all the Excel VBA help you have given, and thanks for helping me out


----------



## xancalagonx

Thanks alot  Already downloaded on my iPad now!

Appreciate all the help!


----------



## obiron

My 2 Cents worth:

A lot of what people try to do in Excel is better suited to databases (think any time you have created reference sheets for drop down lists, or made copious use of Vlookups to link customer records to order records, or job items to their descriptions)

My first port of call would be to download SQLExpress and MSSQL Management studio and learn about SQL and desigining good databases (google 3rd Form Normalisation)

Strong database structures and be levereged into Excel and you *CAN* build decent small applications with Excel VBA and a database connection for internal use.  I think SQL is easy to learn with a lot of quick wins, especially in an environment where you are sharing data with colleagues and all updating the same information.

You could then learn Access: It is also VBA based although the objects and classes are completely different, the code is similar in structure.  You can build great applications in Access, but it is also extremely easy to build really BAD applications in Access if you don't know what you are doing.

My next choice would be PHP.  Along with .NET (VB and C#) it is one of the most common languages for developing web sites.  You can download XAMPP which is the windows version of LAMP (Linux, Apache, MySql & PHP).  the X denotes Windows - from the days of XP!  There is also a MAMP version for Mac fanboys.  Apache is a web server and is one of the default server technologies (the other is IIS which is windows based).  MySQL is a free SQL database and is pretty powerful but not as robust or scalable as MSSQL but will suffice for learning.  PHP is a 'plain englis' 4GL programming language that essentially comes in two flavours: Procedural and Object Oriented.  There is great documentation and a highly active user base who are welcoming to new users.  If you are used to VBA code, then the switch to PHP is relatively easy: The syntax is different but the way you structure a program is similar.  Once you have gotten to grips with PHP and basic web development (creating a form, Processing the results, saving the data, presenting the output etc..) I would suggest learning a PHP framework.  My preferred framework is CodeIgniter but you could just as easily learn Zend, Cake, or a whole bunch of others.

Web based apps seems to be the way of the future (this week...) but to be a complete web developer you need to know as a minimum:

HTML5
CSS
PHP or .NET
SQL
Javascript

Each of these on their own could take at least 6 months to master and they are all developing at increasingly frequent releases so keeping up to date is almost a full time job.

For me, the .NET languages and the different options (web Forms, MVC2, MVC3, WebMatrix, LINQ, RAZR) just make it too difficult to get past the Hello World tutorials.  The proliferation of classes and having to know which workspace to include to get at the classes leaves me frustrated.  As an example find some code for connecting to a database whose connection details are stored in the webconfig file.  (from the MSDN site)


		Rich (BB code):
__


   System.Configuration.Configuration rootWebConfig =
System.Web.Configuration.WebConfigurationManager.OpenWebConfiguration("/MyWebSiteRoot");
System.Configuration.ConnectionStringSettings connString;
if (rootWebConfig.ConnectionStrings.ConnectionStrings.Count > 0)
{
connString =
rootWebConfig.ConnectionStrings.ConnectionStrings["NorthwindConnectionString"];
if (connString != null)
Console.WriteLine("Northwind connection string = \"{0}\"",
connString.ConnectionString);
else
Console.WriteLine("No Northwind connection string");
}


----------



## xancalagonx

I probably should have delved into Access considering how much excel sheets and lists are floating around in the projects with all manners of information. We already have software though which contains alot of the data. However, depending on project, we have three different engineering databases (Kesys, Time and Comos), as well as different document control tools (ProArc and Projectwise) and we have a variety of PCS (Project Completion Systems) software depending on our client (Go Complete, CCMS, MIPS, ProCoSys and a variety of others) as well as our planning tool which for the most part is Safran and our plant design tool which mostly is PDMS.

All of this different software has various bits of information that needs to be linked together. Some information is transferred between the software automatically on a daily (or rather nightly) basis. But alot of the information is kept within each database, and unless you know what to search for (such as specific document numbers. purchase orders, plan activities or objectnumbers for tags, cables, motors etc) then it's very hard to keep an overview (remember we work with anything from 2.000 to 30.000 objects in each project and all those objects have their range of attributes, various documents, linked to planned activities and platform systems). Mostly this is done by exporting one excel report from one database and comparing/transferring information from it to another excel report from another database to get the information they need. Goes without saying that the few people that I have shown how to use VLOOKUP suddenly find themselves working much faster than before.

I don't think I will touch on developing websites and such, except for personal interest if anything. The majority of the work consists of coordinating everything and making sure everything is in place, has the correct data, is being installed according to plan and will be ready for hand-over to our client according to milestone dates. And to do that I need to run several reports to track changes, additional scope of work being designed in, progress for each discipline as well as for each platform system and so on.

So yeah... summed up, it's alot about information management. Which is also why I've seen the huge advantage of Excel's VBA programming. Now I just want to get a few steps further and figure out how to make a program that can fetch the data from the servers I want (as you see above, there's a bunch of databases and servers with information already so I don't need more databases... I think?), piece it together and output the reports that I desire based on different parameters each week.

At the end of the day, it's all about cutting down on the workload for myself (and others) in the projects.

I'm still poking around with the VBA (I quite enjoy the, relative anyways, simplicity of VBA). But I also started reading up on several C# courses and I'm going through the video-course from the app I downloaded (so far it's been a very good course and he does a good job detailing everything in his code so there's little, if any at all, room for questions or assumptions). My greatest (granted, it's only thing I tried so far) achievement in C# is making it prompt for my name and then display it back in the console. Baby steps, I know


----------



## obiron

Based on that response, I would suggest doing some research into Datamarts, and if you are Microsoft orientated then build on SQL Enterprise Edition (has some extra costs but significant benefits), Integration services (SSIS) and Analysis Services (SSAS).  These will enable you to Corral all the data into a single database and develop reports and analysis from there in a structured manner. If you are not Microsoft orientated then Oracle, Crystal Reports, OBIEE and Universes is the direct competitor.  You can also go SAS, Hyperion or a bunch of lesser data mart products.

I would suggest that you get a specialist company in to give you the lowdown on benefits and costs.


----------



## govert

Many of the answer implicitly assume that moving on from Excel VBA also means moving on from Excel. It need not.

I would suggest you get to know the .NET framework, but by moving your VBA code to VB.NET, still running inside Excel. That means you don't have to deal with too many new things at once (getting your head around .NET and Visual Studio can be quite a lot already).

You can download the free Visual Studio 2012 Express for Windows Desktop as a start
Using the free Excel-DNA library (which I develop) your VB.NET code can run inside Excel, as macros or high-performance UDFs. Patrick O'Beirne has written a really nice step-by-step guide on migrating VBA add-ins to VB.NET with Excel-DNA, so that would be a good start.

By starting to use .NET (and getting used to Visual Studio) you will be able to access the many .NET libraries for maths, web access etc, and also make your own libraries that you can use in Excel or from a command-line tool or windows application.


----------



## strive4peace

I've enjoyed reading the wealth of information on this thread,  especially on languages I have not used.  VBA is a good place to start  if you use Excel, Access, Word, or PowerPoint and can experiment so you  can learn and implement logic.  I have some chapters for learning VBA  posted here:

Learn VBA By Crystal

Last  year, I started learning C#.  I found Bob Tabor's free C# course to be  so good that I bought a lifetime subscription to his video tutorials:
https://www.learnvisualstudio.net/start-here/

However,  with all the new stuff, I might recommend focusing on HTML5 and  JavaScript, which can reference objects like C# or VB (which I like  better).  Here are a few of the slew of links I have for js:

https://developer.mozilla.org/en-US/docs/JavaScript 
JavaScript Fundamentals 
JavaScript: from the Ground to Closures | JavaScript Tutorial 
Apache POI - the Java API for Microsoft Documents

I  like the way that the dev center for Windows store (for Win8 apps)  allows you to see code without being able to run the download:

Getting started with Windows Store apps (Windows)

Pluralsight.com has some excellent tutorials but it is a subscription -- well worth the price if you can afford it.


----------



## ethanscott

Kyle123 said:


> Ok I'll start the ball rolling
> 
> One thing before I begin, most of the languages are Object Orientated, it you don't know what that is, you might be best up reading up on it since it's probably going to be the biggest learning curve coming from Excel VBA.
> 
> VBA is a stripped back version of Visual Basic (VB6) so it's the closest thing to VBA you're going to find so the smallest learning curve. That said, I couldn't recommend learning it, it is very outdated now, it isn't explicitly object orientated and there are far better alternatives - they have better support, better libraries to get things done, wider active communities for asking questions to
> 
> Java is an extremely widely used cross platform operating language. That means that most of the code you write in it will generally work across Windows, Mac, Linux and Android operating systems. There's a huge amount of help and resource out there, it also gives you a bit of a heads up when it comes to web development since the syntax is similar to Javascript. The syntax in general is nothing like VBA, but then very few languages are, most of them at least look broadly similar to Java.
> 
> C# (subjectively speaking) is Java "done right" it is relatively easy to pick up and use with a good support base. The following comments apply to both VB.NET and C# although you write them differently are compiled on the same code. They are microsoft languages - so will generally only run on windows (though there are ways of getting it to run on other platforms) utilising the .Net framework. They have a great IDE (Integrated Development Environment - Like the Excel VBE) so it will ease your transition (of the languages you mentioned Python doesn't have one). It's important to note that Visual Basic and Visual Basic.Net are entirely programming languages and about all they have in common is the name . C# and Visual Basic.Net are both Object Orientated Languages. C# syntax is very similar to Java.
> 
> Python is a cross platform language and will run happily on windows, mac, linux machines. It is generally not written in an IDE, though there are IDEs available, it's also the most different from VBA than any of the languages you have listed. It is a great language and if you had no programming experience, I'd recommend it, but with you knowing some VBA, I think it might actually make it harder.
> 
> All the above are known as interpreted languages, that means they're high level languages that are taken and converted into code that the computer understands C/C++ are low level languages, so you write in a language the computer understands this means that technically it's more powerful but it's not particularly easy to pick up and generally you have to work harder to do things than you do in high level languages.
> 
> In short, I'd probably say start with VB.Net, or if you are feeling a bit adventurous, you won't regret it if you learn C#, these will let you create desktop languages and easily create websites if fancy it. Speaking of the web, there if it's websites you're looking to build there are even more choices



I know I am a little late on this reply but Years later and I can still say that C# is still a formidable programming language.
I would say it is worth investing your time and money on this language.
There are multiple courses available online that can teach you the course in a matter of months so that should not be an issue.


----------

