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. #CustomerAcct MgrItemItem PriceQtyValue
1Spacely SprocketsBlow, Joe80800.04710000000470000
1Cogswell CogsBlow, Joe37460.0489800000470400
50Acme CorpBlow, Joe54559.995004995
50Aperture ScienceBlow, Joe1000011600005800000
99MicrosoftBlow, Joe600179.9980000000063992000000
1Black MesaWho, Cindy Lou999998000121176000
1UNSCWho, Cindy Lou1337600000017102000000
1Natas MiningWho, Cindy Lou666610000004444000000
1Blizzard NorthWho, Cindy Lou555559.99650000003899350000
50PopCapWho, Cindy Lou44548.991100000098890000
99Nintendo of AmericaWho, Cindy Lou7777149.99160000002399840000
99General MotorsWho, Cindy Lou1400000020
99Loudifier AmplificationWho, Cindy Lou1111

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 *

Connect with Facebook

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>