Visual Basic – Review

Is it weird to do a review of a programming language? I’m not sure.

I would definitely label myself a “jack of all trades”, always appended with “master of none”. This is also the case with programming. I have used more programming languages than I can count, but I have never really been super comfortable with any one particular language. My programming is almost always limited to accomplishing a simple task that I just can’t figure out how to do with a third-party tool. With that in mind, here is my first programming language review from the point of view of a novice programmer.

 

The Situation:

I’m not sure how, but I earned a reputation at work of being an Excel guru. A user came to me with this notion and a frequent .xls report that he was having to manually copy and paste pieces of to distribute it to his employees. It looked something like this:

Sales Report
Co. # Customer Acct Mgr Item Item Price Qty Value
1 Spacely Sprockets Blow, Joe 8080 0.047 10000000 470000
1 Cogswell Cogs Blow, Joe 3746 0.048 9800000 470400
50 Acme Corp Blow, Joe 5455 9.99 500 4995
50 Aperture Science Blow, Joe 100001 160000 5 800000
99 Microsoft Blow, Joe 6001 79.99 800000000 63992000000
1 Black Mesa Who, Cindy Lou 9999 98000 12 1176000
1 UNSC Who, Cindy Lou 1337 6000000 17 102000000
1 Natas Mining Who, Cindy Lou 6666 1000000 44 44000000
1 Blizzard North Who, Cindy Lou 5555 59.99 65000000 3899350000
50 PopCap Who, Cindy Lou 4454 8.99 11000000 98890000
99 Nintendo of America Who, Cindy Lou 7777 149.99 16000000 2399840000
99 General Motors Who, Cindy Lou 14000000 2 0
99 Loudifier Amplification Who, Cindy Lou 1 1 1 1

Only with way more fields. Joe isn’t allowed to look at Cindy’s sales figures and vice versa, so he was having to copy Joe’s data into a new workbook, email it to Joe, and then do the same with Cindy’s data.

Enter me. I don’t want to get into trying to have a bunch of statically linked workbooks out there pointing to a central workbook, because it’s messy, and because the data is going to be moving around vertically on a weekly basis. The verdict is I will finally be forced to learn some of the VBA magic I see every time I try googling for anything about Excel.

The basic solution, in pseudo code, is:

  • Identify the first cell with a sales person’s name
  • loop through the list until a line is recognized that doesn’t match the first sales person
  • copy the data between the starting point and the point where the loop broke to a new workbook
  • copy over the header to the new workbook
  • save the new workbook as the sales person’s name, close it
  • start the loop again where it broke last time
  • when a blank is encountered, end the macro

Having experience with basic programming control structures, variables and such, I start by figuring out how to declare variables, set up a while loop, and make comments.

The good:

VB (VBA in this case) is very powerful. Microsoft gives the end user an astounding level of control over Office applications, especially when you consider how much you can do in Excel before using VBA. I was able to record a macro automatically, and then look at the code that was generated to try and reverse engineer some of the code, which was very helpful. VB is an object-oriented language, which makes coding flexible. Most importantly, it’s universal. Learning VB will get you far in a Windows environment and allow you to automate repetitive tasks, like splitting excel files, or even create logon and startup scripts to apply with AD.

The bad:

Documentation.

I spent a lot of time trying to figure out how to resize a range object before I finally “selected” the range, did a selection.resize, then assigned the selection to the original range object. Kinda kludgey. The Visual Basic Editor IDE very helpfully suggests Range.Resize when I start to type that, but for some reason It would throw an error every time I tried to run the macro.The community is amazing. Every time I searched for a solution to a problem, or just the proper syntax for some code, I found a post full of great, usable code examples for specific situations. Unfortunately, I only ever found examples. Even the Microsoft websites for VB are dominated by examples, with little explanation as to why a bit of code is structured like it is. I want to learn how to use VB, not copy someone else’s examples and then staple the crap out of it until it kind of works.

The syntax

Visual Basic is a modern, high-level language with a complete object creation and manipulation structure. Why the fuck can I not make a block comment? If you dig up a hidden button in the menus, you can comment or uncomment a selection of lines, but last time I checked, comment lines individually != block comment. Objects inherit characteristics in the Parent.Child format, which works well, until the IDE gives you suggestions that simply don’t work. Speaking of the IDE, it will move your code around without asking, making it hard to read. For some reason, there are single-line and multi-line versions of commands that require very different structure. Good luck adding another line to what was previously a single-line if statement.

Fragmentation / Lack of integration

It’s a fact of life. If you need to script in a Windows environment, there will always be a new language to learn. While VBA is tightly integrated with Office and is the quickest way to automate tasks in Excel, the language itself rarely uses terms related to Office Applications. Microsoft is always so busy trying to sell developers on their next half-assed set of development tools and languages (here’s looking at you, Metro) that they orphan stuff like VB. They are now pushing you to use PowerShell for anything that you would have used VBS for in the past, forcing you to learn yet another set of rules and commands. VB for Applications is trying to do too much and not enough at the same time. I have the full range of Visual Basic at my command, but I really just want to do some comparisons and move data around. Microsoft doesn’t really want me to use VB for anything but Office, so why not give me a Visual Office Macro language?

The Verdict:

Visual Basic isn’t worth mastering. I was able to patch together some code for a specific purpose, but the language was actually getting in my way. While it is the most direct route to advanced Office scripting, there are simpler methods for most other scripted Windows tasks. Do some google searches. Copy and paste from the tireless angels that post replies on stack exchange, and quit when it works. Anything more just isn’t worth your time.

I give it 6 commas out of 10

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>