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

 

[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)

   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]

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.