Author Archive


Reverse geocoding is the process of taking a set of GPS coordinates and turning it into the nearest available address.
Browsing through the net, I found VBA code on many websites to get GPS coordinates(latitude & longitude) from a location address but found it really hard to get a reverse geocoding in VBA.
It can be achieved using following Google API:
http://maps.googleapis.com/maps/api/geocode/json?latlng=-33.856098662,150.996315097333&sensor=false

It will return JSON, that can be parsed to get the nearest available location.
e.g.


Function GEOAddress(dblLatitude As Double, dblLongitude) As String
         
    Dim strJSON         As String
    Dim strAddress      As String
    Dim lngTemp         As Long
    Dim objXml          As Object
    Dim strUrl          As String
        
    strUrl = "http://maps.googleapis.com/maps/api/geocode/json?latlng=" & dblLatitude & "," & dblLongitude & "&sensor=false"
    Set objXml = CreateObject("Microsoft.XMLHTTP")
    With objXml
        .Open "GET", strUrl, False
        .send
        strJSON = .responseText
    End With
    Set objXml = Nothing
        
    lngTemp = InStr(1, strJSON, "formatted_address")
    strAddress = Mid(strJSON, lngTemp + 22, InStr(lngTemp, strJSON, """,") - (lngTemp + 22))
    GEOAddress = strAddress
    
End Function

Happy Coding ūüôā


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…)

The Ultimate Correlated SQL Query

Posted: August 23, 2013 by Transformer in MS Access, SQL
Tags: , ,

Problem Statement:¬† There is a table named SkillSet which has employees’ skill details. We need to find out employees (skill wise) who have the highest proficiency rating. If multiple employees have the highest proficiency rating in some skill then the employee having more experience should be selected.

Employee Skills Proficiency Experience
Thor SQL 9 1
Thor VBA 8 1
Mike VBA 9 1
Mike SQL 6 1
Robert VBA 9 2
Robert C# 6 2
Robert SQL 9 1
Logan C# 9 1
Logan SQL 9 2

Solution:Lets do it in steps. Try to find out skill wise employees having maximum proficiency. (more…)


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.
e.g.

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.
e.g.

Enum Role
     AVP
     Manager
     Analyst
     [_HideMe]
End Enum

Enum

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


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        
ErrHandler:
    MsgBox "An error occured in line: " & Erl    
End Sub

(more…)


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.

e.g.
   

  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 http://msdn.microsoft.com/en-us/library/aa266185(v=vs.60).aspx


Some times we need to compare data located in different parts of a worksheet.Untitled
e.g. I have thousands of records in a sheet and i need to compare 1 to 25 rows with 100 to 125 rows then it is not easy to compare them in a single screen. I will have to scroll up and down again and again.
In that case splitting the worksheet into different panes makes it easy.

There are two ways to split a screen:
1. Using split box located at the top of the vertical scroll bar or the far right end of the horizontal scroll bar.

Split Box

Just pull down that box to split the screen. (more…)

Conditional Insert Query Based on Data in Target Table

Posted: June 23, 2013 by Transformer in MS Access, SQL
Tags: ,

Problem Statement: There is a table named Employee which has department wise employee details as given below. We want to insert some new records in this table but a record should not be inserted if it is already there in the table.


Dept	Employee_ID	Gender
IT	E123	          M
IT	E124	          M
IT	E125	          M
IT	E126	          M
IT	E127	          F
IT	E128	          F
HR	E129	          M
HR	E130	          F
HR	E131	          F
HR	E132	          F
HR	E133	          M

Solution: This can be done using the following query where we use EXISTS to check whether or not that record already exists.


INSERT INTO Employee (Dept,Employee_ID,Gender)

   SELECT  TOP 1 'HR' , 'E132','F' 
       FROM
         EMPLOYEE 
  WHERE Not EXISTS 
         (SELECT 1 
             FROM 
               Employee 
          WHERE Dept = 'HR' AND Employee_ID = 'E132'  AND Gender = 'F')

The above query will not insert the record in the table since it already exists.
Here TOP 1 has been used to create a single record to insert otherwise it will return multiple records(No of records in the Employee Table) with the same values.


At times we need to change cell values in a range based on a calculation. The most common way of doing it is using loop.

e.g.  We have numeric data in range A1:B10 of Sheet1 and we need to multiply all cells of this range by 2.

Sub Test()

    Dim rngData     As Range
    Dim rngCell     As Range

    Set rngData = ThisWorkbook.Worksheets("Sheet1").Range("A1:B10")    
    For Each rngCell In rngData
        rngCell = rngCell * 2
    Next

End Sub

In the above example, Evaluate can be used instead of looping through cells, it works faster than a loop. (more…)