Posts Tagged ‘Trick of the Week’

Problem Statement: I have a database of employees where I store information about each person. I wish to have a picture of the employee in my database that I want to retrieve when I select an employee ID. Solution: I am sharing a VBA code that saves the file in SQL server table in binary format. When the file is retrieved, it can be done in original format. Let’s say I’ve a table Employee, it has two columns ID (Datatype: int) and Image (Datatype: varBinary(Max)). Code to store an image as binary type: (more…)

In my experience I have come across numerous people who so wish that their charts are ‘Interactive’. I’m sure many of you would like to select a data point on your chart and let it act as a selection for sort of a drill-down. Doing this, although not impossible in Excel, requires sound understanding of Class Modules and chart events. I have tried to create a small example to illustrate this.

Problem Statement:I have  data for daily site views for USA, Canada and the rest of North America. While there is a top chart that shows the cumulative counts, I would like to select a data point on the chart and see a detailed view for it as below.

Chart Event Example

Chart Event Example


When your Excel dashboard has multiple users, you might want to customise the options available to different sets of users.

Scenario 1: You created a sales dashboard, you might want pricing column hidden from certain people, or you might want only relevant products visible to the suppliers, or you may want to call the same report by different names for different sets of people and so on and so forth. Essentially, you want to control what you want to show to whom.

Scenario 2: You have an Excel report with huge volumes of data of all regions. You want to set your print area such that each region can print only their own data and the header is customised as per their region name. (more…)

While Preparing an Excel Dashboard one needs to handle a lot of hiding and unhiding of Sheets for various users. For Devloper all sheets should be unhidden and for user some of them should visible. We need different views of one workbook for different people developer, user, admin, etc.
We can handle this with using a Custom View property of the workbook with VBA

ActiveWorkbook.CustomViews.Add Viewname, PrintSettings(value True/False), RowColumnSettings(value True/False)


We protect a worksheet so that it can not be modified and this protection applies to VBA operations and user actions both. Some VBA operations like pasting data cannot be done on a protected worksheet. Now to perform these operations on that sheet we need to unprotect it, do the operations and then protect it again.

Sub EditSheet()

    ThisWorkbook.Worksheets("Sheet1").Unprotect Password:="1234"
    ' Code to perform some operations to edit the sheet
    ThisWorkbook.Worksheets("Sheet1").Protect Password:="1234"

End Sub

Although the above technique works but we need to wrap our code between Unprotect and Protect statements in every procedure that performs some operations on the protected sheet. So if we need to protect our worksheet from user actions only then there is a better way of doing it. (more…)

VBA Trick of the Week :: Hiding Members of Enum

Posted: July 19, 2013 by Transformer in Excel, VBA
Tags: , ,

If you want to prevent a member of Enum from being displayed in VB editor’s intellisense then it can be done by prefixing them with ‘_’ and then putting them in square brackets.

Enum Role
End Enum


In the above image it can be seen that [ _HideMe] is not displayed in intellisense.

Converting a Range to Recordset is a very painfull process. Currently the process which we follow is establishing a connection and then firing a Query to get the values in to a Recordset.
But now we can make a new and better approach using the Range.Value() property.
This is a better way to move data from one workbook to another without making use of the Clipboard.

Sub Test()

        Dim xlXML             As Object
        Dim adoRecordset      As Object
        Dim rng               As Range

        Set rng = ActiveSheet.Range("A1:D20")
        Set adoRecordset = CreateObject("ADODB.Recordset")
        Set xlXML = CreateObject("MSXML2.DOMDocument") 
        xlXML.LoadXML rng.Value(xlRangeValueMSPersistXML)
        adoRecordset.Open xlXML

End Sub

This adorecordset will hold the range in the form of recordset and one make use of this in the way it is required.
Happy Coding….   😉

When an error occurs in your VBA project then it is extremely valuable to know what caused it so that you know how to counter it. Erl function returns the line number where an error occurs. For using this, your lines of code should be numbered (The line numbers don’t have to be in any order, you can assign random numbers to the lines of code). If the line of code where the error occurred is not numbered then it will return the line number which has been numbered before this. If none of the lines are numbered then it will return 0.

Sub Test_Erl()        
    Dim lngval          As Long    
    On Error GoTo ErrHandler     
1   Debug.Print "Error handler enabled"
2   lngval = 2 / 0        
    MsgBox "An error occured in line: " & Erl    
End Sub


When we press [Alt] + [F8], a macro explorer window is displayed, which lists all available macros (Public) in the current project even if the project is password protected. In this window, we can select any macro and can run/execute it. That might be undesirable sometimes.

Using Option Private Module at the top of a module prevents all macros of that module from being displayed in the macro list.


  Option Private Module
      Sub Test1()
      End Sub
      Sub Test2()
      End Sub

In the above example, procedures Test1 and Test2 will not be displayed in the macro explorer window because Option Private Module is written at the top of the module.

Basic use of  Option Private Module is to prevent macros and variables from being accessed by outside of the current projects. For more details

VBA Trick of the Week :: Conditional Compiling

Posted: June 26, 2013 by MaxFraudy in Excel, VBA

In conditional compilation, particular blocks of code in a program are compiled selectively while others are ignored (msdn)

Where Can Conditional Compiling be Used?

There are many situations where you might want to ignore certain parts of code based on a condition. e.g. you may want to create different versions based on Excel version or local language settings or some other parameter. This can be done using conditional compiling. The syntax is as below:

Dim mVers   As String

Sub Init()

    #If Win64 Then
        mVers = "Win64" ' Win64=true, Win32=true, Win16= false
        Call VerCheck
    #ElseIf win32 Then
        mVers = "Win32"  ' Win32=true, Win16=false
        Call VerCheck
    #ElseIf win16 Then
        mVers = "Win16"  ' Win16=true
        Call VerCheck
    #End If

End Sub

Sub VerCheck()
    MsgBox "Version: " & mVers, vbInformation, "Version"
End Sub

Here, the variable mVers is being assigned a value at compile time based on whether it’s a 16-bit, 32-bit or a 64-bit environment. If it’s a 32-bit environment the variable will hold “Win32” value, if it’s 16-bit then “Win16”, otherwise “Win64”.

Similarly, you can conditionally compile blocks of code by checking for the values of other available compiler constants, the list of which can be found here

You could also define your own Compiler Constants as below: (more…)