Posts Tagged ‘Advanced’


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


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

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


Although this is not my personal favorite chart, I have seen that many people find it fabulous since it looks good on a dashboard. I think that it takes too much space for the small information that it conveys. But then, that’s just me.

Gauge Chart

Gauge Chart

(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.


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….   😉


As you can see from the picture, this chart can be used when you want to compare two entities on same parameters. Here, I’m comparing sales at two stores of some items.

Butterfly Chart

Butterfly Chart

To create the chart, follow these steps: (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…)