Which programming languague

[Nuz]

I have been creating applications to my customers through VBA but found too many times problems caused due to different versions of Excel that my customers have. Secondly, I also find it really inconvenient to make user interface to look professional by using userforms in VBA since building menus, listviews etc. into the forms is really annoying. And third, my clients are typically missing some necessary reference library files and due to that receiving error messages when opening the application.

My question is, which is the best programming language that is independent of Excel version (i.e. not require a user to have Excel installed at all) but still in case the user has an Excel, it should be able to generate reports to .xls-format?
And at the same time I should be able to make the user interface professional by creating menus etc. into the application.
Is VB6 or VB.net suitable for that purpose? I have no experience from any C, C++ or similar languages but lot of experience from VBA coding and Excel programming.

 

[Macropod]

Hi Nuz,

Any programming language that makes use of Excel's API is subject to whatever API functions the user's copy of Excel supports. Basically, that's anything you want to do in Excel that isn't built into the programming language you're using and that you don't want to re-write for yourself.

 

[xenou]

You should be sure to code for the earliest Excel version you think your clients will be using. Charts, pivot tables, and lists are probably the most susceptible to changes in recent versions since XL2003. Also after development, convert all your code to late binding for any automation objects you create. This will spare you some support calls when users change versions.

If you want to create your own forms most languages supported forms-based development. I would not expect this to make things easier - there's a steep learning curve if you are coming from a VBA background only. VB6 is no longer being actively licensed.

For creating Excel documents without Excel I guess it depends on how complex of a document you mean. Raw data, tables, and structured XML documents can easily be passed around in various ways that Excel understands. In principle you can write to the OfficeOpenXML standard to create a spreadsheet document. Typically when I get Excel files from other programs it's just raw data, not full blown Excel fireworks.

 

[Nuz]

I only need to write raw data (large amount of numbers and some text) to Excel, without any formulas, charts or similar.

I was thinking VB6 as a suitable progamming language because the coding is pretty much similar than within VBA (where I have experience). Through VB6 programs it should be relatively easy to control Excel, i suppose. Although VB6 is not licensed anymore, it still should be possible to get a copy of it somewhere. Do you agree?

VB.net coding seems too different from VBA for me to start learning it right now due to my time constraints.

 

[Macropod]

Hi Nuz,

Any reason you can't simply write the data in csv format? The csv format is a simple, comma-separated-values text file with data laid out in a column & row structure that Excel can parse. To create such a file, you can use any programming language at all - there's no reason to involve Excel until you actually want to open the completed file in that application (if that's what you need to do).

 

[Nuz]

Yes, csv could work in that particular case well when it's only raw data. But in some other cases, I might need to put more complicated stuff to Excel.

But still, my original question was whether the VB6 is the right choice for my experience to start working with. Now, after reading some manuals for VB6 it seems the right one, with all the menu possibilities, various controls, independecy of Excel and packaging/distribution possibilities to clients.

 

[xenou]

Although VB6 is not licensed anymore, it still should be possible to get a copy of it somewhere. Do you agree?

As I understand it, not a licensed copy.

I've no idea how people choose a language to code in. I suppose its either chance events or something strikes you - or its what you have to do whether you like it or not. I've learned VBA mostly, VB.NET/C# a little, and Java a little. They all have pros/cons.

 

[Nuz]

Thanks for the replies. I am using Windows 7 (64-bit) and just found that there may be some compatability issues with VB6. Maybe it is then best to start learning VB.net straight away and forget the VB6.

 

[xenou]

I guess that's what I would do too, with VB6 at "end of life" (too bad - though I certainly expect VB6 apps to continue to work for years to come). You can download visual studio express for free (or purchase full blown Visual Studio). I think it's a big adjustment from VBA but if you have a knack for it there's lots you can do with it and plenty of opportunities job-wise. You'll also be able to take your knowledge into .NET web programming. Really, you could probably create a first "hello world" vb.net app tonight

Nota bene: besides Visual Studio and Visual Studio Express there's an open source .NET initiative which has been getting better over the years but I don't have first hand familiarity with it (primarily with C#.NET in mind):
http://mono-project.com/Main_Page

 

[Nuz]

Yeah, I really like the opportunities available in VB.net. They seem to solve many of the problems I have had with VBA earlier.

This may be out of this thread but do you have any good books to recommend on VB.net programming (both for beginners and advanced users). I'm going to work on Visual Studio 2010 platform. I see that lot of books have been written on VB.net, but most of them are based on earlier versions (2008, 2005 etc.).