Comments, Definitions in VBA


« Back to videos

Additional Information:

Visual Basic for Excel may be the first programming language you have ever learned. Experienced programmers will always stress the importance of structuring code, especially if they have worked on projects with a number of other people. This is partly because it can be very difficult to find errors in other coding languages. It is also because building custom software may require man years of effort, so it is worthy of thorough documentation.

Visual Basic macros tend to be significantly smaller and easier to analyze. You are generally writing code to make immediate time savings; these savings may not materialize if you have to spend hours working on your code's accessibility.

The most important steps to take are those that benefit you as a developer, especially if you will have to tweak the code at some point in the future. This is why we demonstrate commenting code by putting an apostrophe symbol in front of comments. Note that you may also need to use apostrophes within code but they would only ever appear between quotation marks, where Visual Basic can ignore them. To illustrate, if you write Cells(1, 1) = "This macro's working", it is treated as a line of code with no comment.

Defining variables using the Dim statement tends to be a waste of time. "Dim x as Variant" tells Excel that x is a variable. If the line was not there, this would be implied. It is important to understand the Dim statement, as you will need to use it when working on pre-existing macros where the developer has entered Option Explicit at the top of the module. You will also need to define variables if Tools->Options->Code Settings->Require Variable Declaration is checked within Visual Basic Editor.

There is a further argument for defining variables. Some words or phrases may already mean something in VBA. This can cause problems. So avoid using simple words for variable names such as Row, Left and Date. You can also go wrong using names that could be cell/range addresses such as "A1" or, in Excel 2007 or 2010, "Col1". However, single characters such as "n" and "x" do make good variable names.

Towards the end of the video, this module introduces the Date function. This takes the current date from your system clock and it is worth noting that, despite taking the appearance of a date, it is essentially a date serial. This means Excel stores it as an integer representation of the current day, such that tomorrow's date would be one greater.

The need to tab your code is somewhat assumed in this video, because it was first covered in the Loops and Conditions module. It is a measure tabbing's importance that it had to be covered at an earlier stage in this course.