Wednesday, 21 March 2018

Last updated: 29/03/2018, 1 min read (without the code)

Reverse Geocoding Using VBA & Google API


After updating the existing VBA functions that dealt with Google APIs, it is time to introduce a new function that can be used for Reverse Geocoding, a short definition of which, based on Wikipedia, is the following: “Reverse geocoding is the process of back (reverse) coding of a point location (latitude, longitude) to a readable address or place name. This permits the identification of nearby street addresses, places, and/or areal subdivisions such as neighbourhoods, county, state, or country”.

The developed VBA function can be utilized directly from Excel, as long as the user provides a valid pair of latitude and longitude. The GetAddress function sends a request to the Google server and, then, uses its XML response to read the appropriate information (the formatted address in particular). If you are curious how the server response looks like, see the picture below, where the main nodes are highlighted in red boxes. This function does the exact opposite of the GetCoordinates function that was written a few years ago.

Reverse Geocoding Response

Apart from latitude and longitude, the function incorporates an optional third parameter (ResultTypeFilter). This parameter acts as a filter, by reducing/filtering the results returned from the server. In the code comments, you will find the values that are supported.

VBA code

Below you will find the VBA code for the GetAddress function. Bear in mind that the use of the Google Geocoding API is subject to a limit of 2500 requests per day, so be careful not to exceed this limit. To use this VBA function you will need a valid API key. Check this link that presents a step-by-step guide on how to acquire one for free.

Option Explicit

Function GetAddress(Latitude As Double, Longitude As Double, Optional ResultTypeFilter As String) As String
    'This function returns the address of a given latitude, longitude pair using the Google (Reverse) Geocoding API.
    'The optional paramter ResultTypeFilter is a filter of one or more address types, separated by a pipe (|).
    'If the parameter contains multiple address types, the API returns all addresses that match any of the types.
    'A note about processing: The result_type parameter does not restrict the search to the specified address type(s).
    'Rather, the result_type acts as a post-search filter: the API fetches all results for the specified latlng,
    'then discards those results that do not match the specified address type(s).
    'The following values are supported:
    'street_address: indicates a precise street address.
    'route: indicates a named route (such as "US 101").
    'intersection: indicates a major intersection, usually of two major roads.
    'political: indicates a political entity. Usually, this type indicates a polygon of some civil administration.
    'country: indicates the national political entity, and is typically the highest order type returned by the Geocoder.
    'administrative_area_level_1: indicates a first-order civil entity below the country level. Within the United States,
        'these administrative levels are states. Not all nations exhibit these administrative levels.
        'In most cases, administrative_area_level_1 short names will closely match ISO 3166-2 subdivisions and other
        'widely circulated lists; however this is not guaranteed as our geocoding results are based on a variety of
        'signals and location data.
    'administrative_area_level_2: indicates a second-order civil entity below the country level. Within the United States,
        'these administrative levels are counties. Not all nations exhibit these administrative levels.
    'administrative_area_level_3: indicates a third-order civil entity below the country level.
        'This type indicates a minor civil division. Not all nations exhibit these administrative levels.
    'administrative_area_level_4: indicates a fourth-order civil entity below the country level.
        'This type indicates a minor civil division. Not all nations exhibit these administrative levels.
    'administrative_area_level_5: indicates a fifth-order civil entity below the country level.
        'This type indicates a minor civil division. Not all nations exhibit these administrative levels.
    'colloquial_area" indicates a commonly-used alternative name for the entity.
    'locality: indicates an incorporated city or town political entity.
    'ward: indicates a specific type of Japanese locality, to facilitate distinction between multiple
        'locality components within a Japanese address.
    'sublocality: indicates a first-order civil entity below a locality. For some locations may receive one of the
        'additional types: sublocality_level_1 to sublocality_level_5. Each sublocality level is a civil entity.
        'Larger numbers indicate a smaller geographic area.
    'neighborhood: indicates a named neighborhood
    'premise: indicates a named location, usually a building or collection of buildings with a common name
    'subpremise: indicates a first-order entity below a named location, usually a singular building within
        'a collection of buildings with a common name
    'postal_code: indicates a postal code as used to address postal mail within the country.
    'natural_feature: indicates a prominent natural feature.
    'airport: indicates an airport.
    'park: indicates a named park.
    'point_of_interest: indicates a named point of interest. Typically, these "POI"s are prominent local entities that
        'don't easily fit in another category, such as "Empire State Building" or "Statue of Liberty."
    'The function ignores parameters such as language and location_type and returns always the FIRST RESULT.
    'NOTE: As Google points out, the use of the Google Geocoding API is subject to a limit of 2500
    'requests per day, so be careful not to exceed this limit. For more info check:
    'In order to use this function you must enable the XML, v3.0 library from VBA editor:
    'Go to Tools -> References -> check the Microsoft XML, v3.0.
    'Moreover, to use this function you will also need a valid API key.
    'Check the next link that guides you on how to acquire a free API key:
    'Written By:    Christos Samaras
    'Date:          17/03/2018
    'Last Updated:  29/03/2018
    'Declaring the necessary variables. Using 30 at the first two variables because it
    'corresponds to the "Microsoft XML, v3.0" library in VBA (msxml3.dll).
    Dim ApiKey          As String
    Dim Request         As New XMLHTTP30
    Dim Results         As New DOMDocument30
    Dim StatusNode      As IXMLDOMNode
    Dim AddressNode     As IXMLDOMNode

    'Set your API key in this variable. Check this link for more info:
    ApiKey = "Your API Key goes here!"
    'Check that an API key has been provided.
    If ApiKey = vbNullString Or ApiKey = "Your API Key goes here!" Then
        GetAddress = "Invalid API Key"
        Exit Function
    End If
    'Check the input variables:
    'The valid range of latitude in degrees is -90 and +90 for the Southern and Northern hemisphere respectively.
    If Latitude < -90 Or Latitude > 90 Then
        GetAddress = "Invalid Latitude value"
        Exit Function
    End If

    'Longitude is in the range -180 and +180 specifying coordinates West and East of the Prime Meridian, respectively.
    If Longitude < -180 Or Longitude > 180 Then
        GetAddress = "Invalid Longitude value"
        Exit Function
    End If
    'Generic error handling.
    On Error GoTo errorHandler
    'Create the request based on Google's (Reverse) Geocoding API. Parameters:
    '- latlng: The latitude and longitude values specifying the location for which you wish to obtain the closest, human-readable address.
    '- key: Your application's API key. This key identifies your application for purposes of quota management.
    'Differentiate the request if a filter is provided.
    If ResultTypeFilter = vbNullString Then
        Request.Open "GET", "" _
                            & "latlng=" & Latitude & "," & Longitude & "&key=" & ApiKey, False
        Request.Open "GET", "" _
                            & "latlng=" & Latitude & "," & Longitude & "&result_type=" & ResultTypeFilter & "&key=" & ApiKey, False
    End If
    'Send the request to the Google server.
    'Read the results from the request.
    Results.LoadXML Request.responseText
    'Get the status node value.
    Set StatusNode = Results.SelectSingleNode("//status")
    'Based on the status node result, proceed accordingly.
    Select Case UCase(StatusNode.Text)
        Case "OK"   'The API request was successful. At least one result was returned.
            'Get the formatted address of the first result.
            Set AddressNode = Results.SelectSingleNode("//result/formatted_address")
            'Return the address.
            GetAddress = AddressNode.Text
        Case "ZERO_RESULTS"   'The geocode was successful but returned no results.
            GetAddress = "The address probably not exists"
        Case "OVER_QUERY_LIMIT" 'The requestor has exceeded the limit of 2500 request/day.
            GetAddress = "Requestor has exceeded the server limit"
        Case "REQUEST_DENIED"   'The API did not complete the request.
            GetAddress = "Server denied the request"
        Case "INVALID_REQUEST"  'The API request is empty or is malformed.
            GetAddress = "Request was empty or malformed"
        Case "UNKNOWN_ERROR"    'Indicates that the request could not be processed due to a server error.
            GetAddress = "Unknown error"
        Case Else   'Just in case...
            GetAddress = "Error"
    End Select
    'Release the objects.
    Set AddressNode = Nothing
    Set StatusNode = Nothing
    Set Results = Nothing
    Set Request = Nothing
End Function

NOTE: In case of multiple results, the function returns the first result, so be careful with your inputs. Tip: use the ResultTypeFilter parameter to reduce the results that are returned. For anyone who is interested to learn how the Google (Reverse) Geocoding API works, he/she can visit the corresponding page.



The file can be opened with Excel 2007 or newer. Please enable macros before using it.

Read also

How To Get A Free Google API Key
Geocoding Using VBA & Google API 
Custom Trip Distance Function (VBA & Google Directions API)
Custom Elevation Function (VBA & Google API)

Saturday, 24 February 2018

Last updated: 24/02/2018, 2 min read

How To Get A Free Google API Key


During the previous years, I published several VBA functions that used Google web services, such as Geocoding, Elevation, and Directions. These functions worked in the same way: a properly formatted request was sent to Google server, and, then, a response was received in XML format. If the response was successful, the requested field was retrieved (latitude, longitude, elevation, trip distance etc.) by reading the XML file. These VBA functions working fine for several years, however, Google started to not allow the requests over HTTP protocol anymore, so these functions have become useless. This is the reason why I decided to rewrite all of these VBA functions while guiding you on how to get a free Google API Key that is now required.

Steps to get a valid API key

The tutorial below can be used for ANY Google API. As an example, I use the Geocoding API. The only prerequisite for this process is to just have a Google account.

1.    First of all, log in to your Google Account.

Logged In With Gmail Account

2.    Search for the appropriate API by using something like “Google API Name API Key”. The first result will probably be the one you need to click.

Search For API Key

3.    On the API page, click on the Get A key button.

Navigate To Google API Page

4.    You need to select the Yes option on the radio button. If you need to change the default project name, then click on “My Project”.
Enable API
5.    Select the Create a new project option to name your new project.
Create A New Project
6.    Rename the project and press the Next button.
New Project
7.    After waiting for a few seconds, your API key will pop up. You can simply copy it and then press the Done button. However, I suggest you click on the API console hyperlink.
Get The API Key
8.    On the page that opens on a new browser tab you can rename your API key, but more importantly, you can restrict its access (if you need to do it). Furthermore, you can regenerate or even delete the key if you no longer need it.

Console Options

9.    If you click on the Dashboard option on the top left of the page, you can see useful information for the particular project, such as the requests, the errors per hour etc. Note that you can use multiple APIs on the same project. In this example, I use only the Geocoding API on my test project.

Console Dashboard

10.    Finally, the previous step is useful (Dashboard) because each API has some limits for free usage. You can check these limits on the corresponding page and make sure you don’t exceed them!

API Usage Limits


If you followed these easy steps, you will get a valid API key that you can use it on your projects. To save you some time, here are the links to the API key pages that I have used in the VBA functions presented in this blog:

Sunday, 18 February 2018

Last updated: 19/02/2018, 2 min read (without the code)

Get Image Size In Pixels With VBA


People who are interested in digital photography taking seriously the size of the images that they take. The dimensions of an image – width and height – can be easily spotted in the Windows Explorer. If you hover your mouse over an image file you will see a pop window showing its dimensions.

Get Image Size By Hovering

Alternatively, you can right-click on an image file, select properties on the menu that pop-ups and then, in the Properties form, go to the Details tab. Close to the end, there is the image section where the dimensions, the width and height of the image is shown (in pixels).

Get Image Size Using File Properties

But, is there a way to get programmatically the size of an image with VBA? The answer is YES, and, unlike a previous post, does NOT involve Photoshop!

VBA code

The GetImageSize function uses the Microsoft Windows Image Acquisition Library v2.0 in order to retrieve the necessary information from an image file. The function returns an array of integers that hold the image width and height in pixels. The FileExists and the IsValidImageFormat functions check if a file exists and if a given path corresponds to a valid image file format respectively.

Option Explicit

Function GetImageSize(ImagePath As String) As Variant
    'Returns an array of integers that hold the image width and height in pixels.
    'The first element of the array corresponds to the width and the second to the height.
    'The function uses the Microsoft Windows Image Acquisition Library v2.0, which can be
    'found in the path: C:\Windows\System32\wiaaut.dll
    'However, the code is written in late binding, so no reference is required.
    'Written By:    Christos Samaras
    'Date:          18/02/2018
    'Declaring the necessary variables.
    Dim imgSize(1)  As Integer
    Dim wia         As Object
    'Check that the image file exists.
    If FileExists(ImagePath) = False Then Exit Function
    'Check that the image file corresponds to an image format.
    If IsValidImageFormat(ImagePath) = False Then Exit Function
    'Create the ImageFile object and check if it exists.
    On Error Resume Next
    Set wia = CreateObject("WIA.ImageFile")
    If wia Is Nothing Then Exit Function
    On Error GoTo 0
    'Load the ImageFile object with the specified File.
    wia.LoadFile ImagePath
    'Get the necessary properties.
    imgSize(0) = wia.Width
    imgSize(1) = wia.Height
    'Release the ImageFile object.
    Set wia = Nothing
    'Return the array.
    GetImageSize = imgSize

End Function

Function FileExists(FilePath As String) As Boolean
    'Checks if a file exists (using the Dir function).

    On Error Resume Next
    If Len(FilePath) > 0 Then
        If Not Dir(FilePath, vbDirectory) = vbNullString Then FileExists = True
    End If
    On Error GoTo 0
End Function

Function IsValidImageFormat(FilePath As String) As Boolean
    'Checks if a given path is a valid image file.
    'Declaring the necessary variables.
    Dim imageFormats    As Variant
    Dim i               As Integer
    'Some common image extentions.
    imageFormats = Array(".bmp", ".jpg", ".gif", ".tif", ".png")
    'Loop through all the extentions and check if the path contains one of them.
    For i = LBound(imageFormats) To UBound(imageFormats)
        'If the file path contains the extension return true.
        If InStr(1, UCase(FilePath), UCase(imageFormats(i)), vbTextCompare) > 0 Then
            IsValidImageFormat = True
            Exit Function
        End If
    Next i
End Function

Sample workbook

The sample workbook contains two sheets: the first one (Function Examples) presents several ways of using the GetImageSize function directly from the worksheet. The examples involve the CELL, TRANSPOSE and the INDEX function.

Get Image Size Function Examples

The second sheet (Batch Mode) can be used to get the size information of up to 100 different images. By inserting the image paths in column C, the width and height are automatically shown in columns D and E. If you need an easy way to retrieve the file paths form a folder, use this free tool.

Get Image Size In Batch Mode



The zip file contains a workbook with the two worksheets that presented above, the VBA code and a sample image to play with it. The workbook can be opened with Excel 2007 or newer. Please enable macros before using it.

Monday, 15 January 2018

Last updated: 15/01/2018, 1 min read (without the tables)

Pipe Friction Factor - Online Calculator


After a long time, I am returning to a subject that has bothered me many times in the past: friction factor. This time, however, I tried to switch my typical tools, so instead of an Excel/VBA solution, you will get a free online tool. The tool calculates the pipe friction factor based on Churchill's equation. If you have read any of my previous posts, you will probably remember that Churchill's equation is valid for the entire Reynolds range (e.g. any flow type). The equation involves the calculation of two intermediate values that are used in the final equation.

Churchill Equations


The calculator's usage is quite straightforward: just fill in the input values and press the Calculate button. If you want to re-use it, just clean the inputs and the results by pressing the Reset button. The tables below the calculator will provide you with some suggested values in the case of pipe roughness and kinematic viscosity of the fluid.


Roughness of the pipe material [μm]

Internal Diameter of the (circular) pipe [mm]

Discharge [m³/h]

Kinematic Viscosity of the fluid flowing through the pipe [cSt = 10−6 m²/s]


Fluid Velocity [m/s]

Reynolds [-]

Type of flow [-]

Friction Factor [-]

Table 1: Suggested roughness values for several pipe materials.

Pipe MaterialRoughness (μm)
Asbestos cement25
Cast iron (asphalted)122
Cast iron (uncoated)254
Concrete (steel forms, with smooth joints)180
Drawn brass1.5
Drawn copper1.5
Drawn tubing - glass, brass, plastic1.5
Fiberglass with epoxy3
Galvanized iron152
Seamless commercial steel (galvanised)150
Seamless commercial steel (light rust)250
Seamless commercial steel (new)25
Stainless steel45
Welded steel600
Wood stave600
Wrought iron45

Table 2: Suggested kinematic viscosity values for various liquids.

LiquidTemperature (C)Kinematic Viscosity (cSt)
Acetaldehyde CH3CHO16.10.305
Acetic acid - vinegar - 10% CH3COOH151.35
Acetic acid - 50%152.27
Acetic acid - 80%152.85
Acetic acid - concentrated glacial151.34
Acetic acid anhydride (CH3COO)2O150.88
Acetone CH3COCH3200.41
Alcohol - allyl201.6
Alcohol - butyl-n203.64
Alcohol - ethyl (grain) C2H5OH201.52
Alcohol - methyl (wood) CH3OH150.74
Alcohol - propyl202.8
Aluminum sulfate - 36% solution201.41
Asphalt RC-0, MC-0, SC-025159-324
Automatic crankcase oil SAE 10W-17.81295-max
Automatic crankcase oil SAE 10W-17.81295-2590
Automatic crankcase oil SAE 20W-17.82590-10350
Automatic crankcase oil SAE 2098.95.7-9.6
Automatic crankcase oil SAE 3098.99.6-12.9
Automatic crankcase oil SAE 4098.912.9-16.8
Automatic crankcase oil SAE 5098.916.8-22.7
Automotive gear oil SAE 75W98.94.2 min
Automotive gear oil SAE 80W98.97.0 min
Automotive gear oil SAE 85W98.911.0 min
Automotive gear oil SAE 90W98.914-25
Automotive gear oil SAE 14098.925-43
Automotive gear oil SAE15098.943 - min
Benzene (Benzol) C6H601
Bone oil54.447.5
Butyric acid n201.61
Calcium chloride 5%18.31.156
Calcium chloride 25%15.64
Carbolic acid (phenol)18.311.83
Carbon tetrachloride CCl4200.612
Carbon disulfide CS200.33
Castor oil37.8259-325
China wood oil20.6308.5
Coconut oil37.829.8-31.6
Cod oil (fish oil)37.832.1
Corn oil54.428.7
Corn starch solution, 22 Baumé21.132.1
Corn starch solution, 24 Baumé21.1129.8
Corn starch solution, 25 Baumé21.1303
Cotton seed oil37.837.9
Crude oil 48o API15.63.8
Crude oil 40o API15.69.7
Crude oil 35.6o API15.617.8
Crude oil 32.6o API15.623.2
Diethyl glycol21.132
Diethyl ether200.32
Diesel fuel 2D37.82-Ιουν
Diesel fuel 3D37.86-11.75
Diesel fuel 4D37.829.8 max
54.413.1 max
Diesel fuel 5D5086.6 max
71.135.2 max
Ethyl acetate CH3COOC2H3150.4
Ethyl bromide C2H5Br200.27
Ethylene bromide200.787
Ethylene chloride200.668
Ethylene glycol21.117.8
Formic acid 10%201.04
Formic acid 50%201.2
Formic acid 80%201.4
Formic acid concentrated201.48
Trichlorofluoromethane, R-1121.10.21
Dichlorodifluoromethane, R-1221.10.27
FDichloro-fluoromethane, R-2121.11.45
Fuel oil 121.12.39-4.28
Fuel oil 221.13.0-7.4
Fuel oil 321.12.69-5.84
Fuel oil 5A21.17.4-26.4
Fuel oil 5B21.126.4-
Fuel oil 65097.4-660
Gas oils21.113.9
Gasoline a15.60.88
Gasoline b15.60.64
Gasoline c15.60.46
Glycerine 100%20.3648
Glycerine 50% water205.29
Hydrochloric acid1.9
Ink, printers37.8550-2200
Insulating oil21.124.1 max
37.811.75 max
Jet Fuel-34.47.9
Lard oil37.841-47.5
Linseed oil37.830.5
Methyl acetate200.44
Methyl iodide200.213
Menhaden oil37.829.8
Molasses A, first37.8281-5070
Molasses B, second37.81410-13200
Molasses C, blackstrap37.82630-5500
Neatstool oil37.849.7
Olive oil37.843.2
Palms oil37.847.8
Peanut oil37.842
Petroleum ether15.631(est)
Phenol, carbolic acid11.7
Propylene glycol21.152
Quenching oil100-120
Rapeseed oil37.854.1
Rosin oil37.8324.7
Rosin (wood)37.8216-11M
Sesame seed oil37.839.6
Silicate of soda79
Sodium chloride 5%201.097
Sodium chloride 25%15.62.4
Sodium hydroxide (caustic soda) 20%18.34
Sodium hydroxide (caustic soda) 30%18.310
Sodium hydroxide (caustic soda) 40%18.3
Soya bean oil37.835.4
Sperm oil37.521-23
Sulphuric acid 100%2014.56
Sulphuric acid 95%2014.5
Sulphuric acid 60%204.4
Sulphuric acid 20%
Tar, coke oven21.1600-1760
Tar, gas house21.13300-66M
Tar, pine37.8559
Triethylene glycol21.140
Varnish, spar20313
Water, distilled201.0038
Water, fresh15.61.13
Water, sea1.15
Whale oil37.835-39.6

Read also

Sunday, 5 November 2017

Last updated: 06/11/2017, 1 min read (without the code)


Adobe Photoshop is probably the most famous raster graphics editor that is available on the market for almost three decades. You can do amazing things with this software. I have only played with it for a few hours, and I was quite surprised with the results. One of the things that intrigued me the most was the ability to program it. So, in this post I will take the opportunity to show you how to:

  • Resize an image.
  • Modify an image (apply filters).
  • Convert an image.

Here I will only scratch the surface on the topic of Photoshop VBA programming. However, I hope that it can give you some insights on what can be achieved.

VBA code

The code was written in early binding, just to allow the user to investigate the Photoshop's object model and the available options.

Option Explicit

Sub PlayingWithPhotoshop()

    'The macro demonstrates how to resize, modify (apply filters) and convert an image in Adobe Photoshop using VBA.
    'The macro requires the Adobe Photoshop library in order to work.
    'Go to Tools -> References -> Adobe Photoshop CSx Object Library, where x depends on your
    'Adobe Photoshop version (i.e. 6 or 5) you have installed on your PC.
    'Written By:    Christos Samaras
    'Date:          04/11/2017
    'Declaring the necessary variables.
    Dim InputImagePath      As String
    Dim OutputImagePath     As String
    Dim OutputWidthInPixels As Integer
    Dim PsApp               As Photoshop.Application
    Dim PsDoc               As Photoshop.Document
    Dim PsSaveOptions       As Photoshop.PNGSaveOptions
    'Set the necessary input variables.
    'The input image that will be modified. You can give the full path, e.g.:
    'InputImagePath = "C:\Users\Christos\Desktop\Input.jpg"
    InputImagePath = ThisWorkbook.Path & "\" & "Input.jpg"
    'The output image that will be created. Here it will be a png image. You can give the full path as well, e.g.:
    'OutputImagePath = "C:\Users\Christos\Desktop\Output.png"
    OutputImagePath = ThisWorkbook.Path & "\" & "Output.png"
    'The required width of the output image.
    OutputWidthInPixels = 750
    'Check if the input image path is valid.
    If FileExists(InputImagePath) = False Then
        MsgBox "The path of the input image is invalid!", vbCritical, "Input Image Path Error"
        Exit Sub
    End If
    'Create a new instance of Photoshop application and make it visible.
    On Error Resume Next
    Set PsApp = New Photoshop.Application
    If PsApp Is Nothing Then
        MsgBox "Sorry, it was impossible to start Photoshop!", vbCritical, "Photoshop Application Error"
        Exit Sub
    End If
    PsApp.Visible = True
    'Try to open the input image.
    Set PsDoc = PsApp.Open(InputImagePath)
    If PsDoc Is Nothing Then
        MsgBox "Sorry, it was impossible to open the input image!", vbCritical, "Image Opening Error"
        Exit Sub
    End If
    On Error GoTo 0
    'Print the image dimensions before resizing (in the Immediate window of the VBA editor).
    Debug.Print "Before: Width (px): " & PsDoc.Width * PsDoc.Resolution / 2.54 & " Height (px): " & PsDoc.Height * PsDoc.Resolution / 2.54
    'Resize the image.
    PsDoc.ResizeImage 2.54 * OutputWidthInPixels / PsDoc.Resolution
    'Print the image dimensions after resizing.
    Debug.Print "After: Width (px): " & PsDoc.Width * PsDoc.Resolution / 2.54 & " Height (px): " & PsDoc.Height * PsDoc.Resolution / 2.54
    'Apply the Sharpen filter.
    'Apply the Gaussian Blur filter within the specified radius (in pixels). Valid range: 0.1 - 250.0.
    PsDoc.ArtLayers(1).ApplyGaussianBlur 5
    'Create a new PNGSaveOptions object that will store the necessary saving parameters.
    Set PsSaveOptions = New Photoshop.PNGSaveOptions
    'Set the compression of the image. Valid range: 0 - 9, default: 0.
    PsSaveOptions.Compression = 5
    'Set the Interlaced option, which indicates whether the rows should interlace. Default value: false.
    PsSaveOptions.Interlaced = True
    'Save the modified image using the defined saving options.
    PsDoc.SaveAs OutputImagePath, PsSaveOptions, True, PsExtensionType.psLowercase 'PsExtensionType.psLowercase = 2 in late binding
    'Close the image.
    PsDoc.Close psDoNotSaveChanges 'psDoNotSaveChanges = 2 in late binding
    'Quit Photoshop.
    'Release the objects.
    Set PsSaveOptions = Nothing
    Set PsDoc = Nothing
    Set PsApp = Nothing
    'Inform the user about the process.
    If FileExists(OutputImagePath) = True Then
        MsgBox "The output image was successfully created!", vbInformation, "Finished"
        MsgBox "The output image was not created!", vbCritical, "Output Image Error"
    End If

End Sub

Function FileExists(FilePath As String) As Boolean
    'Checks if a file exists (using the Dir function).

    On Error Resume Next
    If Len(FilePath) > 0 Then
        If Not Dir(FilePath, vbDirectory) = vbNullString Then FileExists = True
    End If
    On Error GoTo 0
End Function


The picture below is the input image in this example:

Input image: 4760 x 3120 pixels, jpg format. Click the image to view it in real size.

And here is the output image, after running the code:

Output image: 750 x 563 pixels, png format, with Sharpen and Gaussian Blur filter.

The result is neither fancy nor impressive (this wasn't my intent). However, it just shows some possible applications of VBA programming in respect to Photoshop.



The zip file contains a workbook with the VBA code and a sample image to play with it (the one that used in this example). The workbook can be opened with Excel 2007 or newer. Please enable macros before using it.

Sunday, 20 September 2015

Road Transport Emissions Evolution In Urban Areas; The Case Of Thessaloniki, Greece


First of all, it seems that in previous months I couldn’t fulfill my own promises regarding this blog. I have an extremely heavy workload the last few months and it seems that until I present my thesis, I will not be able to update this blog regularly. So, dear blog readers please be patient!

Anyway, in the paragraphs that follow you will find a text version of my latest poster, which was presented on the 14th International Conference on Environmental Science and Technology. The conference took place in Rhodes (Greece) during the first week of September. The poster is actually a summary of the corresponding paper. If you are interested about road transport emissions you might find it interesting! 



  • The current study examines the evolution of road transport emissions during the years 2012 – 2014 in Thessaloniki, the second largest city of Greece. The study focuses on the calculation at micro/link level of CO, NOx and VOC emissions.
  • The developed methodology was based on the combination of PTV VISUM and COPERT Micro models, along with measured traffic data from inductive loop detectors, cameras and radars installed at 37 locations across the city.
  • Results indicated the local pollution hot-spots, as well as the local and seasonal variation of pollutants. In general, there is a substantial drop on average annual emissions between 2012 and 2013, whereas a constant trend is observed between 2013 and 2014.


  • According to the latest census (2011), Thessaloniki is the second largest city in Greece, both in population and area.
  • Based on the latest transportation study conducted between 2010 and 2013, the average daily private vehicle traffic on the main roads of the city reaches 1.300.000 vehicle-trips, while the morning peak corresponds to 14% of the daily total. 
  • The main streets in the central area of the city serve daily volumes of through traffic that reach 45% of traffic volumes recorded at peak periods (morning travel to work and afternoon travel back from work to home).

Materials & Methods

Flow Chart Of The Methodology

Flow chart of the methodology.

Traffic Modelling

  • The transportation model for Thessaloniki has been developed with the PTV VISUM software. The network consists of 137.938 directed links, 47.838 nodes and 339 traffic analysis zones connected to physical nodes of the road network via 3.508 connectors. 
  • The demand side is comprised by 24 hourly Origin-Destination (OD) matrices and the travel demand for a typical weekday is within the range of 1.298.745 vehicle trips. 
  • The obtained OD matrices are corrected using the hourly volume data measured by inductive loop detectors, cameras and radars installed at 37 locations across the city. The OD matrix correction is performed with a fuzzy-set based matrix correction procedure.

Emissions Modelling

  • The emissions calculations were performed with COPERT Micro, a specially developed version of COPERT 4 for urban areas. It is a bottom-up model, thus, it can calculate the emissions from a single traffic link up to an entire city, focusing primarily on hot exhaust emissions. 
  • The equations below summarize the main methodology that is applied by COPERT Micro:
            - EFi(V)= fEF (V) : emission factor as a function of average vehicle speed
            - Emissionsi, j = Lj × Nj × Pvehcile category, j × EFi(V) : emissions due to a single traffic link
            - Emissionsi, area = ∑Emissionsi, j : emissions from the entire area

Results & Conclusions


Daily CO Hot Emissions In Thessaloniki For Years 2012 & 2014

Daily CO hot emissions (kg) on a 100 x 100 (500 x 500 m2) grid for a typical weekday of summer 2012 (left) and 2014 (right) in metropolitan area of Thessaloniki.

Seasonal Hourly NOx Hot Emissions In Thessaloniki For 2012

Seasonal Hourly NOx Hot Emissions In Thessaloniki For 2014

Hourly NOx hot emissions (kg) for a typical weekday of each season of 2012 (up) and 2014 (down) in metropolitan area of Thessaloniki.

Seasonal Daily VOC Hot Emissions In Thessaloniki For Years 2012 To 2014

Average Daily VOC Hot Emissions In Thessaloniki Per Year (2012 - 2014)

Daily VOC hot emissions (t) for a typical weekday of each season (up) and each year (down) of years 2012 – 2014 in metropolitan area of Thessaloniki.


  • The results at link level revealed the local pollution hot-spots and the high-emission links, which usually lie along the main urban highways of the city. 
  • The seasonal variation of pollutants is mainly caused by the different transport activity pattern in each season. 
  • There is a significant drop on average annual emissions between 2012 and 2013, while a constant trend is observed between 2013 and 2014. This could be allocated to the reduced transport activity throughout the city, mainly affected by the economic crisis.

Thursday, 30 April 2015

CodeEval 3 – Reverse And Add

About CodeEval post series

CodeEval is a series of posts which are different than the typical engineering/Excel/VBA posts that are being published in this blog. The purpose of this series is to demonstrate possible solutions to various CodeEval programming challenges. Each solution has already been submitted and accepted as valid on CodeEval platform, so if you try to submit the presented solution as it is, you will probably get a “not unique solution” result. The solutions will be presented in C# language, but the logic/algorithm behind them is similar despite the language you might use.

Reverse And Add – challenge description

Choose a number, reverse its digits and add it to the original. If the sum is not a palindrome (which means, it is not the same number from left to right and right to left), repeat this procedure.


195 (initial number) + 591 (reverse of initial number) = 786

786 + 687 = 1473

1473 + 3741 = 5214

5214 + 4125 = 9339 (palindrome)

In this particular case, the palindrome 9339 appeared after the 4th addition. This method leads to palindromes in a few step for almost all of the integers. But there are interesting exceptions. 196 is the first number for which no palindrome has been found. It is not proven though, that there is no such a palindrome.

Input sample

Your program should accept as its first argument a path to a file name. Each line in this file is one test case. Each test case will contain an integer n < 10,000. Assume each test case will always have an answer and that it is computable with less than 100 iterations (additions).

Output sample

For each line of input, generate a line of output which is the number of iterations (additions) to compute the palindrome and the resulting palindrome. (they should be on one line and separated by a single space character). Example:

4 9339

The particular challenge has a relatively low success rate (66.6% - 30/04/2015), and its level of difficulty is medium. More info you can find here.


Apart from the main code, the solution incorporates two helping methods for reversing the input number ("ReverseNumber") and for checking if a number is a palindrome ("IsPalindrome").

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;

The code below solves the CodeEval challenge -  Reverse And Add.

Written by:    Christos Samaras
Date:          02/07/2014

namespace ReverseAndAdd
    class Program
        static void Main(string[] args)
                using (StreamReader reader = File.OpenText(args[0]))            
                while (!reader.EndOfStream)
                    string line = reader.ReadLine();
                    if (line != null)
                        int cnt = 0;

                        while (IsPalindrome(line) == false)
                            int reversedNumber = int.Parse(ReverseNumber(line));
                            line = (int.Parse(line) + reversedNumber).ToString();
                        Console.WriteLine(cnt + " " + line);
        public static string ReverseNumber(string number)
            char[] charArray = number.ToCharArray();
            return new string(charArray);

        public static bool IsPalindrome(string num)
            if (num == ReverseNumber(num))
                return true;
                return false;


Here is the proof that the solution works and the points given by the CodeEval platform.

CodeEval 3 – Reverse And Add - Score

Sunday, 19 April 2015

Automatically Adjust Secondary Y Axis Scale Through VBA


Some days ago, while I was checking the discussions on an Excel-related group on LinkedIn, I bumped into an interesting question about charts. The question was how to automatically set the secondary Y axis scale via code, so that both primary and secondary axes share the same number of gridlines”.

I thought this question was interesting for two reasons: first, it’s quite common to include a secondary Y axis on a chart, just to display another series that have different scale than the first one. In COPERT Micro tool for example, I have many charts that show emissions (primary Y) and number of vehicles (secondary Y) as a function of hour of the day (X axis). Second, the question reminded me a similar need that I had some years ago. So, I found the code that I had written then (2012) and I made some minor changes in order to make it more generic, and, here it is!

The manual way

By the way, it’s not hard to adjust the scale of secondary Y axis manually. Just follow the instructions that follow (for Excel 2013).

Step 1: Set a data series to be plotted on secondary axis:
  1. Select a series on the chart by right clicking on it.
  2. On the pop up menu select Format Data Series.
  3. On the Format Data Series menu that will appear select the Series Options tab.
  4. Select the plot on secondary axis radio button.

Format Data Series Menu Plot Series On Secondary Axis

Step 2: Adjust the scale of the secondary Y axis:
  1. Select the secondary Y axis by right clicking on it.
  2. On the pop up menu select Format Axis.
  3. On the Format Axis menu that will appear select the Axis Options tab.
  4. Finally, set the desired values on Minimum/Maximum Bounds and on Major Unit text boxes.

Format Secondary Y Axis Set Secondary Y Axis Scale

In the last step you might need to make some (repeating) tests on the values that you will enter in the 3 text boxes (especially in the Major Unit text box) in order to achieve that both primary and secondary axes will share the same number of gridlines.

VBA code

And here is the “easy way”; no need to test the values on the text boxes, no worries about how the axes will look uniformly. It’s a short macro that can be customized according to your needs.

Option Explicit

Sub AdjustSecondaryYAxisScale()
    'Automatically adjusts the scale of the secondary Y axis, so that both
    'primary and secondary Y axes share the same number of (major) gridlines.
    'Written By:    Christos Samaras
    'Date:          14/09/2012
    'Last Update:   18/04/2015
    'Declaring the necessary variables.
    Dim ch      As Chart
    Dim Ymin    As Double
    Dim Ymax    As Double
    Dim Yscale  As Double
    Dim Ylines  As Integer
    Dim sYmin   As Double
    Dim sYmax   As Double
    Dim sYscale As Double
    'Set the ch variable to a specific chart on sheet 1 (in this example).
    'If you need to set the ch variable to active chart you can use the next line:
    'Set ch = ActiveChart
    Set ch = Sheet1.ChartObjects(1).Chart
    'A quick test if the ch variable is not empty.
    On Error Resume Next
    If ch Is Nothing Then
        MsgBox "The chart wasn't set!", vbCritical, "Empty Chart"
        Exit Sub
    End If
    On Error GoTo 0
    'Set the minimum/maximum bound and the major unit to Auto for both primary and secondary axes.
    'For the primary Y axis this is NOT always necessary, so the corresponding 3 lines can be deleted/commented.
    With ch
        .Axes(xlValue).MinimumScaleIsAuto = True
        .Axes(xlValue).MaximumScaleIsAuto = True
        .Axes(xlValue).MajorUnitIsAuto = True
        .Axes(xlValue, xlSecondary).MinimumScaleIsAuto = True
        .Axes(xlValue, xlSecondary).MaximumScaleIsAuto = True
        .Axes(xlValue, xlSecondary).MajorUnitIsAuto = True
    End With
    'Get the mininmum bound of the primary Y axis.
    Ymin = ch.Axes(xlValue).MinimumScale
    'Get the maximum bound of the primary Y axis.
    Ymax = ch.Axes(xlValue).MaximumScale
    'Get the major unit of the primary Y axis.
    Yscale = ch.Axes(xlValue).MajorUnit
    'Calculate the number of major gridlines.
    Ylines = Round((Ymax - Ymin) / Yscale)
    'Get the mininmum bound of the secondary Y axis.
    sYmin = ch.Axes(xlValue, xlSecondary).MinimumScale
    'Get the maximum bound of the secondary Y axis.
    sYmax = ch.Axes(xlValue, xlSecondary).MaximumScale
    'Note that you can easily set the minimum/maximum bound of the secondary Y axis to any value you like.
    'In other words, you can make your secondary Y axis look exactly as you want.
    'For example if you uncomment the next 2 lines, the first gridline of the secondary Y axis will start at 0 and
    'the last one will end at the value of 30. The number of gridlines will be automatically calculated/adjusted.
    'sYmin = 0
    'sYmax = 30
    'Calculate the new major unit of the secondary Y axis.
    sYscale = Round((sYmax - sYmin) / Ylines)
    'Calculate the new maximum bound of the secondary Y axis.
    sYmax = sYmin + Ylines * sYscale
    'Set the minimum/maximum bound and the major unit of the secondary Y axis to their new values.
    With ch.Axes(xlValue, xlSecondary)
        .MinimumScale = sYmin
        .MaximumScale = sYmax
        .MajorUnit = sYscale
    End With
    'Release the chart object.
    Set ch = Nothing
End Sub

In the workbook that you will find in the downloads section below you can try the above code by experimenting on a chart that I created using random numbers. Just see how nice the scale of the secondary Y axis "follows" the scale of the primary Y axis.



The file can be opened with Excel 2007 or newer. Please enable macros before using it.