New to Forums ... few questions about VB, .NET Framework, and Excel-VBA

[MushyMoo]

Greetings,

I am working on a new project that involves Excel-VBA but I have no prior experience programming in VBA, Visual Basic, and VB.Net. But, with a strong desire to learn and prior programming experience in Java, C, C++, and even Basic on the Commodore 64 many ages ago, I have a few questions.

1. Best way to learn VBA and Visual Basic? Should I be learning VB.Net and what is .NET programming all about? Also, there is a Visual Basic 6.0 accompanying prior versions of Visual Studio but with the latest VS comes VB.Net... I am a bit confused here.

2. Is VBA similar to JavaScript for Java and does it share that similar relationship? Would it be better to focus on VBA rather than Visual Basic?

Anything else you would like to share that would shepherd me into the world of VB and .NET programming would be appreciated.

[MushyMoo]

Greetings,

I am working on a new project that involves Excel-VBA but I have no prior experience programming in VBA, Visual Basic, and VB.Net. But, with a strong desire to learn and prior programming experience in Java, C, C++, and even Basic on the Commodore 64 many ages ago, I have a few questions.

1. Best way to learn VBA and Visual Basic? Should I be learning VB.Net and what is .NET programming all about? Also, there is a Visual Basic 6.0 accompanying prior versions of Visual Studio but with the latest VS comes VB.Net... I am a bit confused here.

2. Is VBA similar to JavaScript for Java and does it share that similar relationship? Would it be better to focus on VBA rather than Visual Basic?

Anything else you would like to share that would shepherd me into the world of VB and .NET programming would be appreciated.

Please feel free to offer any guidance or knowledge you may have. Perhaps you suggest I learn VB.NET and can recommend a god book or you have experience with Excel-VBA and know of some useful resources.

I may be new to this area but you certainly can contribute and in whatever way you feel you can. Thank you.

[Shaggy Hiker]

VB6 and prior were the Visual Basic language that were included with Visual Studio up until 2002, at which point .NET came out. VB.NET is a totally new language with a syntax that is quite similar (and also quite different) to VB6. The similarity was supposed to make it easy for VB6 programmers to move over to .NET, but the two languages are so different in structure that the transition wasn't all that easy, and was bitterly resented by lots of VB6 programmers. Furthermore, just to add a little more confusion, the .NET part was pretty much dropped from the name in 2005, which means that you often see VB.NET for versions 2002 and 2003, then just VB2005, VB2008, VB2010, and VB2012. They are all still .NET, it's just a name change. Support for VB6 was discontinued a few years back, and the compiler is becoming increasingly hard to find. After all, it is now about 7 versions out of date.

VBA is a language included in Microsoft Office products. The syntax is identical to VB6, though there are some differences due to VB6 being a stand-alone language whereas VBA is built into some other program as a macro language.

There is also VBScript, which is more like the JavaScript to Java, with the notable difference that VBScript largely sucks.

[dilettante]

JavaScript is entirely unrelated to Java except for a casual similarity of some syntax.

VBScript is actually a fantastic and powerful tool, with much better access to system-provided components than JScript (Microsoft's version of JavaScript). It is much easier for a VBA or VB programmer to use because it is almost a proper subset of them.


While VB shares a lot in common with VBA, they are really separate things. One is a professional development tool for creating stand alone programs, and the other is a macro language for user automation within a product that hosts it. The controls and other widgets are different depending on the program that hosts VBA, though in some VBA hosts it is possible (though often a license violation) to make use of some of VB6's ActiveX controls. You can't actually compile VBA.

VBA6 is a subset of VB6, though in later versions of Office you'll find VBA6.x with extensions that are not in VB6.


VB.Net is really something else, an attempt to create a VB-flavored .Net language to accompany Microsoft's Java clone called C#. Calling it VB is a bit of a stretch but they did it for marketing reasons and for better or worse (and massive confusion) we are stuck with it now.

VB.Net experience is almost useless in learning VBA, they have very little in common aside from a few syntax similarites here and there. The .Net people at Microsoft tried to cobble together an extension called VSTO (later VSTA) to be used with Office instead of VBA, but it never saw much popularity and may as well be considered extinct.

[Shaggy Hiker]

And there's a different point of view on the same thing. I disagree on the linneage of much of that, but it's a matter of nothing more than semantics, as the net result (or even the .NET result) is the same.

[MushyMoo]

I thank you for the information shared. VB.Net (VS 2012 as I am a student) and VBA are things I desire to learn. How about resources? I'm a book worm -- I just appreciate books as concise collections of mostly meaningful information. I could go to the university of Google, but the problem is, as I know little of what I am looking for to begin with, Google has a habit of offering far too much information to be of serious value. Helps to know where to start when searching and "VBA tutorial" is by now the best dead-end approach because one gets 65% commerce, 20% relevance, and 15% valueless addition.

Please help me out! I browsed Amazon and I use a simple system of rating books: user rating and feedback; no less than 15 commenters at a minimum and need min rating of 4.5. I agree, not the best strategy but has helped me land good books in the past. So, how about it? What is the best book you used to learn VB.Net and VBA or what is a die-hard can't live without website?

[dilettante]

That's a tough one.

I have a number of books on programming in particular languages using particular IDEs, etc. But what I have found is that the information density is quite low in them and they have proven generally worthless except for a tidbit here and there that they managed to shed some light on.

I've recently gone through (and continue to go through) a process similar to yours. I've started with Android Java and find the forest obscuring the trees myself. It isn't just Java, which I haven't used much since the turn of the century, or even Eclipse which I haven't used in almost 7 years, or even the oddities of the Android platform. It's everything together!


What seems to work best for me is a combination of things. The most valuable things to me are finding my way around the tools and even more finding my way around the official documentation.


Getting up to speed with tools (IDE and compiler, auxiliary utilities) seems to be best done by working with them. IDEs are pretty fancy now, so much so that there are tons of menus, dockable panes, panels, and tabs, etc. These can "disappear" on you and you need to get to the point where you know how to "bring them back" - there is only so much screen real estate even running the IDE maximized. Then there are the sequences of steps required to accomplish different things, and so on.

One help here is to track down some quality tutorial videos that focus on these things. I've found a few for Android development that are quite useful, and a ton that are pretty pointless (poor quality video and sound, only brief coverage of trivial topics, poorly thought out and organized, skip quickly over important points and dwell on silly things). Teaching is hard work and we aren't all cut out for it, and experience counts as in any endeavor.

But after a few hour or two-hour videos you just have to dive in. Watching somebody else "traverse the course" a few times helps, but you have to get on that road yourself then to burn in some hands-on experience.


I put high value on documentation, especially authoritative documentation. While it may have its errors, omissions, and become obsolete at times... when you're at square zero it is Master and you are Apprentice.

Now you might think printed docs more useful than an online equivalent. But this is only true under two conditions: (1.) the online docs are slow and clumsy to navigate, (2.) you are just starting out and need to browse a great deal since you don't have the lay of the land yet to help you home in on information quickly.

Sadly, both current Office online documentation and .Net online documentation are about the worst examples one could point to. They can be very slow, have awkward search facilities that lead you astray more often then to your target, seem to purposely fragment everything into many panes of info (Office Help has the worst UI imaginable).

.Net Help is mostly embedded in the Windows SDK docs, which used to be quite easy to use but fell apart when the Visual Studio people forced the Windows team to move to the truly awful .HXS "Help 2.x" format instead of the speedy and well-organized .CHM format.

The Android SDK docs aren't much better, using a clunky combination of HTML and script that are nothing but an offline copy of the online Web site docs. Poorly indexed, searches are slow and often useless, haphazardly organized like any Web-based endeavor. But then with Eclipse as an IDE you have few alternatives. The "open sores" world consists of large bags of least common denominator software jury-rigged together.


So it is tough to recommend any books.

There are a few online resources that might save you hours wading through a pile of pricey books. Getting Started with VBA in Office 2010 is one example and it concludes with links to more resources.

A 3rd party tutorial video resource https://www.youtube.com/user/Malfunction9 doesn't look too bad, covering MS Access, Excel, and VBA. Sitting through some of these might help take off the sharp edge of trepidation by showing walk-throughs of the tools. If you have prior programming background expect it to be a little low-level in that sense, just go with the flow.

I used to be much less of a fan of video tutorials, but with a little hunting... there are some useful ones now. I'm sure you can find other resources that are a better value than books (in terms of cash price and time spent).

[dilettante]

I just had a peek at the first video in that guy's Excel VBA Tutorial sequence. It is very low-level and worse yet he shows examples using incorrect syntax (that happens to work, by luck, in the degenerate case presented).

However it probably won't damage you too much if you can grit your teeth and sit through a very low level intro, with an awareness that the guy isn't very expert and may do some really bone-headed things.

Example:

In his first Excel VBA video he uses:

MsgBox ("Hello World!")

Which is of course incorrect, and should be:

MsgBox "Hello World!"

Those "parentheses of death" are something to watch out for, and they are a common error for anyone with .Net, Java, JavaScript, etc. experience. The IDE even subtly warns him by inserting a space prior to the "(" but he never twigged to it.

Subroutine calls do not use parentheses around their list of arguments. What he did was tell the interpreter (actually the IDE's incremental p-code compiler) "evaluate this expression and pass its value."

This will fall apart as soon as he needs to supply some of the optional parameters:

MsgBox ("Hello World!", vbOkOnly, "Title")

Will fail due to incorect syntax. properly written it would be:

MsgBox "Hello World!", vbOkOnly, "Title"

So the videos are guaranteed to be flawed, though probably useful as a view of use of the tools. Note that untold number of books have the same kinds of silly mistakes! Some of them get contrarian and recommend the obsolete QBasic-style syntax:

Call MsgBox("Hello World!", vbOkOnly, "Title")

Note that the IDE pulls out the space before the "(" even if you manually force one in.

This works, but it's a crude hack, generally best avoided.

[Shaggy Hiker]

Programming books have been notorious for about two decades now for some of the shenanigans that they pull. A major focus is creating a BIG book rather than a good book. Some BIG books are quite good, though, despite the fact that they make use of all the normal tricks. I prefer going to a brick and mortar store for programming books so that I can perform a couple tests on them. The first test is just to riffle through the book. A couple of the ways the publishers are turning books into BIG books is by using HUGE margins, loads of screenshots, and lots of superfluous code dumps. Some code is very useful, others....not so much. Therefore, if you riffle through the book and see that the ratio of pictures to text or code to text is very high, it is likely that the content of the text is very marginal. Big margins are probably something you have to live with these days, though, as few publishers avoid this. Quite often, the printed portion of a page is half the total area, or even less. It's not a good sign, but is no longer a sure indication of poor quality.

The next step is to examine the table of contents, find a subject that isn't in the first one or two chapters (which are always fluff, though occasionally useful fluff) in which you are interested, and read the section. The question you need to be able to answer is whether or not the subject was explained well enough that you feel you benefitted from reading the section. If you are confused about what they said, or you realize that they spoke a lot without saying much of value, then the book isn't worth the money (which is always fairly high, since they are BIG books).

Lastly, there is a different issue: An introductory book may touch on several areas lightly, but, being introductory, can't get too deep into any single subject. Introductory books are relatively easy to write, because everybody has some list of the core concepts that a person needs to understand to get a basic understanding of a subject. The list of core concepts may be different for each person, but every expert can come up with such a list. Once you get past that set of core concepts, though, the problems become MUCH more difficult. For instance, it would be easy enough to come up with a list of basic information for connecting to a database, such as creating a connection, working with SQL, SELECT statements, and the like. Beyond that, though, the field gets too broad for a single book. If you want to cover SQL, that's a whole book in itself (I have such a book, and use it as a reference). Another book could be on database design. A third book could be on a specific technique for interacting with a database such as the use of the Entity Framework. And all of them, including the introductory book, will leave out options simply because there are so MANY options that could be included.

For this reason, there are lots of "Learning Something in Some Number of Days" books on the market. Those are all introductory books. There are NO "Intermediate Programming" books out there, as a general rule. All the intermediate and expert level books are focused on a single subject, which is a subset of the whole. I have books on Socket Programming, which talks about the TCP/IP stack from hardware to the hightest level without getting into any particular language, that book on SQL, which is mostly T-SQL, but is useful elsewhere. I also have a book on Concurrent Programming which explains down to the hardware level how multithreading is implemented. That book discusses how the various OS multithreading options work for C languages, and also discusses how it is implemented in .NET languages, but there is nothing specifically JAVA oriented, nor Ruby, nor COBOL.

So, you have introductory books that might get you started out, but which you will rapidly outgrow. Beyond that, you will have single-subject reference books. The latter tend to have less of the fluff and padding used to sell the introductory books, but they might still have some. Along the way you will find that certain publishers tend to do better than others. Unfortunately, you may also find that the list of better publishers keeps changing. I have an aversion to SAMS because of some crap they did a decade ago, but I think they are much better now. Addison-Wesley and Wiley were both excellent computer book publishers in the 90s, but both have fallen off a bit of late. O'Reilley was once entirely reliable, but have lately put out some volumes that appear to be pure chaff. That's why I like to get my hands on the book before I buy one, despite the fact that the selection on line is often superior.

[MushyMoo]

Thank you all for your assistance. I am currently working on deciding whether or not I want a VBA macro as an attempt to fix a timesheet or make a Java UI and use an Excel API. I appreciate the feedback on learning and the understanding.