VBA vs VB vs Other

[aerodoc]

I have been developing Excel VBA software, but am not considering going more "commercial". In doing so, I am trying to weigh pro's and con's:

Excel VBA:
Pros:
- short learning curve
- very good user interface

Cons:
- Lacks professionalism (opinion)
- Hard to protect from redistribution (negative effects when using something like LockXLS)
- Hard to insure compatibility (multiple versions, user must activate macros, etc.)

While I have spent a lot of time with Excel VBA, I am starting to think that for a commercial application, VB or something else (C++) might be a better choice. I would like to hear some other opinions about this though.

[Blade Hunter]

While I have spent a lot of time with Excel VBA, I am starting to think that for a commercial application, VB or something else (C++) might be a better choice. I would like to hear some other opinions about this though.

Depends what you want to do, for web apps, Java or ASP. For cross compatible apps (Linux, Windows, Mac) C++.

I have been in an IT based role for over 10 years now, many years of which was as a Programmer, All I know is VBA, VB6 and in recent times VB.NET.

VBA has the benefit that pretty much everyone has Excel installed.

VB.NET is cool because when you install a VB.NET app it checks if you have the framework installed, if you don't it connects to the net and downloads it, it also checks for new versions and does other cool user friendly stuff if you want it to.

The main thing I like about VB.NET over VBA is the file system useability
Try and do this with VBA (it can be done easily enough but is a LOT easier in VB.NET)
[vba]My.Computer.FileSystem.CopyFile(InDir & VideoFileName, OutDir & VideoFileName)
My.Computer.FileSystem.MoveFile(InDir & VideoFileName, OutDir & VideoFileName)
My.Computer.FileSystem.GetFiles(StandardInDir, FileIO.SearchOption.SearchAllSubDirectories,"*.complete").Count
Dim LockFile AsNew System.IO.FileStream("C:\BanditVideoEncoder.lock", IO.FileMode.Create)

[/vba]

The main benefit is the learning curve if you already know VBA, its still basic, just has some extra stuff to learn but the basics are the same. C++ / Java / ASP use a different structure and language.

[Kenneth Hobs]

I like VBA because it is integrated so well.

vb.net offers some advantages. You can even use the express edition for free. http://www.microsoft.com/express/Windows/

Chip Pearson has some information about the various options that might help. http://www.cpearson.com/excel/about.aspx

[aerodoc]

Realistically, I think VBA, VB6, or VB.net (I have heard of this, but am not aware of the distinctions) are my choices. I think the other languages would just be too much learning curve.

So within VB6 or VB.net, what are the pros/cons compared to VBA? My biggest fear is creating a new I/O scheme which Excel already has (as well as the pre defined functions).

[aerodoc]

Can anyone give me some examples of very professional Excel VBA solutions? I can then compare this to other VB solutions.

I too love Excel VBA, but it seems to be an uphill battle to get it "commercialized".

[Blade Hunter]

I can mail you an example if you like, I have written TONS of VBA apps, my best was a continous development over the 2 years it was used, it was a full blow sale reporting tool, all options and report query settings were stored on the database, this was done by finding the window user then linking that to their database login and automatically logging them in.

The app was self updating so when I roled out a new version the next time someone opened it it gave them the option to update.

Happy to mail it to you if you PM me your email address.

In your situation, the only thing I would say would be that VBA is THE best option for anything you want to do inside an office app, if it is a standalone app then VB.NET. I used to be a VB6 man but to be honest I ended up porting across because of certain limitations of VB6 (which I wont go in to the details but it was quite simple in VB.NET and no one seemed to know how to do it in VB6)

To give you an idea, I used to be a Universe designer and report builder for an application called Business Object, the project I can mail you was built because BO had limitations which rendered it useless in our scenario. The reporting engine I built filled that hole.

We are talking close on 10,000 lines of code, 30 or more forms and a bunch of other things .

[Jan Karel Pieterse]

It is nonsense that VBA is not fit for building professional applications. The level of professionality depends wholely on the programmer. VBA offers you all the tools you need to build professional applications.
On VB6 and VB.NET:

VB6 is much easier to learn for a VBA programmer (you can copy 99% of your code and -after telling VB6 all Excel objects are-well- Excel objects, it just works.
VB.NET is far more difficult to implement in automating Office than VB6 IMO.

But.

Office 2010 already has a 64 bit version, for which VB6 COM addins simply do not work. Period.

So to be future-proof as an Office dev, VB6 is a dead-end street.

[Bob Phillips]

It is nonsense that VBA is not fit for building professional applications. The level of professionality depends wholely on the programmer. VBA offers you all the tools you need to build professional applications.

Well said Mr Pieterse, the person is professional not the tool.

If the OP doesn't think you can create professional applications, he should read Professional Excel Development, first or second edition.

[JWhite]

I have an Excel 2007 commercial application with about 10,000 lines of code. I stuck with using VBA in an Excel add-in for almost a year but finally decided to convert it to VB.Net and a COM add-in. Actually, I left the user-defined formulas in VBA since they were much slower in VB.Net.

I was NOT a VBA expert so probably some of my problems with VBA were self-inflicted. My main reason for switching was unexplained Excel crashes with useless error messages. I posted questions about them in this forum and others but was unable to eliminate all of them. The crashes got more frequent as my application grew more complex. This was happening once or twice a month, not every day, but it still worried me. I can hear people out there saying "He just doesn't know how to debug very well" but it wasn't that simple, believe me or not.

Advantages: VB.Net has been totally reliable. The Visual Studio editor is much better than the VBA editor. You have a lot of new capabilities. Maybe best of all for me is the ClickOnce update ability. I can update my users automatically to new versions with no action necessary on their end.

Disadvantages: Obviously a learning curve. Debugging is not nearly as easy although it's pretty slick. Some VBA commands are more difficult when run from VB.Net and a few aren't there at all (none of them critical, IMO). VB.Net is slower - probably by a factor of 3 or 4. The compiled code is very fast but VBA commands (which is what you're using to talk to Excel) have to interface with Excel which slows things down. For most things it's unnoticeable.

I'm personally very happy I made the change. If I had had more experience with VBA before I started maybe I would have stuck with it.

[aerodoc]

Was not trying to offend anyone, let's not get into that. These are only my (and perhaps others) impressions. And don't forget if you are trying to sell software, an impression is very important, regardless if it is a reality.

Locking down a project/licensing seem to be tricky, especially without really influencing the final product. I tried a few programs and they really bogged everything down. Does anyone have a good solution for this? I have tried LockXLS and others, but was not impressed.

Trust me, I REALLY REALLY want to stay with Excel VBA since I have put a lot of effort there. But I seem to hit some walls that cannot be passed at times. That is why I am *considering* other options.

I will look into getting "Professional Excel Development". Don't forget, If I did have all the answers, I would not be asking the question.

[Bob Phillips]

I can't say I am that familiar with LockXLS, did try it once but I couldn't have been doing it properly as I couldn't get it to work, but you have been quite hard on it so what are its shortcomings that you see?

[aerodoc]

Fair question. I was using LockXLS as a baseline since it appeared to be the best one out there (so it was a compliment). Some of the others I tried were very bad in the way they fundamentally changed the file. So it became a question of whether this was feasible, since the best one was still lacking.

But that being said, it was several months ago that I tried it (and others). I think a closer look is in order with fresh eyes. Perhaps I am jumping to a conclusion so let me reevaluate this.

[Jan Karel Pieterse]

WHat I really don't like about .NET COM addins is that they slow down Excel's startup enormously, because that entire .NET CLR library has to load.

[Blade Hunter]

My general rule that I work by is if it is using an MS Office application I stick with VBA (ie Excel based reporting tool), if it is not I do it in VB.NET (ie video encoding software).

[aerodoc]

FYI, I tried LockXLS again, but it seems to have an error with forms showing when starting (or something to that effect). This rendered my sheet useless since there is an "I agree" part first.

From the version history, it looks like they are having problems with the Workbook Open part. I emailed them a few days ago and have not heard back.

EDIT: Looks like they rebuilt it and fixed the issue I was having (though I have not further evaluated it). They did not log the previous version in the their version history though.