Differences between VB and VBA in excel

[StackemEvs]

Hi,

I am now working on coding macro's within excel, which are inputting and processing .dat, .txt .csv files, and outputting results as .txt files. The work has nothing to do with excel, word, outlook etc.

How similar is this to writing and using VB code, or are they completely different?

[JuggaloBrotha]

VBA in Office is a stripped down version of vb6, so taking code from a macro and putting it into a vb6 project is relatively simply since they're extremely similar.

Also if you're looking to have this program run outside of excel, you could look into re-writing it in .Net.

[Shaggy Hiker]

.NET would be very different from VBA, though. If you want the most similar language you'd have to find a copy of VB5 or 6, but those haven't been sold in years. They might be found on e-bay, or some such, though.

[Hack]

Although a bit more subdued than in the past, Chit Chat is still not good place to post serious questions, especially if they are of a technical nature.

Moved To General Developer

[RobDog888]

Assuming you are talking about vb.net then yes, like above, its completely different. If you are talking about vb6 then its practically the same.

[si_the_geek]

There are lots of similarities between VBA and VB6 (mainly the kind of thing you are doing), but also several differences (mainly Forms and Controls, but also usage of the Clipboard, and some functions too).

You can often move code from one to the other with just minor changes, but even so it may not be the best code for the environment you move it to.

[JuggaloBrotha]

Other than Hack's post, hasn't every post after my last one been the same as what I posted?

[si_the_geek]

There is definitely a degree of crossover, but each post seems to have info that wasn't previously mentioned.

For example, my post explains that despite the earlier implication, there are some things that simply cannot be used in both (either VB6 specific, or VBA specific), and therefore must be completely re-written for the other.

[RobDog888]

Other than Hack's post, hasn't every post after my last one been the same as what I posted?

No not exactly. VBA is not a "stripped down version of vb6". VBA has been in existance for many years and may in fact have started at the same time as vb1. You can do things in VBA that you can not do in VB6 and visa versa.

It all depends upon the VBA code and controls being used. There just isnt equilivalent controls for some vb6 controls. And like si mentioned, some controls are not as rohbust in one environment then the other. This will make for complications and code refactoring, not copy/paste code from one to another.

[JuggaloBrotha]

No not exactly. VBA is not a "stripped down version of vb6". VBA has been in existance for many years and may in fact have started at the same time as vb1. You can do things in VBA that you can not do in VB6 and visa versa.

It all depends upon the VBA code and controls being used. There just isnt equilivalent controls for some vb6 controls. And like si mentioned, some controls are not as rohbust in one environment then the other. This will make for complications and code refactoring, not copy/paste code from one to another.

Yes, vba is tightly integrated with the office products which means there's com object already linked for you to use in those macros and whatnot of which is kinda hard to do in vb6 itself, but is certainly possible. Also vba is lacking a number of the vb6 runtime files which means there's a lot vb6 can do that vba can't, unless you reference those dll's manually in which then you can. This is why I say vba is a stripped down version of vb6.

I also say vba is a a stripped down version of vb6 because the last 3 releases of Office's vba has had the common vb6 files which means they're basically the same. Earlier versions of Office has had earlier versions of vba as well, I believe Office 2000's vba was a stripped down version of vb5, I could be wrong as that's not the point here.

And as stated a few times before, if he's wanting to do what the excel macro does outside of vba, he should consider re-writing it in .Net to keep it up to par with the lastest version of technology. I'm hoping office 15 (roughly 2012) will be the version to no longer have vba as the backend, I was really hoping office 2010 would be the deal breaker but they decided to implement a stripped down vb6.5 as the vba version instead.

[RobDog888]

I just got back last night from the MVP summit at Microsoft and this question was addressed. VBA will indeed be around for the foreseeable future. So I wouldnt hold your breath about it going away anytime soon.

Office 15 will not be out for some time still as 2010 is due to release in Q2 or Q3 of 2010. 1.5 - 2 years is too soon with Office growing as it is. 3 years is a good minimum timeframe to guestimate off of.

[dilettante]

VBA is a relative newcomer on the scene, and began creeping into a few things like MS Excel and MS Project by 1993, in more Office applications by 1995, and a few more by 1997. Prior to that Office programs used various approaches to saving operations as macros and for a while Word had WordBasic. By Office 2000 a stable VBA6 existed and Microsoft made VBA6 more available to 3rd parties as well - for embedding within their own host applications.

Because they share a great deal in common, moving non-Office logic from VBA6 to VB6 is fairly easy. Many VBA6 programming skills translate easily as well because the core syntax and the IDE are so similar.


The third pillar of the VB world is VBScript. This is an entirely different implementation of a large subset of VB5/6 language syntax. The biggest ways it differs from VB/VBA are the lack of strongly-typed data (everything is a Variant) and event binding (more like JScript event binding).

Like VBA however, VBScript cannot live on its own. It must be hosted within some other program. Common hosts include WSH (WScript and CScript), IE, MSHTA, IIS's Classic ASP subsystem, and several Office programs. Just like VBA there are also 3rd party programs that host VBScript. VBA was never licensed for the construction of a general purpose programming host though, while there is no such restriction on the use of VBScript.

One script host that I use is called NS Basic/Desktop. This adds a forms engine, its own set of VB6-like controls, access to most API calls, and an IDE wrapped around the VBScript engine. It is much closer to using VB6 than most script hosts are, and can be used to create stand alone programs.

One bonus is that you can still actually buy it and there is ongoing support.

However being script-based it will perform worse than VBA and miserably compared to VB6 on crunching tasks. But by leveraging COM libraries to do the heavy lifting it can still be used to accomplish many classes of tasks very well. For processing text-based data files you can usually use your script code as "glue" along with power tools like Jet 4.0 and Log Parser 2.2, and see little performance impact.

Of course you should be using these in VBA6/VB6 anyway.


VB.Net is often referred to as the Great Pretender because it shares little of the VB legacy. While it is perfectly possible to use it for these sorts of tasks it is an entirely different language, uses different core libraries and controls, and has an entirely different IDE. It can also be clumsy to deploy, especially to older versions of Windows where you can't assume its gigantic runtime requirements already exist.

In terms of learning you might find something as funky as REALBasic or Jabaco easier to move to. VB.Net will be around a while longer though (despite its roughly biannual mutation into different forms) which is harder to claim for some of these 3rd party offerings.

[StackemEvs]

I do like questions which gets everyone having to have their say and oppionion. I was just asking because I'm not a programmer, I'm a data manager (posh way to say inputter and teaboy). Who it trying to understand and work on some of the macros we have here (small company, no actual programmers).

And first thread I posted was in VB not VBA (I didn't know there was a difference!). Learning quick now....

[JuggaloBrotha]

I do like questions which gets everyone having to have their say and oppionion. I was just asking because I'm not a programmer, I'm a data manager (posh way to say inputter and teaboy). Who it trying to understand and work on some of the macros we have here (small company, no actual programmers).

And first thread I posted was in VB not VBA (I didn't know there was a difference!). Learning quick now....

Threads like these is where I learn the most types of stuff, and on occasion I'm even proven wrong which gives me the most experience.