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