Category Archives: Tip of the Day

VBA Comments

Making proper VBA Comments

1 Star2 Stars3 Stars4 Stars5 Stars (4 votes, average: 3.25 out of 5)
Loading...

Today’s Tip of the Day will focus on VBA Comments, how to make comments in VBA and how to make the most of them. Comments are needed in any script to explain what the code is supposed to do or provide some verbatim on certain lines / blocks of code. Scripts without comments are a nightmare for many of us – making it sometimes impossible to read through the intentions of the author. If you want to make it as easy as possible for someone to reuse your code – learn how to make proper comments.

How to make comments in VBA?

VBA Comments
Opening the Edit Toolbar to comment/uncomment code
VBA Comments are lines of code preceded with the comment character '. VBA supports only single-line comments. Everything after the ' character will be ignored by the Visual Basic for Applications compiler.

Alternatively, to make comments, you can utilize the Edit toolbar from the View->Toolbars menu in VBA Project.

Below a simple example of a single line VBA comment:

Dim x As Long
'This is a comment
x = 10

A multiline VBA comment:

Dim x As Long
'This is a 
'multiple line
'comment
x = 10
Little known fact: you can also create comments in VBA by preceding them with the REM keyword, although it does not make much sense to do so:

'This is a comment
Rem this is also a comment in VBA!

VBA Comments – Best practices

Making VBA comments is not hard. It is the rule of making proper comments in the right place that make a difference. Below I am list a couple best practices that I think anyone writing Visual Basic for Applications scripts should adopt.

Commenting procedures

Make sure to place comments within VBA procedures (Functions and Subs) that provide a more complex capability.

Comment the purpose of procedures (Subs and Functions) . Ideally each procedure should accomplish a single tasks and have no more lines of code than can be made visible on your screen. A procedure should also be described by:

  • Explaining the purpose / goal of the procedure e.g. read file
  • If needed, describing the parameters and outputs

A simple example below:

Function ReadFile(fileName as String, length as Long) as String
'Read a text file, and return [length] first characters
'fileName - name of the file to open
'length - number of characters to read
  '...
  '...
End Function

Comment associated blocks of code. Usually your procedure will consist of several block of code, each one realizing a different activity. Providing even a single line of comment to explain each block of code will make your code more readable.

Explain conditional statements. Conditional statements are your codes Fork to different execution pathways. If the code does not speak for itself it makes sense to provide some verbatim.

If age < 18 Then 'Can't drink or drive
  '...
ElseIf age < 21 Then 'Can drive, can't drink
  '...
Else 'Can drink and drive. But not simultaneously :)
  '...
End If
ActiveWorkbook vs ThisWorkbook

VBA ActiveWorkbook vs ThisWorkbook

1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 5.00 out of 5)
Loading...

Often Excel / VBA Developers mix two distinguished properties in VBA when wanting to reference their Workbook – the first being the ActiveWorkbook object and the second being ThisWorkbook. In situations when running code on the same workbook you are working you needn’t really worry which reference you use as both will keep pointing at the same Workbook. The problem starts to arise when you work in an environment where there can be multiple Excel Workbooks and you may be executing code in one Workbook and wanting it to run/modify another open Workbook.

In today’s VBA Tip of the Day let’s explain the distinct difference between ActiveWorkbook vs ThisWorkbook.

ActiveWorkbook

The ActiveWorkbook is not just the visible Workbook as many would think, as you can just as well have multiple Workbooks open and placed side by side. However, only one Excel Workbook is considered Active in any point in time. Being Active is a top-most property – only the Workbook who’s window is currently selected is considered Active. See below for a precise definition:

Application.ActiveWorkbook or ActiveWorkbook in Excel / VBA:
Will return the Workbook object that represents the workbook in the active window (the window on top). The property will return Nothing if the Info window or the Clipboard window is the active window.

Using ActiveWorkbook may be risky sometimes see try running the following piece of code to see why:

Private Sub Workbook_Open()
    MsgBox Iif(Application.ActiveWorkbook Is Nothing, _
      "Current Workbook is not Active", _
      "Current Workbook is Active")
End Sub

ThisWorkbook

The ThisWorkbook property is much easier to understand as it simply references the Excel Workbook in which the VBA code is executing. For the full definition see below:

Application.ThisWorkbook or ThisWorkbook in Excel / VBA:
Will return the Workbook object that represents the workbook where the current macro code is running. Always return a Workbook object

ActiveWorkbook vs ThisWorkbook

If you are still confused about the difference between these two properties see below:

ActiveWorkbook vs ThisWorkbook
ActiveWorkbook vs ThisWorkbook

Hope that’s clear now!

Conclusions

Now that you can distinguish between both these properties it is important to keep a couple of things in mind:

  • On a daily basis use ThisWorkbook over the more erroneous ActiveWorkbook, when you expect your code to run on the same Workbook you are working on
  • Use ActiveWorkbook carefully, as it is a top-most property it might not always return a property if other Excel pop-up windows will be involved. Although you might need the ActiveWorkbook property when working with Excel AddIns (which execute code on the Active Workbook not your AddIn)
VBA ParamArray

The VBA ParamArray for a dynamic list of VBA arguments

1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 4.00 out of 5)
Loading...

Today’s VBA Tip of the Day concerns dynamic parameter declaration via the VBA ParamArray. When declaring Functions/Procedures in some cases you may need to specify Optional arguments. Let’s say we want to write a procedure that works similarly as other procedures you know natively from Excel like SUM:

Excel SUM
Excel SUM takes any number of arguments separated by the locale array delimiter (; or ,)

Now how would you create such a Function in VBA? Well certainly NOT like THIS!!!:
SUM Excel example
Optional arguments are nice to use, only when you know there is going to be only a few. But in the above example we may as well get 20 different Excel Ranges!

Interestingly, this totally non-sense approach is used by Microsoft Excel WorksheetFunctions. See for yourself:
WorksheetFunctions.Sum declaration
WorksheetFunctions.Sum declaration
If only the guy writing this knew about ParamArrays…

Use the VBA ParamArray

In the above example, when dealing with a large amount of optional parameters use the VBA ParamArray statement in your Function/Procedure declaration:

Public Function MySUM(ParamArray args())
    For Each arg In args
        MySUM = MySUM + arg
    Next arg
End Function

Notice that I replaced Optional arg1, Optional arg2, Optional arg3, Optional arg4, Optional arg5 with ParamArray args(). Cool huh? Well that’s basically it! Nothing hard there!

Remember! The ParamArray statement works only with Variant variables. So don’t try the following

ParamArray args() as String

Also the ParamArray statement can’t be used with the following variable statements:

  • ByVal
  • ByRef
  • Optional

That’s it for today’s VBA Tip of the Day!

Next steps

Be sure to read my other posts on:
VBA Array Tutorial
VBA Collection Tutorial
VBA Dictionary and other data structures

Short VBA Code

Shorter VBA code!

1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 4.50 out of 5)
Loading...

Today let’s expand on how to create shorter VBA code that is easier to read and manage. Writing VBA code that works is usually the tip of the iceberg for most. However, more importantly it is useful to be able to write VBA code that is clear and concise. Shorter and concise blocks of code are usually easier to maintain and debug, hence allowing you to located errors more quickly than in blocks of code that are long and chaotic.

Wrapping variable declaration

Variables should always be declared at the beginning of the current scope – whether it’s the scope of a procedure, class or module. VBA allows you to merge your variable declarations to a single row, thus making you code more concise. Remember to use the Option Explicit statement if you want the compiler to throw errors if a variable was not declared – a very useful best practice. Remember not to wrap too many variable declarations – so they will require scrolling to the right afterwards!

Instead of this:

Dim i As Integer
Dim str As String
Dim dat As Date

Do this:

Dim i As Integer, str As String, dat As Date

Wrapping lines of codes

The “:” character can be used in most cases to consolidate several lines of code into 1.

Instead of this:

i = 10
str = "Hello"
dat = Now

Do this:

i = 10: str = "Hello": dat = Now

Consolidating rows is especially useful when wanting to declare and define a variable in 1 line as we are used to in most other programming languages, as such:

Dim i as Integer: i = 10

The VBA With Statement

The With statement defines a scope which refers to a single object and allows to interact directly with properties of that object without having to reference the object every time. The “.” character is used directly to reference properties within that object.

Instead of this:

Cells(1, 1).Font.Color = RGB(10, 20, 30)
Cells(1, 1).Font.Bold = True
Cells(1, 1).Font.Size = 20

Do this:

With Cells(1, 1).Font
  .Color = RGB(10, 20, 30)
  .Bold = True
  .Size = 20
End With

Although this may not save you lines of code, but allows you to remove the redundant references to your object and makes sure each line reference the same object.

Use Iif instead of If-Else when possible

The Iif function is defined as follows:

IIf ( [boolean expression] , [return if true] , [return if false] )

The Iif function returns either value depending on the evaluation of the boolean expression. It can easily replace a single If-Else statement in scenarios where a variable needs to be defined. You can also nest several Iif functions if needed to replace If-ElseIf-Else statements.

Instead of this:

Dim i as Integer, str as String

If i = 10 Then
  str = "=10"
Else
  str = "<>10"
End If

Do this:

Dim i as Integer, str as String

str = IIf(i = 10, "=10", "<>10")

Reference Named Ranges directly

Named Ranges can be referenced directly by escaping the Named Range name with square brackets “[ ]”.

Instead of this:

ThisWorkbook.Names("MyNamedRange").RefersToRange.Value = "Hello there!"

Do this:

[MyNamedRange] = "Hello there!"

Encapsulate reusable objects

Often you will find the need to reuse certain variables or sets of variables within your code. It is a best practice to encapsulate such properties/variables into objects such as a VBA Class, VBA Enum or VBA Type object.

Often we find variables that are strongly associated, or describe properties of a single object. In the example below notice how the code is illegible:

'So many variables... all describing obviously 3 different vehicles
Dim car1Type as String, car1FuelLeft as Double, car1Miles as Long, car1License as String, car1Miles as Long
Dim car2Type as String, car2FuelLeft as Double, car2Miles as Long
Dim car3FuelLeft as Double, car3License as Double, car3Miles as Long
car1Type = "Sedan"
car2Type = "Hatchback"
car3Type = "Sedan"
'...What a mess...

Now let’s rewrite the code again:

Enum CarType
    Sedan
    Hatchback
    SUV
End Enum

Type Car
    myType as CarType    
    myLicense As String
    myMiles As Long
    myFuel as Double
End Type
'...
Dim car1 as Car, car2 as Car, car3 as Car
car1.myType = CarType.Sedan
car2.myType = CarType.Hatchback
car3.myType = CarType.Sedan
'... So much better...

Notice the second approach is so much easier to read and maintain – when we encapsulate the Car properties into a Car Type object and the CarType enumeration into an Enum object. It might not look shorter at first glance. But believe me it will definitely translate to less cluster and fewer bugs. Enumerations are especially useful as VBA “Intelisense” will hint the possible values within the enumeration.

Similarly you might also want to learn how to use VBA Class modules to encapsulate not only variables but also object methods (Functions and Procedures).

Rule of Thumb – Entire VBA procedure visible without scrolling

Shortening your code makes sense when you are optimizing the amount of space used by VBA code in a smart way – in order to reduce the need for scrolling (in both the horizontal and the vertical dimensions) which is simply a waste of your coding time. You are advised to used the suggestions for shortening your VBA code wisely. However, above all you need to learn…

Rule of the Thumb: An entire Procedure should be visible w/o scrolling

This is one of the most important rules I learned early on – the scope of an entire Procedure should be visible without scrolling on your screen. Ok, but what if my procedure is too long? Split it into separate procedures!
vba_screen
Why use this Rule of Thumb?:

  • Easier to read – you can more easily understand what the procedure is meant to do if you can see what it does end-to-end
  • Encourages code re-usability – group blocks of code that can be reused frequently into separate procedures
  • Easier debugging – when you have divide one huge procedure into smaller consecutive parts debugging takes less time as you can quickly jump over whole logical blocks of code (F8 vs SHIFT+F8)

Example: Smart procedure split

Let’s use our newly learned Rule of Thumb in a simple exercise. See the procedure below obstructed partially by the bottom panel of our VBA Project view:

Obstructed VBA Procedure
Obstructed VBA Procedure

Code:

Sub SomeProcedure()
    'Load file
    Dim fileName As String, fileNo As Long, textData As String
    fileName = "..."
    fileNo = FreeFile
    Open fileName For Input As #fileNo
    Do While Not EOF(fileNo)
       Line Input #fileNo, textRow
       textData = textData & textRow
    Loop
    Close #fileNo
    
    'Save file
    fileName = "..."
    fileNo = FreeFile
    Open fileName For Output As #fileNo
    Print #fileNo, textData
    Close #fileNo
End Sub

What we need to do is split this procedure so that our code is visible within a single screen. It makes sense to group blocks of code that provide some logical feature. In the example above it’s obviously 2 separate blocks – the 1st one reading in a text file, and the second one which dumps the text into a separate file:

Sub NewSomeProcedure()
    Dim textData As String
    
    'Load file
    textData = LoadFile("...")

    'Save file
    Call SaveFile("...", textData)
End Sub

Function LoadFile(fileName As String)
    Dim fileNo As Long, textData As String
    fileNo = FreeFile
    Open fileName For Input As #fileNo
    Do While Not EOF(fileNo)
       Line Input #fileNo, textRow
       textData = textData & textRow
    Loop
    Close #fileNo
    LoadFile = textData
End Function

Sub SaveFile(fileName As String, textData As String)
    Dim fileNo As Long
    fileNo = FreeFile
    Open fileName For Output As #fileNo
    Print #fileNo, textData
    Close #fileNo
End Sub

Much better! Notice each procedure is now easy to read and does not require scrolling. What is more our new procedures – LoadFile and SaveFile can well be reused in future cases which will definitely translate ultimately into less lines of code in your VBA Project.

Summary

Got any more tips? Do share in the comments!

autofilter featured

Automatic Excel Autofilter

1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 5.00 out of 5)
Loading...

With today’s Excel tip of the day let’s have some fun with learning how to create an auto-applying filter to an Excel Table (or range). Let’s jump right to see how we expect our Excel Autofilter to work:

excel autofilter
Excel Autofilter: Type while the table is filtered
We want to achieve the following behavior where our Table column is filtered as we type. This is a very convenient feature especially if you need to dynamically sit through a large dataset of different values. It is perfectly possible to be done in Excel with just a little VBA code.

The whole exercise takes just a minute or so – therefore I find the Excel Autofilter a really useful and flexible tool to use.

How to create an Autofilter

Feel free to review my video or read through for a step-by-step tutorial:

Let’s assume we have a similar dataset as shown above. This does not need be limited to a single column but can be spread out across as many columns we want. To facilitate this example I have however limited the data just to 1 data column of a simple list of first names.

Convert the Range to a Table

This step is not necessary, although will make matters much easier when wanting to create the macro as we move on.

convert to table
Convert the column to an Excel Table

Insert a TextBox from the Developer tab

Now we need to go to the DEVELOPER tab and select Insert and pick Text Box from the ActiveX Controls section.

insert a textbox
Insert a TextBox control

It is suggested that you name your text box appropriately e.g. “NameTextBox”.

Add filtering macro to TextBox Change event

Now the hard part. To create our Excel Autofilter we need to make sure that a filtering event is triggered whenever we want it to happen. Fortunately the TextBox ActiveX Control has a Change Event. The Change Event is triggered whenever the text in the Text Box is modified.

Open the DEVELOPER tab and open your VBA Project using the Visual Basic button. Now make sure to open the Worksheet on which your data and the Text Box is located as shown below:

macro placement
Open the Worksheet where the filtering takes place
.
Now assuming the textbox is named “NameTextBox” and your Table is named “Names” paste the following macro into your Worksheet module:

Private Sub NameTextBox_Change()
    ActiveSheet.ListObjects("Names").Range _
        .AutoFilter Field:=1, Criteria1:="=*" & NameTextBox.Text & "*"
End Sub

Notice that the criteria is specified as follows “=*” & NameTextBox.Text & “*”. How to understand this? The “*” symbol is a wildcard which captures any number of characters (0 or more). Hence this expression will capture any string containing the text we type in our text box. If you want to filter names beginning with the string you are typing simply replace it with: “=” & NameTextBox.Text & “*” – notice I removed the first “*”.

Download the example

Feel free to download the working Excel Autofilter example here: